8 Replies Latest reply: Jul 2, 2013 5:02 AM by Hoek RSS

    Best way to check if table is empty or not

    Fr3dY
      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.