Please help !
I have a prity simple query but in reality it takes a long time.
SELECT ProjectCode_Item_Code
FROM mwebProjectCode
WHERE ProjectCode_Item_Code
NOT IN(select distinct Work_Item_Code from mwebWork where Work_Level=6
and Work_Entity_Type=3
and Work_ID <> 2531)
ORDER BY ProjectCode_Item_Code;

I also tried:
SELECT ProjectCode_Item_Code
FROM mwebProjectCode
WHERE NOT EXISTS(select work_id from mwebWork where Work_Level=6 and Work_Entity_Type=3
and Work_Item_Code=ProjectCode_Item_Code and Work_ID <> 2531)
ORDER BY ProjectCode_Item_Code;

It returns 45 rows selected which is small number.

The execution plan for both is same:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'MWEBPROJECTCODE'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBWORK'
5 4 AND-EQUAL
6 5 INDEX (RANGE SCAN) OF 'IX_WORK_LEVEL' (NON-UNIQUE)
7 5 INDEX (RANGE SCAN) OF 'IX_WORK_ENTITY' (NON-UNIQUE
)





Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
94702 consistent gets
0 physical reads
0 redo size
1246 bytes sent via SQL*Net to client
1081 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
45 rows processed

SQL>
mwebProjectCode has 425 rows and mwebwork(subquery has 6000 rows and distinct records from mwebwork are just 380 rows.
Why does this has this big 94702 consistent gets and why does it take long time for execution ?

Parse 0.01 (Elapsed) 0.00 (CPU)
Execute/Fetch 7.22 (Elapsed) 0.00 (CPU)
Total 7.23 0.00



Thanks