Re: how to optimize insert into t select * from t2

From: Mladen Gogala <mladen_at_wangtrading.com>
Date: Fri, 12 Mar 2004 08:59:19 -0500
Message-ID: <20040312135919.GG1600@mladen.wangtrading.com>

On 03/12/2004 08:45:42 AM, Ron Rogers wrote:
> j,
> Would an index on the selected table column reduce the "ACCESS (FULL)
> OF 'CUENTASF' " and make it faster?

It probably would, if he had a "WHERE" condition, but he doesn't have one. May be "append" hint would speed things up or parallelizing both CUENTASF table and the target table. Truncating CUENTASF table before that statement would also speed things up tremendously, as would reading of the manuals. I don't like the way the question was asked: "here's my INSERT, optimize it", without anything that he considered or has done, or anything at all that would show that he invested some brain effort in the question before asking it. If he hasn't invested a mental second, why would I do it?

.
> Ron
>
> >>> jreyes_at_dazasoftware.com 03/12/2004 8:34:29 AM >>>
> 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 - 07:57:54 CST