Cursor in PL/SQL [message #41135] |
Fri, 29 November 2002 00:02 |
aruna
Messages: 33 Registered: January 2002
|
Member |
|
|
Hi,
In Developer2000,I am using an explicit cursor in a PL/SQL block.
When I run the PL/SQL block which is a SELECT involving multiple joins) on the SQLPLUS prompt ,the result set is retrieved within a matter of seconds.
Now,the same when run through the front-end takes an enormous amount of time say 5 mins.
The problem is that I am unable to identify the reason for the delay in the response time.
In SQLPLUS,the optimizer goal is "CHOOSE" and the Oracle version is 7.1.Also,the query has a hint of FIRST_ROWS.
Please help.
Thanks,
Aruna
|
|
|
Re: Cursor in PL/SQL [message #41156 is a reply to message #41135] |
Mon, 02 December 2002 16:47 |
Darryl D'souza
Messages: 2 Registered: December 2002
|
Junior Member |
|
|
Avoid using the hints and try ordering the FROM clause that is the table list in the order of least selective to most selective and order the WHERE clause that is the joins from MOST selective to LEAST selective.
For example
FROM A, B, C, D
WHERE D.column1 = C.column1
and C.column2 = B.column1
and B.column2= A.Column2
If you give me the exact query that you are trying , I might me able to give more help.
Darryl
|
|
|