Faster Exist Checking in Oracle

When performing checks in business logic to see if a record is used as part of a foreign key in a child table people often opt for the Select Count(*) type SQL call. This is a clean way to check for dependant rows but not necessarily the fastest. The Count(*) function call may result in a table scan. A better way to check for this is to make use of a handy Oracle feature called ROWNUM. Using this feature we can select a single row matching our parent key and then check the number of rows returned. This will be quicker than the Count(*) method. For example:
SQL Using Count(*)

SELECT Count(*) INTO :ll_Count
      FROM ORDER
      WHERE PROD_ID = :ls_CheckProd
      USING SQLCA;
IF ll_Count > 0 THEN // Cannot delete product

SQL Using ROWNUM

SELECT ORDER_ID INTO :ll_OrderID
      FROM ORDER
      WHERE PROD_ID = :ls_CheckProd
         AND ROWNUM < 2        USING SQLCA;     
IF SQLCA.SQLNRows <> 0 THEN // cannot delete product

Read More