Joining Tables: Table OrderWhat is the best order?

A then B

1,000 reads from A

For each A row do an index

scan into B (4 reads)

Total reads: 5,000

(1,000 for A + 1,000*4 for B)

Total Rows Returned: 10

B then A

Index scan of B (3 reads), then the data (10 reads) for a toal of 13

For each B row do an index

scan into A (3 reads)

Total reads: 43

(13 for B+10*3 for A)

Total Rows Returned: 10

Table A - 1,000 rows Table B - 50,000 rows
Select * from A, B

where A.join_col = B.join_col
and B.filter_col = 1

General Rule: The table which returns the fewest rows, either through a filter or the record count, should be first.

Assume 10 rows in B meet this condition

Previous slide

Next slide

Back to first slide

View graphic version