Bruce,

> SELECT * FROM
> TABLE_2 T2
>   INNER JOIN TABLE_1 T1  ON T2.TABLE_1_ID = T1.ID
>
> After executing this query I am getting such plan:
>
> PLAN JOIN (T1 NATURAL, T2 INDEX (FK_TABLE_2))
>
> Why in case of TABLE_1 optimizer did not chose index PK_TABLE_1?

Given that there is no ORDER BY clause it is much faster to walk Table_1 in natural order, then it is to use any index (even primary key).

Using an index always requires that the rows related to the index value be read to confirm that the indexed value is still valid (indexes contain all of the index values which a row could have had -- so the index may have a value which is no longer valid). So, reading by index would generate a huge amount of random IO, whereas walking the table is much more lightweight.


Sean