[ORACLE] INDEX 튜닝정리 I/O 효율

1. index scan : 인덱스는 많은 데이터에서 소량의 데이터 찾을 때 
   full table scan : 많은 데이터에서 많은데이터 가져오는 때  
   
   인덱스 컬럼값이 많다--> 한블럭에 담을수 있는 인덱스 양은 작아짐(복합인덱스)
   인덱스 컬럼값이 작다--> 한블럭에 담을수 있는 인덱스 양이 많아짐

   소량의 데이터에 인덱스를 만들면 root=leaf 수도 있음
   
      
2. 인덱스탐색원리
   이진트리 2의 N제곱만큼 경우의 수
   
3. 인덱스는 정렬된 상태로 저장 되어 있으므로, 먼저 찾은 데이터를 먼저 결과집합으로 만들기 때문에,
   인덱스가 저장된 그대로 결과 데이터도 정렬되어 나옴(정렬에 대한 부하를 인덱스 통해서 없앨 수 있음).
   
4.블로단위 I/O

5.sequential Access와 Random Access이해
  1) root -> branch ->leaf : random access
  2) leaf -> leaf : sequential access (double linked list) : 한방향으로 움직임
  3) leaf -> table : random access  : 부하가 가장 많음
  
  leaf block : 컬럼값+ rowid(데이터오브젝트번호+데이터 파일번호+블록번호+로우번호)
               인덱스 컬럼값이 커질수록(복합인덱스 및 결합인덱스) 한블록에 담을수 있는 양이 작아짐.
                  
6. single block /multi block 
  single block=8/ multi block=8 읽는 수치가 같다고 하여 같은 속도가 나온느건 아님.
  블록수도 중요하지만 블록읽기 시도회수 또한 중요함.
  multi block는 extent가 가지는 블록개수로 읽는 갯수결정
  db_file_multiblock_read count
  
7. I/O 효율화 중요성 : 쿼리 작성시 최소한의 블록만 읽을 수 있도록 작성
                       오라클이 최적의 경로를 판단할 수있도록 통계정보 제공
                       오라클이 잘 못판단할 수 도 있으니 오라클이 제공하는 힌트로 최적의 경로 유도 
                       
                       
8. 오라클 인덱스 :  null 값은 인덱스 생성안함 
   인덱스 생성 시 컬럼수와 데이터사이즈마다 인덱스저장되는 건수가 다를 수 있음
   --여부속성 : NOT NULL 인덱스가 필요한 경우  NULL이면 인덱스생성이 안되므로 FULL table scan

8-1)
SELECT * FROM ITEM 
where ITEM_NM like '%버거%'  >> 인덱스 있지만 시작과 끝점을 모름/ index ful

                                              null 제외되어 sort order by 를 대체 ORDER BY  ITEM_NM              
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |      1 |        |     29 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ITEM        |      1 |     29 |     29 |00:00:00.01 |       2 |
|*  2 |   INDEX FULL SCAN                  | INX_ITEM_02 |      1 |      5 |     29 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("ITEM_NM" IS NOT NULL AND "ITEM_NM" LIKE '%버거%'))
 



8-2) SELECT * FROM ITEM ORDER BY  ITEM_NM
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |     92 |00:00:00.01 |       7 |       |       |          |
|   1 |  SORT ORDER BY      |        |      1 |     92 |     92 |00:00:00.01 |       7 | 33792 | 33792 |30720  (0)|
|   2 |   TABLE ACCESS FULL| ITEM |      1 |     92 |     92 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------
 ==> ITEM_NM에 NULL 값도 찾아야 하므로.. SORT ORDER BY 로 대체
 
 
 
 8-3) SELECT * FROM ITEM where ITEM_NM IS NOT NULL ORDER BY  ITEM_NM 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |     92 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ITEM        |      1 |     92 |     92 |00:00:00.01 |       2 |
|*  2 |   INDEX FULL SCAN           | INX_ITEM_02 |      1 |     92 |     92 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("ITEM_NM" IS NOT NULL)

 ==> ITEN_NM 컬럼이 NULL이 아닌 데이터를 찾는것이므로 인덱스를 이용해 정렬 수행.(SORT ORDER BY 연산 사라짐)
 
 /* SORT ORDER BY를 ORDER BY로 대체할려면 해당컬럼이 NOT NULL인걸로 조건을 주든 컬럼을 NOT NULL로해줘야함*/

 --order by rowid로 하면 같은 블록 있는것끼리 같은블록저장되면 (buffer pinning효과 볼수 있음)
 
 -- 두개조건 or면 하나의 인덱스로 해결안됨
   거주지 ='서울' or 성명 like '박수%' / 그러나 rowid로 조합은 가능함. (A->B n C->B)  rowid
   
--SKIP SCAN : 선두컬럼의 변별력이 없어야 효과 볼수 있음 (distinct 갯수가 작아야함)ex) 구분 , 성별
                   선두컬럼이 양이 엄청많아(회원번호 같은거) 성능 안나옴.최악
  A+B+C
  X+O+O : index full , index skip scan
  O+X+O : index range scan(시작과 끝을 알고있음)
  
-- index fast full scan : 절대 빠르지 않는 테이블 / full scan보다는 빠름
                              인덱스 특징을 사용못함 (정렬보장 안됨)
                               index multiblock 읽음: 빠르겠지.
                               제약사항 : select  list에 index 컬럼다 갖고 있어야 사용가능
--index_combine(두개 인덱스사용가능 힌트) :  각각조건을 찾아서 bit_map 연산함                                               
  
 
 9. ROWID : ROWID로 정렬되어 있다면 같은 블록끼리 모여 있어 버퍼피닝 효과 볼수 있음
                 ROWID를 알고 있다면 인덱스를 통하지 도 않고 바로 접근가능함.