Hi all,
I am having one issue....
Currently I am using oracle 10gR2(10.2.0.4) EE on Solaris 10.I need to replaces not in clause with not exits.., The not in query taking too long to execute. So need to change the sql...
Here the first query...
select substr(edtr_itchs_org,1,4),sum(edtr_val)/10000000 from
epx_trd where
substr(edtr_itchs_org,1,4) in
('7101','7102','7103','7104','7113','7118' ) and
edtr_sldt between '01-apr-13' and '31-aug-13'
and edtr_port not in ( 112,625,481)
group by substr(edtr_itchs_org,1,4) order by
substr(edtr_itchs_org,1,4);
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2300 | 62100 | 277K|
| 1 | FILTER | | | | |
| 2 | SORT GROUP BY | | 2300 | 62100 | 277K|
| 3 | TABLE ACCESS FULL| EPX_TRD | 167K| 4407K| 277K|
------------------------------------------------------------------
Statistics
----------------------------------------------------------
1597 recursive calls
0 db block gets
1007072 consistent gets
491602 physical reads
0 redo size
759 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
37 sorts (memory)
0 sorts (disk)
6 rows processed
so I am also having index on edtr_port column...but the index being ignored . Please suggest how to rewrite the query to achieve maximum performance and if we can use not exists in place of not in.