PBDR.COM

About   -   Contact   -   Purchase   -   Search   -   What's New

 
 
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

 

Top of Page

Legal Notice

Ken Howe 2011