Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Too many CONSISTENT GETS using NOT EXISTS
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