Oracle 域索引在或条件下的错误执行计划

你好，
我有一个非常简单的查询与两个列索引fulltext的表。
如果where子句中有两个AND条件，则优化器使用正确的计划，利用两个索引。如果相反，我在或中使用两个条件，则使用的计划始终执行完整的表扫描。即使通过提示指定索引，也无法使他使用索引。
在Oracle Enterprise edition中，在这两种情况下都使用了正确的计划。

begin
    for rec in 1..100000 loop
        insert into test_ft values(dbms_random.string('x', 30), dbms_random.string('x', 30));
    end loop;
end;

commit;

create index ndx_ft_col1 on test_ft(col1) indextype is ctxsys.context;
create index ndx_ft_col2 on test_ft(col2) indextype is ctxsys.context;

begin
dbms_stats.gather_table_stats(OWNNAME=>'INVOICE_DMO', TABNAME=>'TEST_FT', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);
end;

select * from test_ft where contains(col1, 'pippo') > 0 or contains(col2, 'pluto') > 0;

谢谢，
大卫·米托。

这不是 * 错误的 * 执行计划，它只是优化器可用的计划。

来自标准版的Oracle许可证指南:

The following methods are not available in SE:

Bitmapped index, bitmapped join index, and bitmap plan conversions


所以当我们看企业版时，你会看到这个

SQL> select banner from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> create table test_ft ( col1 varchar2(50), col2 varchar2(50));

Table created.

SQL>
SQL>         insert into test_ft
  2          select dbms_random.string('x', 30), dbms_random.string('x', 30)
  3          from dual
  4          connect by level <= 100000;

100000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index ndx_ft_col1 on test_ft(col1) indextype is ctxsys.context;

Index created.

SQL> create index ndx_ft_col2 on test_ft(col2) indextype is ctxsys.context;

Index created.

SQL>
SQL> exec  dbms_stats.gather_table_stats('','TEST_FT');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from test_ft where contains(col1, 'pippo') > 0 or contains(col2, 'pluto') > 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 4174159475

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    62 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_FT     |     1 |    62 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |             |       |       |            |          |
|   3 |    BITMAP OR                        |             |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |             |       |       |            |          |
|   5 |      SORT ORDER BY                  |             |       |       |            |          |
|*  6 |       DOMAIN INDEX                  | NDX_FT_COL1 |       |       |     1   (0)| 00:00:01 |
|   7 |     BITMAP CONVERSION FROM ROWIDS   |             |       |       |            |          |
|   8 |      SORT ORDER BY                  |             |       |       |            |          |
|*  9 |       DOMAIN INDEX                  | NDX_FT_COL2 |       |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("CTXSYS"."CONTAINS"("COL1",'pippo')>0)
   9 - access("CTXSYS"."CONTAINS"("COL2",'pluto')>0)

这不是SE可以使用的计划。但是即使在SE上，优化器也可以做得很好，并将其转变为可以利用最新版本索引的计划

SQL> select banner from v$version;

BANNER
---------------------------------------------------------------------------
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

SQL> create table test_ft ( col1 varchar2(50), col2 varchar2(50));

Table created.

SQL>
SQL>         insert into test_ft
  2          select dbms_random.string('x', 30), dbms_random.string('x', 30)
  3          from dual
  4          connect by level <= 100000;

100000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index ndx_ft_col1 on test_ft(col1) indextype is ctxsys.context;

Index created.

SQL> create index ndx_ft_col2 on test_ft(col2) indextype is ctxsys.context;

Index created.

SQL>
SQL> exec  dbms_stats.gather_table_stats('','TEST_FT');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from test_ft where contains(col1, 'pippo') > 0 or contains(col2, 'pluto') > 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 1568130183

-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |     2 |   108 |     2   (0)| 00:00:01 |
|   1 |  VIEW                         | VW_ORE_A5827389 |     2 |   108 |     2   (0)| 00:00:01 |
|   2 |   UNION-ALL                   |                 |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_FT         |     1 |    62 |     1   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX              | NDX_FT_COL1     |       |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| TEST_FT         |     1 |    62 |     1   (0)| 00:00:01 |
|*  6 |     DOMAIN INDEX              | NDX_FT_COL2     |       |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("CTXSYS"."CONTAINS"("COL1",'pippo')>0)
   5 - filter(LNNVL("CTXSYS"."CONTAINS"("COL1",'pippo')>0))
   6 - access("CTXSYS"."CONTAINS"("COL2",'pluto')>0)

SQL> set autotrace off
SQL>