Sudheer Palaparambil wrote:

>
> The following query using 2 tables, sale and sale_det
>
> sale has ( COMPANY_ID, ID ) as PK and
> index SALE_CID_ED on (COMPANY_ID, ENTRY_DATE )
>
> sale_det has a PK ( COMPANY_ID, ID ) and
> FK ( COMPANY_ID, PARENT_ID ) to sale
>
> SELECT ...
> FROM sale S JOIN sale_det SD
> ON ( SD.company_id = S.company_id )
> AND ( SD.parent_id = S.id )
> WHERE ( ( S.company_id = :dCID ) AND ( S.entry_date < :dAsOn )
> AND ( S.cancelled = 0 ) )
> .....
>
> PLAN JOIN (S INDEX (SALE_CID_ED),SD INDEX (FK_SALE_DET_TO_SALE)).
>
> Is there any way to utilise the PK (index) of sale also to speed up the
> query ?
>

I don't think so. The WHERE clause qualifies sale, but not sale_det,
so matching from sale_det to sale would require a full table scan of
sale_det, which is probably the larger table. Generally an index
scan of a smaller table followed by a foreign key lookup into the
larger table produces a smaller number of reads than a full table
scan of a large table followed by a primary key lookup on a smaller
table.


Regards,


Ann