how to optimize insert into t select * from t2

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Fri, 12 Mar 2004 09:34:29 -0400
Message-ID: <001701c40836$c9299be0$2501a8c0@dazasoftware.com>

Hi I have in 9.2 a historic table, everyr day there is all records insert into historic table.

Do you have please some idea trick to optimize it. Here is the insert plus statistics, the table and the tablespaces, thanks anyway.

  1 INSERT INTO FON.HICUENTASF_RW(
  2 HCF_FECHA, HCF_CODCLI, HCF_CTACORR, HCF_DBCUO, HCF_CRCUO,   3 HCF_IVA, HCF_MONTO_IVA, HCF_STATUS, HCF_CIUDAD )   4 SELECT
  5 sysdate, CUF_CODCLI, CUF_CTACORR, CUF_DBCUO, CUF_CRCUO,   6 NVL(CUF_IVA,0), NVL(CUF_MONTO_IVA,0), CUF_STATUS, CUF_CIUDAD   7* FROM CUENTASF 4965 filas creadas.

Execution Plan

   0      INSERT STATEMENT Optimizer=CHOOSE (Cost=21 Card=4868 Bytes=1
          84984)

   1    0   TABLE ACCESS (FULL) OF 'CUENTASF' (Cost=21 Card=4868 Bytes
          =184984)

Statistics

        699  recursive calls
      43055  db block gets
        616  consistent gets
       4946  physical reads
    5034476  redo size
        405  bytes sent via SQL*Net to client
        796  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
       4965  rows processed

SQL> rollback;

Rollback terminado.

CREATE TABLE fon.hicuentasf_rw

(hcf_codcli NUMBER(10,0) NOT NULL,

hcf_ctacorr NUMBER(2,0) NOT NULL,

hcf_fecha DATE NOT NULL,

hcf_dbcuo NUMBER(17,8) NOT NULL,

hcf_crcuo NUMBER(17,8) NOT NULL,

hcf_iva NUMBER(10,5) DEFAULT 0 NOT NULL,

hcf_monto_iva NUMBER(10,2) DEFAULT 0 NOT NULL,

hcf_status VARCHAR2(3) DEFAULT 'IND' NOT NULL,

hcf_ciudad VARCHAR2(3) DEFAULT 'XXX' NOT NULL)

PCTFREE 3 INITRANS 1 MAXTRANS 255 TABLESPACE tbl_users

STORAGE ( INITIAL 65536 NEXT 65536 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645
)

Indexes for FON.HICUENTASF_RW

CREATE INDEX fon.cst_hcf_fecha_status_rw ON fon.hicuentasf_rw

(

hcf_fecha ASC,

hcf_status ASC

)

PCTFREE 3 INITRANS 2 MAXTRANS 255 TABLESPACE tbl_indx

STORAGE ( INITIAL 65536 NEXT 65536 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645
)

COMPRESS 2 /

CREATE INDEX fon.idx_hcf_ciudad_rw ON fon.hicuentasf_rw

(

hcf_fecha ASC,

hcf_ciudad ASC

)

PCTFREE 3 INITRANS 2 MAXTRANS 255 TABLESPACE tbl_indx

STORAGE ( INITIAL 65536 NEXT 65536 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645
)

COMPRESS 2 /

CREATE UNIQUE INDEX adm.cst_hcf_cod_rw ON fon.hicuentasf_rw

(

hcf_codcli ASC,

hcf_ctacorr ASC,

hcf_fecha ASC

)

PCTFREE 3 INITRANS 2 MAXTRANS 255 TABLESPACE tbl_indx

STORAGE ( INITIAL 65536 NEXT 65536 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645
)

COMPRESS 2 /

CREATE INDEX adm.idx_hcf_fecha_db_cr_cuo_rw ON fon.hicuentasf_rw

(

hcf_fecha ASC,

hcf_dbcuo ASC,

hcf_crcuo ASC

)

PCTFREE 3 INITRANS 2 MAXTRANS 255 TABLESPACE tbl_indx

STORAGE ( INITIAL 65536 NEXT 65536 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645
)

COMPRESS 3 /

CREATE TABLESPACE TBL_USERS DATAFILE 'E:\oraxxx\datafiles\DFL_USER_xxx'

       SIZE 100M
       REUSE AUTOEXTEND ON NEXT  640k MAXSIZE UNLIMITED
       SEGMENT SPACE MANAGEMENT AUTO
       EXTENT MANAGEMENT LOCAL  UNIFORM SIZE 64K ;







----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Mar 12 2004 - 07:34:50 CST