Re: SQL query tune up, use of indexes

Try something like this,
select * from cavkeys
where
(TERM_KEY,TERM_ID,KEY_TYPE) in
 (select (TERM_KEY,TERM_ID,KEY_TYPE) from cavkeys  minus
 select (TERM_KEY,TERM_ID,KEY_TYPE) from CAV_MRKEY);

Jason Selby wrote:

> 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 - 18:44:38 CDT