WITH using INDEX hint in Oracle

indexmaterialized-vieworacle

I have a fairly complicated query. I have written two different query to accomplish my goal.
First one is using WITH, other one is using global temporary tables.

First One:

WITH A
(
 KNO 
 ..
 )
 , B
 (
 KNO
 ...
 )
 , C
 (
 KNO
 ...
 )
 SELECT * from 
 A INNER JOIN B 
 on A.KNO = B.KNO
 INNER JOIN C
 on B.KNO = C.KNO

Second One:

Truncate Table tempA;
Truncate Table tempB;
Truncate Table tempC;

INSERT INTO tempA SELECT -- Same select which constructs WITH A
INSERT INTO tempB SELECT -- Same select which constructs WITH B
INSERT INTO tempC SELECT -- Same select which constructs WITH C

 SELECT * from 
 tempA A INNER JOIN tempB B 
 on A.KNO = B.KNO
 INNER JOIN tempC C
 on B.KNO = C.KNO

They are on par. What I mean is I observe same query time with both of them. But When I add KNO index to tempA,tempB,tempC temporary tables. Second one skyrockets.
What I mean is : It gets faster, a lot. I query for 1 year of values. using WITH: about 58 minutes. using Temp Table with INDEX: about 30 minutes.

Query times for 4 months of values are given below.

Using WITH

DENEME@DENEME   Finished    15:37:02    15:40:38    03:36 mins  Select  500 WITH A

Total:216000 msecs

Using Temporary Tables + Index

DENEME@DENEME   Finished    15:41:54    15:41:55    1 sec   Select  500 WITH D
DENEME@DENEME   Finished    15:41:38    15:41:38    665 msecs   Insert  34660   INSERT INTO C
DENEME@DENEME   Finished    15:41:21    15:41:35    14 secs Insert  34660   INSERT INTO B 
DENEME@DENEME   Finished    15:41:17    15:41:17    109 msecs   Insert  1804    INSERT INTO  A
DENEME@DENEME   Finished    15:41:12    15:41:12    33 msecs    Unknown 0   TRUNCATE TABLE C
DENEME@DENEME   Finished    15:41:12    15:41:12    39 msecs    Unknown 0   TRUNCATE TABLE B
DENEME@DENEME   Finished    15:41:11    15:41:11    203 msecs   Unknown 0   TRUNCATE TABLE A

Total: 16049 msecs

My question is :

Can I give hint to oracle to add index when it is constructing WITH temporary tables?

Best Answer

No, I don't think defining an index for a common table expression is possible.

You can experiment with the materialize hint, but I don't think you'll get anything faster than the solution with (indexed) temporary tables.