|
월 1500만의 Data 가 입력이 되는 Table가 있습니다.
이넘이 I/O가 많이 발생하여서 입력이 지연이 되서, I/O분산을 위해 파티셔닝 처리를 하려고 하는데요..
아 이넘이 파티셔닝해도 속도가 크게 개선이 안되서요..
일단 테이블 구조를 올리겠습니다.
물론 트리거가 걸려 있구요 트리거는 젤 마지막에 있습니다.
시퀀스를 이용하여 고유 일련번호를 부여 하는거구요 그 트리거를 위해서
다음과 같은 Table가 존재합니다.
데이타 입력은 C++을 이용한 프로그램에서 파일을 읽어서 입력합니다.
한개의 파일에는 약 150건의 데이타가 있고 한개의 행씩 읽어서 인서트를 하고 다입력이 되었을때 커밋이 됩니다.
파티셔닝을 위와같이 무식하게 한것은 중복을 체크하기 위해서 어쩔수 없이 위와같이 무식한 방법으로 파티션을 나눴습니다.
파티션은 고유번호(필드명 seqno)로 나누는게 젤 좋은 방법인거 같은데 트리거를 이용하여 입력이 되기에 사용을 하지 못했구요..
차선책으로 일자별로 나누고 싶지만 일자별로 나눌경우에는 PK 설정이나 Uniqe index로 설정을 할수 없어서 위와 같이 무식하게 설정하였습니다.
문제는 이넘들이 입력이 되면 될수록 속도가 저하가 된다는 사실입니다. 매월 1500만건씩 입력을 하는데 단시간에 입력이 이루어 집니다.
1500만건을 7일주야를 통해서 입력을 하는거죠. loader 등을 쓰고 싶지만 계속 Data를 사용하기 때문에 loader로는 입력이 어려울것 같구요
보통 저희가 4개 그룹으로 나뉘어서 입력이 되는데(group_name) 한개는 약 800만, 한군데는 500만, 나머지 두군데는 100만씩 입력이 됩니다.
4개의 쓰레드가 입력을 하구있구요..각 그룹별로요..
아 말이 자꾸 삼천포로 가네요..
한개의 쓰레드가 입력되는 약을 보면..최초에는 80개/초 이상입력이 되다가 나중에는 27/개 로 떨어진다는 겁니다.
이넘들을 좀 빨리 넣을수 있는 방법이 없을까요?
파티셔닝 하기 전에는 테이블을 2일정도 입력하고, 리빌드 해서 또 2일정도 입력하고, 리빌드하고(리빌드해서 테이블명을 바꾸고 view로 묶어서 사용) 했는데.. 논리적으로는 파티션한거나 리빌드해서 3개로 나눈거나 비슷하다고 생각은 하거든요..
파티셔닝시 기본 분할필드가 설정이 잘못된거 같기도 하고, 인덱스들이 문제 인거 같기도 하고.. 아 암튼 괴롭습니다. 벌써 2달이 넘게 Test 를 하고 있는데 방법이 안보이네요..
고수님들 제발좀 살려주세요~~~
CREATE TABLE TEST_BARCODE_DATA
(
SNO NUMBER(12) NOT NULL,
GROUP_NAME VARCHAR2(10 BYTE) NOT NULL,
LINE_NO NUMBER(4) NOT NULL,
CODE_DATA VARCHAR2(50 BYTE) NOT NULL,
CODE_ACNT CHAR(10 BYTE) NOT NULL,
READ_DATE VARCHAR2(8 BYTE) NOT NULL,
READ_TIME VARCHAR2(9 BYTE) NOT NULL,
STATUS CHAR(1 BYTE) DEFAULT 'G',
SEQNO NUMBER,
BARCODE1 VARCHAR2(15 BYTE)
)
TABLESPACE MGPOP
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
LOGGING
PARTITION BY RANGE (CODE_DATA)
(
PARTITION TEST_BAECODE_DATA_PART1 VALUES LESS THAN ('0119900000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_1
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART2 VALUES LESS THAN ('0139900000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_2
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART3 VALUES LESS THAN ('0152100000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_3
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART4 VALUES LESS THAN ('0152200000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_4
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART5 VALUES LESS THAN ('0152300000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_5
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART6 VALUES LESS THAN ('0152400000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_6
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART7 VALUES LESS THAN ('0152500000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_7
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART8 VALUES LESS THAN ('0152600000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_8
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART9 VALUES LESS THAN ('0152700000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_9
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART10 VALUES LESS THAN ('0152800000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_10
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART11 VALUES LESS THAN ('0152900000000000000000000000000000000000000')
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_1
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_PART12 VALUES LESS THAN (MAXVALUE)
LOGGING
NOCOMPRESS
TABLESPACE SDTMC_2
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL;
CREATE INDEX TEST_BARCODE_DATA_IDX1 ON TEST_BARCODE_DATA
(SNO, GROUP_NAME, LINE_NO)
INITRANS 2
MAXTRANS 255
LOGGING
LOCAL (
PARTITION TEST_BAECODE_DATA_IDX_PART1
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_1_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART2
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_2_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART3
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_3_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART4
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_4_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART5
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_5_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART6
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_6_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART7
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_7_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART8
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_8_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART9
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_9_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART10
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_10_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART11
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_1_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART12
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_2_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
NOPARALLEL;
CREATE INDEX TEST_BARCODE_DATA_IDX2 ON TEST_BARCODE_DATA
(CODE_ACNT, READ_DATE, READ_TIME)
INITRANS 2
MAXTRANS 255
LOGGING
LOCAL (
PARTITION TEST_BAECODE_DATA_IDX_PART1
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_1_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART2
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_2_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART3
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_3_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART4
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_4_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART5
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_5_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART6
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_6_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART7
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_7_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART8
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_8_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART9
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_9_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART10
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_10_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART11
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_1_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART12
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_2_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
NOPARALLEL;
CREATE INDEX TEST_BARCODE_DATA_IDX3 ON TEST_BARCODE_DATA
(GROUP_NAME, LINE_NO, CODE_ACNT)
INITRANS 2
MAXTRANS 255
LOGGING
LOCAL (
PARTITION TEST_BAECODE_DATA_IDX_PART1
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_1_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART2
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_2_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART3
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_3_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART4
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_4_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART5
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_5_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART6
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_6_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART7
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_7_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART8
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_8_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART9
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_9_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART10
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_10_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART11
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_1_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART12
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_2_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
NOPARALLEL;
CREATE UNIQUE INDEX TEST_BARCODE_DATA_IDX6 ON TEST_BARCODE_DATA
(CODE_DATA)
INITRANS 2
MAXTRANS 255
LOGGING
LOCAL (
PARTITION TEST_BAECODE_DATA_IDX_PART1
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_1_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART2
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_2_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART3
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_3_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART4
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_4_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART5
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_5_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART6
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_6_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART7
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_7_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART8
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_8_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART9
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_9_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART10
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_10_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART11
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_1_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART12
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_2_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
NOPARALLEL;
CREATE INDEX TST_BARCODE_DATA_IDXSEQNO ON TEST_BARCODE_DATA
(SEQNO)
INITRANS 2
MAXTRANS 255
LOGGING
LOCAL (
PARTITION TEST_BAECODE_DATA_IDX_PART1
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_1_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART2
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_2_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART3
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_3_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART4
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_4_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART5
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_5_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART6
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_6_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART7
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_7_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART8
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_8_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART9
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_9_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART10
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_10_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART11
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_1_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION TEST_BAECODE_DATA_IDX_PART12
NOLOGGING
NOCOMPRESS
TABLESPACE SDTMC_2_IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER TRIGGER_TEST_BEFORE_INSERT
BEFORE INSERT ON TEST_BARCODE_DATA
FOR EACH ROW
Declare
v_Sep Number;
BEGIN
IF INSERTING THEN
SELECT test_barcode_data_seq.NEXTVAL INTO :new.seqno FROM dual;
Update SCAN_STATUS1 Set
SCAN_ACNT = :NEW.CODE_ACNT,
SCAN_DATETIME = :NEW.READ_DATE||:NEW.READ_TIME
Where SCAN_GROUP = :NEW.GROUP_NAME
And SCAN_LINE = :NEW.LINE_NO
And SCAN_YYYYMM = substr(:NEW.READ_DATE, 1, 6);
If SQL%NOTFOUND Then
Insert Into SCAN_STATUS1 values (
:NEW.GROUP_NAME,
:NEW.LINE_NO,
substr(:NEW.READ_DATE, 1, 6),
:NEW.CODE_ACNT,
:NEW.READ_DATE||:NEW.READ_TIME);
END IF;
END IF;
END TRIGGER_test_AFTER_INSERT;
/
SHOW ERRORS;
CREATE TABLE SCAN_STATUS1
(
SCAN_GROUP VARCHAR2(10 BYTE) NOT NULL,
SCAN_LINE NUMBER(4) NOT NULL,
SCAN_YYYYMM VARCHAR2(6 BYTE) NOT NULL,
SCAN_ACNT CHAR(10 BYTE) NOT NULL,
SCAN_DATETIME VARCHAR2(17 BYTE) NOT NULL
)
TABLESPACE MGPOP
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE UNIQUE INDEX SCAN_STATUS_PK1_0606 ON SCAN_STATUS1
(SCAN_GROUP, SCAN_LINE, SCAN_YYYYMM)
NOLOGGING
TABLESPACE MPOP
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE SCAN_STATUS1 ADD (
CONSTRAINT DSCAN_STATUS_PK1_0606 PRIMARY KEY (SCAN_GROUP, SCAN_LINE, SCAN_YYYYMM));
GRANT DELETE, INSERT, SELECT ON SCAN_STATUS1 TO GO015B;
|
