Re: analyze produces inferior query

Ryan Gaffuri wrote:

> I have a materialized view that joins to another table.
>
> Without my analyze, i do an index 'range scan' on my materialized view
> when I have a 'FIRST_ROWS' hint and this returns in 1/2 of a second.
>
> If I analye my materialized view and the index, I do an index full
> scan and the queries LIOs go from 1000 to 50,000 and the query takes 6
> seconds.
>
> I dont think there is a hint for index range scan. I also dont like
> leaving materialized views un-analyzed or possibly using stored
> outlines(data may change).
>
> any ideas why it would choose an inferior TYPE of index scan. 10053
> trace isnt helping here. Wont tell why its choosing that particular
> type of index scan. Im at a loss.

Don't even know what you are doing? Lets get some basic out of the way?

What version of Oracle including patch level?

What method is being used to do the analysis? Likely should be DBMS_STATS not ANALYZE TABLE.

What percentage of the rows are you trying to return?

Any histograms or stored outlines?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)