Interesting analysis. Thanks.
Henry
-Original Message-
Wolfgang Breitling
Sent: Monday, August 04, 2003 11:49 PM
To: Multiple recipients of list ORACLE-L
Yes, the index can be used, but only the portion up to the first missing
column, or a column where the predicate is not an equal
Yes, the index can be used, but only the portion up to the first missing
column, or a column where the predicate is not an equal predicate, is used.
Those are so-called access predicates. The remaining predicates are
resolved after the row is fetched from the datablock - a so-called filter
Wolfgang,
as long as the leading columns are present in the where clause with an
equal predicate, the index can be used. The first omission or non-equal
predicate breaks the chain and only the part of the index up to that
column
can be used.
Could you please elaborate what you meant by that?
Please help resolve this dispute.
We have a query that runs over 5 hours. Sections of the query are
listed below.
The table QOH_DAY_FACT table had only on index and that was on the
TIME_ID column. I propose adding an index with PROD_ID, WHS_ID, LOT_ID,
WHS_LOC, TIME_ID and QUALITY_ID. My
Title: RE: HELP! Index Debate!
How does one know that only one column is being used in an index and others are being ignored ... ?? This is new to me.
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views
Title: Message
No
answer for that.
-Original Message-From: Jamadagni,
Rajendra [mailto:[EMAIL PROTECTED] Sent: Thursday, July
31, 2003 3:44 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: HELP! Index Debate!
How does one know that only one column is being used
Ron,
It has been my understanding that in order for the conpound index to
be used the query had to match the order the index was created. Multiple
indexes would not do a whole lot of good for you just make the optmizer
work harder to figure things out. It would add a confusion factor other
wise
can have facts, having an opinion is an art !
-Original Message-From: Smith, Ron L.
[mailto:[EMAIL PROTECTED]Sent: Thursday, July 31, 2003 4:49
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
HELP! Index Debate!
No
answer for that.
-Original Message
ORACLE-LSubject: RE: HELP! Index Debate!
do you have corresponding indexeson referenced columns on T1-5
tables ??
Raj
Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this
email
Provided we are talking cost based optimizer, then the order of the
predicates in the where clause does not matter - except under very rare
conditions to break a tie. Nor is the order of the predicates in the where
clause related to the order of the columns in the index. The only thing
that
Thank you very much for the detailed info!
Ron
-Original Message-
Sent: Thursday, July 31, 2003 5:09 PM
To: Multiple recipients of list ORACLE-L
Provided we are talking cost based optimizer, then the order of the
predicates in the where clause does not matter - except under very rare
11 matches
Mail list logo
