Re: Cursor Problem

The simplest change which may solve the problem is:

>Cursor ResultCur (nMinTestResultHistoryID Number) Is
> Select /*+ first_rows */ *
> From ATT.Per_Test_Rslt_Hist
> Where Num < nMinTestResultHistoryID
> Order By Num Desc;
>

Otherwise try:
> Select /*+ index (PTR PK_PER_TEST_RSLT_HIST) */ *
> From ATT.Per_Test_Rslt_Hist PTR

It would help though if you told us which version of Oracle you were using.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Brad wrote in message ...
>I have a cursor defined thusly:
>
>Cursor ResultCur (nMinTestResultHistoryID Number) Is
> Select *
> From ATT.Per_Test_Rslt_Hist
> Where Num < nMinTestResultHistoryID
> Order By Num Desc;
>
>The where clause is causing me to blow out my temp tablespace even though
>the Num field is indexed. If I take the where clause out the rest of the
>stored proc starts running right away. The table contains 1.6 million
>records.
>
>The index in Num is defined thusly:
>
>CREATE UNIQUE INDEX ATT.PK_PER_TEST_RSLT_HIST
> ON ATT.PER_TEST_RSLT_HIST(NUM ASC)
>PCTFREE 10
>INITRANS 2
>MAXTRANS 255
>TABLESPACE ATT_DATA
>STORAGE(INITIAL 1M
> NEXT 19928K
> MINEXTENTS 2
> MAXEXTENTS 249
> PCTINCREASE 50
> FREELISTS 1
> FREELIST GROUPS 1)
>NOPARALLEL;
Received on Fri Jan 14 2000 - 10:08:25 CST