Vance Wu wrote:
>
> 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.
Have you run explain plan? What optimizer are you using? If CBO, are
the tables, indexes and indexed column analyzed?
--
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.
Received on Mon Sep 24 2001 - 15:54:24 CDT
