Message-Id: <10700.123590@fatcity.com> From: Alex Hillman Date: Mon, 4 Dec 2000 17:11:58 -0500 Subject: 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@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@SLAC.STANFORD.EDU -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: ian@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@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-printable RE: A Complaint About Tuning Books and Presentations

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@SLAC.Stanford.EDU]<= /FONT>
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 =3D B.ID
and B.ITEM_TYPE =3D '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 =3D '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@SLAC.STANFORD.EDU


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
  INET: ian@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@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 =