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.