Oracle SQL Performance Tuning Tips- Using UNION ALL instead of UNION-Using NOT EXISTS in place of NOT IN for indexed columns at A to Z help

Using UNION ALL instead of UNION

•The SORT operation is very expensive in terms of CPU consumption.

•The UNION operation sorts the result set to eliminate any rows, which are within the sub-queries.

•UNION ALL includes duplicate rows and does not require a sort. Unless you require that these duplicate rows be eliminated, use UNION ALL

Using NOT EXISTS in place of NOT IN for indexed columns

•In sub-query statements such as the following, the NOT IN clause causes an internal sort/merge.

  select * from Student

  where STUDENT_NUM not in

  (select STUDENT_NUM from CLASS)

 So use-

  select * from STUDENT C

  where not exists

  (select 1 from CLASS A where  A.STUDENT_NUM = C.STUDENT_NUM)

Using NOT EXISTS in place of NOT IN for indexed columns

•In sub-query statements such as the following, the NOT IN clause causes an internal sort/merge.

 

  select * from Student

  where STUDENT_NUM not in

  (select STUDENT_NUM from CLASS)

• So use-

 

  select * from STUDENT C

  where not exists

  (select 1 from CLASS A where        A.STUDENT_NUM = C.STUDENT_NUM)

Note : Provide your comments by clicking below options! Thanks ! :)

Tweet