RE: how to optimize insert into t select * from t2
- From: April Wells <AWells@xxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Mar 2004 08:52:12 -0600
Juan... why don't you check on a test system to see if
insert into select from where
will for sure not create redo logs.
April Wells
Oracle DBA/Oracle Apps DBA
Corporate Systems
Amarillo Texas
@>-->-->--
?Few people really enjoy the simple pleasure of flying a kite?
Adam Wells age 11
"Imagination is the highest kite one can fly."
Lauren Bacall
-----Original Message-----
From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx]
Sent: Friday, March 12, 2004 8:42 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: how to optimize insert into t select * from t2
Thanks Jack, but I need redo logs,
I had investigated all options, but I didn't found any one, maybe
this can't be optimized.
I was thinking there could be some new feature in 9.2 I didn't found.
----- Original Message -----
From: "Jack van Zanen" <JACK@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, March 12, 2004 10:28 AM
Subject: RE: how to optimize insert into t select * from t2
> Hi
>
>
> Like sugested before try the
>
> Insert /*+ APPEND */ into t1 Select * from t2
>
>
> Jack
> -----Original Message-----
> From: Juan Cachito Reyes Pacheco [mailto:jreyes@xxxxxxxxxxxxxxxx]
> Sent: Friday, March 12, 2004 2:34 PM
> 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
-----------------------------------------------------------------
The information contained in this communication, including attachments, is
strictly confidential and for the intended use of the addressee only; it may
also contain proprietary, price sensitive, or legally privileged information.
Notice is hereby given that any disclosure, distribution, dissemination, use,
or copying of the information by anyone other than the intended recipient is
strictly prohibited and may be illegal. If you have received this communication
in error, please notify the sender immediately by reply e-mail, delete this
communication, and destroy all copies.
Corporate Systems, Inc. has taken reasonable precautions to ensure that any
attachment to this e-mail has been swept for viruses. We specifically disclaim
all liability and will accept no responsibility for damage sustained as a
result of software viruses and advise you to carry out your own virus checks
before opening any attachment.
Other related posts:
