PK 컬럼을 포함하는 일반 인덱스를 만들어도 될까?

성능 개선을 위해 PK 컬럼 NO를 선두로하는 인덱스를 만들어야 한다.  고민이 많아진다.  PK 컬럼을 선두컬럼으로 하는 인덱스를 만드는게 맞는건가?  PK 인덱스는 빠른거 아닌가?  PK 뒤에 넣는것도 찝찝하고. 컬럼 중복 인거 같기도하고... 당황스럽다. 

 

경험을 쌓다보니, 성능 개선을 위해 반드시 필요하면 신규 생성하는 것은 옳은 방법임을 알게 되었다. 단, 명확한 사유가 있어야야 한다. 당연히 성능 개선을 위한 것이지만, 오성고 저자분의 의견은 아래와 같이 명확하다.

 

   1) TB_TEST_PK : 중복 방지용 ( PK )
   2) TB_TEST_IDX01 : 조회용 ( PK를 포함하는 인덱스  ) 

 

아래 TEST로 어떻게 구성하는지 확인!

 

[ PK 컬럼을 포함하는 인덱스 생성 ]    

[1] 테이블 생성
DROP TABLE TB_TEST PURGE;
CREATE TABLE TB_TEST
(
   NO       NUMBER NOT NULL
 , YYYYMMDD VARCHAR2(8) NOT NULL
 , KEY1     VARCHAR2(12) 
 , KEY2     VARCHAR2(12) 
 , COL1     VARCHAR2(8)
 , COL2     VARCHAR2(8)
)
;


[2] PK 생성 
CREATE UNIQUE INDEX TB_TEST_PK ON TB_TEST ( NO ) ;
ALTER TABLE TB_TEST ADD CONSTRAINT TB_TEST_PK PRIMARY KEY ( NO );
;


[3] 성능 개선을 위해 PK 컬럼을 포함하는 인덱스 필요성   
 : INDEX에서 필터처리가 되지 않아, 많은 TABLE RANDOM I/O로 성능부하 발생한 경우 
   FILTER 처리가 우수한 컬럼을 인덱스 구성컬럼에 추가하여 TABLE RANDOM I/O를 감소하는 시키기 방법
  
* 필요 인덱스 
CREATE INDEX TB_TEST_IDX01 ON TB_TEST ( NO, KEY1, COL1 );


[4] 2가지 방법 
  1) PK INDEX(TB_TEST_PK) 와 일반 INDEX(TB_TEST_IDX01) 공존
   : 공간 낭비는 있으나, 운영 업무에서 PK 인덱스와 제약조건 변경은 위험하므로 보통 이와 같이 작업됨.
     CREATE INDEX TB_TEST_IDX01 ON TB_TEST ( NO, KEY1, COL1 );

  2) 일반 INDEX(TB_TEST_IDX01)와 PK 제약조건 공존 
   : PK 인덱스는 제거하되, PK 제약조건을 별도 설정. 공간 낭비가 없음. 
     신규 프로젝트인 경우 가능하게 보이나 명확성(인덱스명에 _PK 없음)이 부족해보임.
     개인경험으로는 사용하는 곳 본 적 없음. 
     
    CREATE INDEX TB_TEST_IDX01 ON TB_TEST ( NO, KEY1, KEY2 );
    ALTER TABLE TB_TEST DROP PRIMARY KEY; 
    ALTER TABLE TB_TEST ADD CONSTRAINT TB_TEST_PK PRIMARY KEY ( NO ) USING INDEX TB_TEST_IDX01;
    DROP INDEX TB_TEST_PK;

[5] 확인 
  SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'TB_TEST';
  SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, INDEX_NAME FROM DBA_CONSTRAINTS A WHERE TABLE_NAME= 'TB_TEST' AND CONSTRAINT_TYPE ='P';

[ 참고 ]
 ALTER TABLE TB_TEST ADD CONSTRAINT TB_TEST_PK PRIMARY KEY ( NO );
 위와 같이 "USING INDEX 인덱스명"을 제거하고 실행해도 PK 설정 된다. 
 유의할 것은 NO 컬럼을 선두로 하는 인덱스 중 가장 먼저 생성된 인덱스로 설정된다는 것이다.
 그러므로 필히 기입하는 것이 옳다.