RE: how to optimize insert into t select * from t2

  • From: Jack van Zanen <JACK@xxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 12 Mar 2004 15:28:37 +0100

Hi


Like sugested before try the 

Insert /*+ APPEND */ into t1 Select * from t2


Jack
-----Original Message-----
From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx] 
Sent: Friday, March 12, 2004 2:34 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: how to optimize insert into t select * from t2


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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: