0

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 '18 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 '18 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 '18 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 '18 at 7:02
  • How many rows did datagrip retrieve? Oracle version? Are the table statistics up to date? – Gerard H. Pille Oct 11 '18 at 7:44
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.

0

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 '18 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 '18 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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