Best way to check if table is empty or not
Fr3dYApr 5 2011 — edited Jul 2 2013Hi. 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.