Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> changed query plan depending on inner subquery

changed query plan depending on inner subquery

From: <asya999_at_gmail.com>
Date: 9 Dec 2004 22:58:09 -0800
Message-ID: <1102661889.682829.194350@c13g2000cwb.googlegroups.com>


I have the following structure query in a datawarehouse:

select /*+ star_transformation */ from fact, dimensions where <the usual joins>
and (x, y, z) in
(select /*+ star_tranformation */ from different_fact, dims, etc)

When I run this query, I get full table scan of fact (the inner query plan looks good, just like when it's run by itself).

If I run the inner query and create a table out of its results create table t select /*+ star_tranformation */ from different_fact, dims, etc

and then rewrite the original query as:
select /*+ star_transformation */ from fact, dimensions where <the usual joins>
and (x, y, z) in
(select * from t)

then the query plan shows table access by index rowid on the fact table and I get super-good query time (on the order of seconds, instead of minutes
with the original query).

Anyone know what's going on? I can only assume that the optimizer somehow
gets influenced by the inner query into trying for a full table scan on the outer query, but I'm not sure how I can avoid that, short of creating
a temp table t and running two queries.

This is 9.2.0.4. I've got the full set of bitmap indexes, etc. etc. and
all the simpler queries are working fine, except for this combination. Any thoughts would be appreciated. Received on Fri Dec 10 2004 - 00:58:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US