Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Best way to check if table is empty or not

Fr3dYApr 5 2011 — edited Jul 2 2013
Hi. I had to check if a table was empty or not, and found different solutions.

One of them called my atention, I readed it was the best way to do it (some 'dual' trick from Steven Feuerstein, they say) :

SELECT 1 FROM DUAL WHERE EXISTS (SELECT 'X' FROM TABLE);
Plan
SELECT STATEMENT ALL_ROWSCost: 4 Cardinality: 1
3 FILTER
1 FAST DUAL Cost: 2 Cardinality: 1
2 INDEX FAST FULL SCAN INDEX (UNIQUE) TABLE.UB_PK Cost: 2 Cardinality: 1


But doing some tests I found this query to have lower cost:

SELECT 1 FROM TABLE WHERE ROWNUM=1;
Plan
SELECT STATEMENT ALL_ROWSCost: 2 Cardinality: 1
2 COUNT STOPKEY
1 INDEX FAST FULL SCAN INDEX (UNIQUE) TABLE.UB_PK Cost: 2 Cardinality: 1


So, what about that dual table trick? Should I keep the 'select 1 where rownum=1' as best possible way?
I know it shouldn't matter much, but just wanna know what method works best for checking empty tables :)

Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 30 2013
Added on Apr 5 2011
8 comments
17,671 views