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 !!

"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, Oct 11, 2018 at 6:50
"create table query takes too too long to complete" Do you get an error or did you cancel it? — Gerard H. Pille, Oct 11, 2018 at 6:53
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, Oct 11, 2018 at 7:00
In create table query, last time 4 hours passed and we have no output it just executing with no error — jimi, Oct 11, 2018 at 7:02
How many rows did datagrip retrieve? Oracle version? Are the table statistics up to date? — Gerard H. Pille, Oct 11, 2018 at 7:44

Gerard H. Pille · Accepted Answer · 2018-10-11 10:01:36Z

0

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.

answered Oct 11, 2018 at 10:01

Gerard H. Pille

3,2351 gold badge8 silver badges13 bronze badges

Add a comment |

Balazs Papp · Accepted Answer · 2018-10-11 10:34:39Z

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"))

thank you very much, yes it works! using materialize hit. what do "this_b_tree_bitmap_plans" exactly? — jimi, Oct 13, 2018 at 5:00
@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. — Balazs Papp, Oct 13, 2018 at 8:19

Stack Exchange Network

why optimizer in Oracle database does not use index?

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
oracle
index
join
optimization
select
or ask your own question.

Hot Network Questions

why optimizer in Oracle database does not use index?

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged oracleindexjoinoptimizationselect or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
oracle
index
join
optimization
select
or ask your own question.