Hello,
I have a very simple query on a table with two columns indexed fulltext.
If there are two AND conditions in the where clause, the optimizer uses a correct plan, taking advantage of the two indices. If instead I use the two conditions in OR, the plan used always performs a complete table scan. There is no way to make him use the index even specifying it via hint.
In Oracle Enterprise edition, the correct plan is used in both cases.
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;
Thanks,
Davide Mietto.
It is not the *wrong* execution plan, it is just the plan that is available to the optimizer.
From the Oracle License Guide on Standard Edition:
The following methods are not available in SE:
Bitmapped index, bitmapped join index, and bitmap plan conversionsSo when we look at Enterprise Edition you see this
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)
which is not a plan that is available to SE. But even on SE, the optimizer can do a good job and turning this into a plan that can take advantage of the index on the latest version
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>