From oracle-l-bounce@freelists.org Fri Mar 12 07:40:34 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2CDeYG01772 for ; Fri, 12 Mar 2004 07:40:34 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2CDeYo01767 for ; Fri, 12 Mar 2004 07:40:34 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A1629394E00; Fri, 12 Mar 2004 08:39:55 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 12 Mar 2004 08:38:41 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from lakemtao02.cox.net (lakemtao02.cox.net [68.1.17.243]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BCE68394DC2 for ; Fri, 12 Mar 2004 08:38:37 -0500 (EST) Received: from smtp.east.cox.net ([172.18.52.56]) by lakemtao02.cox.net (InterMail vM.5.01.06.08 201-253-122-130-108-20031117) with SMTP id <20040312134353.REOC13694.lakemtao02.cox.net@smtp.east.cox.net> for ; Fri, 12 Mar 2004 08:43:53 -0500 From: To: oracle-l@freelists.org, Subject: Re: how to optimize insert into t select * from t2 Date: Fri, 12 Mar 2004 8:43:53 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Message-Id: <20040312134353.REOC13694.lakemtao02.cox.net@smtp.east.cox.net> X-archive-position: 641 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ryan.gaffuri@cox.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l 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" > Date: 2004/03/12 Fri AM 08:34:29 EST > To: > 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@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@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 -----------------------------------------------------------------