Oracle checking for existence of rows in a large table

Question

Trying to find whether id2 is used in table X. Easiest way to use 'count':

SELECT COUNT(*) FROM X WHERE id1 = :1 AND id2 = :2 ;

If X is a large table containing over 90,00,000 data, above query has severe performance impact. Is there any alternative with better performance?

Note: both column have indexing in table X.

How 'bout do a `Select First Row`? – Doan Cuong Jul 22 '13 at 05:51 — Doan Cuong, Jul 22 '13 at 05:51

score 3 · Answer 1 · answered Jul 22 '13 at 05:56

3

You need only one row exists with this condition so try:

select 1 from dual where EXISTS(SELECT id1 FROM X WHERE id1 = :1 AND id2 = :2)

answered Jul 22 '13 at 05:56

valex

23,966
7
43
60

David Aldridge · Accepted Answer · 2013-07-22T07:38:13.400

If you use:

SELECT COUNT(*)
FROM   X
WHERE  id1 = :1 AND
       id2 = :2 AND
       ROWNUM = 1;

Then you will get a 0 if no rows are not found, and a 1 if a single row is found.

The query will stop executing when it finds the first row, and you will not have to deal with NO_DATA_FOUND exceptions if no row is found.

you could really do with a composite index on both columns for this, as combining an index may not be efficient. However if one of the predicates is very selective then the index combine can be efficiently skipped and the table accessed to check the other column's value.

score 2 · Answer 3 · answered Jul 22 '13 at 05:52

2

SELECT * FROM X WHERE id1 = :1 AND id2 = :2 LIMIT 1;

answered Jul 22 '13 at 05:52

StanislavL

56,971
9
68
98

That works in MySQL [not in Oracle](http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php) – valex Jul 22 '13 at 06:01
@valex agree. that's link to alternative http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering – StanislavL Jul 22 '13 at 06:13

score 0 · Answer 4 · answered Jul 22 '13 at 05:56

0

It could be that the table is in use at the time/ongoing transactions. You could add WITH (NOLOCK) and see if that improves anything.

answered Jul 22 '13 at 05:56

andrewb

2,995
7
54
95

1

This is not relevant to Oracle. Writers do not block readers. – David Aldridge Jul 22 '13 at 07:34

Oracle checking for existence of rows in a large table

4 Answers4