indexes require alot of extra work when doing dml. do you really need them? If you can blow away a few it might help. how big will your inserts be? if they will be very large consider the following: create table mynewtable as select * from tables a union select * from history drop your old table. rename newtable to oldtable. then add your indexes in parallel either with dbms_job or some external job program. this is resource intensive and will not scale if you have alot of users on. > > From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx> > Date: 2004/03/12 Fri AM 08:34:29 EST > 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 -----------------------------------------------------------------