| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: A Complaint About Tuning Books and Presentations
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_01C05E3F.383F5D30
Content-Type: text/plain;
charset="iso-8859-1"
Why not to have index on item_type, item_id - in this order - in this case query will be satisfied using indexes only.
Alex Hillman
-----Original Message-----
From: MacGregor, Ian A. [mailto:ian_at_SLAC.Stanford.EDU]
Sent: Monday, December 04, 2000 2:12 PM
To: Multiple recipients of list ORACLE-L
Subject: A Complaint About Tuning Books and Presentations
I have yet to find a book which gives examples along the following lines.
Table A and table B are related as parent and child. Table A has 80,000 rows and table B has 120,000 rows. The average rowsize of table A is twice that of table B.
Table A has unique index on the column, id; table B has a concatenated unique index on id and line_number. A query is to be written based on the value of the item_type column in table B. A count of each value of item_type returns about 50 rows per value. What index would you build to optimize the following query?
select A.field_non_in_index, B.field_not_in_index
from table A, TABLE B
WHERE A.ID = B.ID
and B.ITEM_TYPE = 'SOUP'
/
Would you make an index on table B comprised of id, line_number, item_type, one containing id and item_type, or one with only item_type?
The index should be placed on item_type. The reasons are as follows. The
restriction and B.ITEM_TYPE = 'SOUP' returns about 50 rows fromm table B.
This is tne most restrictive condition in the query. Once it is applied you
have a 50 row
relation, I'll call it B', being joined to one with 80,000 rows. The
number difference in the number of rows indicates a nested loops join with
B' as the driving table and A as the inner table. The inner loop would use
the index on the id
column of table A.
What about the other indexes. The index on id, line_number, and item_type
won't be used because there is no restriction on line_type in the where
clause. The index on id and item_type can be used. However, it cannot
be used to reduce the
number of rows returned from table B before the join as the left most column
of the index, id, is restricted only by values returned by the join itself.
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.STANFORD.EDU
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: ian_at_SLAC.Stanford.EDU 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). ------_=_NextPart_001_01C05E3F.383F5D30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printableReceived on Mon Dec 04 2000 - 16:11:58 CST
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: A Complaint About Tuning Books and Presentations</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>Why not to have index on item_type, item_id - in this =
order - in this case query will be satisfied using indexes only.</FONT>
</P>
<P><FONT SIZE=3D2>Alex Hillman</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: MacGregor, Ian A. [<A =
HREF=3D"mailto:ian_at_SLAC.Stanford.EDU">mailto:ian_at_SLAC.Stanford.EDU</A>]<= /FONT>
<BR><FONT SIZE=3D2>Sent: Monday, December 04, 2000 2:12 PM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: A Complaint About Tuning Books and =
Presentations</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>I have yet to find a book which gives examples along =
the following lines.</FONT>
</P>
<P><FONT SIZE=3D2>Table A and table B are related as parent =
and child. Table A has 80,000</FONT>
<BR><FONT SIZE=3D2>rows and table B has 120,000 rows. The average =
rowsize of table A is twice</FONT>
<BR><FONT SIZE=3D2>that of table B.</FONT>
</P>
<P><FONT SIZE=3D2>Table A has unique index on the column, id; =
table B has a concatenated</FONT>
<BR><FONT SIZE=3D2>unique index on id and line_number. A =
query is to be written based on the</FONT>
<BR><FONT SIZE=3D2>value of the item_type column in table B. A =
count of each value of</FONT>
<BR><FONT SIZE=3D2>item_type returns about 50 rows per value. =
What index would you build to</FONT>
<BR><FONT SIZE=3D2>optimize the following query?</FONT>
</P>
<P><FONT SIZE=3D2>select A.field_non_in_index, =
B.field_not_in_index</FONT>
<BR><FONT SIZE=3D2>from table A, TABLE B</FONT>
<BR><FONT SIZE=3D2>WHERE A.ID =3D B.ID </FONT>
<BR><FONT SIZE=3D2>and B.ITEM_TYPE =3D 'SOUP'</FONT>
<BR><FONT SIZE=3D2>/</FONT>
</P>
<P><FONT SIZE=3D2>Would you make an index on table B comprised =
of id, line_number,</FONT>
<BR><FONT SIZE=3D2>item_type, one containing id and =
item_type, or</FONT>
<BR><FONT SIZE=3D2>one with only item_type?</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>The index should be placed on item_type. The =
reasons are as follows. The</FONT>
<BR><FONT SIZE=3D2>restriction and B.ITEM_TYPE =3D 'SOUP' returns =
about 50 rows fromm table B.</FONT>
<BR><FONT SIZE=3D2>This is tne most restrictive condition in the =
query. Once it is applied you</FONT>
<BR><FONT SIZE=3D2>have a 50 row</FONT>
<BR><FONT SIZE=3D2>relation, I'll call it B', being joined to one =
with 80,000 rows. The</FONT>
<BR><FONT SIZE=3D2>number difference in the number of rows indicates a =
nested loops join with</FONT>
<BR><FONT SIZE=3D2>B' as the driving table and A as the inner =
table. The inner loop would use</FONT>
<BR><FONT SIZE=3D2>the index on the id</FONT>
<BR><FONT SIZE=3D2>column of table A.</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>What about the other indexes. The index on id, =
line_number, and item_type</FONT>
<BR><FONT SIZE=3D2>won't be used because there is no restriction on =
line_type in the where</FONT>
<BR><FONT SIZE=3D2>clause. The index on id and =
item_type can be used. However, it cannot</FONT>
<BR><FONT SIZE=3D2>be used to reduce the</FONT>
<BR><FONT SIZE=3D2>number of rows returned from table B before the join =
as the left most column</FONT>
<BR><FONT SIZE=3D2>of the index, id, is restricted only by values =
returned by the join itself. </FONT>
</P>
<BR>
<BR>
<BR>
<P><FONT SIZE=3D2>Ian MacGregor</FONT>
<BR><FONT SIZE=3D2>Stanford Linear Accelerator Center</FONT>
<BR><FONT SIZE=3D2>ian_at_SLAC.STANFORD.EDU</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" = TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: MacGregor, Ian A.</FONT>
<BR><FONT SIZE=3D2> INET: ian_at_SLAC.Stanford.EDU</FONT>
</P>
<P><FONT SIZE=3D2>Fat City Network Services -- (858) =
538-5051 FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California -- Public Internet = access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------= -----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
 |
 |