Re: Most efficient large update?

no, it is not possible to "turn off" redo logs.

there are some SQL statements where an additional keyword ("nologging" or "unrecoverable") can be added to potentially reduce the amount of information written to the redo logs, but these do not "turn off" redo logs.

you also need to consider the ramifications on database recovery when using either of the keywords "nologging" or "unrecoverable".

"Murat YILDIZ" <myildiz_at_bellona.com.tr> wrote in message news:9etsn1$16urc$1_at_ID-82167.news.dfncis.de...
> As it is the right time and subject to ask...isn't it possible to turn off
> redo logs in massive inserts or updates?
>
> Murat
> broom <broom_at_voicenet.com> wrote in message
> news:c948eb61.0105280621.33ac16c2_at_posting.google.com...
> > Here's the solution:
> > -----------------------------------------------------------
> > DECLARE
> > CURSOR HH_CURSOR IS
> > SELECT
> > rowid,
> > FINDER_NUMBER_IND,
> > FINDER_NUMBER_HH
> > FROM
> > appl_unq partition (zip9)
> > ;
> >
> > TYPE ROWLIST IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
> >
> > TYPE INDLIST IS TABLE OF appl_unq.FINDER_NUMBER_IND%TYPE INDEX BY
 BINARY_INTEGER;
> > TYPE HHLIST IS TABLE OF appl_unq.FINDER_NUMBER_HH%TYPE INDEX BY
 BINARY_INTEGER;
> >
> > RID ROWLIST;
> > INDID INDLIST;
> > HH HHLIST;
> >
> >
> > ROWS NATURAL := 100000;
> >
> > BEGIN
> > OPEN HH_CURSOR;
> > LOOP
> > FETCH HH_CURSOR BULK COLLECT INTO
> > RID,
> > INDID,
> > HH
> > limit rows;
> >
> > FORALL j IN RID.FIRST..RID.LAST
> > UPDATE appl2 SET
> > FINDER_NUMBER_HH =HH(j)
> > WHERE
> > FINDER_NUMBER_IND = INDID(j);
> > COMMIT;
> >
> > EXIT WHEN HH_CURSOR%NOTFOUND;
> > END LOOP;
> >
> > CLOSE HH_CURSOR;
> >
> > V_ERR_MSG := SUBSTR(SQLERRM,1,200);
> > DBMS_OUTPUT.PUT_LINE('Good Done: ERROR MSG - '|| V_ERR_MSG);
> >
> > COMMIT;
> >
> > EXCEPTION
> > WHEN OTHERS THEN
> >
> > COMMIT;
> >
> > V_ERR_MSG := SUBSTR(SQLERRM,1,200);
> > DBMS_OUTPUT.PUT_LINE('Bad: ERROR CODE - '|| SQLCODE);
> > DBMS_OUTPUT.PUT_LINE('ERROR MSG - '|| V_ERR_MSG);
> >
> > COMMIT;
> > END;
> > /
> > --------------------------
> >
> > This assumes a partitioned by zip source list.
> > This code will update 4761 records a second on a 3 CPU
> > sun 450.
> > I'll attempt multiple parallel runs to see if I gain that way.
>
>
>
Received on Mon May 28 2001 - 12:18:46 CDT