대용량 데이터베이스 솔루션 1권 #15 SQL문 생성 예제

문제 1. 3-TIER WEB SYSTEM에서 한 PAGE 당 이름순으로 고객 정보가 나와야 한다.
         같은 이름의 고객의 경우 ROWID 순으로 나와야 한다.
         다음, 이전 버튼을 누르면 현재 화면에 보이는 DATA 다음/이전 25건의 DATA가 나와야 한다.
         절대로 같은 DATA를 2번 읽는 일은 없어야 한다.

 만약 같은 이름이 없다면 아래와 같이 하면 된다.

SELECT ...
  FROM ...
 WHERE NAME > :last_name
   AND ROWNUM <= 25
   AND :sw = 'PGDN'
 UNION ALL
SELECT ...
  FROM ...
 WHERE NAME < :first_name
   AND ROWNUM <= 25
   AND :sw = 'PGUP';

같은 이름이 있다면...
뭔가 unique한게 필요하긴 한데...
분명 이름은 아닌데...
그럼 뭐가 ???
바로 ROWID를 활용하면 된다.

위에서 조금만 고치면 된다.

SELECT ...
  FROM ...
 WHERE NAME >= :last_name AND NAME||ROWID > :last_name||:last_rid
   AND ROWNUM <= 25
   AND :sw = 'PGDN'
 UNION ALL
SELECT /*+ INDEX_DESC(cust cust_name) */
       ...
  FROM ...
 WHERE NAME <= :first_name AND NAME||ROWID < :first_name||:first_rid
   AND ROWNUM <= 25
   AND :sw = 'PGUP';

NAME으로 INDEX를 만들면 당연히 ROWID도 같이 저장이 되니깐 실질적으로 TABLE ACCESS가 추가로 일어나거나 그러진 않는다.
하지만 이것도 80점짜리다.
만약 같은 이름이 현재 PAGE에 여러개가 있으면 그것을 중복으로 읽어야 한다.

문제 2. 1천만건의 DATA가 있는 TABLE이 있는데, 거기서 990만건의 DATA를 삭제하고 10만건을 남겨두어야 한다.
          어떻게 하는게 가장 효율적일까 ?

 아주 쉬운 문제다.
 DELETE * FROM A WHERE DATE < :date;
 이런 SQL을 실행하면 ROLLBACK SEGMENT 오류가 일어 날수도 있고 시간도 엄청 느리다. 만약 INDEX라도 몇개 걸려있고 그러면 휠씬 더 느릴 것이다.

 그냥 10만건 짜리 TABLE을 하나 새로 만들고, 기존 TABLE을 DROP하면 된다.
 CREATE TABLE B SELECT * FROM A WHERE DATE >= :date ;
 DROP TABLE A

문제 3. 아래의 INDEX, SQL, 실행계획을 보고 문제점은 무엇이며, 해결책은 어떻게 되어야 하나 ?

* INDEX 구성
  - GFXC130
    1. GFXC130_X1 : UPFR_DATE + PART_CODE + TEAM_CODE
    2. GFXC130_PK : PART_CODE + TEAM_CODE
  - GFLT950
    1. GFLT950_X1 : TEAM_CODE + SPUM_CODE
    2. GFLT950_PK : CAR_CODE + D_DAY + PART_CODE + TEAM_CODE

* SQL 문

SELECT A.CAR_CODE, A.D_DAY, A.PART_CODE, A.SPUM_CODE, A.ORDER_PRT,
       B.TEAM_CODE, B.UPFR_DATE, B.B_F_QTY
  FROM GFLT650 A, GFXC130 B
 WHERE A.PART_CODE = B.PART_CODE
   AND A.D_DAY = :VALUE1
   AND B.TEAM_CODE = :VALUE2
   AND A.PART_CODE BETWEEN :VALUE3 AND :VALUE4

* Excution Plan

       3  SELECT
      10  NESTED LOOP
187718  TABLE ACCESS (FULL) OF 'GFLT950'
      10  TABLE ACCESS (BY ROWID) OF 'GFXC130'
      10      INDEX (UNIQUE SCAN) OF 'GFXC130_PK'


 =>

정답은 INDEX 전략이 잘못되었다.
EQUAL 과 BETWEEN이 있을때는 EQUAL이 복합INDEX의 앞에 가야 한다.
GFLT950의 PK 중 WHERE 조건에 CAR_CODE가 없다.

1. GFXC130_PK : TEAM_CODE + PART_CODE 로 수정
2. SQL문의 A.PARTCODE_ BETWEEN  부분을 B.PART_CODE 로 수정
3. GFLT950_PK : TEAM_CODE + PART_CODE + D_DAY + CAR_CODE 로 수정