OraFAQ Forum: Performance Tuning » View not using index on underlying tables

Home » RDBMS Server » Performance Tuning » View not using index on underlying tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

View not using index on underlying tables [message #273797]

Thu, 11 October 2007 13:05

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.

Report message to a moderator

Re: View not using index on underlying tables [message #273798 is a reply to message #273797]

Thu, 11 October 2007 13:08

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: View not using index on underlying tables [message #273799 is a reply to message #273797]

Thu, 11 October 2007 13:34

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.

Report message to a moderator

Re: View not using index on underlying tables [message #273801 is a reply to message #273797]

Thu, 11 October 2007 13:41

BlackSwan
Messages: 26766
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)!

Report message to a moderator

Previous Topic:	Large table help
Next Topic:	sample size in user_table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 25 20:41:17 CDT 2024