;) either have partition in standar edition
----- Original Message -----
From: <ryan.gaffuri_at_cox.net>
To: <oracle-l_at_freelists.org>; <oracle-l_at_freelists.org>
Sent: Friday, March 12, 2004 9:43 AM
Subject: Re: how to optimize insert into t select * from t2
> another option which i forgot is to partition your history table.
>
> create table mynewtable
> as select *
> from table
>
> then merge it in as a new partition. >
> > 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
> -----------------------------------------------------------------
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 - 08:16:54 CST
