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.
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
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.
DENEME@DENEME Finished 15:37:02 15:40:38 03:36 mins Select 500 WITH A
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?