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