Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Too many CONSISTENT GETS using NOT EXISTS

Too many CONSISTENT GETS using NOT EXISTS

From: Nilendu Misra <nilendu_misra_at_yahoo.com>
Date: 28 May 2002 14:55:17 -0700
Message-ID: <23af4225.0205281355.360988c3@posting.google.com>


The query -

select au.user_id, min(aa.owner) as new_owner from BC_AUTH_USERS au, BC_AUTH_ACCESS aa
where au.user_id = aa.user_id
and not exists
(select '' from BC_AUTH_ACCESS aa2 where aa2.user_id = au.user_id and aa2.owner = au.owner)
group by au.user_id;

BC_AUTH_ACCESS has around 20K rows, BC_AUTH_USERS has around 3K rows.

The autotrace of this statement shows we are doing 48198 consistent gets.

Whereas both the tables are indexed on USER_ID as well as on OWNER.

The execution plan looks like -

Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop

SELECT STATEMENT Hint=CHOOSE 190 84
SORT GROUP BY 190 2 K 84
FILTER
HASH JOIN 1 K 15 K 18
TABLE ACCESS FULL BC_AUTH_USERS 190 1 K 5 TABLE ACCESS FULL BC_AUTH_ACCESS 20 K 156 K 12 AND-EQUAL 1 8
INDEX RANGE SCAN BC_AUTH_ACCESS_OWNER_I 1 1 INDEX RANGE SCAN BC_AUTH_USER_ID_I 1 1 Received on Tue May 28 2002 - 16:55:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US