Oracle SQL efficiency practices

9: replace in with exists.Important

Case: Search for the dept_1 Department employee name

9. 1 SQL statement

 --  Subquery  Select Pname From T_person Where Deptid In  (  Select Deptid From T_department Where Deptname Like   '  Dept_1  '  );  --  Exsist  Select Pname From T_person Where   Exists  (  Select   *   From T_department Where T_department.deptid =  T_person.deptid  And Deptname Like   '  Dept_1  '  );  --  External Connection
Select pname
From t_person P left join t_department D on p. deptid = D. deptid
Where D. deptname like 'dept _ 1 ';
 

9. 2 TestCodeBlock

 -- Test the subquery code block    Declare  V_ SQL  Varchar2 ( 300  ); V_diff  Number  ;  Begin  V_ SQL:  =   '  Select pname from t_person where deptid in (select deptid from t_department where deptname like  '' Dept_1  ''  )  '  ;  Select F_test_time_efficiency (v_ SQL) Into V_diff From  Dual;  End  ;  --Exists code block test      Declare  V_ SQL Varchar2 ( 300  ); V_diff  Number  ;  Begin  V_ SQL:  =   '  Select pname from t_person where exists (select * From t_department where t_department.deptid = t_person.deptid and deptname like  ''  Dept_1  ''  ) '  ;  Select F_test_time_efficiency (v_ SQL) Into V_diff From  Dual;  End  ;--External Connection code block test      Declare  V_ SQL  Varchar2 ( 300  ); V_diff  Number ;  Begin  V_ SQL:  =   '  Select pname from t_person P left join t_department D on p. deptid = D. deptid where D. deptname like  ''  Dept_1  '''  ;  Select F_test_time_efficiency (v_ SQL) Into V_diff From  Dual; End ;

9. 3 output results

 
Time DifferencesIs10Time DifferencesIs9Time DifferencesIs10
 

9. 4

Efficiency from high to low: exists> external connection = subquery

Focus on 10: replace not in with not exists.

Case: Search for the name of a non-dept_1 employee

10.1 SQL statements

 --  Subquery   Select  P. pname  From  T_person  Where Deptid Not   In ( Select  Deptid  From  T_department  Where Deptname Like   ' Dept_1  '  );  --  The external connection query result contains persons without departments   Select  P. pname  From T_person P Left   Join  T_department d  On P. deptid =  D. deptid  Where D. deptname Not  Like   '  Dept_1  '   Or D. deptname Is   Null  ;  --  The exists method does not contain people without departments.  Select  P. pname  From  T_person P  Where   Exists  ( Select   *   From T_department d Where P. deptid = D. deptid And D. deptname Not   Like   '  Dept_1  ' );

10.2 run the code block (clear the database cache before each test)

 

 --  Test the subquery code block  Declare V_ SQL  Varchar2 ( 300  ); V_diff  Number  ;  Begin  V_ SQL:  =   '  Select t_person.pname from t_person where deptid not in (select deptid from t_department where deptname like  ''  Dept_1  '' )  '  ;  Select F_test_time_efficiency (v_ SQL) Into V_diff From  Dual;  End  ;  --  External Connection code block test      Declare  V_ SQL  Varchar2 ( 300 ); V_diff  Number  ;  Begin  V_ SQL:  =   '  Select P. pname from t_person P left join t_department D on p. deptid = D. deptid where D. deptname not like  ''  Dept_1  ''  Or D. deptname is null  '  ;  Select F_test_time_efficiency (v_ SQL) Into V_diff From  Dual;  End  ;  --  Exists code block test      Declare  V_ SQL  Varchar2 ( 300  ); V_diff  Number  ;  Begin V_ SQL:  =   '  Select P. pname from t_person P where exists (select * From t_department d Where p. deptid = D. deptid and D. deptname not like  ''  Dept_1  ''  )  '  ;  Select F_test_time_efficiency (v_ SQL) Into V_diff From  Dual; End  ; 

10.3 statement for clearing Oracle Cache

 
AlterSystem flush buffer_cache;AlterSystem flush shared_pool;AlterSystem flush global context;AlterSystemSetEvents='Immediate trace name flush_cache';
 

10.4 output results

Time differences is 9

Time differences is 8

Time differences is 8

10.5 conclusion

Efficiency from high to low: External Connection = exists> subquery