SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-24-2008, 09:36 AM
| ||||
| ||||
 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.      |
 |
« 10G on AIX 5.1 - Is it ready for 'prime time'?? - How should we get ready?
|
9i: Native vs Interpreted execution »
| Thread Tools | |
| Display Modes | |
Linear Mode
|
|
All times are GMT. The time now is 01:47 AM.
