Speed up SYS_* table access created during WITH subquery

Question

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

Answer 1

In order to use index scan you have to create it first. If you want to speed up your query, rewrite it without WITH data AS, because WITH takes a lot of Oracle resources. Here's how:

select *
  from t2
 where not exists (select 1
                     from t2
                    where object_id between 1 and 100000 
                      and object_id = t2.object_id);