Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

대상 버전

6.1.1 이하 버전

증상

파티션 테이블에 PK 또는 UNIQUE INDEX 생성 시 다음 에러 발생.

...

다음과 같이 altierr 유틸리티를 이용하여 해당 에러에 대한 설명을 확인할 수 있습니다.

Panel

$ altierr 0x31283

0x31283 ( 201347) qpERR_ABORT_QDX_NOT_ALLOWED_PRIMARY_AND_UNIQUE_KEY_OF_NONE_PREFIXED_INDEX Unable to create a primary key or a unique key constraint in the local non-prefixed index.
# - The user tried to create a primary key or a unique key constraint in the local prefixed index.
# *Action:
# - Please do not create a primary key or a unique key constraint in the local non-prefixed index.

알티베이스 6.1.1 이하 버전에서는 global index를 지원하지 않습니다.

...

3. 알티베이스를 6.3.1 이상 버전으로 업그레이드 하면 global index로 PK나 UNIQUE INDEX 생성이 가능합니다.

 

# 예제

Code Block

iSQL> CREATE TABLE REALSET_CONTENTS
2 (
3 CT_ID VARCHAR (32) NOT NULL,
4 CT_TYPE VARCHAR (2) NOT NULL,
5 CT_PATH VARCHAR (256) NOT NULL,
6 CT_URL VARCHAR (256) NOT NULL,
7 REG_DATE DATE NOT NULL,
8 FILE_NAME VARCHAR (256) NOT NULL,
9 STATUS VARCHAR (4) NOT NULL
10 )
11 PARTITION BY RANGE (REG_DATE)
12 (
13 PARTITION P_1 VALUES LESS THAN (to_date('2013-05-01', 'YYYY-MM-DD')),
14 PARTITION P_2 VALUES LESS THAN (to_date('2013-09-01', 'YYYY-MM-DD')),
15 PARTITION P_DEF VALUES DEFAULT
16 )
17 TABLESPACE SYS_TBS_DISK_DATA;
Create success.
iSQL> alter table REALSET_CONTENTS add primary key(CT_ID,REG_DATE);
[ERR-31283 : Unable to create a primary key or a unique key constraint in the local non-prefixed index.]

1. PK 컬럼 순서를 바꾸어 local prefixed index를 PK로 생성하는 예제.

Code Block

iSQL> alter table REALSET_CONTENTS add primary key(REG_DATE,CT_ID);
Alter success.
iSQL> desc REALSET_CONTENTS
[ TABLESPACE : SYS_TBS_DISK_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME                                     TYPE                        IS NULL
------------------------------------------------------------------------------
CT_ID                                    VARCHAR(32)                 NOT NULL
CT_TYPE                                  VARCHAR(2)                  NOT NULL
CT_PATH                                  VARCHAR(256)                NOT NULL
CT_URL                                   VARCHAR(256)                NOT NULL
REG_DATE                                 DATE                        NOT NULL
FILE_NAME                                VARCHAR(256)                NOT NULL
STATUS                                   VARCHAR(4)                  NOT NULL
[ INDEX ]
------------------------------------------------------------------------------
NAME                                     TYPE     IS UNIQUE     COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_142                         BTREE    UNIQUE        REG_DATE ASC,
                                                                CT_ID ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
REG_DATE, CT_ID

2. local non-prefixed index를 PK로 생성하지 않고 non-unique index로 생성하는 예제.

Code Block

iSQL> create index REALSET_CONTENTS_IDX1 on REALSET_CONTENTS(CT_ID,REG_DATE) local;
Create success.
iSQL> desc REALSET_CONTENTS
[ TABLESPACE : SYS_TBS_DISK_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME                                     TYPE                        IS NULL
------------------------------------------------------------------------------
CT_ID                                    VARCHAR(32)                 NOT NULL
CT_TYPE                                  VARCHAR(2)                  NOT NULL
CT_PATH                                  VARCHAR(256)                NOT NULL
CT_URL                                   VARCHAR(256)                NOT NULL
REG_DATE                                 DATE                        NOT NULL
FILE_NAME                                VARCHAR(256)                NOT NULL
STATUS                                   VARCHAR(4)                  NOT NULL
[ INDEX ]
------------------------------------------------------------------------------
NAME                                     TYPE     IS UNIQUE     COLUMN
------------------------------------------------------------------------------
REALSET_CONTENTS_IDX1                    BTREE                  CT_ID ASC,
                                                                REG_DATE ASC
REALSET_CONTENTS has no primary key

3. 6.3.1 버전으로 업그레이드 후 global index로 PK를 생성하는 예제.

Code Block

iSQL>  alter table REALSET_CONTENTS add primary key(CT_ID,REG_DATE);
Alter success.
iSQL> desc REALSET_CONTENTS
[ TABLESPACE : SYS_TBS_DISK_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME                                     TYPE                        IS NULL
------------------------------------------------------------------------------
CT_ID                                    VARCHAR(32)                 NOT NULL
CT_TYPE                                  VARCHAR(2)                  NOT NULL
CT_PATH                                  VARCHAR(256)                NOT NULL
CT_URL                                   VARCHAR(256)                NOT NULL
REG_DATE                                 DATE                        NOT NULL
FILE_NAME                                VARCHAR(256)                NOT NULL
STATUS                                   VARCHAR(4)                  NOT NULL
[ INDEX ]
------------------------------------------------------------------------------
NAME                                     TYPE     IS UNIQUE     COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_922                         BTREE    UNIQUE        CT_ID ASC,
                                                                REG_DATE ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
CT_ID, REG_DATE

...

분류조건1

분류조건2

분류조건3

인덱스 종류

알티베이스 지원여부

인덱스가 파티션 되어 있음.

index part key = table part key

index part key = index key

(Partitioned) Local prefixed Index

지원

 

 

index part key != index key

(Partitioned) Local nonprefixed Index

지원

 

index part key != table part key

index part key = index key

(Partitioned) Global prefixed Index

미지원

 

 

index part key != index key

(Partitioned) Global nonprefixed Index

미지원

인덱스가 파티션 되어 있지 않음.

 

 

Nonpartitioned global index

6.3.1 이상 버전에서 지원

...