Saturday, September 29, 2012

Query Optimization OR and Union

 
When we execute query with OR clause,filtering the records from two 
different field. Oracle Database can not use index to filter the that.
 
1) create Index Idx_empno on emp(empno)

2) create Index Idx_salary on emp(salary)


Select * from emp
where empno = 7319 or salary > 15000

In this query Both the field have an index then also you can see
that it takes to much time because of "or" indexing field not work because of OR

so use Union to overcome this,

Select * from emp where empno = 7319
Union
Select * from emp where salary > 15000

 

No comments:

Post a Comment