Oracle Cursor Performance -- How to Optimize?

From: Alan Fusco <alanfusco_at_earthlink.net>
Date: Wed, 02 Feb 2000 17:41:34 GMT
Message-ID: <i%Zl4.696$Gi4.24897@newsread1.prod.itd.earthlink.net>

I am returning a cursor set that was setup in a package.

If I run the select statement without the cursor:

SELECT /*+ALL_ROWS*/

  A.field1,
  A.field 2,
  B.field 3,

  etc.
FROM
  TABLE A,
  TABLE B,
  etc.
WHERE
  table joins

It is fast (under a second).

Without the optimizer hint it takes over a minute even though all the tables are indexed.

When a return the results using a cursor:

OPEN AUDIO_CV FOR
SELECT /*+ALL_ROWS*/

  A.field1,
  A.field 2,
  B.field 3,

  etc.
FROM
  TABLE A,
  TABLE B,
  etc.
WHERE
  table joins

It takes forever -- as if the optimizer hint was ignored, which it probably is.

Is there anyway to increase this speed or force the use of the hint?

Thanks.

Alan Received on Wed Feb 02 2000 - 11:41:34 CST