CODEWITHSUNDEEP
sqloracle-database
duncanportelli
duncanportelli

Reputation: 3229

Check if records exist in a table

I am trying to check if records exist in my table. I found out that the most efficient way is to use the DUAL table. However, I am not 100% sure on how this method works. I have a table which contains thousands of records (almost half a million). Is there any major difference between these two methods, keeping in mind that a lot of records are present in the table:

Method 1:

SELECT 1
FROM DUAL
WHERE EXISTS(SELECT *
             FROM MY_TABLE
             WHERE MY_COLUMN_PK = 'MY_VALUE'
             AND MY_COLUMN = 'MY_VALUE')

Method 2:

SELECT 1
FROM DUAL
WHERE EXISTS(SELECT *
             FROM MY_TABLE
             WHERE MY_COLUMN_PK = 'MY_VALUE'
             AND MY_COLUMN = 'MY_VALUE'
             AND ROWNUM=1)

I have executed both queries in my database client software and both queries had similar execution times.

Note: MY_COLUMN_PK is the primary key of my table while MY_COLUMN isn't a primary key and doesn't have an index.

Upvotes: 1

Views: 517

Answers (2)

duncanportelli
duncanportelli

Reputation: 3229

I finally found the answer in another question. Both methods in the above question are identical since the EXISTS clause is being used. As explained by Justin Cave in the question attached, "the EXISTS clause allows Oracle to stop looking as soon as it finds the first matching row".

Upvotes: 0

Colin 't Hart
Colin 't Hart

Reputation: 7729

Assuming that my_column forms a unique or primary key,

select 1
from my_table
where my_column = 'my_value';

is enough.

If the column to check is not unique, then you only need to check for the existence of 1 row. In this case:

select /*+ first_rows */ 1
from my_table
where my_column = 'my_value'
and rownum <= 1;

is best. The use of the first_rows hint was necessary in older versions of Oracle but is probably unnecessary these days. It tells the optimizer that we want to optimize to get rows back as fast as possible (because we only want 1 row anyway).

In addition, rownum = 1 is OK too, but I always write my checks on rownum as being less than or equal to a value to remind me that I can never do rownum > n...

Upvotes: 2

Related Questions