인덱스 생성
CREATE INDEX 유저명.인덱스명 ON 대상테이블명(대상컬럼명1,대상컬럼명2..);
인덱스 삭제
DROP INDEX 유저명.인덱스명;
인덱스 리빌드
ALTER INDEX 유저명.인덱스명 REBUILD;
ALTER INDEX 유저명.인덱스명 REBUILD PARALLEL 4; /* 인덱스 리빌드 병렬처리 */
ALTER INDEX 유저명.인덱스명 REBUILD ONLINE; /* 인덱스 리빌드 온라인 */
인덱스 UNUSABLE
ALTER INDEX 유저명.인덱스명 UNUSABLE;
인덱스 조회
SELECT OWNER,
INDEX_NAME,
INDEX_TYPE,
TABLE_OWNER,
TABLE_NAME,
UNIQUENESS,
TABLESPACE_NAME,
LOGGING,
STATUS,
LAST_ANALYZED
FROM DBA_INDEXES A
조회결과
OWNER|INDEX_NAME |INDEX_TYPE |TABLE_OWNER|TABLE_NAME |UNIQUENESS|TABLESPACE_NAME|LOGGING|STATUS|LAST_ANALYZED |
-----|------------------------------|---------------------|-----------|------------------------------|----------|---------------|-------|------|-------------------|
SYS |SYS_IL0000013507C00003$$ |LOB |SYS |JAVA$JVM$RJBC |UNIQUE |SYSTEM |YES |VALID | |
SYS |JAVA$JVM$RJBC$I |NORMAL |SYS |JAVA$JVM$RJBC |UNIQUE |SYSTEM |YES |VALID |2010-04-02 13:34:52|
SYS |JAVA$JVM$RUNTIME$PARAMETERS$I |NORMAL |SYS |JAVA$JVM$RUNTIME$PARAMETERS |UNIQUE |SYSTEM |YES |VALID |2010-04-02 13:34:52|
SYS |JAVA$MC$OBJN_IDX |NORMAL |SYS |JAVA$MC$ |NONUNIQUE |SYSTEM |YES |VALID |2010-04-02 13:34:52|
SYS |JAVA$MC$DEPS$OBJN_IDX |NORMAL |SYS |JAVA$MC$DEPS |NONUNIQUE |SYSTEM |YES |VALID |2010-04-02 13:34:52|
SYS |JAVA$METHOD$METADATA$$OBJN_IDX|NORMAL |SYS |JAVA$METHOD$METADATA |NONUNIQUE |SYSTEM |YES |VALID |2010-04-02 13:34:52|
SYS |SYS_IL0000000191C00005$$ |LOB |SYS |SQL$ |UNIQUE |SYSAUX |YES |VALID | |
SYS |I_SQL$_PKEY |NORMAL |SYS |SQL$ |UNIQUE |SYSAUX |YES |VALID |2010-04-02 13:28:51|
SYS |SYS_IL0000000195C00005$$ |LOB |SYS |SQL$TEXT |UNIQUE |SYSAUX |YES |VALID | |
SYS |SYS_IL0000000195C00003$$ |LOB |SYS |SQL$TEXT |UNIQUE |SYSAUX |YES |VALID | |
SYS |I_SQL$TEXT_PKEY |NORMAL |SYS |SQL$TEXT |UNIQUE |SYSAUX |YES |VALID |2010-04-02 13:28:51|
SYS |I_SQL$TEXT_HANDLE |NORMAL |SYS |SQL$TEXT |UNIQUE |SYSAUX |YES |VALID |2010-04-02 13:28:51|
SYS |SQLLOG$_PKEY |IOT - TOP |SYS |SQLLOG$ |UNIQUE |SYSAUX |YES |VALID |2010-04-02 13:28:51|
SYS |SQLOBJ$_PKEY |IOT - TOP |SYS |SQLOBJ$ |UNIQUE |SYSAUX |YES |VALID |2010-04-02 13:28:51|
SYS |SYS_IL0000000202C00009$$ |LOB |SYS |SQLOBJ$ |UNIQUE |SYSAUX |YES |VALID | |
인덱스 컬럼조회
select INDEX_OWNER,
INDEX_NAME,
TABLE_OWNER,
TABLE_NAME,
COLUMN_NAME,
COLUMN_POSITION,
COLUMN_LENGTH,
CHAR_LENGTH,
DESCEND
from DBA_IND_COLUMNS
where INDEX_NAME = 'I_TRIGGERCOL1'
ORDER BY COLUMN_POSITION
조회결과
INDEX_OWNER|INDEX_NAME |TABLE_OWNER|TABLE_NAME |COLUMN_NAME|COLUMN_POSITION|COLUMN_LENGTH|CHAR_LENGTH|DESCEND|
-----------|-------------|-----------|-----------|-----------|---------------|-------------|-----------|-------|
SYS |I_TRIGGERCOL1|SYS |TRIGGERCOL$|OBJ# | 1| 22| 0|ASC |
SYS |I_TRIGGERCOL1|SYS |TRIGGERCOL$|COL# | 2| 22| 0|ASC |
SYS |I_TRIGGERCOL1|SYS |TRIGGERCOL$|TYPE# | 3| 22| 0|ASC |
SYS |I_TRIGGERCOL1|SYS |TRIGGERCOL$|POSITION# | 4| 22| 0|ASC |
인덱스별 구성 컬럼 조회
SELECT A.OWNER,
A.INDEX_NAME,
A.INDEX_TYPE,
A.UNIQUENESS,
LISTAGG(B.COLUMN_NAME,' , ') WITHIN GROUP(ORDER BY COLUMN_POSITION) INDEX_COLUMNS
FROM DBA_INDEXES A, DBA_IND_COLUMNS B
WHERE A.OWNER=B.INDEX_OWNER
AND A.TABLE_NAME=B.TABLE_NAME
AND A.INDEX_NAME=B.INDEX_NAME
GROUP BY A.OWNER,A.INDEX_NAME,A.INDEX_TYPE,A.UNIQUENESS
조회결과
OWNER|INDEX_NAME |INDEX_TYPE |UNIQUENESS|INDEX_COLUMNS |
-----|-------------------------|---------------------|----------|-----------------------------------------------------------------------------------------------------------|
HR |JOB_ID_PK |NORMAL |UNIQUE |JOB_ID |
HR |LOC_ID_PK |NORMAL |UNIQUE |LOCATION_ID |
HR |REG_ID_PK |NORMAL |UNIQUE |REGION_ID |
HR |DEPT_ID_PK |NORMAL |UNIQUE |DEPARTMENT_ID |
HR |EMP_JOB_IX |NORMAL |NONUNIQUE |JOB_ID |
HR |EMP_NAME_IX |NORMAL |NONUNIQUE |LAST_NAME , FIRST_NAME |
HR |LOC_CITY_IX |NORMAL |NONUNIQUE |CITY |
HR |EMP_EMAIL_UK |NORMAL |UNIQUE |EMAIL |
HR |JHIST_JOB_IX |NORMAL |NONUNIQUE |JOB_ID |
HR |EMP_EMP_ID_PK |NORMAL |UNIQUE |EMPLOYEE_ID |
HR |EMP_MANAGER_IX |NORMAL |NONUNIQUE |MANAGER_ID |
HR |LOC_COUNTRY_IX |NORMAL |NONUNIQUE |COUNTRY_ID |
HR |COUNTRY_C_ID_PK |IOT - TOP |UNIQUE |COUNTRY_ID |
HR |DEPT_LOCATION_IX |NORMAL |NONUNIQUE |LOCATION_ID |
HR |EMP_DEPARTMENT_IX |NORMAL |NONUNIQUE |DEPARTMENT_ID |
HR |JHIST_EMPLOYEE_IX |NORMAL |NONUNIQUE |EMPLOYEE_ID |
HR |JHIST_DEPARTMENT_IX |NORMAL |NONUNIQUE |DEPARTMENT_ID |
HR |LOC_STATE_PROVINCE_IX |NORMAL |NONUNIQUE |STATE_PROVINCE |
HR |JHIST_EMP_ID_ST_DATE_PK |NORMAL |UNIQUE |EMPLOYEE_ID , START_DATE |
IX |SYS_C0010846 |NORMAL |UNIQUE |MSGID |
IX |SYS_C0010849 |NORMAL |UNIQUE |SUBSCRIBER_ID |
IX |SYS_C0010854 |NORMAL |UNIQUE |MSGID |
IX |SYS_C0010857 |NORMAL |UNIQUE |SUBSCRIBER_ID |
IX |SYS_IOT_TOP_74052 |IOT - TOP |UNIQUE |NEXT_DATE , TXN_ID , MSGID |
IX |SYS_IOT_TOP_74054 |IOT - TOP |UNIQUE |MSGID , SUBSCRIBER# , NAME , ADDRESS# |
IX |SYS_IOT_TOP_74057 |IOT - TOP |UNIQUE |MSGID , SUBSCRIBER# , NAME , ADDRESS# |
IX |SYS_IOT_TOP_74060 |IOT - TOP |UNIQUE |SUBSCRIBER# , NAME , QUEUE# , MSG_ENQ_TIME , MSG_STEP_NO , MSG_CHAIN_NO , MSG_LOCAL_ORDER_NO , MSGID |
IX |SYS_IOT_TOP_74070 |IOT - TOP |UNIQUE |NEXT_DATE , TXN_ID , MSGID |
IX |SYS_IOT_TOP_74072 |IOT - TOP |UNIQUE |MSGID , SUBSCRIBER# , NAME , ADDRESS# |
IX |SYS_IOT_TOP_74075 |IOT - TOP |UNIQUE |MSGID , SUBSCRIBER# , NAME , ADDRESS# |
IX |SYS_IOT_TOP_74078 |IOT - TOP |UNIQUE |SUBSCRIBER# , NAME , QUEUE# , MSG_ENQ_TID , SENDER# , TXN_STEP# , MSG_CHAIN_NO , MSG_LOCAL_ORDER_NO , MSGID|'DATABASE > ORACLE' 카테고리의 다른 글
| 오라클 시노님(동의어) 생성/삭제/조회 방법 (0) | 2020.01.13 |
|---|---|
| 오라클 시퀀스 생성/수정/삭제/조회 방법 (0) | 2020.01.13 |
| 오라클 제약조건 추가/삭제/비활성화/활성화 (0) | 2020.01.13 |
| 오라클 유저 생성 (0) | 2020.01.10 |
| 오라클 테이블스페이스 생성 (0) | 2020.01.10 |
