Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

 , B
 , C
 SELECT * from 
 on A.KNO = B.KNO
 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
 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?

share|improve this question
"Second one skyrockets" you mean it gets slower or it gets faster? – Jack Douglas Nov 24 '11 at 12:53
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. I also added this information to question. – Atilla Ozgur Nov 24 '11 at 13:21
up vote 3 down vote accepted

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.

share|improve this answer
GTTs still generate some undo (though minimal in the case of inserts), which might not be the case with a materialized CTE (though at least in some cases/versions internally Oracle creates GTTs for materialized CTEs!) – Jack Douglas Nov 24 '11 at 12:58

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

  • A CTE or 'with' clause does not necessarily mean Oracle 'constructs temporary tables' - this is SQL, you are generally telling the database what you want, rather than how to go about getting it
  • there is a hint to ask the CBO to 'construct a temporary table - but hints are a last resort - if the CBO is choosing the wrong plan, the first thing to do is find out why (eg: bad stats)
  • you are joining the whole of tempA, tempB and tempC so the positive impact of an index will be minimal at best (in most cases the CBO will choose a hash join so you will not even save a sort) - ie don't try and make it faster magically by adding indexes
  • are you comparing apples with apples? the 'query time' for the case with temporary tables needs to include the three insert statements as well as the select
share|improve this answer
From execution plan: oracle seems to construct view or temporary tables. 1 Every row in the table SYS.SYS_TEMP_0FD9FC87B_B1CA4F26 is read. 2 A view definition was processed, either from a stored view XXXX. or as defined by steps 1. – Atilla Ozgur Nov 24 '11 at 12:53
yes, it might - but with a different execution plan it might not. Materializing is sometimes chosen if the CTE is used multiple times - does your real query do that? – Jack Douglas Nov 24 '11 at 13:01
Yes, Information in A and B is queried in C for aggregation purposes. Also hint /*+ materialize */ give a small (%1-%5) boost to query time. – Atilla Ozgur Nov 24 '11 at 13:20
Ok, you need to post either your real queries or a test simplified test case that demonstrates the same issue for us to be able to help further – Jack Douglas Nov 24 '11 at 13:49
have you checked the explain plan has sensible estimated cardinalities for each step? – Jack Douglas Nov 24 '11 at 14:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.