Check the high water mark on TEMP_RESULT_TABLE. Reset it
if it's set too high.
vwu_at_anacomp.com (Vance Wu) wrote in message news:<c3d4638a.0109232153.6e8a3f43_at_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 Thu Oct 04 2001 - 11:29:14 CDT
