Oracle views not using indexes I am experiencing the unexplainable - at least to me.

I have some tables in my production database which have some indexes on. We then have a number of views which merge the data across tables to make it easier to select data that we need.

Last weekend, we recreated all the tables from scratch. We made sure to recreate all the indexes. However, now I am finding that my views are taking a huge amount of time to complete (from 2 secs to 43 mins). We have gathered stats on the tables using AUTO sample sizes but the explain plan still shows the joins being completed by FULL TABLE SCAN rather than index join.

Does anybody have any ideas? Do you have to do something to force the views to use the indexes.

Thanks Iain
The rebuild of the table and indexes probably caused the statistics about the distribution of the values of certain columns to reset thus causing the optimizer to be fed inaccurate information.

You will probably need to change the statistics gathering for some of the columns.  See Oracle Documentation on Histograms