index(인덱스)

인덱스

인덱스란? 
- 검색을 빠르게 하기 위해 사용
- 인덱스 구성시 자동정렬이 이루어짐

- 사용을 해야하는 경우
. 테이블에 행의 수가 많을때
. 조건절인 where 문에 column 수가 많을 때
. join , null 포함하는 컬럼이 많을 떄

- 사용하지 말아야 하는 경우
. 테이블의 행의 수가 적을 때
. where 문에 해당 컬럼이 자주 사용되지 않을 때
. 테이블의 DML 작업이 많은 경우

- 확인
. USER_INDEXES : 인덱스 이름 및 고유성 
. USER_IND_COLUMNS : 인덱스 이름, 테이블 이름, 열 이름


-index 생성법
. create index index명 on table명(index걸어줄 table에 colum명);
  ex)create index idx_emp_ename on emp(ename);

-index 제거
. drop index index명
  ex)drop index idx_emp_ename;

-index 재생성
. alter index index명 rebuild;
  ex)alter index idx_emp_ename rebuild;

. DML 같은 작업이 많을 경우 효율성이 떨어지게 된다. 이때 rebuild 필요

-index 조회(index를 모두 보여준다.)
. select object_name, object_type from user_objects where object_type ='INDEX';


-인덱스 종류
. Unique Index(고유 인덱스) - 유일한 값을 갖는 컬럼에 대해서 생성하는 인덱스
  . NonUnique Index(비고유 인덱스) - 중복된 데이터를 갖는 컬럼에 대해서 생성하는 인덱스
. Single Index(단일 인덱스)
. Composite Index(결합 인덱스)

- 결합 인덱스 우선순위
. 1순위 - 컬럼이 사용한 연산자에 의한 인덱스 컬럼 선정 
. 2순위 - 랜덤 액세스를 고려한 인덱스 컬럼 선정 
. 3순위 - 정렬 제거를 위한 인덱스 컬럼 선정 
. 4순위 - 단일 컬럼의 분포도를 고려한 인덱스 컬럼 선정 
 



# 자동정렬 확인 

create table idx_test(
  id varchar(10) not null,
  sid varchar(10) not null,
  suid varchar(10) not null);


  create table idx_test(
  id varchar(10) not null,
  sid varchar(10) not null,
  suid varchar(10) not null);
  
  insert into idx_test values ('3','1','3');
  insert into idx_test values ('3','2','1');
  insert into idx_test values ('3','2','2');
  insert into idx_test values ('3','2','3');
  insert into idx_test values ('3','3','3');
  insert into idx_test values ('1','1','1');
  insert into idx_test values ('2','3','2');
  insert into idx_test values ('2','3','3');
  insert into idx_test values ('3','1','1');
  insert into idx_test values ('1','3','2');
  insert into idx_test values ('1','3','3');
  insert into idx_test values ('2','1','1');
  insert into idx_test values ('2','2','1');
  insert into idx_test values ('2','2','2');
  insert into idx_test values ('2','2','3');
  insert into idx_test values ('2','3','1');
  insert into idx_test values ('3','3','1');
  insert into idx_test values ('3','3','2');
  insert into idx_test values ('3','1','2');
  insert into idx_test values ('1','1','2');
  insert into idx_test values ('1','1','3');
  insert into idx_test values ('1','2','1');
  insert into idx_test values ('1','2','2');
  insert into idx_test values ('1','2','3');
  insert into idx_test values ('1','3','1');
  insert into idx_test values ('2','1','2');
  insert into idx_test values ('2','1','3');
  
  
// 뒤죽박죽으로 들어간 것을 확인

SQL> select * from IDX_TEST;

ID   SID  SUID
---- ---- ----
3    1    3
3    2    1
3    2    2
3    2    3
3    3    3
1    1    1
2    3    2
2    3    3
3    1    1
1    3    2
1    3    3

ID   SID  SUID
---- ---- ----
2    1    1
2    2    1
2    2    2
2    2    3
2    3    1
3    3    1
3    3    2
3    1    2
1    1    2
1    1    3
1    2    1

ID   SID  SUID
---- ---- ----
1    2    2
1    2    3
1    3    1
2    1    2
2    1    3



// 인덱스 생성
create index idx on idx_test(id,sid,suid);


//인덱스 확인
SQL> select a.index_name , a.table_name , b.column_name, b.DESCEND
     from user_indexes a, user_ind_columns b
     where a.index_name=b.index_name 
     and a.index_name='IDX';

INDEX_NAME      TABLE_NAME      COLUMN_NAM DESC
--------------- --------------- ---------- ----
IDX             IDX_TEST        ID         ASC
IDX             IDX_TEST        SID        ASC
IDX             IDX_TEST        SUID       ASC



// 인덱스 (정렬된 데이터 확인) 
SQL>  select * from IDX_TEST;

ID   SID  SUID
---- ---- ----
1    1    1
1    1    2
1    1    3
1    2    1
1    2    2
1    2    3
1    3    1
1    3    2
1    3    3
2    1    1
2    1    2

ID   SID  SUID
---- ---- ----
2    1    3
2    2    1
2    2    2
2    2    3
2    3    1
2    3    2
2    3    3
3    1    1
3    1    2
3    1    3
3    2    1

ID   SID  SUID
---- ---- ----
3    2    2
3    2    3
3    3    1
3    3    2
3    3    3

27 rows selected.





select * from idx_test 
where id = '3' and sid='2' and suid='1';













create table idx_test2(
  id varchar(10) not null,
  sid varchar(10) not null,
  suid varchar(10) not null);

  
  insert into idx_test2 values ('3','1','3');
  insert into idx_test2 values ('3','2','1');
  insert into idx_test2 values ('3','2','2');
  insert into idx_test2 values ('3','2','3');
  insert into idx_test2 values ('3','3','3');
  insert into idx_test2 values ('1','1','1');
  insert into idx_test2 values ('2','3','2');
  insert into idx_test2 values ('2','3','3');
  insert into idx_test2 values ('3','1','1');
  insert into idx_test2 values ('1','3','2');
  insert into idx_test2 values ('1','3','3');
  insert into idx_test2 values ('2','1','1');
  insert into idx_test2 values ('2','2','1');
  insert into idx_test2 values ('2','2','2');
  insert into idx_test2 values ('2','2','3');
  insert into idx_test2 values ('2','3','1');
  insert into idx_test2 values ('3','3','1');
  insert into idx_test2 values ('3','3','2');
  insert into idx_test2 values ('3','1','2');
  insert into idx_test2 values ('1','1','2');
  insert into idx_test2 values ('1','1','3');
  insert into idx_test2 values ('1','2','1');
  insert into idx_test2 values ('1','2','2');
  insert into idx_test2 values ('1','2','3');
  insert into idx_test2 values ('1','3','1');
  insert into idx_test2 values ('2','1','2');
  insert into idx_test2 values ('2','1','3');
  
  
  
  create index id_idx on idx_test(id);
  
  
  commit;
  
  select a.index_name , a.table_name , b.column_name, b.DESCEND
     from user_indexes a, user_ind_columns b
     where a.index_name=b.index_name 
     and a.TABLE_name LIKE 'IDX_%'

INDEX_NAME      TABLE_NAME      COLUMN_NAM DESC
--------------- --------------- ---------- ----
IDX2            IDX_TEST2       ID         ASC
IDX             IDX_TEST        SUID       ASC
IDX             IDX_TEST        SID        ASC
IDX             IDX_TEST        ID         ASC






SQL> select count(*) from idx_test;

  COUNT(*)
----------
      5500

SQL> select count(*) from idx_test2;

  COUNT(*)
----------
      5500




  select a.index_name , a.table_name , b.column_name, b.DESCEND
     from user_indexes a, user_ind_columns b
     where a.index_name=b.index_name 
     and a.TABLE_name LIKE 'IDX_%'

INDEX_NAME      TABLE_NAME      COLUMN_NAM DESC
--------------- --------------- ---------- ----
ID_IDX         IDX_TEST2       ID         ASC
IDX             IDX_TEST        SUID       ASC
IDX             IDX_TEST        SID        ASC
IDX             IDX_TEST        ID         ASC




실행계획 결과 

select * from idx_test 
where id = '3' and sid='2' and suid='432';
==> INDEX (RANGE SCAN) OF 'IDX'(INDEX)(Cost=1 Card=1 Bytes=21)
==> where 문에 인덱스 칼럼이 모드 들어간 경우 인덱스를 잘탐





select * from idx_test2
where id = '3' and sid='2' and suid='432';

===>> TABLE ACCESS (FULL) OF 'IDX_TEST2(TABLE)(Cost=5 Card=1 Bytes=21)
==> where 문에 index 문이 하나가 들어간 경우 full scan





select * from idx_test 
where sid='2' and suid='432';

===>> TABLE ACCESS (FULL) OF 'IDX_TEST(TABLE)(Cost=5 Card=3 Bytes=63)
==> where 문에 인덱스 중간부터 들어간 경우 full scan













# 함수 인덱스




create table fidx_test(
  id varchar(10) not null,
  sid varchar(10) not null,
  suid varchar(10) not null);

  create index FIDX on FIDX_TEST(id);
  COMMIT;
  

 
 select column_name, last_analyzed from user_tab_cols where table_name = 'FIDX_TEST';


  select a.index_name , a.table_name , b.column_name, b.DESCEND
     from user_indexes a, user_ind_columns b
     where a.index_name=b.index_name AND
        B.TABLE_NAME='FIDX_TEST';

INDEX_NAME      TABLE_NAME      COLUMN_NAM DESC
--------------- --------------- ---------- ----
FIDX            IDX_TEST       SID         ASC
FIDX            IDX_TEST       ID         ASC



select ID,SID from FIDX_TEST
WHERE ID='a' AND SID='3'
==> INDEX (RANGE SCAN) OF 'FIDX'(INDEX)(Cost=2 Card=500 Bytes=7K)




select substr(id,3,1) ,sid 
from FIDX_TEST
WHERE ID='asd' and SID='3';
==> TABLE ACCESS (FULL) OF 'FIDX_TEST'(table)(Cost=3, Card=5000 Bytes=7k)





==> 쿼리문에 함수 즉, 인덱스를 갖는 칼럼의 값에 변경이 일어나는 경우
where 문이 같더라도 index 를 타지 않는 것을 확인




drop index FIDX




create index FIDX_F on FIDX_TEST(substr(id,'3','1'),sid);


SQL>   select a.index_name , a.table_name , b.column_name, b.DESCEND
        from user_indexes a, user_ind_columns b
         where a.index_name=b.index_name AND
        B.TABLE_NAME='FIDX_TEST';

INDEX_NAME TABLE_NAME      COLUMN_NAME     DESC
---------- --------------- --------------- ----
FIDX_F     FIDX_TEST       SYS_NC00004$    ASC
FIDX_F     FIDX_TEST       SID             ASC





select substr(id,3,1) ,sid 
from FIDX_TEST
WHERE ID='asd' and SID='3';
==>INDEX (fast scan )

인덱스를 타지만 FAST FULL SCAN 을 사용함










#인덱스 Rebuild

DELETE FROM IDX_TEST WHERE ID='2' AND SID='3'


alter index idx REBUILD ;




############### 인덱스가 얼마나 손상이 됬는지 확인 하기위해 
                index_stats 뷰를 인덱스 정보로 채운다



ANALYZE INDEX IDX VALIDATE STRUCTURE



SELECT blocks, pct_used, distinct_keys,lf_rows, del_lf_rows
 FROM index_stats;
 
 
BLOCK  PCT_USED  DISTINCT_KEYS LF_ROWS  DEL_LF_ROWS
16 82   4500         4500    1500



alter index idx REBUILD ONLINE; 




ANALYZE INDEX IDX VALIDATE STRUCTURE

SELECT blocks, pct_used, distinct_keys,lf_rows, del_lf_rows
 FROM index_stats;


BLOCK  PCT_USED  DISTINCT_KEYS LF_ROWS DEL_LF_ROWS
16 79   3000 3000  0



###  DEL_LF_ROWS 값이 줄어듬  













##################

힌트를 사용하기 위해
SCOTT 계정 으로 테스트

-----------------------------------------
EXPLAIN PLAN 권한이 없으므로 권한부여

롤 plus 생성
plus 롤에 부여할 객체권한 부여
사용자 scott 에게 만든 plus 권한 부여
-----------------------------------------


sys 계정


create index idx_emp_hiredate on emp(hiredate);
create index idx_emp_sal on emp(sal);
create index idx_emp_ename on emp(ename);
select * from dba_indexes where table_name='EMP';



create role plus;

grant select on v_$sesstat to plus;
grant select on v_$statname to plus;
grant select on v_$mystat to plus;
grant plus to dba with admin option;



grant plus to scott;



SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='SCOTT';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
SCOTT                          RESOURCE                       NO  NO
SCOTT                          HR_CLERK                       NO  NO
SCOTT                          PLUS                           NO  NO
SCOTT                          CONNECT                        NO  NO






SQL> select * from role_tab_privs where role ='PLUS';

ROLE    OWNER   TABLE_NAME      COLUMN_NAM PRIVILEGE  GRA
------- ------- --------------- ---------- ---------- ---
PLUS    SYS     V_$SESSTAT                 SELECT     NO
PLUS    SYS     V_$MYSTAT                  SELECT     NO
PLUS    SYS     V_$STATNAME                SELECT     NO



select index_name, table_owner, table_name from user_indexes where table_name='EMP';

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
IDX_EMP_ENAME                  SYS                            EMP
IDX_EMP_SAL                    SYS                            EMP
IDX_EMP_HIREDATE               SYS                            EMP








-----------------------
sqlplus 에서 EXPLAIN PLAN 실행 하려만 아래 두 명령어중 하나 실행 ---> 근데 보기가 불편
set autot on     
set autot trace exp

----------------------

아래는 오렌지에서 확인한것



select 
    ename, sal, hiredate
from emp
where  to_char(hiredate,'yymmdd')='810220'
and sal > 1200
and deptno = 30
and ename like '%ALLEN%';

===> table access (full) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=32)
===> index 를 타지 못함





select /*+ index(emp idx_emp_sal) */
    ename, sal, hiredate
from emp
where  to_char(hiredate,'yymmdd')='810220'
and sal > 1200
and deptno = 30
and ename like '%ALLEN%';
===> INDEX (RANGE SCAN) OF 'IDX_EMP_SAL' (INDEX)(Cost=1 Card=11)
===> EMP  idx_emp_sal의 인덱스 힌트를 주었더니 index 를 타기 시작함




select /*+ index(emp idx_emp_hiredate) */
    ename, sal, hiredate
from emp
where  to_char(hiredate,'yymmdd')='810220'
and sal > 1200
and deptno = 30
and ename like '%ALLEN%';
==>  TALBE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=42)
==>  HIREDATE 값이 변경되서 못타는듯 ? => TO_CHAR 함수때문에 






select /*+ index(emp idx_emp_hiredate) */
    ename, sal, hiredate
from emp
where hiredate='810220'
and sal > 1200
and deptno = 30
and ename like '%ALLEN%';
===> INDEX (RANGE SCAN) OF 'IDX_EMP_HIREDATE' (INDEX)(Cost=1 Card=1)
===> hiredate 쿼리문을 변경 함






select /*+ index(emp idx_emp_ename) */
    ename, sal, hiredate
from emp
where hiredate='810220'
and sal > 1200
and deptno = 30
and ename like '%ALLEN%';
===> INDEX ( FULL SCAN) OF 'IDX_EMP_ENAME(INDEX_ ( Cost=1 Card=1 )
==> 인덱스를 타지만 풀스캔임 ==> ename 구문이 like 로 검색을 해서 그런거 같음
==> Optimizer 에서 INDEX 태운다음 풀스캔이 빠르다고 판단을 한것. 



select /*+ index(emp idx_emp_ename) */
    ename, sal, hiredate
from emp
where hiredate='810220'
and sal > 1200
and deptno = 30
and ename = 'ALLEN';
===> INDEX (RANGE SCAN) OF 'IDX_EMP_ENAME' (INDEX)(Cost=1 Card=1)
===> ename 쿼리문을 변경 함




Cost 와 Card 값을 보면 
인덱스 힌드를 ename, hiredate 로 줄 경우 Cost=1 Card=1
sal 는 Cost=1 Card=11  다른것을 확인

이유는 where 문에서 sal > 1200 으로 설정 했기 때문


sal > 1200  인것이 11개가 있는거 같음 



확인해보쟈




SQL> select * from EMP
  2  where sal > 1200;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

11 rows selected.

 위와 같이 sal > 1200 값이 11개였음










###### to_char(hiredate,'yymmdd')  << 함수인덱스 만들어서 인덱스 검색 하기

select /*+ index(emp idx_emp_hiredate) */
    ename, sal, hiredate
from emp
where  to_char(hiredate,'yymmdd')='810220'
and sal > 1200
and deptno = 30
and ename like '%ALLEN%';
==>  TALBE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=42)
==>  HIREDATE 값이 변경되서 못타는듯 ? => TO_CHAR 함수때문에 







create index idx_emp_to_hiredate on emp(TO_CHAR(hiredate),'yyddmm');



SQL> select LAST_ANALYZED, index_type ,index_name, table_owner, table_name  from user_indexes where table_name='EMP';

INDEX_TYPE                  INDEX_NAME           TABLE_OWNER     TABLE_NAME
--------------------------- -------------------- --------------- ---------------
FUNCTION-BASED NORMAL       IDX_EMP_TO_HIREDATE  SYS             EMP
NORMAL                      IDX_EMP_ENAME        SYS             EMP
NORMAL                      IDX_EMP_SAL          SYS             EMP
NORMAL                      IDX_EMP_HIREDATE     SYS             EMP


select /*+ index(emp idx_emp_hiredate) */
    ename, sal, hiredate
from emp
where  to_char(hiredate,'yymmdd')='810220'
and sal > 1200
and deptno = 30
and ename like '%ALLEN%';



==>  TALBE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=42)
===>> 걸려있는데 못탐!!




이유 : 오라클에서 함수 인덱스에 대한 통계정보를 아직 이루지 못했기 때문 
함수 인덱스 생성시 -> ANALYZE 자동으로 이루이지지 않음

ANALYZE INDEX IDX_EMP_TO_HIREDATE VALIDATE STRUCTURE   << 명령어로 ANALYZE 실행

ANALYZE 시간은 user_indesx 테이블에 확인 가능



ANALYZE INDEX IDX_EMP_TO_HIREDATE VALIDATE STRUCTURE 

select LAST_ANALYZED, index_type ,index_name, table_owner, table_name  from user_indexes where table_name='EMP';
LAST_ANALYZED
2016/01/15 18:12:16 





select /*+ index(emp idx_emp_hiredate) */
    ename, sal, hiredate
from emp
where  to_char(hiredate,'yymmdd')='810220'
and sal > 1200
and deptno = 30
and ename like '%ALLEN%';

===> INDEX (RANGE SCAN) OF 'IDX_EMP_TO_HIREDATE' (INDEX)(Cost=1 Card=1)
==> 함수 인덱스를 타기 시작함 



######################################################
*** 즉 함수 인덱스를 사용시 
ANALYZE 를 항상 해줘야 함수인덱스를 탐