Re: Insert / update in big tables

From: Bertrand Guillaumin <guillauminb_at_hotmail.com>
Date: 22 Mar 2006 06:41:37 -0800
Message-ID: <1143038497.128756.74510@e56g2000cwe.googlegroups.com>

The query is a simple insert into select :

insert into prod_usage SELECT setid, inst_prod_id,'001' as usage_code,average_001, tendency_001, SYSDATE, 'BATCH_USER',SYSDATE,'BATCH' FROM TEMP_TABLE WHERE ERROR_FLAG = 'N' AND UPD_FLAG = 'I' The total of insert statements in the actual version is 15(14 identical to the example above), and there are 17 update statements(I propose to go down to 2 Merge statements and 2 Updates).

The Db has some db file sequential reads waits(coming certainly from reading the index of prod_usage(128M rows) and rearranging it).

CPU is not a problem either thats why I intend to pass every used TABLE parallel in order to be able to use the power of the machine more effectively.(otherwise it is one CPU per SQL instruction)

I add the create table/index functions (no remark on the index and table name being the same i have no choice).

CREATE TABLE PROD_USAGE
(

  SETID               VARCHAR2(5 BYTE)          NOT NULL,
  INST_PROD_ID        VARCHAR2(20 BYTE)         NOT NULL,
  USAGE_CODE          VARCHAR2(3 BYTE)          NOT NULL,
  AMOUNT              NUMBER(29,3)              NOT NULL,
  TENDENCY         INTEGER                   NOT NULL,
  ROW_ADDED_DTTM      DATE,
  ROW_ADDED_OPRID     VARCHAR2(30 BYTE)         NOT NULL,
  ROW_LASTMANT_DTTM   DATE,
  ROW_LASTMANT_OPRID  VARCHAR2(30 BYTE)         NOT NULL,

)
PCTUSED 80
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (

            INITIAL          40K
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )

LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING; CREATE UNIQUE INDEX PROD_USAGE ON PROD_USAGE
(SETID, INST_PROD_ID, USAGE_CODE)

LOGGING
TABLESPACE PSINDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (

            INITIAL          40K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )

NOPARALLEL; Received on Wed Mar 22 2006 - 08:41:37 CST