magendranjsathaiah_at_gmail.com wrote:
> 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??
An explain plan might help. Can you convert the OR statement to use a
BETWEEN clause, which might allow Oracle to perform a range scan on the
primary key index?
SELECT
COL1,
COL2
FROM
TABLE
WHERE
ENTITY_ID BETWEEN 1 AND 4999;
A 10046 or 10053 (cost based optimizer) trace might be helpful. The
cost based optimizer can perform various "tricks" to improve
performance, the availability of the various "tricks" depends on the
Oracle version and the various init.ora parameters in use. Oracle may
be processing your original SQL statement similar to this:
SELECT
COL1,
COL2
FROM
TABLE
WHERE
ENTITY_ID=1
UNION
SELECT
COL1,
COL2
FROM
TABLE
WHERE
ENTITY_ID=2
UNION
SELECT
COL1,
COL2
FROM
TABLE
WHERE
ENTITY_ID=3
UNION
SELECT
COL1,
COL2
FROM
TABLE
WHERE
ENTITY_ID=4
UNION
SELECT
COL1,
COL2
FROM
TABLE
WHERE
ENTITY_ID=5
UNION
SELECT
COL1,
COL2
FROM
TABLE
WHERE
ENTITY_ID=6
UNION
SELECT
COL1,
COL2
FROM
TABLE
WHERE
ENTITY_ID=7
UNION
SELECT
COL1,
COL2
FROM
TABLE
WHERE
ENTITY_ID=8
UNION
...
SELECT
COL1,
COL2
FROM
TABLE
WHERE
ENTITY_ID=4999;
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Wed Oct 11 2006 - 06:22:10 CDT
