I was taught and still practice that a composite index or parts there of will only
be used if the index order matches the where clause order.
Ex: index on col1,col2,col3
where col1,col2,col3 index used
where col1,col2 index used
where col2,col3 index not used
ROR m���m
>>> [EMAIL PROTECTED] 10/17/01 02:18PM >>>
Directly from the horse's mouth...er the "Oracle8i Designing and Tuning for
Performance" manual.
If all keys are used in WHERE clauses equally often, then ordering these
keys from most selective to least selective in the CREATE INDEX statement
best improves query performance.
Kevin
-----Original Message-----
Sent: Wednesday, October 17, 2001 1:48 PM
To: Multiple recipients of list ORACLE-L
but for cost-based optimization, it not SUPPOSED to matter the order of
tables in the from clause or order of where clauses.
joe
>>> [EMAIL PROTECTED] 10/17/01 01:14PM >>>
I would think it would make more sense to put the columns you are most
likely to include in a where clause first. Selectivity doesn't enter
into it.
--- Dave Leach <[EMAIL PROTECTED]> wrote:
> Richard,
>
> If you have a chance read page 111 of Practical Oracle 8i by Jonathan
> Lewis,
> there's a couple of paragraphs regarding this. The crux of this is
> that
> here is no great performance gain in having the most selective column
> first
> in a compound index.
>
> Please correct me if anyone feels I have misread or misinterpreted
> this.
>
> Dave Leach
>
> -----Original Message-----
> Sent: 17 October 2001 14:05
> To: Multiple recipients of list ORACLE-L
>
>
>
> Hello,
>
> Does anyone have a definitive answer as to whether or not the most
> selective
> column(s) in an compound index should be placed first to improve
> performance? I've read in more than one book that this is so - it
> seems
> reasonable.
>
> However after ploughing through some of the expert one-on-one by
> Thomas Kyte
> he disputes the theory. Has anyone got any views or has performed
> similar
> tests on large tables/ large compound indexes?
>
> Thanks
>
> Richard Corfield
>
>
>
>
**********************************************************************
> The above information is confidential to the addressee and may be
> privileged. Unauthorised access and use is prohibited.
>
> Internet communications are not secure and therefore this Company
> does
> not accept legal responsibility for the contents of this message.
>
> If you are not the intended recipient, any disclosure, copying,
> distribution or any action taken or omitted to be taken in reliance
> on
> it, is prohibited and may be unlawful.
>
> Claybrook Computing Limited is a subsidiary of
> Claybrook Computing (Holdings) Limited
> Registered Office: Abbey House. 282 Farnborough Road, Farnborough,
> Hampshire GU14 7NJ
> Registered in England and Wales No 1287205
>
> A Hogg Robinson plc company
>
**********************************************************************
>
__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com <http://personals.yahoo.com>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Toepke, Kevin M
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
