why optimizer in Oracle database does not use index?

by jimi   Last Updated October 11, 2018 06:06 AM

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



Related Questions


Updated November 27, 2017 17:06 PM

Updated May 08, 2018 18:06 PM

Updated July 28, 2017 10:06 AM

Updated December 19, 2016 08:02 AM

Updated February 21, 2018 15:06 PM