Re: PL/SQL Performance Issue or Slow Cursor?

Michael,

Thanks for your response, I did what you said to change the order of tables in the FROM clause, it didn't make any different, it still too slow.

I just wondering the temporay table TEMP_RESULT_TABLE can cause the slow down because the only row that was inserted to the temporary table earlier is not yet committed when the cursor is fetched, but it all happened in the same session, any idea ?

Vance.

bialik_at_isdn.net.il (Michael Bialik) wrote in message news:<969f8022.0109240258.7ccf7299_at_posting.google.com>...
> Try changing the order of tables in FROM clause:
>
> DECLARE
> CURSOR c_search IS
> SELECT PT.ID,
> PT.SET_TYPE,
> PT.LOCATION,
> PT.BACKUP,
> PT.ARCHIVE_ID
> FROM P_TYPES PT, TEMP_RESULT_TABLE TRT
> WHERE TRT.ID = PT.ID
> ORDER BY TRT.ID;
>
> I think you are working with rule based optimizer.
> You don't have any conditions in your WHERE ( except join), so it starts from
> right to left and performs full table scan on your big table.
>
> HTH. Michael.
Received on Mon Sep 24 2001 - 15:17:01 CDT