Re: Most efficient large update?

From: broom <broom_at_voicenet.com>
Date: 28 May 2001 20:20:11 -0700
Message-ID: <c948eb61.0105281920.1e57aee8@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. Received on Mon May 28 2001 - 22:20:11 CDT