SQL> SELECT I.ITEM_ID FROM ITEM I WHERE ACCOUNT_CODE = 'ICMPVMONTHEND' AND ACCOU
NT_TYPE_CODE = 'TRADE' AND LATEST_IND = 'Y' AND ITEM_STATUS_CODE IN ('RECONCILED
', 'UNRECONCILED') AND NOT EXISTS (SELECT 1 FROM LINK, ITEM_LINK WHERE LINK.LINK
_ID = ITEM_LINK.LINK_ID AND VALID_LINK_IND = 'Y' AND ITEM_ID = I.ITEM_ID);
no rows selected
Elapsed: 00:00:10.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=59407 Card=10673 Byt
es=320190)
1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=6042 Card=
10673 Bytes=320190)
3 2 INDEX (RANGE SCAN) OF 'IX_ITEM_6' (NON-UNIQUE) (Cost=1
297 Card=533657)
4 1 NESTED LOOPS (Cost=5 Card=1 Bytes=19)
5 4 INDEX (RANGE SCAN) OF 'IX_ITEM_LINK_1' (UNIQUE) (Cost=
3 Card=1 Bytes=12)
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'LINK' (Cost=2 Card=1
Bytes=7)
7 6 INDEX (UNIQUE SCAN) OF 'PK_LINK' (UNIQUE) (Cost=1 Ca
rd=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
962919 consistent gets
0 physical reads
0 redo size
157 bytes sent via SQL*Net to client
234 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> SELECT I.ITEM_ID,j.LINK_id FROM ITEM I left join (SELECT a.link_id, c.item
id FROM LINK a, ITEM_LINK b,item c WHERE a.LINK_ID = b.LINK_ID AND a.VALID_LINK
IND = 'Y' AND b.ITEM_ID = c.ITEM_ID) j on (i.ITEM_id = j.ITEM_id) WHERE ITEM_ST
TUS_CODE IN ('RECONCILED', 'UNRECONCILED') and j.LINK_id is null and ACCOUNT_CO
E = 'ICMPVMONTHEND' AND ACCOUNT_TYPE_CODE = 'TRADE' AND LATEST_IND = 'Y';
no rows selected
Elapsed: 00:00:07.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=51262 Card=25061 Byt
es=1227989)
1 0 FILTER
2 1 NESTED LOOPS (OUTER)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=1140 Car
d=25061 Bytes=751830)
4 3 INDEX (RANGE SCAN) OF 'IX_ITEM_6' (NON-UNIQUE) (Cost
=137 Card=55556)
5 2 VIEW PUSHED PREDICATE (Cost=2 Card=1 Bytes=19)
6 5 NESTED LOOPS (Cost=6 Card=1 Bytes=25)
7 6 NESTED LOOPS (Cost=4 Card=1 Bytes=18)
8 7 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=
2 Card=1 Bytes=6)
9 7 INDEX (RANGE SCAN) OF 'IX_ITEM_LINK_1' (UNIQUE)
(Cost=2 Card=1 Bytes=12)
10 6 TABLE ACCESS (BY INDEX ROWID) OF 'LINK' (Cost=2 Ca
rd=1 Bytes=7)
11 10 INDEX (UNIQUE SCAN) OF 'PK_LINK' (UNIQUE) (Cost=
1 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
963230 consistent gets
6575 physical reads
0 redo size
185 bytes sent via SQL*Net to client
234 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed