Re: how to optimize insert into t select * from t2
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Mar 2004 10:16:07 -0400
Thanks ryan, but I have standard edition, can't do parallel operations.
----- Original Message -----
From: <ryan.gaffuri@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, March 12, 2004 9:43 AM
Subject: Re: how to optimize insert into t select * from t2
> 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
> -----------------------------------------------------------------
>
----------------------------------------------------------------
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:
