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 Received on Mon Dec 04 2000 - 13:10:55 CST