HighLight in your life~

대용량 테이블이고 DML(INSERT, UPDATE, DELETE 등)가 계속 일어나는 테이블에 신규 인덱스 생성을 하려면

Lock이 걸려서 서비스가 원활하지 않을 수 있습니다.

그럴 경우에 아래와 같이 ONLINE, NOLOGGING 옵션을 사용하면 좋습니다.

ONLINE은 서비스 중에 Lock이 걸리지 않고 인덱스 생성하는 옵션입니다.

NOLOGGING은 UNDO, REDO를 최소하면서 인덱스 생성할 수 있게 도와주는 옵션입니다.

1. 테이블 EMPLOYEE 에 기 생성된 인덱스 확인

SELECT IDX.OWNER, IDX.INDEX_NAME, IDX.TABLE_OWNER,
IDX.TABLE_NAME, IDX.TABLESPACE_NAME, IDX_COL.COLUMN_NAME
FROM DBA_INDEXES IDX, DBA_IND_COLUMNS IDX_COL
WHRE IDX.TABLE_NAME = 'EMPLOYEE'
AND IDX.INDEX_NAME=IDX_COL.INDEX_NAME
ORDER BY INDEX_NAME;

2. 아래의 구문을 사용한 인덱스 생성

CREATE INDEX EMP.IX_EMPLOYEE01 ON EMP.IX_EMPLOYEE(IDX01, IDX02, IDX03, IDX04)
NOLOGGING TABLESPACE TS_IDX_EMP LOCAL ONLINE;

3. 생성된 인덱스 10% 샘플링 통계수집

BEGIN
  SYS.DBMS_STATS.GATHER_INDEX_STATS (
     OwnName           => 'EMP'
    ,IndName           => 'IX_EMPLOYEE01'
    ,Estimate_Percent  => 10
    ,Degree            => 4
    ,No_Invalidate  => FALSE);
END;
/

감사합니다.

SELECT IDX.OWNER, IDX.INDEX_NAME, IDX.TABLE_OWNER,
IDX.TABLE_NAME, IDX.TABLESPACE_NAME, IDX_COL.COLUMN_NAME
FROM DBA_INDEXES IDX, DBA_IND_COLUMNS IDX_COL
WHRE IDX.TABLE_NAME = 'EMP'
AND IDX.INDEX_NAME=IDX_COL.INDEX_NAME
ORDER BY INDEX_NAME;

SQL> desc dba_indexes;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 OWNER NOT NULL VARCHAR2(30)
 INDEX_NAME NOT NULL VARCHAR2(30)
 INDEX_TYPE VARCHAR2(27)
 TABLE_OWNER NOT NULL VARCHAR2(30)
 TABLE_NAME NOT NULL VARCHAR2(30)
 TABLE_TYPE VARCHAR2(11)
 UNIQUENESS VARCHAR2(9)
 COMPRESSION VARCHAR2(8)
 PREFIX_LENGTH NUMBER
 TABLESPACE_NAME VARCHAR2(30)
 INI_TRANS NUMBER
 MAX_TRANS NUMBER
 INITIAL_EXTENT NUMBER
 NEXT_EXTENT NUMBER
 MIN_EXTENTS NUMBER
 MAX_EXTENTS NUMBER
 PCT_INCREASE NUMBER
 PCT_THRESHOLD NUMBER
 INCLUDE_COLUMN NUMBER
 FREELISTS NUMBER
 FREELIST_GROUPS NUMBER
 PCT_FREE NUMBER
 LOGGING VARCHAR2(3)
 BLEVEL NUMBER
 LEAF_BLOCKS NUMBER
 DISTINCT_KEYS NUMBER
 AVG_LEAF_BLOCKS_PER_KEY NUMBER
 AVG_DATA_BLOCKS_PER_KEY NUMBER
 CLUSTERING_FACTOR NUMBER
 STATUS VARCHAR2(8)
 NUM_ROWS NUMBER
 SAMPLE_SIZE NUMBER
 LAST_ANALYZED DATE
 DEGREE VARCHAR2(40)
 INSTANCES VARCHAR2(40)
 PARTITIONED VARCHAR2(3)
 TEMPORARY VARCHAR2(1)
 GENERATED VARCHAR2(1)
 SECONDARY VARCHAR2(1)
 BUFFER_POOL VARCHAR2(7)
 USER_STATS VARCHAR2(3)
 DURATION VARCHAR2(15)
 PCT_DIRECT_ACCESS NUMBER
 ITYP_OWNER VARCHAR2(30)
 ITYP_NAME VARCHAR2(30)
 PARAMETERS VARCHAR2(1000)
 GLOBAL_STATS VARCHAR2(3)
 DOMIDX_STATUS VARCHAR2(12)
 DOMIDX_OPSTATUS VARCHAR2(6)
 FUNCIDX_STATUS VARCHAR2(8)
 JOIN_INDEX VARCHAR2(3)

 SQL> desc dba_ind_columns;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 INDEX_OWNER NOT NULL VARCHAR2(30)
 INDEX_NAME NOT NULL VARCHAR2(30)
 TABLE_OWNER NOT NULL VARCHAR2(30)
 TABLE_NAME NOT NULL VARCHAR2(30)
 COLUMN_NAME VARCHAR2(4000)
 COLUMN_POSITION NOT NULL NUMBER
 COLUMN_LENGTH NOT NULL NUMBER
 CHAR_LENGTH NUMBER
 DESCEND VARCHAR2(4)

 SQL> desc v$object_usage;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 INDEX_NAME NOT NULL VARCHAR2(30)
 TABLE_NAME NOT NULL VARCHAR2(30)
 MONITORING VARCHAR2(3)
 USED VARCHAR2(3)
 START_MONITORING VARCHAR2(19)
 END_MONITORING VARCHAR2(19)

 SQL> spool off

토드 사용시 OWNER.TABLE_NAME 에 커서를 위치시키고 F4 를 누르면 별도의 Description을 확인할 수 있습닏.