[sql 튜닝] 인덱스 튜닝 3-2. 부분범위 처리

3.2.1 부분범위처리

• fetch call 의 원리.
  • 전체 쿼리 결과집합을 쉽 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call 이있을 때마다 일정량씩 나누어 전송하는 것을 이른바 ‘부분범위 처리라고 한다.
  • sql 문에서 페이징 마냥 조회를 나누어서 하는 것도 해당될까? -> 맞다. 매우 중요한 부분! 5장에서 또 등장한다. 
• sql 상에서 limit, row num 지정하는 것에도 해당한다.
• 정렬
  • 정렬 이슈 때문에 부분범위 처리를 하지 못하는 경우.
  • 인덱스 특성 상 정렬을 생략할 수 있는 경우에만 부분범위 처리 또한 가능하다.

• Array Size 조절을 통한 Fetch Call 최소화
  • 네트워크 전송량에 따라 Array size 를 조절하는 것이 좋다.
  • 파일 다운로드처럼 어차피 모두 전송해야한다면 최대한 크게 하는 게 좋겠고, 앞쪽 데이터만 fetch 하는 프로그램이라면 줄이는 게 좋을 것이다.
• 실제 쿼리툴에서 Array Size 가 설정된 예시
  • 

3.2.2 부분범위 처리 구현

• java program 에서도 프레임워크 내부 구현체에서 array size 에 맞게 fetch 하는 방식이 있다.
• 예시.

public static int fetch(ResultSet rs, int arraySize) { 
    int i = 0;
    while(rs.next()) {
        if(++i >= arraySize) return i;
    }
    return i;
}

public static void  execute(Connection con) throws Exception {
    int arraySize = 10;
    String SQLStmt = "select object_id, object_name from all_objects";
    Statement stmt = con. createStatement();
    stmt. setFetchSize(arraysize);
    ResultSet rs = stmt.executeQuery(SQLStmt);
    while(true){
        int r = fetch (rs, arraysize);
        if (r < arraySize) break;
        System.out .println (“ Enter to Continue ... (Q)uit? ");
        BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
        String input = in.readLine();
        if(input.equals("Q")) break;
    }
    rs.close();
    stmt.close();
}

3.2.3. OLTP 환경에서 부분범위 처리에 의한 성능개선 원리

• 대용량이지만 특정한 순서로 상위 일부 데이터만 확인하는 경우. -> 항상 정렬을 유지하는 인덱스를 활용하면 매우 빠르게 조회할 수 있다. (정렬과정을 생략할 수 있기 때문에.)

멈출 수 있어야 의미있는 부분범위 처리

• 정렬을 생략할 수 있어도 문제는 부분범위 처리가 되기 위해서는 중간에 특정 시점에서 멈출 수 있느냐 이다.
• 클라이언트가 직접 서버에 연결하는 2-Tier 툴 (오렌지 등) 은 당연히 가능하고, 그 외 n-Tier 환경에서도 가능하다. (5.3.절에 내용 존재. )
  • n-Tier 환경(WAS, AP 등이 존재)은 기본적으로 클라이언트가 DB 서버 커넥션을 독점할 수 없다. 작업을 마치면 커넥션을 커넥션 풀에 반환해야하므로, 모두 전송한 후에 cursor 를 닫아야 한다. (*cursor: java 기준 ResultSet, Statement 객체. )

• Batch IO
  • 논리적 IO, 즉 실제 데이터의 물리적 주소를 얻기 위해 캐시 조회를 하고, 없으면 Disk IO 를 일으키는데, Disk IO 를 모아두었다가 한번에 실행하는 기능이다.
  • 특징적으로, 수행 결과가 순서가 보장되지 않기 때문에, index 활용 시 정렬을 생략할 수 있었던 경우에도 불구하고 순서가 보장되지 않는다. 따라서 정렬이 반드시 필수적으로 필요하다.
    • 이러한 특징때문에 Batch IO 기능을 거두는 경우도 존재한다.
    • 예시 : 필요한 Order By 절을 생략한 SQL 패턴. : batchIO 를 사용하는데 order by 절이 있다면 무조건 이 정렬이 필요한데, 기본적으로 순차적으로 조회된다는 전제하에 order by 절을 생략해두었던 경우 batchIO 를 켜두면 결과의 순서에 문제가 생길 수 있다.
    • 굳이 order by 절을 사용하지 않아도 되는 SQL:```sql
• • 상태변경이력 _PK : 장비번호 + 변경일시
    SELECT /*+ INDEX( H 상태변경이력 PK ) */ 장비번호, 변경일시, 상태코드
    FROM 상태변경이력 H
    WHERE 장비번호 :eqp_no
    AND ROWNUM <= 10; -- order by 를 사용하지 않더라도 변경일시 순으로 10개 데이터 조회.
  • - Disk IO 모아서 한번에 실행하고 sort 하기 vs 블록 당 캐시가 없을 경우 한번씩 Disk IO 가 일어나지만 sort 는 생략하기