Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Cursor Problem

Cursor Problem

From: Brad <Brad_at_SeeSigIfThere.com>
Date: Fri, 14 Jan 2000 09:49:57 -0500
Message-ID: <MPG.12e9019da54c5e3898980b@24.2.81.218>


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 - 08:49:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US