Hi,
I am having a table(lf_hotel_temp) which contains 3 lakh records. While running explain plan for below query its not using indexes. And it seems, its doing full table scan.Actually i have created primary key (hotel_code,service_id) in lf_hotel_temp and also created non unique index on hotel_code column in rest of all tables. pls help me to avoid full table scan in below query.
SELECT a.hotel_code
FROM lf_hotel_temp a
WHERE a.service_id = : p_service_id
AND (NOT EXISTS (SELECT *
FROM lf_ts_roomtype_properties b
WHERE a.hotel_code = b.hotel_code)
or NOT EXISTS (SELECT *
FROM lf_gta_roomtype_properties b
WHERE a.hotel_code = b.hotel_code)
or NOT EXISTS (SELECT *
FROM lf_hb_roomtype_properties b
WHERE a.hotel_code = b.hotel_code))
cost cardinality bytes
| SELECT STATEMENT, GOAL = ALL_ROWS | | 135 | 12613 | 113517 |
| FILTER | | | | |
| TABLE ACCESS FULL | LF_HOTEL_TEMP | 135 | 88433 | 795897 |
| INDEX RANGE SCAN | LF_TS_ROOMTYPE_PROP_IDX | 1 | 1 | 7 |
| INDEX RANGE SCAN | LF_GTA_ROOMTYPE_PROP_IDX | 1 | 1 | 9 |
| INDEX RANGE SCAN | LF_HB_ROOMTYPE_PROP_IDX | 3 | 2 | 14 |
Regards,
Jai