RE: FW: insert into partitioned table
- From: "Yasin Baskan" <yasbs@xxxxxxxxxxxxxx>
- To: "Martic Zoran" <zoran_martic@xxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
- Date: Mon, 4 Apr 2005 17:52:51 +0300
This is the script for the non-partitioned table.
CREATE TABLE EVENT
(
ISPEC VARCHAR2(5 BYTE) DEFAULT ' '
NOT NULL,
TRANNO NUMBER(6) DEFAULT 0
NOT NULL,
INPUT_DATE VARCHAR2(7 BYTE) DEFAULT ' '
NOT NULL,
ACTMTH NUMBER(4) DEFAULT 0
NOT NULL,
CA_GL_IND VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
DAYS_CLEAR NUMBER(3) DEFAULT 0
NOT NULL,
GL_ACCOUNT VARCHAR2(12 BYTE) DEFAULT ' '
NOT NULL,
INPUTDATE NUMBER(6) DEFAULT 0
NOT NULL,
OD_IND VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
ORIG_CN_NO NUMBER(12) DEFAULT 0
NOT NULL,
OVER_FLUCT VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
SUP_IND VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
VAL_DATE NUMBER(6) DEFAULT 0
NOT NULL,
REL_DAY NUMBER(5) DEFAULT 0
NOT NULL,
DEPT VARCHAR2(8 BYTE) DEFAULT ' '
NOT NULL,
POST_NO NUMBER(7) DEFAULT 0
NOT NULL,
CHEQUE VARCHAR2(16 BYTE) DEFAULT ' '
NOT NULL,
TRANTYPE VARCHAR2(2 BYTE) DEFAULT ' '
NOT NULL,
MINMAX_FLG VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
OLD_DATE NUMBER(6) DEFAULT 0
NOT NULL,
BAL_UPDATD VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
REVERSE_NO NUMBER(7) DEFAULT 0
NOT NULL,
NARR_CODE VARCHAR2(3 BYTE) DEFAULT ' '
NOT NULL,
INPUT_REL NUMBER(5) DEFAULT 0
NOT NULL,
BRANCH VARCHAR2(4 BYTE) DEFAULT ' '
NOT NULL,
AUD_SEQNO NUMBER(7) DEFAULT 0
NOT NULL,
CH_TRANS VARCHAR2(4 BYTE) DEFAULT ' '
NOT NULL,
KW_CAAC VARCHAR2(16 BYTE) DEFAULT ' '
NOT NULL,
SOFTLOCK NUMBER(2) DEFAULT 0
NOT NULL,
STATEMENT NUMBER(9) DEFAULT 0
NOT NULL,
POST_NARR VARCHAR2(34 BYTE) DEFAULT ' '
NOT NULL,
POST_DATE NUMBER(6) DEFAULT 0
NOT NULL,
ORIG_ISPEC VARCHAR2(5 BYTE) DEFAULT ' '
NOT NULL,
AC_SRV_REF VARCHAR2(16 BYTE) DEFAULT ' '
NOT NULL,
AC_OWN_REF VARCHAR2(16 BYTE) DEFAULT ' '
NOT NULL,
TRANS_ID VARCHAR2(4 BYTE) DEFAULT ' '
NOT NULL,
NO_ITEMS NUMBER(3) DEFAULT 0
NOT NULL,
VALBAL_UPD VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
VALBAL_RDN NUMBER(5) DEFAULT 0
NOT NULL,
STATCODE NUMBER(5) DEFAULT 0
NOT NULL,
CLIENT_NO NUMBER(8) DEFAULT 0
NOT NULL,
AVA_DATE NUMBER(6) DEFAULT 0
NOT NULL,
AVA_RDN NUMBER(5) DEFAULT 0
NOT NULL,
BV_IND VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
USERCODE VARCHAR2(10 BYTE) DEFAULT ' '
NOT NULL,
AMOUNT NUMBER(18,2) DEFAULT 0
NOT NULL,
PRT_FLG VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
AVA_UPDATD VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
BATCH_NO NUMBER(12) DEFAULT 0
NOT NULL,
HOLD_NO NUMBER(6) DEFAULT 0
NOT NULL,
GLB_REPORT CHAR(1 BYTE) DEFAULT ' '
NOT NULL,
LAST_LINE NUMBER(3) DEFAULT 0
NOT NULL
)
TABLESPACE EVENT_DATA
NOLOGGING=20
NOCACHE
NOPARALLEL;
CREATE INDEX POSTCDACC ON EVENT
(ORIG_CN_NO, REL_DAY, AUD_SEQNO)
NOLOGGING
TABLESPACE EVENT_INDEX
NOPARALLEL;
CREATE INDEX POSTSTDIP ON EVENT
(BRANCH, KW_CAAC, INPUT_REL, POST_NO)
NOLOGGING
TABLESPACE EVENT_INDEX
NOPARALLEL;
CREATE INDEX POSTSTMTD ON EVENT
(BRANCH, KW_CAAC, REL_DAY, POST_NO)
NOLOGGING
TABLESPACE EVENT_INDEX
NOPARALLEL;
And this is the one for the partitioned table:
CREATE TABLE EVENT_PAR
(
ISPEC VARCHAR2(5 BYTE) DEFAULT ' '
NOT NULL,
TRANNO NUMBER(6) DEFAULT 0
NOT NULL,
INPUT_DATE VARCHAR2(7 BYTE) DEFAULT ' '
NOT NULL,
ACTMTH NUMBER(4) DEFAULT 0
NOT NULL,
CA_GL_IND VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
DAYS_CLEAR NUMBER(3) DEFAULT 0
NOT NULL,
GL_ACCOUNT VARCHAR2(12 BYTE) DEFAULT ' '
NOT NULL,
INPUTDATE NUMBER(6) DEFAULT 0
NOT NULL,
OD_IND VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
ORIG_CN_NO NUMBER(12) DEFAULT 0
NOT NULL,
OVER_FLUCT VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
SUP_IND VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
VAL_DATE NUMBER(6) DEFAULT 0
NOT NULL,
REL_DAY NUMBER(5) DEFAULT 0
NOT NULL,
DEPT VARCHAR2(8 BYTE) DEFAULT ' '
NOT NULL,
POST_NO NUMBER(7) DEFAULT 0
NOT NULL,
CHEQUE VARCHAR2(16 BYTE) DEFAULT ' '
NOT NULL,
TRANTYPE VARCHAR2(2 BYTE) DEFAULT ' '
NOT NULL,
MINMAX_FLG VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
OLD_DATE NUMBER(6) DEFAULT 0
NOT NULL,
BAL_UPDATD VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
REVERSE_NO NUMBER(7) DEFAULT 0
NOT NULL,
NARR_CODE VARCHAR2(3 BYTE) DEFAULT ' '
NOT NULL,
INPUT_REL NUMBER(5) DEFAULT 0
NOT NULL,
BRANCH VARCHAR2(4 BYTE) DEFAULT ' '
NOT NULL,
AUD_SEQNO NUMBER(7) DEFAULT 0
NOT NULL,
CH_TRANS VARCHAR2(4 BYTE) DEFAULT ' '
NOT NULL,
KW_CAAC VARCHAR2(16 BYTE) DEFAULT ' '
NOT NULL,
SOFTLOCK NUMBER(2) DEFAULT 0
NOT NULL,
STATEMENT NUMBER(9) DEFAULT 0
NOT NULL,
POST_NARR VARCHAR2(34 BYTE) DEFAULT ' '
NOT NULL,
POST_DATE NUMBER(6) DEFAULT 0
NOT NULL,
ORIG_ISPEC VARCHAR2(5 BYTE) DEFAULT ' '
NOT NULL,
AC_SRV_REF VARCHAR2(16 BYTE) DEFAULT ' '
NOT NULL,
AC_OWN_REF VARCHAR2(16 BYTE) DEFAULT ' '
NOT NULL,
TRANS_ID VARCHAR2(4 BYTE) DEFAULT ' '
NOT NULL,
NO_ITEMS NUMBER(3) DEFAULT 0
NOT NULL,
VALBAL_UPD VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
VALBAL_RDN NUMBER(5) DEFAULT 0
NOT NULL,
STATCODE NUMBER(5) DEFAULT 0
NOT NULL,
CLIENT_NO NUMBER(8) DEFAULT 0
NOT NULL,
AVA_DATE NUMBER(6) DEFAULT 0
NOT NULL,
AVA_RDN NUMBER(5) DEFAULT 0
NOT NULL,
BV_IND VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
USERCODE VARCHAR2(10 BYTE) DEFAULT ' '
NOT NULL,
AMOUNT NUMBER(18,2) DEFAULT 0
NOT NULL,
PRT_FLG VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
AVA_UPDATD VARCHAR2(1 BYTE) DEFAULT ' '
NOT NULL,
BATCH_NO NUMBER(12) DEFAULT 0
NOT NULL,
HOLD_NO NUMBER(6) DEFAULT 0
NOT NULL,
GLB_REPORT CHAR(1 BYTE) DEFAULT ' '
NOT NULL,
LAST_LINE NUMBER(3) DEFAULT 0
NOT NULL
)
TABLESPACE EVENT_DATA
NOLOGGING
PARTITION BY RANGE (INPUT_REL)=20
( =20
PARTITION EVENT2005_1 VALUES LESS THAN (17563)
NOLOGGING
NOCOMPRESS
TABLESPACE EVENT_DATA, =20
PARTITION EVENT2005_2 VALUES LESS THAN (17591)
NOLOGGING
NOCOMPRESS
TABLESPACE EVENT_DATA, =20
PARTITION EVENT2005_3 VALUES LESS THAN (17622)
NOLOGGING
NOCOMPRESS
TABLESPACE EVENT_DATA
)
NOCACHE
NOPARALLEL;
CREATE INDEX POSTCDACC_PAR ON EVENT_PAR
(ORIG_CN_NO, REL_DAY, AUD_SEQNO)
NOLOGGING
TABLESPACE EVENT_INDEX
NOPARALLEL;
CREATE INDEX POSTSTDIP_PAR ON EVENT_PAR
(BRANCH, KW_CAAC, INPUT_REL, POST_NO)
NOLOGGING
TABLESPACE EVENT_INDEX
NOPARALLEL;
CREATE INDEX POSTSTMTD_PAR ON EVENT_PAR
(BRANCH, KW_CAAC, REL_DAY, POST_NO)
NOLOGGING
TABLESPACE EVENT_INDEX
NOPARALLEL;
The insert scripts are:
Insert /*+ append */ into event select * from event2;
Commit;
Insert /*+ append */ into event_par select * from event2;
Commit;
-----Original Message-----
From: Martic Zoran [mailto:zoran_martic@xxxxxxxxx]=20
Sent: Monday, April 04, 2005 11:15 AM
To: Yasin Baskan; Oracle-L@xxxxxxxxxxxxx
Subject: Re: FW: insert into partitioned table
Show us the DDL for both tables and indexes extracted
from the database and not from the creation script.
Any global index on the table?
How are you doing these inserts? With commit at the
end?
Show us the method doing inserts.
Regards,
Zoran
=09
__________________________________=20
Yahoo! Messenger=20
Show us what our next emoticon should look like. Join the fun.=20
http://www.advision.webevents.yahoo.com/emoticontest
--
//www.freelists.org/webpage/oracle-l
Other related posts:
