Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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