Skip to Main Content
  • Questions
  • Why does the optimizer in Oracle Database does not use an index for create-table-as-select?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, admin.

Asked: October 11, 2018 - 6:37 am UTC

Last updated: October 12, 2018 - 4:54 pm UTC

Version: 12.2.0.1.0

Viewed 1000+ times

You Asked

hi
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                   
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                        
|   1 | VIEW                              | VW_ORE_65071C6B        
|   2 | UNION-ALL                         |                        
|   3 | NESTED LOOPS                      |                        
|   4 | NESTED LOOPS                      |                        
|   5 | TABLE ACCESS FULL                 | VERY_SMALL_TABLE          
|   6 | PARTITION RANGE ALL               |                        
|*  7 | INDEX RANGE SCAN                  | ID_2_INDX              
|   8 | TABLE ACCESS BY LOCAL INDEX ROWID | BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX 
|   9 | NESTED LOOPS                      |                        
|  10 | NESTED LOOPS                      |                        
|  11 | TABLE ACCESS FULL                 | VERY_SMALL_TABLE       
|  12 | PARTITION RANGE ALL               |                        
|* 13 | INDEX RANGE SCAN                  | ID_1_INDX              
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID | BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX 
--------------------------------------------------------------------------------------
---------------------------------------------------------
| Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------
|    72M|  9554M|    11M  (1)| 00:07:16 |       |       |
|    72M|  9554M|    11M  (1)| 00:07:16 |       |       |
|       |       |            |          |       |       |
|    50M|  6821M|  6056K  (1)| 00:03:57 |       |       |
|    50M|  6821M|  6056K  (1)| 00:03:57 |       |       |
|     7 |    98 |     3   (0)| 00:00:01 |       |       |
|  7246K|       |   621   (0)| 00:00:01 |     1 |1048575|
|  7246K|       |   621   (0)| 00:00:01 |     1 |1048575|
|  7246K|   877M|   865K  (1)| 00:00:34 |     1 |     1 |
|    21M|  2870M|  5097K  (1)| 00:03:20 |       |       |
|    42M|  2870M|  5097K  (1)| 00:03:20 |       |       |
|     7 |    98 |     3   (0)| 00:00:01 |       |       |
|  6098K|       |   621   (0)| 00:00:01 |     1 |1048575|
|  6098K|       |   621   (0)| 00:00:01 |     1 |1048575|
|  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                 
----------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |                      
|   1 |  LOAD AS SELECT        | DUMMY_TABLE                   
|   2 |   NESTED LOOPS         |                      
|   3 |    TABLE ACCESS FULL   | VERY_SMALL_TABLE             
|   4 |    PARTITION RANGE ALL |                      
|*  5 |     TABLE ACCESS FULL  | BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX    
----------------------------------------------------------------------------
------------------------------------------------------
Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop 
------------------------------------------------------
  222G|    28T|  1971M  (1)| 21:23:21 |       |       
      |       |            |          |       |       
  222G|    28T|  1200M  (1)| 13:01:53 |       |       
    7 |    98 |     3   (0)| 00:00:01 |       |       
   31G|  3756G|   171M  (1)| 01:51:42 |     1 |1048575
   31G|  3756G|   171M  (1)| 01:51:42 |     1 |1048575
------------------------------------------------------

                      



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


and Chris said...

The optimizer's estimating the first query will return 72 million rows. I seriously doubt you're fetching all of these in a few seconds.

Much more likely (if you're using a tool like SQL Developer) is you're getting the first 100-200 rows. Which is why the optimizer chose an index. You're getting a small fraction of a large table.

But the create-table-as-select needs to fetch all the data (assuming most/all rows in BIG_TAB join to a row in SMALL_TAB). So it makes no sense to use an index. You're reading everything anyway. In this case the full scan is the right option.

For more on this read:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968
https://asktom.oracle.com/pls/asktom/asktom.search?tag=blocks-read-using-index-vs-full-table-scan

And watch my video series on indexes:

https://www.youtube.com/watch?v=f3U9F_wbo1I&list=PL78V83xV2fYlLA-bjMU2ZvUKQOZNrqLEa

If you want to speed up the create table process, look into parallel processing:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/types-parallelism.html#GUID-7E3B7D97-3AF2-4A5D-8C48-64AD35FD55D7

Finally, when it comes to analyzing SQL performance, make sure you get an execution plan. Not an explain plan. Find the difference between the plans and how to get an execution variety at:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

No, when I use first_rows hint everything works right

A reader, October 11, 2018 - 5:31 pm UTC

thanks for your answer
I think I express my question imperfect!
The result of the select statement is about 7200 rows
but when I used first_rows hint in create_table_as_select everything works correctly and query executed fast
Chris Saxon
October 12, 2018 - 4:54 pm UTC

So the query returns 7 thousand rows, not 70 million? If so there's something up with your table stats...

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.