Home » RDBMS Server » Performance Tuning » View not using index on underlying tables
View not using index on underlying tables [message #273797] Thu, 11 October 2007 13:05 Go to next message
Terry Burns
Messages: 13
Registered: January 2004
Junior Member
I have tables based on month (though not partitioned) each
table has it's own indexes (7). I have a view which is based on all the monthly tables. When I query the view the explain plan show the query uses an index (and the same index) on 10 of
the 12 tables. Why would the query against the view not use the index on 2 of the tables, instead it does a full table scan.
Re: View not using index on underlying tables [message #273798 is a reply to message #273797] Thu, 11 October 2007 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Why would the query against the view not use the index on 2 of the tables, instead it does a full table scan.
Because the CBO thought a FTS would e faster.

Do all the tables & indexes have current & accurate statistics?
Re: View not using index on underlying tables [message #273799 is a reply to message #273797] Thu, 11 October 2007 13:34 Go to previous messageGo to next message
Terry Burns
Messages: 13
Registered: January 2004
Junior Member
All tables and indexes have been analyzed using the same method.
Stats have been gathered using different methods (ie tables & indexs at the same time, and seperately) but always the same method on all at the same time.

I relieze the CBO determined the FTS was faster(which is wrong)
but is there anything other than stats that would cause the CBO to use an index on 10 of 12 tables and FTS on the other 2 tables?

If I modifiy the view so it does not include the 2 tables that run a FTS, the querry returns in minutues.
Re: View not using index on underlying tables [message #273801 is a reply to message #273797] Thu, 11 October 2007 13:41 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Here is what we know.
You are reporting that a query is slow because an index is not used.
Here is what we don't know.
Oracle version to 4 decimal places.
The actual SQL
The EXPLAIN PLAN
SQL> DESC of the tables involved.

& you expect somebody to give a solution to this mystery when you've provided NO actual facts.

You're On Your Own (YOYO)!
Previous Topic: Large table help
Next Topic: sample size in user_table
Goto Forum:
  


Current Time: Fri Dec 09 14:01:47 CST 2016

Total time taken to generate the page: 0.16536 seconds