Re: indexing for left join

Milorad Poluga wrote:
>  Try to execute this modification of your query : 
> 
>  SELECT ITEM.ITEM_PK FROM ITEM
>  LEFT JOIN SERIAL_NO 
>     ON ( SERIAL_NO.ITEM_FK = ITEM.ITEM_PK
>                        AND SERIAL_NO.NO ='WX1234' )
>  GROUP BY ITEM.ITEM_PK
> 

>>SELECT ITEM.ITEM_PK FROM ITEM
>>LEFT JOIN SERIAL_NO ON SERIAL_NO.ITEM_FK = ITEM.ITEM_PK
>>WHERE SERIAL_NO.NO ='WX1234'
>>GROUP BY ITEM.ITEM_PK

For my small test DB both queries result in the same strategy.
The query will be generated by an object relational interface depending
on the user's search criteria. It will definitely be of the form I
specified.

I wanted to make sure that I have chosen the indices correctly. I am
presuming, if the tables are big, that the index on SERIAL_NO.NO will be
used for the WHERE clause and the one on SERIAL_NO.ITEM_FK for the join.

-- 


Regards,

Tarlika Elisabeth Schmitz