Is there a way to force Oracle to always use a specified index. The hint does not guarantee this.
Is there another method? Below is an example where I want to use the specified index. However
Oracle uses 2 other indices. The ones chosen by Oracle and the query never completes. If I delete
those 2 indices Oracle has no choice but to use the ones specified and query runs fine. The tables/indices are analyzed. The hints are syntactically correct.
Any ideas?
SELECT /*+ INDEX (mbr3 mrts_batches_received_idx_
02) */ mbr.date_of_service,
mbr.batch_received_date, mbr.billing_area_id,
mbr.charts_total, mbr.check_in_out, mbr.record_type, mbr.dept_id
FROM mrts.mrts_batches_received
mbr, mrts.mrts_rpt_billing_area
s rba3
WHERE rba3.billing_area_id = mbr.billing_area_id
AND (mbr.record_type, mbr.billing_area_id, mbr.date_of_service) IN (
SELECT /*+ INDEX (mbr3 mrts_batches_received_idx_
01) */
mbr3.record_type, mbr3.billing_area_id, mbr3.date_of_service
FROM mrts.mrts_batches_received
mbr3
WHERE mbr3.batch_received_date BETWEEN '01-oct-2007' AND '01-nov-2007'
AND mbr3.dept_id = 'C'
AND mbr3.check_in_out = 'IN')
AND (mbr.record_type = 'R'
OR mbr.record_type = 'M')
AND mbr.date_of_service IS NOT NULL
ORDER BY mbr.billing_area_id, date_of_service, record_type DESC;
Start Free Trial
