Top 38 오라클 인덱스 조회 5795 Votes This Answer

See more articles in the same category here: toplist.maxfit.vn/blog.

[오라클] 인덱스 조회 방법 (Index)

오라클(Oracle) 인덱스 조회 쿼리

오라클 쿼리를 작성하기 전에 필수적으로 인덱스(Index)를 확인해야 한다. 인덱스를 무시하고 쿼리를 작성하면 심각한 성능 이슈가 발생 할 수 있다. 인덱스는 오라클 시스템뷰의 하나인 ALL_IND_COLUMNS 뷰를 사용하여 쉽게 조회가 가능하다.

SELECT a.table_name , a.index_name , a.column_name FROM all_ind_columns a WHERE a.table_name = ‘EMP’ ORDER BY a.index_name , a.column_position

[컬럼 코멘트를 포함하여 조회]

SELECT a.table_name , a.index_name , a.column_name , b.comments FROM all_ind_columns a , all_col_comments b WHERE a.table_name = ‘EMP’ AND a.table_owner = b.owner AND a.table_name = b.table_name AND a.column_name = b.column_name ORDER BY a.index_name , a.column_position

사용자에 따라서 사용할 수 있는 뷰가 달라진다. 위에 쿼리가 조회되지 않는다면 뷰를 바꿔서 조회하면 된다. (XXX_IND_COLUMNS, XXX_COL_COMMENTS) ALL _IND_COLUMNS 현재 사용자가 엑세스할 수 있는 테이블에 대한 인덱스 정의 USER _IND_COLUMNS 현재 사용자의 스키마에 있는 테이블에 대한 인덱스 정의 DBA _IND_COLUMNS 데이터베이스의 모든 인덱스 정의 SELECT a.table_name , a.index_name , a.column_name , b.comments FROM user_ind_columns a , user_col_comments b WHERE a.table_name = ‘EMP’ AND a.table_owner = b.owner AND a.table_name = b.table_name AND a.column_name = b.column_name ORDER BY a.index_name , a.column_position ; SELECT a.table_name , a.index_name , a.column_name , b.comments FROM dba_ind_columns a , dba_col_comments b WHERE a.table_name = ‘EMP’ AND a.table_owner = b.owner AND a.table_name = b.table_name AND a.column_name = b.column_name ORDER BY a.index_name , a.column_position ;

두부의 코딩일기 :: [ORACLE] 인덱스 (Index) 조회, 사용 여부 조회 및 리빌드

반응형

인덱스는 트리 구조를 가지고 있으며 수정, 삽입, 삭제 등이 오랫동안 누적되면 트리의 깊이가 깊어지면서 조회 속도가 현저히 떨어집니다. 그렇기 때문에 주기적인 관리가 필수적입니다. 그렇다면 어떻게 인덱스를 관리할까요? 인덱스를 조회하는 방법부터 사용 여부 확인 및 리빌드 하는 방법에 대해서 알아보겠습니다.

인덱스 조회

SELECT * FROM USER_INDEXS [USER_IND_COLUMNS, ALL_INDEXS, ALL_IND_COLUMNS];

USER_INDEXS : 현재 계정 소유의 인덱스 정보

USER_IND_COLUMNS : 현재 계정 소유의 인덱스 컬럼 정보

ALL_INDEXS : 전체 인덱스 정보

ALL_IND_COLUMNS : 전체 인덱스 컬럼 정보

인덱스 사용 여부 조회 및 변경 (모니터링)

ALTER INDEX 인덱스명 MONITORING USAGE; // 인덱스 모니터링 설정 SELECT * FROM V$OBJECT_USAGE; // 모니터링 설정된 인덱스 조회 ALTER INDEX 인덱스명 MONITORING USAGE; // 모니터링 초기화 ALTER INDEX 인덱스명 NOMONITORING USAGE; // 모니터링 중지 —————————————————————————— ALTER INDEX 인덱스명 VISIBLE[INVISIBLE] // 인덱스 자동 사용[미사용]

사용 전/후 INDEX_NAME USED START_MONITORING END_MONITORING 사용 전 인덱스명 NO 시작 시간 NULL 사용 후 인덱스명 YES 시작 시간 NULL 모니터링 중지 인덱스명 NO 시작 시간 종료 시간

조회하고자 하는 인덱스에 모니터링을 설정 후, 위와 같이 조회를 하면 모니터링 설정된 인덱스를 조회할 수 있습니다.

인덱스가 사용되면 USED 컬럼이 ‘YES’로 바뀌며, 모니터링을 초기화하면 ‘NO’로 다시 바뀌게 되고, 모니터링 설정을 중지하면 END_MONITORING 컬럼에 종료 시간이 출력됩니다.

사실 인덱스의 사용 여부를 조회하는 방법은 두 가지가 존재하는데, 앞에서 설명한 모니터링과 ‘실행 계획’입니다. 실행 계획에 대해서는 다른 포스팅에서 설명하는 걸로 하고 지금은 존재한다는 것만 알아두고 넘어가겠습니다.

인덱스는 데이터베이스 내의 옵티마이저가 최적의 길로 안내해서 자동으로 사용되어집니다. 수동으로 사용할 수 있는 방법은 없을까요? 아닙니다! 힌트를 이용하면 본인이 원할 때 언제든지 사용할 수 있습니다. 하지만 무분별하게 사용하면 오히려 조회 속도를 감소시키는 원인이 되기 때문에 제대로 숙지하고 사용하시는 걸 권장합니다.

SELECT /*+ INDEX [테이블명/인덱스명] */ COLUMN1 , COLUMN2 FROM 테이블명 [WHERE ···]

주석 (/* */)에서 +만 추가하면 힌트를 사용하는 방법입니다. 주석과 비슷해서 헷갈리기 쉬우니 사용하실 때 주의하셔야합니다. 힌트에 대한 자세한 내용은 다음 포스팅에서 설명하겠습니다.

인덱스 리빌드

① ANALYZE 명령어를 사용해서 상태를 확인하고자 하는 인덱스의 상태를 분석한다.

ANALYZE INDEX 인덱스명 VALIDATE STRUCTURE; // 인덱스 상태 조회 Index ‘인덱스명’이(가) 분석되었습니다.

② 아래와 같이 조회한다. 0에 가까울수록 좋은 상태를 의미하며, 20% 초과 시에 성능 저하로 간주되오니 해당 인덱스를 리빌드 해주는게 좋습니다.

SELECT (DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100 AS BALANCE FROM INDEX_STATS;

③ 해당 인덱스 리빌드

ALTER INDEX 인덱스명 REBULID;

※ 무수한 DML 작업과 무분별한 인덱스 사용을 오랫동안 방치하면 성능 저하를 일으킵니다. 주기적으로 리빌드해주는 것은 선택이 아닌 필수이며, 위와 같이 하나씩 검색해서 하는 방법은 굉장히 귀찮습니다. 전체 인덱스를 리빌드 하는 방법은 아래와 같습니다.

SELECT ‘ALTER INDEX ‘||INDEX_NAME||’ REBULID; ‘ FROM USER_INDEXS;

– 인덱스 조회 관련 포스팅 –

반응형

[Oracle] 오라클 인덱스(Index) 사용법 총정리(생성, 조회, 삭제, 리빌드)

인덱스(Index)란?

인덱스는 데이터베이스 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체이며 일종의 색인기술입니다. 테이블에 index를 생성하게 되면 index Table을 생성해 관리합니다. 인덱스는 테이블에 있는 하나이상의 컬럼으로 만들 수 있습니다. 가장 일반적인 B-tree 인덱스는 인덱스 키(인덱스로 만들 테이블의 컬럼 값)와 이 키에 해당하는 컬럼 값을 가진 테이블의 로우가 저장된 주소 값으로 구성됩니다.

인덱스(Index) 사용 예시

인덱스 생성

–문법 CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3…….)

–예제 CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS); –예제 컬럼 중복 X CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS);

위와같이 쿼리문을 작성하면 INDEX를 생성할 수 있습니다. UNIQUE 키워드를 붙이면 컬럼값에 중복값을 허용하지 않는다는 뜻입니다.

인덱스 조회

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = ‘CUSTOMERS’; SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = ‘CUSTOMERS’;

인덱스를 생성하면 USER_INDEXES나 ALL_IND_COLUMNS 에서 조회할 수 있습니다. 방금 CUSTOMERS 테이블에 만들었던 EX_INDEX가 첫번째 ROW에 있군요.

인덱스 삭제

–문법 DROP INDEX [인덱스 명]

–예제 DROP INDEX EX_INDEX;

인덱스는 조회성능을 극대화하기 위해 만든 객체인데 너무 많이 만들면 insert, delete, update시에 부하가 발생해 전체적인 데이터베이스 성능을 저하합니다. 고로 안쓰는 인덱스는 삭제시키는것이 좋습니다.

인덱스명 수정

–문법 DROP INDEX [기존 인덱스 명] TO [바뀔 인덱스 명]

–예제 ALTER INDEX EX_INDEX RENAME TO EX_INDEX_NEW

인덱스 명을 바꾸고 싶다면 위와 같이하시면 됩니다. 참고로 오라클에서 인덱스의 구성 컬럼을 추가하는 것은 ALTER이 불가능합니다. 인덱스를 변경하기 위해서는 삭제 DROP을 한 후 새로 생성 CREATE를 해주어야 합니다.

인덱스(Index) 리빌드(Rebuild)사용 예시

인덱스를 리빌드하는 이유

인덱스 파일은 생성 후 insert, update, delete등을 반복하다보면 성능이 저하됩니다. 생성된 인덱스는 트리구조를 가집니다. 삽입,수정,삭제등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어집니다. 이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는것이 좋습니다.

인덱스 리빌드 할 대상 조회쿼리

SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL, DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,’?’,’Rebuild’),’Check’) CNF FROM USER_INDEXES I WHERE I.BLEVEL > 4 ORDER BY I.BLEVEL DESC

해당쿼리는 index 트리의 깊이가 4이상인 index를 조회하는 쿼리입니다. 해당 쿼리문을 실행하여 검색되는 index가 있다면 리빌딩을 하는것이 좋습니다.

인덱스 리빌드

–문법 ALTER INDEX [인덱스명] REBUILD;

–예제 ALTER INDEX EX_INDEX REBUILD;

위의 쿼리를 실행시키면 인덱스가 리빌드 됩니다. 이렇게 일일히 리빌드 시키기 귀찮으신 분들은 USER_INDEXES에 있는 인덱스를 조회하여 인덱스 리빌드 쿼리를 만들어 한번에 실행시키시면 간편합니다.

전체 인덱스 리빌드 쿼리문 만들기

SELECT ‘ALTER INDEX ‘||INDEX_NAME||’ REBUILD; ‘FROM USER_INDEXES;

인덱스(Index)를 남발하지 말아야 하는 이유

개발을 진행할때에 대개 개발서버와 운영서버를 나누어서 관리합니다. 대부분 개발서버에서 개발을 할때에는 적은량의 데이터를 가지고 로직검사를 하며 로직검사에 통과한 코드들이 운영서버에 업데이트가 되죠. 하지만 개발서버에는 잘 동작하던 로직들이 운영서버의 많은량의 데이터들을 처리하다보면 성능이슈가 많이 발생합니다. 그 성능이슈의 주요원인은 바로 데이터베이스에 있습니다. 데이터베이스 관리자는 성능문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성입니다.

하지만 문제를 그저 쉽게 해결하기 위해 쿼리 속도 문제가 날때마다 인덱스를 추가하는것은 바람직하지 못합니다. 성능 이슈가 나서 인덱스를 만들고 또 다른 SQL에서문에서 성능이슈가 발생하여 또 인덱스를 만들었다고 합시다. 이렇게 문제가 발생할때마다 인덱스를 생성하면서 인덱스가 계속 추가되면 생성된 인덱스를 참조하는 하나의 쿼리문을 빠르게는 만들 수 있지만 전체적인 데이터베이스의 성능 부하를 초래합니다. 그렇기에 인덱스를 생성하는것 보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야합니다. 인덱스생성은 꼭 마지막 수단으로 강구해야 할 문제입니다.

인덱스에 대해 더 자세히 알고 싶다면 아래의 글을 참고해주세요.

[DB] 데이터베이스 인덱스(Index) 란 무엇인가?

So you have finished reading the 오라클 인덱스 조회 topic article, if you find this article useful, please share it. Thank you very much. See more: 오라클 인덱스 타는지 확인, 오라클 인덱스 힌트, 오라클 전체 인덱스 조회, 오라클 인덱스 상태 확인, 오라클 인덱스 생성, 오라클 인덱스 삭제, 오라클 인덱스 깨지는 이유, 오라클 인덱스 사용