| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_dazasoftware.com]
Sent: Friday, March 12, 2004 2:34 PM
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
)
/
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
-- 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:30:43 CST
 |
 |