CODEWITHSUNDEEP
sqloracle10g
Ravi
Ravi

Reputation: 1784

optimize query using NOT EXIST

I want to optimize my query using NOT EXIST in following query how can i do so, and please also explain its execution plan

 Select I_Ftn, I_Col, count(c.i_id_num) cnt 
From DSCL_ALL.W_CALENDER c 
Where c.UNIT_CODE= '01' 
AND c.i_g_vill = '45'
and c.i_g_code = '1'
and c.survey_year = '2012-2013'
and c.i_number not in (select m.m_indent from w_mill_pur m where m.unit_code = c.unit_code and m.m_vill = c.i_g_vill and m.m_grow = c.i_g_code)  
Group By I_Ftn, I_Col
ORDER BY I_ftn, I_col)

Upvotes: 0

Views: 125

Answers (2)

Aditya Kakirde
Aditya Kakirde

Reputation: 5225

There is difference between "NOT IN" and "NOT EXISTS". Please follow the link by ASKTOM -

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684

Upvotes: 0

FabienM
FabienM

Reputation: 96

you may want to try this :

Select I_Ftn, I_Col, count(c.i_id_num) cnt 
From DSCL_ALL.W_CALENDER c 
Where c.UNIT_CODE= '01' 
AND c.i_g_vill = '45'
and c.i_g_code = '1'
and c.survey_year = '2012-2013'
and not exists (select 1 from w_mill_pur m where m.unit_code = c.unit_code and m.m_vill = c.i_g_vill and m.m_grow = c.i_g_code and m.m_indent = c.i_number)  
Group By I_Ftn, I_Col
ORDER BY I_ftn, I_col)

It's more efficient because of the added where clause : Oracle is able to run a more filtered subquery and then just test if the result set is empty or not. You may also want to check that you have a (unit_code, m_vill, m_grow, m.m_indent) index for w_mill_pur.

The "not in" way require one more join in the main query (the subquery result set with the main one).

Regards,

Upvotes: 1

Related Questions