| 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
Mladen,
I understand and agree on what you state about realizing that there
is a problem, developing a plan to fix the problem, and then asking for
assistance if the percieved problem still exists.
>From what I gather reading the question is that I insert 4965 rows of
data from another table, Why does it take so long?
I re-read you reply and digested it more completely. Good response.
Yankee humor is rubbing off on you or is it the fact that Danbury might
get a Martha Stewart face lift soon?
Ron
>>> mladen_at_wangtrading.com 03/12/2004 8:59:19 AM >>>
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
> -----------------------------------------------------------------
>
-- 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:38:11 CST
 |
 |