SQL query tune up, use of indexes

Hi

I need a bit of advice on the age old subject of SQL query tuning.

Basically I have 2 tables CAV_KEYS and CAV_MRKEY consisting of 882051 and 901810 rows respectively. I need to report on any rows which exist in the CAV_KEYS table but not in the CAV_MRKEY table with the same TERM_KEY, TERM_ID and KEY_TYPE fields. I have created 2 indexes as follows :-

CREATE INDEX I_CAV_MRKEY_TERMIDTYPE
ON CAV_MRKEY(TERM_KEY,TERM_ID,KEY_TYPE); CREATE INDEX I_CAV_KEYS_TERMIDTYPE
ON CAV_KEYS(TERM_KEY,TERM_ID,KEY_TYPE); And to produce my list of missing rows run the following query :-

SELECT
term_key||'|'||term_id||'|'||key_type||'|'||rank||'|'||medical_type FROM cav_mrkey m
WHERE NOT EXISTS (
  SELECT * FROM cav_keys k
  WHERE m.term_key = k.term_key
  AND m.term_id = k.term_id
  AND m.key_type = k.key_type);

This never finishes. So I kill it and produce a query plan which brings back the following :-

Query Plan

SELECT STATEMENT Cost =
  FILTER
    TABLE ACCESS FULL CAV_MRKEY
    INDEX RANGE SCAN I_CAV_KEYS_TERMIDTYPE As you can see the CAV_MRKEY table has a FULL SCAN rather than using the index. Even when I add the /* +INDEX(m I_CAV_MRKEY_TERMIDTYPE) */ hint the query plan is still the same.

Q/
1. How can I force the use of the index on CAV_MRKEY ?

2. Is their any benefit in doing so ?

In case your wondering this setup is ORACLE 7.1.4.3.4 on NOVELL and the server is a Pentium 150 and yes, I know this is crap but until I can get the funding it's all I have to work with.

Many thanks

Jason Received on Thu Apr 30 1998 - 07:53:49 CDT