Oracle Data Access Pattern

1. Rowid로 데이터 찾기
   
   - 첫번째 방법 : 일반적 방법
   
   
   select deptno, max(hiredate) as max_hiredate
   from emp
   group by deptno;
   
   
   
   select *
   from emp e, (select deptno, max(hiredate) as max_hiredate
                      from emp
                      group by deptno) d
   where e.deptno = d.deptno
   and e.hiredate = d.max_hiredate; 
   
   - 두번째 방법 : Join 할 때 직접 rowid를 활용하는 방법
   
   
   select deptno, to_char(hiredate,'YYYYMMDD')||ROWID as rid
   from emp
   order by deptno, rid;
   
   
   
   select deptno, max(to_char(hiredate,'YYYYMMDD')||ROWID) as rid
   from emp
   group by deptno
   order by deptno;
   
   
   
   select deptno, substr(max(to_char(hiredate,'YYYYMMDD')||ROWID), 9) as rid
   from emp
   group by deptno
   order by deptno;
   
   
   
   select b.empno, b.ename, b.deptno, b.job, b.hiredate
   from (select substr(max(to_char(hiredate,'YYYYMMDD')||ROWID), 9) as rid
           from emp
           group by deptno) a, emp b
   where b.rowid = a.rid;
   
   
   
   select b.empno, b.ename, b.deptno, b.job, b.hiredate
   from emp b
   where b.rowid in (select substr(max(to_char(hiredate,'YYYYMMDD')||ROWID), 9)
                             from emp
                             group by deptno);
   
   
   
2. Index Unique Scan
   
   - Unique Index에 대한 동등 질의
   
   
   alter table emp add constraint emp_empno_pk primary key(empno);
   select * from emp where empno = 7788;
   
   
   
3. Index Range Scan (O3 152p 참조)
   
   - Unique Index에 대한 범위 질의
   
   
   select * from emp where empno between 7788 and 8000;
   
   
   - Nonunique index에 대한 모든 질의
   
   
   create index emp_deptno_idx on emp(deptno);
   select * from emp where deptno = 20;
   
   
   
4. Index Full Scan (O3 174p 참조)
   
   - index 첫번째 entry 부터 마지막 entry까지 모두 scan + (table 방문은 있을수도 있고 없을수도 있음)
   - single block I/O
   - 정렬의 부하를 줄이거나(order by를 위한 정렬 작업을 피할 목적) 또는 인덱스만을 읽어도 되기 때문에 index Full scan
   - table의 거의 모든 row가 지워져서 인덱스를 이용하는 것이 빠를 경우 (사실 table shrink를 통해 이런 상활을 피해야 한다.)
   
   
   select empno            from emp order by empno;
   select empno, ename from emp order by empno;
   
   
   
5. Index Fast Full Scan (03 185p 참조)
   
   - index 전체 블럭 scan (단, table 방문은 없음)
   - multi block I/O
   - 순서는 보장되지 않는다.
   
   
   select empno from emp;
   
   --> select /*+ index_ffs(emp EMP_EMPNO_PK) */ empno from emp;
   
   
   
6. Index Skip Scan (9i~) (O3 189p 참조)
   
   - Index : deptno + sal 경우
      조건절에 sal만을 사용하면 deptno의 모든 조건으로 포함한 것과 같이 적용한다.
   
   
   select * from emp
   where sal between 2000 and 3000;
   
   
   위의 문장은 아래 문장과 같은 의미이다.
   
   
   select * from emp
   where deptno = deptno
   and sal between 2000 and 3000;
   
   
   --> 그렇다면 이와같은 상황에서의 Cost를  계산해서 FTS 비용과 비교해 보면 된다.
         (http://cafe.naver.com/gseducation/554)
   
   
7. Index Inlist Iterator
   
   - 동일 Index의 여러건의 or 조건을 수행
   - 겹치는 경우가 없기 때문에 비효율이 없다.
   
   
   select * from emp
   where deptno = 10    -- INDEX : O
   or job = 'CLERK'     -- INDEX : X
   
   
   --> 무조건 Full Table Scan 수행
   
   
   select * from emp
   where deptno = 10    -- INDEX : O
   or    deptno = 30    -- INDEX : O
   or    deptno = 80    -- INDEX : O
   
   
   --> Inlist Iterator가 수행
   
   Inlist Iterator 예제)
   
   SELECT /*+ no_expand */
   empno, ename, sal
   FROM emp
   WHERE empno IN (7501,7502,7503);
   
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |              |     3 |    33 |     2   (0)| 00:00:01 |
    |   1 |  INLIST ITERATOR             |              |       |       |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| EMP          |     3 |    33 |     2   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | EMP_EMPNO_PK |     3 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
   
    3 - access("EMPNO"=7501 OR "EMPNO"=7502 OR "EMPNO"=7503)
   
   
   
8. Index Concatenation
   
   - 조건절에 있는 col이 Index에 포함되어 있는 경우
   - Inlist Iterator과는 달리 겹치는 것이 있을 수 있다.
   
   
   select * from emp
   where deptno = 10    -- INDEX : O
   or job = 'CLERK'       -- INDEX : O
   
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=RULE
       1    0   CONCATENATION
       2    1     TABLE ACCESS (BY INDEX ROWID) OF 'emp'
       3    2       INDEX (range SCAN) OF 'deptno_idx'
       4    1     TABLE ACCESS (BY INDEX ROWID) OF 'emp'
       5    4       INDEX (range SCAN) OF 'job_idx'
   
   
   
9. Bit Map(or B*Tree) Index Combination (O3 200p 참조)
   
10. Index Join (O3 209p 참조)
    - index와 index를 rowid로 join 해서 처리한다.
    
11. AND-EQUAL (Index Merge, UDS)

 * 다음 문장이 처리될 수 있는 방법은?

select * from emp
where deptno = 10    -- INDEX : O
and job = 'CLERK'    -- INDEX : O

    1. Full table scan

    2. deptno index -> table -> job으로 filtering                              -> 단일 인덱스 1개 사용

    3. job index    -> table -> deptno로 filtering                              -> 단일 인덱스 1개 사용

    4. Bit Map(또는 B*Tree) Index Combination (O3 200 페이지)   -> 단일 인덱스 2개 사용 : 관심의 대상

    5. Index Join (O3 209 페이지)                                              -> 단일 인덱스 2개 사용 : 관심의 대상 

    6. AND-EQUAL (Index Merge, UDS)                                     -> 단일 인덱스 2개 사용 : 관심의 대상
    7. deptno + job index -> table

    8. MView에 대한 Rewrite, Index cluster, Hash cluster, IOT, Partitioned table 등등일 수 있다.