Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Forcing more than one Index using hint?

AK111Aug 21 2007 — edited Aug 22 2007
How can i force more than one index in query.

I have following-

SELECT /*+ INDEX(a xyz)*/
DISTINCT b.rtvmemberid AS rtvmemberid,
a.datetimesurveytaken, b.campaign
FROM TableA a, TableB b
WHERE a.datetimestaken BETWEEN ADD_MONTHS(SYSDATE,-14) AND SYSDATE
AND b.creationdate BETWEEN ADD_MONTHS(SYSDATE,-14) AND SYSDATE
AND a.completedind='Y'
AND a.id = b.id
AND b.typeid=2

Above index 'xyz' is on TableA (id, datetimestaken).
I have another index 'mno' on TableB. Query analyzer is not picking up that index. Hence there is full table scan on TableB. How can I solve this problem? Using index hint i tried to add one more index (Plz see statement below) but not sure if i am using the correct syntax.?

Select /*+ INDEX(a xyz), INDEX(b mno)*/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 19 2007
Added on Aug 21 2007
9 comments
683 views