analyze produces inferior query

From: Ryan Gaffuri <rgaffuri_at_cox.net>
Date: 29 Dec 2003 13:26:26 -0800
Message-ID: <1efdad5b.0312291326.7e3a1f88@posting.google.com>

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. Received on Mon Dec 29 2003 - 15:26:26 CST