Use Implicit Cursor for Single Row Lookup!

View All Scripts Login to Run Script

Script Name Use Implicit Cursor for Single Row Lookup!
Description In the first edition of my Oracle PL/SQL Programming, published in 1995, I recommended that you use an explicit, not implicit, cursor when fetching a single row? (and I wasn't the only one) The argument: with SELECT-INTO, you have to fetch twice to see if there are TOO_MANY_ROWS, while with the explicit, you can just fetch once. And maybe waaaaay back then, it *was* faster (Oracle7). But that hasn't been the case for a long time. If you need to fetch a single row, the best and fastest way to do it is with a SELECT-INTO - preferably from inside a function.
Area PL/SQL General
Contributor Steven Feuerstein
Created Thursday January 21, 2016

Statement 1

Simple API to a calculator of elapsed time, using SYSTIMESTAMP.

Package to Calculate Elapsed Time

CREATE OR REPLACE PACKAGE tmr  
IS  
   PROCEDURE start_timer;  
  
   PROCEDURE show_elapsed_time (message_in IN VARCHAR2 := NULL);  
END tmr;

Package created.

Statement 2

CREATE OR REPLACE PACKAGE BODY tmr 
IS 
   last_timing   NUMBER := NULL; 
 
   PROCEDURE start_timer 
   IS 
   BEGIN 
      last_timing := DBMS_UTILITY.get_time; 
   END; 
 
   PROCEDURE show_elapsed_time (message_in IN VARCHAR2 := NULL) 
   IS 
   BEGIN 
      DBMS_OUTPUT.put_line ( 
            message_in 
         || ': ' 
         || MOD (DBMS_UTILITY.get_time - last_timing + POWER (2, 32), 
                 POWER (2, 32))); 
      start_timer; 
   END; 
END tmr;

Package Body created.

Statement 3

CREATE TABLE explimpl_data 
( 
   n      INTEGER PRIMARY KEY 
 , name   VARCHAR2 (100) 
)

Table created.

Statement 4

Load up the table!

BEGIN  
   FOR indx IN 1 .. 100000  
   LOOP  
      INSERT INTO explimpl_data  
      VALUES (indx, 'My name is ' || indx);  
   END LOOP;  
  
   COMMIT;  
END;

1 row(s) inserted.

Statement 5

Test Package: Both Explicit and Implicit Pathways

CREATE OR REPLACE PACKAGE explimpl  
AS  
   FUNCTION explicit (n_in IN explimpl_data.n%TYPE)  
      RETURN explimpl_data%ROWTYPE;  
  
   FUNCTION implicit (n_in IN explimpl_data.n%TYPE)  
      RETURN explimpl_data%ROWTYPE;  
  
   FUNCTION explicit_with_conversion (n_in IN explimpl_data.n%TYPE)  
      RETURN explimpl_data%ROWTYPE;  
END explimpl;

Package created.

Statement 6

Hopefully no surprises here. Note that in my implicit implementation, I trap NO_DATA_FOUND and return an empty record. One of the advantages of the SELECT-INTO is that it does raise both NO_DATA_FOUND and TOO_MANY_ROWS. This means I have to make a conscious decision about what to do with these errors. With the explicit query, I can easily overlook the impact of "no rows found" or "multiple rows with what should be a unique value".

CREATE OR REPLACE PACKAGE BODY explimpl  
IS  
   FUNCTION implicit (n_in IN explimpl_data.n%TYPE)  
      RETURN explimpl_data%ROWTYPE  
   IS  
      onerow_rec   explimpl_data%ROWTYPE;  
   BEGIN  
      SELECT *  
        INTO onerow_rec  
        FROM explimpl_data  
       WHERE n = n_in;  
  
      RETURN onerow_rec;  
   EXCEPTION  
      WHEN NO_DATA_FOUND  
      THEN  
         RETURN onerow_rec;  
   END implicit;  
  
   FUNCTION explicit (n_in IN explimpl_data.n%TYPE)  
      RETURN explimpl_data%ROWTYPE  
   IS  
      CURSOR onerow_cur  
      IS  
         SELECT *  
           FROM explimpl_data  
          WHERE n = n_in;  
  
      onerow_rec   explimpl_data%ROWTYPE;  
   BEGIN  
      OPEN onerow_cur;  
      FETCH onerow_cur INTO onerow_rec;  
      CLOSE onerow_cur;  
  
      RETURN onerow_rec;  
   END explicit;  
  
   FUNCTION explicit_with_conversion (n_in IN explimpl_data.n%TYPE)  
      RETURN explimpl_data%ROWTYPE  
   IS  
      CURSOR onerow_cur  
      IS  
         SELECT *  
           FROM explimpl_data  
          WHERE n = TO_CHAR (n_in);  
  
      onerow_rec   explimpl_data%ROWTYPE;  
   BEGIN  
      OPEN onerow_cur;   
      FETCH onerow_cur INTO onerow_rec;   
      CLOSE onerow_cur;  
  
      RETURN onerow_rec;  
   END explicit_with_conversion;  
END explimpl;

Package Body created.

Statement 7

With this procedure I can more easily, comprehensively compare performance. I specify the title of the test run, the number of times I want to execute the code, and the lookup value. The last is important, since it allows me to test positive and negative performance. That is: which is faster when it finds a row? Which is faster when it does NOT find a row?

Performance Comparison Driver

CREATE OR REPLACE PROCEDURE compare_explimpl (  
   title_in   IN VARCHAR2  
 , counter_in IN INTEGER  
 , n_in       IN explimpl_data.n%TYPE := 138  
)  
IS  
   emprec   explimpl_data%ROWTYPE;  
BEGIN  
   DBMS_OUTPUT.put_line ('Compare Explicit and Implicit: ');  
   DBMS_OUTPUT.put_line (title_in);  
   DBMS_OUTPUT.  
    put_line ('Iterations and Primary Key: ' || counter_in || '-' || n_in);  
   tmr.start_timer;  
  
   FOR i IN 1 .. counter_in  
   LOOP  
      emprec := explimpl.implicit (n_in);  
   END LOOP;  
  
   tmr.show_elapsed_time ('Implicit');  
   --  
   tmr.start_timer;  
  
   FOR i IN 1 .. counter_in  
   LOOP  
      emprec := explimpl.explicit (n_in);  
   END LOOP;  
  
   tmr.show_elapsed_time ('Explicit');  
   --  
   tmr.start_timer;  
  
   FOR i IN 1 .. counter_in  
   LOOP  
      emprec := explimpl.explicit_with_conversion (n_in);  
   END LOOP;  
  
   tmr.show_elapsed_time ('Explicit with datatype conversion');  
   DBMS_OUTPUT.put_line ('....');  
END compare_explimpl;

Procedure created.

Statement 8

You should see, consistently, that the implicit lookup is faster than either explicit, though the difference has narrowed through successive Oracle Database versions.

Run the Comparison

BEGIN   
   compare_explimpl ('Successful lookup', 10000, 138);   
   compare_explimpl ('Unsuccessful lookup', 10000, 138980);   
   compare_explimpl ('Lookup NULL', 10000, NULL);   
END;

Compare Explicit and Implicit: 
Successful lookup
Iterations and Primary Key: 10000-138
Implicit: 33
Explicit: 36
Explicit with datatype conversion: 37
....
Compare Explicit and Implicit: 
Unsuccessful lookup
Iterations and Primary Key: 10000-138980
Implicit: 51
Explicit: 42
Explicit with datatype conversion: 45
....
Compare Explicit and Implicit: 
Lookup NULL
Iterations and Primary Key: 10000-
Implicit: 48
Explicit: 40
Explicit with datatype conversion: 41
....

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database