Subject Re: Query plan insists on NOT USING an index
Author Franz J Fortuny
--- In, Dmitry Yemanov <dimitr@...>
> What is the reported plan? Do you have an index for tor_equiv.idsub1?

The reported plan uses indexes in every single relation, EXCEPT where
it should be using the primary key of table SUBARTIS, which is a very
huge table (126,000 rows or more).

TOR_EQUIV.IDSUB1 AND ...IDSUB2 are BOTH columns that reference the
primary key of SUBARTIS.IDSUBARTI.

It scans 6 times all 126,000 rows in order to generate the 6 rows from
SUBARTIS that the IDSUB2 column of TOR_EQUIV indicates. IDSUB2 is a
column that has as foreign key the primary key of SUBARTIS.

The index it is NOT USING is the one corresponding to table SUBARTIS
(as reported by the PLAN). If I try to force it to use the index that
it should use, it responds that IT CAN NOT USE THAT INDEX IN THAT
QUERY. I don't see why not!