PL/SQL Performance Issue or Slow Cursor?

From: Vance Wu <vwu_at_anacomp.com>
Date: 23 Sep 2001 22:53:16 -0700
Message-ID: <c3d4638a.0109232153.6e8a3f43@posting.google.com>

Hi,

I have the following PL/SQL code, table TEMP_RESULT_TABLE has only 1 row and table P_TYPES has around 1 million rows, the execution is too slow, it took almost 15 seconds with the result of 2 output line from dbms_output.put_line. All columns ID, LOCATION, BACKUP and ARCHIVE_ID were indexed already.

DECLARE
  CURSOR c_search IS

        SELECT PT.ID,
               PT.SET_TYPE,
               PT.LOCATION,
               PT.BACKUP,
               PT.ARCHIVE_ID
          FROM TEMP_RESULT_TABLE TRT, P_TYPES PT
          WHERE TRT.ID = PT.ID
          ORDER BY TRT.ID;

  rec_search c_search%ROWTYPE;
BEGIN

        dbms_output.enable(100000);
        OPEN c_search;
        FETCH c_search INTO rec_search;
        WHILE c_search_level4%FOUND LOOP
           dbms_output.put_line('ARCHIVE_ID = '||
                           rec_search.ARCHIVE_ID);
           FETCH c_search INTO rec_search;
        END LOOP;
        CLOSE c_search;

END; Since I am sure TRT.ID = 1708767 because table TEMP_RESULT_TABLE contains only 1 row, so when I re-declared the CURSOR as the following, the execution took less than 1 seconds with the same result:

  CURSOR c_search IS

        SELECT PT.ID,
               PT.SET_TYPE,
               PT.LOCATION,
               PT.BACKUP,
               PT.ARCHIVE_ID
          FROM P_TYPES PT
          WHERE PT.ID = 1708767
          ORDER BY PT.POLICY_ID;

Can someone tell me why I got such big different in performance hit since table TEMP_RESULT_TABLE contains just only 1 row. I am running Oracle 8.1.6 on SUN/Solaris.

Thanks in Advanced.
Vance. Received on Mon Sep 24 2001 - 00:53:16 CDT