j, Would an index on the selected table column reduce the "ACCESS (FULL) OF 'CUENTASF' " and make it faster? Ron >>> jreyes@xxxxxxxxxxxxxxxx 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@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 -----------------------------------------------------------------