Re: A Complaint About Tuning Books and Presentations

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Tue, 5 Dec 2000 10:36:00 -0500
Message-Id: <10701.123659@fatcity.com>

Hello Ian,

Doesn't Guy Harrison's book get into this type of thing? Have you looked at that?

Best regards,

Jonathan
mailto:jonathan_at_gennick.com
http://gennick.com

Monday, December 04, 2000, 2:11:31 PM, you wrote:

MIA> I have yet to find a book which gives examples along the following lines.

MIA> Table A and table B  are related as  parent and child.  Table A has 80,000
MIA> rows and table B has 120,000 rows. The average rowsize of table A is twice
MIA> that of table B.

MIA> Table A has unique index  on the column, id; table B has a concatenated
MIA> unique index on id and line_number.   A query is to be written based on the
MIA> value of the item_type column in table B.  A count of each value of

MIA> item_type returns about 50 rows per value. What index would you build to MIA> optimize the following query?

MIA> select A.field_non_in_index, B.field_not_in_index
MIA> from table A, TABLE B
MIA> WHERE A.ID = B.ID 
MIA> and B.ITEM_TYPE = 'SOUP'
MIA> /

MIA> Would you  make an index on table B comprised of  id, line_number,

MIA> item_type, one containing id and item_type, or MIA> one with only item_type?

MIA> The index should be placed on item_type.  The reasons are as follows.  The
MIA> restriction and B.ITEM_TYPE = 'SOUP'  returns about 50 rows fromm table B.
MIA> This is tne most restrictive condition in the query.  Once it is applied you
MIA> have a 50 row
MIA> relation, I'll call it B', being joined to  one with 80,000 rows.  The
MIA> number difference in the number of rows indicates a nested loops join with
MIA> B' as the driving table and A as the inner table.  The inner loop  would use
MIA> the index on the id
MIA> column of  table A.


MIA> What about the other indexes.  The index on id, line_number, and item_type
MIA> won't be used because there is no restriction on line_type in the where
MIA> clause.   The index on  id and  item_type can be used.  However, it cannot
MIA> be used to reduce the

MIA> number of rows returned from table B before the join as the left most column MIA> of the index, id, is restricted only by values returned by the join itself.

MIA> Ian MacGregor
MIA> Stanford Linear Accelerator Center Received on Tue Dec 05 2000 - 09:36:00 CST