Subject Re: ODP: [firebird-support] Question about index use
Author Svein Erling Tysvær
Post date 2019-03-07T11:44:46Z
Adding +0 or || '' (depending on type of field_A) would make it more likely that the table_B.field_B index will be used. It wouldn't force the optimizer, just be a strong hint...

Select * from table_A a left join table_B b on a.field_A = b.field_A+0 where b.field_B = 1 

HTH,
Set

Den tor. 7. mar. 2019 kl. 12:32 skrev Carsten Schäfer ca_schaefer@... [firebird-support] <firebird-support@yahoogroups.com>:


Yes I'm sure.

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

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)

Is it possible to force the optimizer tu use the index on table_B.field_B?


Regards
Carsten


Am 06.03.2019 um 21:49 schrieb Karol Bieniaszewski liviuslivius@... [firebird-support]:
 

Are you sure that this query looks like this?

 

For me such query use indexes in both cases left and inner.

Show full example with plans

 

Regards,

Karol Bieniaszewski