Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> changed query plan depending on inner subquery
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
![]() |
![]() |