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.