오라클 INDEX

네이버 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