Oracle SQL Performance Tuning Tips- Using UNION in Place of OR Avoiding NOT on Indexed Columns at atoz help

Avoiding NOT on Indexed Columns

•In general avoid using NOT when testing indexed columns.

 

•When Oracle encounters a NOT, it will choose not to use index and will perform a full-table scan instead.

 

•Remember, indexes are built on what is in a table, but not what is NOT in a table.

•For example the following select statement will never use the index on STUDENT_NUM column

 

  Select * from

  student

  Where STUDENT_NUM not like ‘9%’

Using UNION in Place of OR

•In general, always consider the UNION verb instead of OR verb in the WHERE clauses.

 

• Using OR on an indexed column causes the optimizer to perform a full-table scan rather than an indexed retrieval.

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

Tweet