Thanks Jack, but I need redo logs, I had investigated all options, but I didn't found any one, maybe this can't be optimized. I was thinking there could be some new feature in 9.2 I didn't found. ----- Original Message ----- From: "Jack van Zanen" <JACK@xxxxxxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Friday, March 12, 2004 10:28 AM Subject: RE: how to optimize insert into t select * from t2 > 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 > ----------------------------------------------------------------- > ---------------------------------------------------------------- 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 -----------------------------------------------------------------