네이버 joypheonix님 블로그에서 퍼옴
---------------------인덱스 확인----------------------------------
SELECT index_name, table_owner, table_name, column_name
FROM dba_ind_columns
WHERE index_owner = 'EDU_USER' and table_name='TCM_WAREHOUSING'
ORDER BY index_name, column_position;
--TCM_WAREHOUSING_PK EDU_USER TCM_WAREHOUSING WHYEARMON
--TCM_WAREHOUSING_PK EDU_USER TCM_WAREHOUSING SEQ
SELECT ic.index_name, ic.column_name,
ic.column_position, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ix.table_name = 'TCM_WAREHOUSING'
--TCM_WAREHOUSING_PK WHYEARMON 1 UNIQUE
--TCM_WAREHOUSING_PK SEQ 2 UNIQUE
1. 기본적으로 primary key나 unique생성시에는 자동적으로
index가 생성된다.
2. index 생성하기
create table it(
no number,
bigo varchar2(30) constraint it_bigo_uk unique
using index(create index i_it_bigo on it(bigo)
tablespace indx));
3. CREATE INDEX idx_emp ON tb_emp (empno);
-->tb_emp 테이블에 empno 컬럼을 이용하여 idx_emp를 생성한다.
3_1. CREATE INDEX idx_emp ON tb_emp (empno desc);
-->tb_emp 테이블에 empno 컬럼을 이용하여 역순으로 정렬하는 idx_emp를 생성한다.
4. 삭제: DROP INDEX 인덱스명
5.
* 인덱스가 무시되는 경우
인덱스의 유무를 탐지할 수 있는 단순 열에 대해 적용되어야만 인덱스의 장점이 활용된다. 즉 substr이나 || 와 같은 함수에 의해 변형되어 있다면 오라클은 인덱스를 사용하지 않는다. 그러나 min과 max도 열 자체와 함께 사용된다면 인덱스의 장점을 활용할 수 있다. 또한 클러스터 인덱스를 제외하고는 null인 열은 인덱스 상에 나타나지 않는다. 하나 이상의 열에 대해 지정된 인덱스는 전체 열 중에 어느 하나라도 null이 아니면 엔트리를 가진다. 모든 열이 null이면 인덱스에 이와 관련된 엔트리는 생기지 않는다.
--> where ~~ is null 등과 같은 sql문에는 적합하지 않다.
--> 컬럼에 null값이 들어가있는 컬럼은 인덱스로 사용하기 불가능 하다???
--index의 사용유무 확인하기
1. @C:\oracle\ora92\rdbms\admin\utlxplan.sql
2. @C:\oracle\ora92\sqlplus\admin\plustrce.sql
3. create public synonym plan_table for sys.plan_table;
4. ora602에게 권한을 부여
grant plustrace to ora602;
5. grant select, insert, update, delete on plan_table to ora602;
6. ora602에 접속
7. set autotrace on
8. select 컬럼 실행
9. 사용이 끝난 후엔 set autotrace off
--------------------------------------------------------------------------------
-- 16장 인덱스
--------------------------------------------------------------------------------
-- SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해 생성하는 오라클객체
--------------------------------------------------------------------------------
-- 인덱스를 사용해야 하는 경우 --
-- 1.테이블에 행의 수가 많을때 --
-- 2.조건절인 WHERE 문에 해당 컬럼이 많이 사용될 때 --
-- 3.검색 결과가 데이터의 2% ~ 4% 정도 일 때 --
-- 4.JOIN에 자주 사용되는 컬럼 --
-- 5.NULL을 포함하는 컬럼이 많은 경우 --
-- --
-- 인덱스를 사용하지 말아야 하는 경우 --
-- 1.테이블에 행의 수가 적을 때 --
-- 2.WHERE 문에 해당 컬럼이 자주 사용되지 않을 때 --
-- 3.검색 결과가 전체 데이터의 10% ~ 15% 이상 높을 때 --
-- 4.테이블에 DML 작업이 많은 경우 --
-- 즉, 입력 수정 삭제 등이 자주 일어 날 때 --
--------------------------------------------------------------------------------
-- 인덱스 생성/제거와 인첵스 정보 조회
CREATE TABLE E2
AS
SELECT *
FROM EMP;
INSERT INTO E2 SELECT * FROM E2;
-- 시간 체크
SQL> SET TIMING ON
SQL> SELECT DISTINCT EMPNO, ENAME FROM E2 WHERE ENAME = 'SCOTT';
EMPNO ENAME
---------- ----------
7788 SCOTT
Elapsed: 00:00:00.80
CREATE INDEX IDX_E2_ENAME
ON E2(ENAME);
-- 인덱스 생성후 시간 체크
SQL> SET TIMING ON
SQL> SELECT DISTINCT EMPNO, ENAME FROM E2 WHERE ENAME = 'SCOTT';
EMPNO ENAME
---------- ----------
7788 SCOTT
Elapsed: 00:00:00.37 -->> 인덱스 속도가 줄었음.
-- 인덱스 생성 유무 확인 데이터 딕셔너리(USER_INDEXES, USER_IND_COLUMNS(컬럼명확인))
SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME IN('EMP','DEPT','E2');
SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME IN('EMP','DEPT','E2');
-- 인덱스 제거
DROP INDEX IDX_E2_ENAME;
-- 인덱스 재생성
SQL> SET TIMING ON
SQL> ALTER INDEX IDX_E2_DEPTNO REBUILD;
-- 인덱스의 종류
-- 1.고유 인덱스(Unique Index) : 유일한 값을 갖는 컬럼에 대해서 생성하는 인덱스
-- 2.비고유 인덱스(NonUnique Index) : 중복된 데이터를 갖는 컬럼에 대해서 생성하는 인덱스
-- 3.단일 인덱스(Single Index) : 한 개의 컬럼으로 구성한 인덱스
-- 4.결합 인덱스(Composite Index) : 두 개 이상의 컬럼으로 구성한 인덱스
-- 5.함수 기반 인덱스(Function Based Index)
CREATE TABLE D2
AS
SELECT *
FROM DEPT;
-- 고유 인덱스
CREATE UNIQUE INDEX IDX_D2_DEPTNO
ON D2(DEPTNO);
-- 비고유 인덱스
CREATE INDEX IDX_D2_LOC
ON D2(LOC);
-- 결합 인덱스
CREATE INDEX IDX_D2_COM
ON D2(DEPTNO, DNAME);
SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME ='D2';
-- 함수 기반 인덱스
CREATE TABLE E3
AS
SELECT *
FROM EMP;
CREATE INDEX IDX_E3_ANNSAL
ON E3(SAL*12);
SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME ='E3';
------사용법-----------
select /*+ INDEX (A UI_TEMP ) */ --> 테이블명 한칸띄고 인덱스명 (INDEX_desc-->인덱스를 꺼꾸로 탐)
name, se_code, dt
from temp A
where chk = 0 and rownum = 1
