Re: INTERSECT QUESTION

Angelito Dizon wrote:
>
> SELECT EMP_ID FROM V_EMPLOYEES WHERE CITY = 'RESTON'
>
> the response is quick. If I do
>
> SELECT EMP_ID FROM V_EMPLOYEES WHERE STATE = 'VA'
>
> the response is equally quick. If I do
>
> SELECT EMP_ID FROM V_EMPLOYEES WHERE CITY = 'RESTON' AND STATE = 'VA'
>
> the response is painfully slow. But if I do an Intersect like the
> following
>
> SELECT EMP_ID FROM V_EMPLOYEES WHERE CITY = 'RESTON'
> INTERSECT
> SELECT EMP_ID FROM V_EMPLOYEES WHERE STATE = 'VA'
  You can use EXPLAIN PLAN to examine the execution plan.

  The reason may be due to each component query uses its own access path to execute whereas the first query select the rows where city = 'RESTON' (or STATE = 'VA' depends on the other constraints such as primary key or unique key) and then compares the fetched rows with the condition STATE = 'VA'. Certainly, there are a lot of possibilities. The best way to solve it is by using EXPLAIN PLAN to examine.

---
Name   : Lun Wing San
Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841