Re: IN Clause or OR conditions..

<magendranjsathaiah_at_gmail.com> wrote in message news:1160551414.514243.187330_at_i42g2000cwa.googlegroups.com...
> Hello,
>
> I have question which might sound very basic but i could not help
> seeking out help
>
> we have database about 5000 recs in oracle 8i - with an index on the
> entity_id which is the PK.
>
>
> when we fire queries like the below, they take about 10 secs
>
>
> select col1, col2 from table where entity_id IN
> (...............................) or entity_id IN (.............)
>
> select col1, col2 from table where entity_id = 1 OR entity_id = 2 OR
> .......... OR entity_id = 4999
>
>
> But if just fire the query without any where clause it takes just 0.2
> secs like
>
> select col1, col2 from table
>
> then i write my java code to apply the fiilter, everything works out
> within 0.4 secs whereas it took about 10 secs when fire the query with
> such a huge filter criteria...
>
> Where is the problem now, is it with data, index, huge filter criteria
> or any database configuration that makes the it 10 secs? (Can the query
> or database itself be tuned)
>
> Any idea??
>

Rule based or Cost based optimizer ?

The large IN lists may be using concatenation, i.e. 5,000 consecutive indexed access paths - one per values, when a tablescan may be much more efficient. But without some concrete indication of what is actually happening, it is pointless trying to guess the reason for the difference.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html