I am writing SQL like below. It is causing scanning of table "TBLA" every time which is a big table. I don't have any option to use indexing due to table design. How can i optimize the below one so that i don't have to scan tables again and again. Also I need to keep conditions for TBLB i.e. there are different condition for TBLB in each join. Appreciate any help
Thanks in advance :)
Sel SUB1.COLX,
SUB1.COLY,
SUB1.COLZ,
SUB2.COLX,
SUB2.COLY,
SUB2.COLZ,
SUB3.COLX,
SUB3.COLY,
SUB3.COLZ
FROM
TBLA
LEFT JOIN
(SELECT COLX , COLY, COLZ FROM TBLB WHERE rec='123' ) SUB1
ON TBLA.SK=SUB1.SK
LEFT JOIN
(SELECT COLX , COLY, COLZ FROM TBLB WHERE rec='456' ) SUB2
ON TBLA.SK=SUB2.SK
LEFT JOIN
(SELECT COLX , COLY, COLZ FROM TBLB WHERE rec='789' ) SUB3
ON TBLA.SK=SUB3.SK