Re: INTERSECT QUESTION

I would guess that perhaps you have two single column indexes on the EMPLOYEES table in this scenario: one on CITY and one on STATE. When your where clause contains only one column, the optimizer can easily tell which index to use. When you use both indexes in your where clause, your optimizer apparently doesn't choose intelligently, and does a table scan. I believe that an "intersect" will perform two separate queries and return the intersection of the result sets, so each of your original queries will have only one column in the where clause, making the index selection easy for the optimizer.

Sounds like an optimizer bug. But if you have a need to select with both fields in the where clause on a continual basis, you should create a composite index on both fields anyway.

Angelito Dizon wrote:
>
> I have a view called V_EMPLOYEES, which is a join of 3 tables. If I do
> this Select against the view:
>
> 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'
>
> the response is also quick.
>
> Is there an explanation from such behavior?

-- 
Brenda S. Muller
Sr. Consultant, Miaco Corporation / U S West

"But I have promises to keep,
 And miles to go before I sleep."

			- Robert Frost