Skip to Main Content

Speed up SYS_* table access created by WITH subquery

User_35RC9Nov 21 2014 — edited Nov 28 2014

Hi,

SELECT * FROM v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production

PL/SQL Release 11.2.0.4.0 - Production

"CORE 11.2.0.4.0 Production"

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Have the below use case:-

--Create Table T1

create table t1 as select * from all_objects where 1=0;

--Create Table T2

create table t2 as select * from all_objects where 1=0;

--Gather stats on T1

exec dbms_stats.set_table_stats( user, 'T1', numrows=>10000000, numblks=> 1000000 );

--Gather stats on T1

exec dbms_stats.set_table_stats( user, 'T2', numrows=>10000000, numblks=> 1000000 );

--Enable autotrace

set autotrace traceonly explain

--Run SQL

with data

    as

    (select  * from t2 where object_id between 1 and 100000 )

    select *

      from t2

     where not exists (select 1 from data where data.object_id = t2.object_id)

    /

Execution Plan

----------------------------------------------------------

Plan hash value: 2779716391

---------------------------------------------------------------------------------------------------------

| Id  | Operation                  | Name                       | Rows  | Bytes | Cost(%CPU)| Time     |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT           |                            |  9200K|   991M|   618K (2)| 00:48:59 |

|   1 |  TEMP TABLE TRANSFORMATION |                            |       |       |           |          |

|   2 |   LOAD AS SELECT           | T2                         |       |       |    |          |

|*  3 |    TABLE ACCESS FULL       | T2                         |  25000|  2441K|   308K (2)| 00:24:28 |

|*  4 |   HASH JOIN RIGHT ANTI     |                            |  9200K|   991M|   309K (2)| 00:24:32 |

|   5 |    VIEW                    |                            |  25000|   317K|   110  (4)| 00:00:01 |

|   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6620_1BF817E |  25000|  2441K|   110  (4)| 00:00:01 |

|   7 |    TABLE ACCESS FULL       | T2                         |    10M|   953M|   309K (2)| 00:24:30 |

---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter("OBJECT_ID">=1 AND "OBJECT_ID"<=100000)

   4 - access("DATA"."OBJECT_ID"="T2"."OBJECT_ID")

--Disable autotrace

set autotrace off

My question is -- can I somehow make the Optimizer use Index scan on the TEMP table created 'SYS_TEMP_0FD9D6620_1BF817E' ??

The above is example for illustration purpose to ask my question. In real scenario, I am faced with a situation, that my SYS_* tables (3 to be exact) created have huge data set (~5+ millions), and the FULL table scan is costing my query response time dearly.

Is there any way by undocumented hint change (for example??), I can speed up the access to the SYS_* tables created from within WITH suquery??

Would really appreciate response.

Tia..

Comments
Post Details
Added on Nov 21 2014
10 comments
2,560 views