RE: Index Myth or Fact: Most discriminating elements should be

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Thu, 18 Oct 2001 05:15:22 -0700
Message-ID: <F001.003AE7AE.20011018052022@fatcity.com>

  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

>>> ktoepke_at_trilegiant.com 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

>>> wisernet100_at_YAHOO.COM 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 <Dave.Leach_at_claybrook.co.uk> 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: wisernet100_at_yahoo.com 

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: ListGuru_at_fatcity.com (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: ktoepke_at_trilegiant.com 

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: ListGuru_at_fatcity.com (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: RROGERS_at_galottery.org

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: ListGuru_at_fatcity.com (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).

Received on Thu Oct 18 2001 - 07:15:22 CDT