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