why optimizer in Oracle database does not use index?

Question

when I execute following queries see completely different result where is problem?

here I execute a simple select query which takes a few seconds to complete:

SELECT * FROM
 PRODUCTION.VERY_SMALL_TABLE L
 INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX R
 on R.ID_1 = L.ID or R.ID_2 = L.ID

and its execution plan is:

----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72M| 9554M| 11M (1)| 00:07:16 | | |
| 1 | VIEW | VW_ORE_65071C6B | 72M| 9554M| 11M (1)| 00:07:16 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | NESTED LOOPS | | 50M| 6821M| 6056K (1)| 00:03:57 | | |
| 4 | NESTED LOOPS | | 50M| 6821M| 6056K (1)| 00:03:57 | | |
| 5 | TABLE ACCESS FULL | VERY_SMALL_TABLE | 7 | 98 | 3 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ALL | | 7246K| | 621 (0)| 00:00:01 | 1 |1048575|
|* 7 | INDEX RANGE SCAN | ID_2_INDX | 7246K| | 621 (0)| 00:00:01 | 1 |1048575|
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX | 7246K| 877M| 865K (1)| 00:00:34 | 1 | 1 |
| 9 | NESTED LOOPS | | 21M| 2870M| 5097K (1)| 00:03:20 | | |
| 10 | NESTED LOOPS | | 42M| 2870M| 5097K (1)| 00:03:20 | | |
| 11 | TABLE ACCESS FULL | VERY_SMALL_TABLE | 7 | 98 | 3 (0)| 00:00:01 | | |
| 12 | PARTITION RANGE ALL | | 6098K| | 621 (0)| 00:00:01 | 1 |1048575|
|* 13 | INDEX RANGE SCAN | ID_1_INDX | 6098K| | 621 (0)| 00:00:01 | 1 |1048575|
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX | 3049K| 369M| 728K (1)| 00:00:29 | 1 | 1 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

but when I want to use above query to create a table like this:

 CREATE TABLE DUMMY_SCHEMA.DUMMY_TABLE AS
 (SELECT * FROM
 PRODUCTION.VERY_SMALL_TABLE L
 INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX R
 on R.ID_1 = L.ID or R.ID_2 = L.ID);

execution plan changes to this:

------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 222G| 28T| 1971M (1)| 21:23:21 | | |
| 1 | LOAD AS SELECT | DUMMY_TABLE | | | | | | |
| 2 | NESTED LOOPS | | 222G| 28T| 1200M (1)| 13:01:53 | | |
| 3 | TABLE ACCESS FULL | VERY_SMALL_TABLE | 7 | 98 | 3 (0)| 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 31G| 3756G| 171M (1)| 01:51:42 | 1 |1048575|
|* 5 | TABLE ACCESS FULL | BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX | 31G| 3756G| 171M (1)| 01:51:42 | 1 |1048575|
-------------------------------------------------------------------------------------------------------------------------------------

and create table query takes too too long to complete !!

• Gerard H. Pille: "takes a few seconds to complete"? The plan estimates it will take 7 minutes to select 72 million rows. Which is correct, you or the optimizer?
• Gerard H. Pille: "create table query takes too too long to complete" Do you get an error or did you cancel it?
• jimi: I execute first query which is select and it takes only 172 ms to complete (in Datagrip of course which returns first 500 rows) I change Datagrip setting to bring back all result and it takes 3 min
• jimi: In create table query, last time 4 hours passed and we have no output it just executing with no error
• Gerard H. Pille: How many rows did datagrip retrieve? Oracle version? Are the table statistics up to date?

Answer 1

Add a first row hint, that way oracle may respond the same way as to the datagrip query.

CREATE TABLE DUMMY_SCHEMA.DUMMY_TABLE AS
 (SELECT /*+ first_rows */ * FROM
 PRODUCTION.VERY_SMALL_TABLE L
 INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX R
 on R.ID_1 = L.ID or R.ID_2 = L.ID);

As an alternative you could analyze your tables, so that the optimizer can use up to date statistics.

Answer 2

It can be clearly seen that while the optimizer performed OR-expansion on your query (VW_ORE_*****), it was unable to perform it in your CREATE TABLE DDL statement. I could not find any official reference to this, but it seems that OR-expansion is not performed on the top level in a CTAS.

You could try the below:

 CREATE TABLE DUMMY_SCHEMA.DUMMY_TABLE AS
 WITH G as (
 SELECT /*+ materialize */ * FROM
 PRODUCTION.VERY_SMALL_TABLE L
 INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX R
 on R.ID_1 = L.ID or R.ID_2 = L.ID
 )
 SELECT * FROM G;

Here is a simplified example if your case:

create table t1 (id1 number not null, id2 number not null);
insert into t1 select rownum, rownum + 1000000 from dual connect by level <= 1000000;
commit;
create index t1_id1 on t1(id1);
create index t1_id2 on t1(id2);
create table t2 (id number not null);
insert into t2 select rownum from dual connect by level <= 5;
insert into t2 select rownum + 1000000 from dual connect by level <= 5;
commit;
exec dbms_stats.gather_table_stats(user, 'T1');
exec dbms_stats.gather_table_stats(user, 'T2');

Then (as my tables are different than yours, I needed to set this to achieve similar results to yours and avoid B-tree index bitmap plans):

alter session set "_b_tree_bitmap_plans"=false;

Now the query (OR-expansion is performed, uses both indexes):

SQL> select t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id);
 ID1 ID2 ID
---------- ---------- ----------
 1 1000001 1
 2 1000002 2
 3 1000003 3
 4 1000004 4
 5 1000005 5
 1 1000001 1000001
 2 1000002 1000002
 3 1000003 1000003
 4 1000004 1000004
 5 1000005 1000005
10 rows selected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5ax9k9jpjczk9, child number 0
-------------------------------------
select t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or
t1.id2 = t2.id)
Plan hash value: 2262899810
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 66 (100)| |
| 1 | VIEW | VW_ORE_5133193F | 18 | 702 | 66 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 10 | 160 | 33 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 10 | 160 | 33 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T1_ID1 | 1 | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 3 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 8 | 128 | 33 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 10 | 128 | 33 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | T1_ID2 | 1 | | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 6 - access("T1"."ID1"="T2"."ID")
 11 - access("T1"."ID2"="T2"."ID")
 12 - filter(LNNVL("T1"."ID1"="T2"."ID"))
Note
-----
 - this is an adaptive plan

CTAS:

SQL> create table t5 as with g as (select t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g;
Table created.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cafkj1bahx8hu, child number 0
-------------------------------------
create table t5 as with g as (select t1.id1, t1.id2, t2.id from t1 join
t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g
Plan hash value: 2652785614
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 6268 (100)| |
| 1 | LOAD AS SELECT | T5 | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 20 | 320 | 6267 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 20 | 320 | 6267 (3)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 2 | 22 | 626 (3)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 5 - filter(("T1"."ID1"="T2"."ID" OR "T1"."ID2"="T2"."ID"))

No OR-expansion, no indexes.

CTAS with CTE and materialize hint:

SQL> create table t4 as with g as (select /*+ materialize */ t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g;
Table created.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID c80w81ckc4727, child number 1
-------------------------------------
create table t4 as with g as (select /*+ materialize */ t1.id1, t1.id2,
t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select *
from g
Plan hash value: 653110608
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 73 (100)| |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6722_A1FFB3 | | | | |
| 3 | VIEW | VW_ORE_5133193F | 18 | 558 | 68 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | NESTED LOOPS | | 10 | 160 | 33 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 10 | 160 | 33 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | T1_ID1 | 1 | | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 11 | 3 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 8 | 128 | 33 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 10 | 128 | 33 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | T1_ID2 | 1 | | 2 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 11 | 3 (0)| 00:00:01 |
| 15 | LOAD AS SELECT | T4 | | | | |
| 16 | OPTIMIZER STATISTICS GATHERING | | 18 | 702 | 3 (0)| 00:00:01 |
| 17 | VIEW | | 18 | 702 | 3 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6722_A1FFB3 | 18 | 558 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 8 - access("T1"."ID1"="T2"."ID")
 13 - access("T1"."ID2"="T2"."ID")
 14 - filter(LNNVL("T1"."ID1"="T2"."ID"))

OR-expansion is performed, indexes are used. Note that you need the materialize hint, without it, it behaves the same as the original CTAS:

SQL> create table t5 as with g as (select t1.id1, t1.id2, t2.id from t1 join t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g;
Table created.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cafkj1bahx8hu, child number 0
-------------------------------------
create table t5 as with g as (select t1.id1, t1.id2, t2.id from t1 join
t2 on (t1.id1 = t2.id or t1.id2 = t2.id)) select * from g
Plan hash value: 2652785614
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | | | 6268 (100)| |
| 1 | LOAD AS SELECT | T5 | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 20 | 320 | 6267 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 20 | 320 | 6267 (3)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 10 | 50 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 2 | 22 | 626 (3)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 5 - filter(("T1"."ID1"="T2"."ID" OR "T1"."ID2"="T2"."ID"))

• jimi: thank you very much, yes it works! using materialize hit. what do "this_b_tree_bitmap_plans" exactly?
• Balazs Papp: @jimi It disables B-tree bitmap plans, where b-tree index rowids are converted to bitmaps, and after bitmap operations they are converted back to rowids.