To add what hpuxac said I believe the optimizer will also look at how
many of the table blocks it expects to have to visit. Even with good
selectivity if the data is not clustered the rdbms could have to read
nearly every block in the table to get one row from each block.
Reading all the blocks sequentially using the multi_block_read_count
could take less work than reading each block via the index so a hash
join could appear to be a better choice than a nested loop join.
HTH -- Mark D Powell --
Received on Wed Mar 22 2006 - 11:33:41 CST
