Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

closed as unclear what you're asking by Mark Storey-Smith, RolandoMySQLDBA, Kin, Max Vernon, Mat Mar 2 '14 at 11:14

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

Do you have an example? Can't really help you otherwise. Need table definitions, view defs and an example explain plan. Are you certain that the DDL is identical? Not missed PKs or anything? Could also be histograms. Anyway, not much we can go on at this point – Phil Feb 18 '14 at 22:12
Well we're talking about a view that combines data from about 20 different tables. We actually then have multiple views and the query we are executing is against more than one. Might be hard to post the DDLs etc. Any high level tips would be greatly appreciated. You mentioned histograms? – user2294382 Feb 18 '14 at 22:16

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

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.