Re: Most efficient large update?

I've missed the bit where you managed to turn off logging for your actual inserts. All we've got here is a create table statement that happens to mention the 'nologging' keyword. The Nologging keyword is NOT respected (ie, it's totally ignored) by ordinary inserts, updates and deletes. They will ALWAYS generate redo, whatever you do.

SQL Loader will respect the nologging attribute. So will a direct load insert. So will a 'create index' statement. But 'insert into...' will generate redo, regardless.

There is, however, one way to turn off *all* logging, database-wide, and that's by using the "_disable_logging" hidden parameter. But as I've posted elsewhere tonight, use of that parameter is totally unsupported, and renders your entire database vulnerable to non-recoverability in the event of an Instance crash.

Regards
HJR

--
=============================!!=============================
The views expressed are my own only, and definitely NOT those of Oracle
Corporation
=============================!!=============================


"broom" <broom_at_voicenet.com> wrote in message
news:c948eb61.0105281920.1e57aee8_at_posting.google.com...


> "Spencer" <spencerp_at_swbell.net> wrote in message

 news:<pKvQ6.14$4_5.60328_at_nnrp2.sbc.net>...


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

>

> Sure there is.  Here's the create statement for a table which does that:

> -------------------------------------------------------

> CREATE TABLE INDIVIDUAL (

>     FINDER_NUMBER_IND   VARCHAR2(10),

>     FINDER_NUMBER_HH    VARCHAR2(10),

> snip, snip snip.

>     )

>         PCTUSED 70

>         PCTFREE 29

>         INITRANS 20

>         PARALLEL

>     STORAGE (

>                 INITIAL 100M

>                 NEXT 100M

>                 PCTINCREASE 0

>                 MINEXTENTS 1

>                 MAXEXTENTS 1024

>          )

>     PARTITION BY RANGE (ZIP_CODE)

>     (PARTITION ZIP0 VALUES LESS THAN ('09999') TABLESPACE TEST_TAB

 NOLOGGING,


>      PARTITION ZIP1 VALUES LESS THAN ('19999') TABLESPACE TEST_TAB

 NOLOGGING,


>      PARTITION ZIP2 VALUES LESS THAN ('29999') TABLESPACE TEST_TAB

 NOLOGGING,


>      PARTITION ZIP3 VALUES LESS THAN ('39999') TABLESPACE TEST_TAB

 NOLOGGING,


>      PARTITION ZIP4 VALUES LESS THAN ('49999') TABLESPACE TEST_TAB

 NOLOGGING,


>      PARTITION ZIP5 VALUES LESS THAN ('59999') TABLESPACE TEST_TAB

 NOLOGGING,


>      PARTITION ZIP6 VALUES LESS THAN ('69999') TABLESPACE TEST_TAB

 NOLOGGING,


>      PARTITION ZIP7 VALUES LESS THAN ('79999') TABLESPACE TEST_TAB

 NOLOGGING,


>      PARTITION ZIP8 VALUES LESS THAN ('89999') TABLESPACE TEST_TAB

 NOLOGGING,


>      PARTITION ZIP9 VALUES LESS THAN ('99999') TABLESPACE TEST_TAB

 NOLOGGING


>           )

> ;

>

> -----------------------------------------------------------------

>

> Note:

> After testing parallel runs, I can update 11,000 records per

> second on a 3 cpu box.

>

> As far as worrying about recoverability, this is a data warehousing

> environmnet.  After any large update I take a Veritas snapshot, which

> gives me online backup without hassling with archivelog mode.  I'm

> about 10 times faster without it, which is well worth it.