general index question

Posted in 2005

ANTHONY — Wed, 13 Jul 2005 15:20:56 — source: IIUG Forums & Mailing Lists

I have a question about indexes in general. We have several tables that have an index, say, on columns a,b,c then an index on a, or an index on a,b. It has always been my understanding that the second index is redundant as the first index can be used to satisfy queries on a, or a,b. Is this correct? Is there any reason to duplicate the indices like this?

↪ replying to ANTHONY

Prateek.Jain@ril.com — Thu, 14 Jul 2005 04:57:23 — source: IIUG Forums & Mailing Lists

--0__=EABBFAADDF8A62BF8f9e8a93df938690918cEABBFAADDF8A62BF Content-type: multipart/alternative; Boundary="1__=EABBFAADDF8A62BF8f9e8a93df938690918cEABBFAADDF8A62BF" --1__=EABBFAADDF8A62BF8f9e8a93df938690918cEABBFAADDF8A62BF Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable Dear You are wrong . The first index that is on column a can be used t= o satisfy Query on column a only . But to satisfy Query on column a ,b you have to use second index on a a= nd b and that can be used for Query on a also. So conclusion is that your first index is redundant if your second ind= ex is created with first as a column and second as b. Regards, Prateek Jain Reliance Industries Limited (M) - 09377966130 (O) - 079 - 30215010 (Ext - 381) = "ANTHONY" = <ajudish@lextron- To: ids@iiug.org = inc.com> cc: = Sent by: Subject: general index = question [5412] forum.subscriber@ = iiug.org = = = 07/13/05 08:51 PM = = I have a question about indexes in general. We have several tables tha= t have an index, say, on columns a,b,c then an index on a, or an index o= n a,b. It has always been my understanding that the second index is redun= dant as the first index can be used to satisfy queries on a, or a,b. Is thi= s correct? Is there any reason to duplicate the indices like this? = --1__=EABBFAADDF8A62BF8f9e8a93df938690918cEABBFAADDF8A62BF Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> Dear You are wrong . The first index that is on column a can be used to sat= isfy Query on column a only . But to satisfy Query on column a ,b you have to use second index on a a= nd b and that can be used for Query on a also. So conclusion is that your first index is redundant if your second ind= ex is created with first as a column and second as b. Regards, Prateek Jain Reliance Industries Limited (M) - 09377966130 (O) - 079 - 30215010 (Ext - 381) <img src=3D"cid:10__=3DEABBFAADDF8A62BF8f9e8a93df938690@ril.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for "ANTHONY"= <ajudish@lextron-inc.com>">"ANTHONY" <ajudish@lextr= on-inc.com> <table V5DOTBL=3Dtrue width=3D"100%" border=3D"0" cellspacing=3D"0" cel= lpadding=3D"0"> <tr valign=3D"top"><td width=3D"1%"><img src=3D"cid:20__=3DEABBFAADDF8A= 62BF8f9e8a93df938690@ril.com" border=3D"0" height=3D"1" width=3D"72" al= t=3D""> </td><td style=3D"background-image:url(cid:30__=3DEABBFAADDF8A62BF8f9e8= a93df938690@ril.com); background-repeat: no-repeat; " width=3D"1%"><img= src=3D"cid:20__=3DEABBFAADDF8A62BF8f9e8a93df938690@ril.com" border=3D"= 0" height=3D"1" width=3D"225" alt=3D""> <ul> <ul> <ul> <ul>"ANTHONY" <ajudish@lextron-inc.com= > Sent by: forum.subscriber@iiug.org 07/13/05 08:51 PM</ul> </ul> </ul> </ul> </td><td width=3D"100%"><img src=3D"cid:20__=3DEABBFAADDF8A62BF8f9e8a93= df938690@ril.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""> To: ids@iiug.org cc: Subject: general index questi= on [5412]</td></tr> </table> <tt>I have a question about indexes in general.  We have several t= ables that have an index, say, on columns a,b,c  then an index on = a, or an index on a,b. It has always been my understanding that the sec= ond index is redundant as the first index can be used to satisfy querie= s on a, or a,b.  Is this correct?  Is there any reason to dup= licate the indices like this? </tt> </body></html>= --1__=EABBFAADDF8A62BF8f9e8a93df938690918cEABBFAADDF8A62BF-- --0__=EABBFAADDF8A62BF8f9e8a93df938690918cEABBFAADDF8A62BF Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=EABBFAADDF8A62BF8f9e8a93df938690@ril.com> Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=EABBFAADDF8A62BF8f9e8a93df938690918cEABBFAADDF8A62BF Content-type: image/gif; name="ecblank.gif" Content-Disposition: inline; filename="ecblank.gif" Content-ID: <20__=EABBFAADDF8A62BF8f9e8a93df938690@ril.com> Content-transfer-encoding: base64 R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7 --0__=EABBFAADDF8A62BF8f9e8a93df938690918cEABBFAADDF8A62BF Content-type: image/gif; name="pic19072.gif" Content-Disposition: inline; filename="pic19072.gif" Content-ID: <30__=EABBFAADDF8A62BF8f9e8a93df938690@ril.com> Content-transfer-encoding: base64 R0lGODlhWABDALP/AAAAAK04Qf79/o+Gm7WuwlNObwoJFCsoSMDAwGFsmIuezf///wAAAAAAAAAA AAAAACH5BAEAAAgALAAAAABYAEMAQAT/EMlJq704682770RiFMRinqggEUNSHIchG0BCfHhOjAuh EDeUqTASLCbBhQrhG7xis2j0lssNDopE4jfIJhDaggI8YB1sZeZgLVA9YVCpnGagVjV171aRVrYR RghXcAGFhoUETwYxcXNyADJ3GlcSKGAwLwllVC1vjIUHBWsFilKQdI8GA5IcpApeJQt8L09lmgkH LZikoU5wjqcyAMMFrJIDPAKvCFletKSev1HBw8KrxtjZ2tvc3d5VyKtCKW3jfz4uMKmq3xu4N0nK BVoJQmx2LGVOmrqNjjJf2hHAQo/eDwJGTKhQMcgQEEAnEjFS98+RnW3smGkZU6ncCWav/4wYOnAI TihRL/4FEwbp28BXMMcoscQCVxlepL4IGDSCyJyVQOu0o7CjmLN50OZlqWmyFy5/6yBBuji0AxFR M00oQAqNIstqI6qKHUsWRAEAvagsmfUEAImyxgbmUpJk3IklNUtJOUAVLoUr1+wqDGTE4zk+T6FG uQb3SizBCwatiiUgCBN8vrz+zFjVyQ8FWkOlg4NQiZMB5QS8QO3mpOaKnL0Z2EKvNMSILEThKhCg zMKPVxYJh23qm9KNW7pArPynMqZDiErsTMqI+LRi3QAgkFUbXpuFKhSYZALd0O5RKa2z9EYKBbpb qxIKsjUPRgD7I2XYV6wyrOw92ykExP8NW4URhknC5dKGE4v4NENQj2jXjmfNgOZDaXb5glRmXQ33 YEWQYNcZFnrYcIQLNzyTFDQNkXIff0ExVlY4srziQk43inZgL4rwxxINMvpFFAz1KOODHiu+4aEw NEjFl5B3JIKWKF3k6I9bfUGp5ZZcdunll5IA4cuHvQQJ5gcsoCWOOUwgltIwAKRxJgbIkJAQZEq0 2YliZnpZZ4BH3CnYOXldOUOfQoYDqF1LFHbXCrO8xmRsfoXDXJ6ChjCAH3QlhJcT6VWE6FCkfCco CgrMFsROrIEX3o2whVjWDjoJccN3LdggSGXLCdLEgHr1lyU3O3QxhgohNKXJCWv8JQr/PDdaqd6w 2rj1inLiGeiCJoDspAoQlYE6QWLSECehcWIYxIQES6zhbn1iImTHEQyqJ4eIxJJoUBc+3CbBuwZE V5cJPPkIjFDdeEabQbd6WgICTxiiz0f5dBKquXF6k4senwEhYGnKEFJeGrxUZy8dB8gmAXI/sPvH ESfCwVt5hTgYiqQqtdRNHQIU1PJ33ZqmzgE90OwLaoJcnMop1WiMmgkPHQRIrwgFuNV90A3doNKT mrKIN07AnGcI9BQjhCBN4RfA1qIZnMqorJCogKfGQnxSCDilTVIA0yl5ciTovgLuBDKFUDE9aQcw 9SA+rjSNf9/M1gxrj6VwDTS0IUSElMzBfsj0NFXR2kwsV1A5IF1grLgLL/r1R40BZEnuBWgmQEyb jqRwSAt6bqMCOFkvKFN2GPPkUzIm/SCF8z8pVzpbjVnMsy0vOr1hw3SaSRUhpY09v0z0J1FnwzPl fmh+xl4WtR0zGu24I4KbMQm3lnVu2oNWxI9W/lcyzA+mCKF4DBikxb/+UWtOGRiFP8qEwAayIgI

↪ replying to ANTHONY

Simmons, Keith — Thu, 14 Jul 2005 07:58:52 — source: IIUG Forums & Mailing Lists

Anthony Yes, your assumption is correct, the index(a,b,c) will be used where a = x where a = x and b = y where a = x and b = y and c = z thus only one index is needed. Keith -> -----Original Message----- -> From: ANTHONY [mailto:ajudish@lextron-inc.com] -> Sent: Wednesday, July 13, 2005 4:21 PM -> To: ids@iiug.org -> Subject: general index question [5412] -> -> -> I have a question about indexes in general. We have several -> tables that have an index, say, on columns a,b,c then an -> index on a, or an index on a,b. It has always been my -> understanding that the second index is redundant as the -> first index can be used to satisfy queries on a, or a,b. Is -> this correct? Is there any reason to duplicate the indices -> like this? -> ******************************************************************************** ** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ******************************************************************************** **