performance problems

a sample of mY query goes like this:


select

CASE A.SALKEY=466 THEN 1 ELSE 0 END AS SAL

from A,B

WHERE

(A.SALKEY = B.SALKEY OR A.SALKEY = 466)



the query works faster with a simple join ,but with OR conditon it takes lot of time.
with the number of records increasing in A..this query takes more time.

I have made sure all basic indexes exist.


Please help

What database are you using? The OR clauses will invalidate the use of an index as the data proportionally increases.

Thanks for your reply.

I am using oracle 9i.
The OR clauses had been added to cater to requirements.

That is , in the example what i had given i had given only a single join which has a OR clause combined.we have around 4 OR clauses like that combined with a join.

Our requirement is to pick up the most specific combination of all the 4 keys as per the requirements (4 * 4 combinations).

can you please tell any work arounds.

The closer the result set gets to returning a complete subset of the data the more likely the optimizer is to choosing a full table scan over using an index. In general the threshold is 30 percent. Depending on your fields and knowleged of wha the users will be wanting you could create multi column indexes that would span multiple combinations.

Thanks a lot ,for patiently looking at my doubts and for your answer.It solved our problem