오라클 SQL - Data 처리 방법

Data 처리 방법 개요

Data를 처리는 아래와 같은 방법들이 있다. 
각 방법의 I/O가 얼마나 발생하는지 보고 더 적게 발생하는 방법을 선택하는 것이 좋다.

1. full table scan

2. rowid scan

2-1) by user rowid scan

2-2) by index rowid

      2.2.1  index unique scan

      2.2.2  index range scan

      2.2.3  inlist iterator

      2.2.4  index full scan

      2.2.5  index fast full scan

---

1. full table scan

• 많은 양의 데이터를 검색할때 유용하다.
• 그룹함수 쓸 때 사용한다.
• multi block I/O가 발생한다.
  multi block : block을 한번에 여러개씩 묶어서 메모리에 올린다.(기준 : extent 크기)
  
  extent 크기 확인하기 :
  {SYS SESSION}
  show parameter db_file_multiblock_read_count;

      데이터 파일에서 block을 읽을때, 한번의 I/O로 읽을 수 있는 block의 수.
      하지만, 하나의 extent안에 128개의 block이 들어가 있어야 한다.

      실제 extent안에 들어간 block 수 확인하기 :
      select * from dba_extents where segment_name = 'EMP';

      실제로는 extent 하나에 block이 8개 들어가있음.   
      즉, 파라미터를 128개로 지정해 두었더라도 extent크기가 8이니까 8개씩 읽어들인다.
   
      수동으로 파라미터 설정 바꾸기 :
      alter session set db_file_multiblock_read_count = 256;

---

full table scan 유발해보기

1) serial path read 방식 : 순서대로 읽는 방식(disk-> data buffer cash -> server process)
   * data buffer cash 란? disk에서 읽어들인 block이 메모리에 있는 공간

   select /*+ full(e)*/* from emp e; --------------------> hint

2) direct path read 방식 : 순서대로 읽는 방식(disk -> server process)

   select /*+ full(e) parallerl(e,2) */* --------------------> parallerl(테이블이름, 프로세스 분리 개수)
   from emp e;                                                     * 프로세스 분리 개수는 서버 CPU 개수에 종속됨 /
                                                                          짝수 단위

  (단, 오라클 xe 버전에서는 실행되지 않는다.)

---

2. rowid scan

• by user rowid scan방식, by index rowid방식이 있음.
• 물리적인 행의 주소를 가지고 스캔하는 방식이다.
• 소량의 데이터를 검색할때 유용하다.
• single block I/O 발생한다.
  single block : block을 한개씩 읽어들인다.

---

2.1 by user rowid scan

     : row마다 갖고있는 고유 주소를 찾아가는 방법

자세한 내용은 이곳을 참고해주세요.

https://truman.tistory.com/59

오라클 SQL - ROWID와 INDEX

 

2.2 by index rowid scan

2.2.1 index unique scan

• 컬럼의 유일한 값으로 인덱스가 구성되어있다.
• unique index를 생성하면 수행된다.
• 단, '='비교연산자를 사용할 경우에만 가능하다. (nonuniqe index는 '='을 쓰더라도 range 방식이다.)
• select * from user_indexes where table_name = 'EMP';

 

2.2.2 index range scan

• nonunique index을 생성하면 무조건 index range scan으로 수행된다.
• unique index을 생성하지만 사용할때 =비교연산자가 아닌 다른 연산자를 사용할 경우도
  index range scan으로 수행된다.
• on plus one scan : leaf를 마지막엔 꼭 한번은 건드린다.

 

2.2.3 inlist iterator

• index를 반복수행한다.(root-branch-leaf를 반복한다.)
• 동일할 leaf block을 읽어들이는데 root, branch도 계속 수행한다는게 문제가 된다.
• inlist iterator 발생 예 )
  select * from employees where employee_id in (101, 102, 103, 104);

      위의 실행방식은 이것과 같다. :
      select * from employees where employee_id = 101
      union all
      select * from employees where employee_id = 102
      union all
      select * from employees where employee_id = 103   
      union all
      select * from employees where employee_id = 104;   

      불필요한 작업을 반복하게 된다.

      그래서 연속적인 데이터를 수행할 때에는 between and 연산자를 사용하는 것이 좋다. :
      select * from employees where employee_id between 101 and 105;

 

2.2.4 index full scan

• single block I/O
• 순서(정렬)를 보장한다.
• 속도가 느리다.

select count(*) from employees;

employees의 전체 건수를 세는 것인데  index를 사용하고있다.
내부에서 무슨일이 일어나고 있는것일까?

건수를 세게 되면(count) 오라클은 *을 쓰더라도
1) pkcolumm(primary key가 걸린 columm)을 찾는다.
2) 그 columm에 걸린 index를 찾는다.
3) 그 index의 block에서 row의 건수를 센다.
의 순서로 진행한다.

따라서 대용량 테이블의 건수를 셀 때 매우 빠르다.

위의 실행방식은 이것과 동일하다. :
sleect count(employee_id) from employees;

 

2.2.5  index fast full scan

• index full scan의 속도를 개선한 방식
• multi block I/O
• 속도가 빠르다
• 순서를 보장하지 않는다.

전체 건수를 셀때 굳이 정렬을 할 필요가 없으므로, 정렬하지않고 바로 세니까 훨씬 빠르다.

하지만 실제 건수를 셀때 오라클에선 index full scan을 사용하도록 유도한다.

그래서 수동으로 index fast full scan을 유도해보자. :
select /*+ index_ffs(e emp_id_idx) */count(*) from emp e;

 

위의 방식보다 더 빠른 방식도 있다.

(매우중요!!!)
index fast full scan에 direct read 방식을추가하기 :
select /*+ index_ffs(e emp_id_idx) parallel_index(e,emp_id_pk,2)*/count(*) from emp e;