RE: indexes

From: Webber Valerie H <Valerie.H.Webber_at_irs.gov>
Date: Tue, 9 May 2000 15:19:42 -0400
Message-Id: <10492.105272@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFB9EB.86CF07D2
Content-Type: text/plain;

charset="ISO-8859-1"

Is it safe to say that Oracle will use the composite index on A,B,C if A is a foreign key. What I'm getting at is that I don't need a separate index on column A which is a foreign key if I already have the composite index A,B,C

Thanks for all the help!
Val

-----Original Message-----
From: Kirsh, Gary [mailto:gary.kirsh_at_gs.com] Sent: Tuesday, May 09, 2000 12:35 PM
To: Multiple recipients of list ORACLE-L Subject: RE: indexes

Valerie,

I believe it can use the A column of the index, but not the C column, so depending on how selective A is, this may or may not be a good thing.

Gary

Gary Kirsh
Next Extent, Inc.

-----Original Message-----
From: Webber Valerie H [mailto:Valerie.H.Webber_at_irs.gov] Sent: Tuesday, May 09, 2000 11:10 AM
To: Multiple recipients of list ORACLE-L Subject: indexes

I know this issue has been discussed before but I can't find the answer in my list archives.

If you have a composite index on a table consisting of columns A,B,C in that order, will the index be used if I have a query/where clause on columns A and C?

I know that it will if I query on A alone or A,B or A,B,C but I can't find documentation about A and C (no B)

Thanks in advance!
Val

Valerie H. Webber
Management Systems Designers, Inc
Valerie.H.Webber_at_m1.irs.gov
704-569-1002 x107

------_=_NextPart_001_01BFB9EB.86CF07D2
Content-Type: text/html;

charset="ISO-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1">

<TITLE>indexes</TITLE>
<META content='"MSHTML 4.72.3612.1706"' name=GENERATOR>
</HEAD>
<BODY>
<DIV><SPAN class=19522619-09052000><FONT color=#0000ff face="Bookman Old Style"
size=3>Is it safe to say that Oracle will use the composite index on A,B,C if A is a foreign key. What I'm getting at is that I don't need a separate index on column A which is a foreign key if I already have the composite index A,B,C</FONT></SPAN></DIV>
<DIV><SPAN class=19522619-09052000><FONT color=#0000ff face="Bookman Old Style"
size=3></FONT></SPAN> </DIV>
<DIV><SPAN class=19522619-09052000><FONT color=#0000ff face="Bookman Old Style"
size=3>Thanks for all the help!</FONT></SPAN></DIV>
<DIV><SPAN class=19522619-09052000><FONT color=#0000ff face="Bookman Old Style"
size=3></FONT></SPAN><SPAN class=19522619-09052000><FONT color=#0000ff face="Bookman Old Style" size=3>Val</FONT></SPAN></DIV>
<BLOCKQUOTE>

    <DIV class=OutlookMessageHeader><FONT face="Times New Roman"     size=2>-----Original Message-----<BR><B>From:</B> Kirsh, Gary     [mailto:gary.kirsh_at_gs.com]<BR><B>Sent:</B> Tuesday, May 09, 2000 12:35     PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> RE:     indexes<BR><BR></FONT></DIV>
    <DIV><FONT color=#0000ff face=Arial size=2><SPAN     class=582241415-09052000>Valerie,</SPAN></FONT></DIV>     <DIV><FONT color=#0000ff face=Arial size=2><SPAN     class=582241415-09052000></SPAN></FONT> </DIV>     <DIV><FONT color=#0000ff face=Arial size=2><SPAN class=582241415-09052000>I     believe it can use the A column of the index, but not the C column, so     depending on how selective A is, this may or may not be a good     thing.</SPAN></FONT></DIV>
    <DIV><FONT color=#0000ff face=Arial size=2><SPAN     class=582241415-09052000></SPAN></FONT> </DIV>

    <DIV><FONT color=#0000ff face=Arial size=2><SPAN 
    class=582241415-09052000>Gary</SPAN></FONT></DIV>
    <DIV><FONT color=#0000ff face=Arial size=2><SPAN

class=582241415-09052000></SPAN></FONT> </DIV> <DIV><FONT color=#0000ff face=Arial size=2><SPAN class=582241415-09052000>Gary Kirsh</SPAN></FONT></DIV> <DIV><FONT color=#0000ff face=Arial size=2><SPAN class=582241415-09052000>Next Extent, Inc.</SPAN></FONT></DIV> <BLOCKQUOTE>

        <DIV align=left class=OutlookMessageHeader dir = ltr><FONT face=Tahoma 
        size=2>-----Original Message-----<BR><B>From:</B> Webber Valerie H 
        [mailto:Valerie.H.Webber_at_irs.gov]<BR><B>Sent:</B> Tuesday, May 09, 2000 
        11:10 AM<BR><B>To:</B> Multiple recipients of list 
        ORACLE-L<BR><B>Subject:</B> indexes<BR><BR></DIV></FONT>
        <P><FONT color=#800000 face="Bookman Old Style">I know this issue has 
        been discussed before but I can't find the answer in my list 
        archives.</FONT> </P>
        <P><FONT color=#800000 face="Bookman Old Style">If you have a composite 
        index on a table consisting of columns A,B,C in that order, will the 
        index be used if I have a query/where clause on columns A and 
        C?</FONT></P>
        <P><FONT color=#800000 face="Bookman Old Style">I know that it will if I 
        query on A alone or A,B or A,B,C but I can't find documentation about A 
        and C (no B)</FONT> </P>
        <P><FONT color=#800000 face="Bookman Old Style">Thanks in 
        advance!</FONT> <BR><FONT color=#800000 
        face="Bookman Old Style">Val</FONT> </P>
        <P><B><I><FONT color=#000080 face="Bookman Old Style">Valerie H. 
        Webber</FONT></I></B><I></I><FONT color=#000000 
        face=Arial><BR></FONT><FONT color=#000080 face="Comic Sans MS" 
        size=1>Management Systems Designers, Inc</FONT><FONT color=#000000 
        face=Arial><BR></FONT><FONT color=#000080 face="Comic Sans MS" 
        size=1>Valerie.H.Webber_at_m1.irs.gov</FONT><FONT color=#000000 
        face=Arial><BR></FONT><FONT color=#000080 face="Comic Sans MS" 
        size=1>704-569-1002 x107</FONT><FONT color=#000000 face=Arial>

Received on Tue May 09 2000 - 14:19:42 CDT