07.03.2019 14:23, Carsten Schäfer wrote: > > With the left join FB does a full scan on Table_A and uses the Foreign > Key index on Table_B. The index on table_B.field_B is not used at all. > > So you get millions of reads if the table contains millions of data
How many rows in table_B exist for every row in table_A? > With the inner join the index on table_B.field_B is used and you only > get 1 read (if It's only 1 row that matches the condition) Yes, this is possible because the join starts with table_B! When INNER JOIN is used, the optimizer chooses the most effective join order. But this is impossible for LEFT JOIN, because of its nature. table_A is always read first and then joined to table_B. And it does not make sense to use an index on field_B when the foreign key index is already used. > Is it possible to force the optimizer tu use the index on table_B.field_B? Disable usage of foreign key index, e.g. via +0 hint. But I doubt it will make the timing better. Or specify an explicit plan with both indices used for table_B. Dmitry
