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

From: <ryan.gaffuri_at_cox.net>
Date: Fri, 12 Mar 2004 8:43:12 -0500
Message-Id: <20040312134312.RGYZ5813.lakemtao04.cox.net@smtp.east.cox.net>

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_at_dazasoftware.com>
> Date: 2004/03/12 Fri AM 08:34:29 EST
> To: <oracle-l_at_freelists.org>
> 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_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
> -----------------------------------------------------------------
>

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:39:46 CST