테이블 random 액세스 쿼리 튜닝 예시

테스트 데이터 환경 아래 스크립트 참조  인덱스 : 주문지역코드, 주문일자, 주문금액 * 튜닝 포인트 : 인덱스 수정 불가 하며 SQL은 수정 가능 * 전체데이터 : 1,000만건 * 주문일자     : 2019.05.24 - 2019.08.31 (100일)  1일당 10만건 * 주문지역코드 : 1, 2, 3, 4, 5 (고르게 분포) * 주문금액     : 1만원 - 100만원 (고르게 분포)

ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SESSION SET SORT_AREA_SIZE = 2000000000;

drop table test.t_order_km;

--테이블생성
create table test.t_order_km AS
SELECT A.custno, orderAmt, orderLoc, B.orderDt
     , 'ASDFFDSAASDFFDSAASDFFDSAASDFFDSAASDFFDSA' C1
     , 'ASDFFDSAASDFFDSAASDFFDSAASDFFDSAASDFFDSA' C2
     , 'ASDFFDSAASDFFDSAASDFFDSAASDFFDSAASDFFDSA' C3
     , 'ASDFFDSAASDFFDSAASDFFDSAASDFFDSAASDFFDSA' C4
     , 'ASDFFDSAASDFFDSAASDFFDSAASDFFDSAASDFFDSA' C5
     , 'ASDFFDSAASDFFDSAASDFFDSAASDFFDSAASDFFDSA' C6
FROM   (select  'C' || lpad(trim(to_char(rownum)), 4, '0')             custno
              , round(dbms_random.value(10000, 100000))                orderAmt
              , lpad(to_char(round(dbms_random.value(1, 5))), 2, '0') orderLoc
        from dual connect by level <= 1000
        ) A,
       (SELECT  TO_CHAR(TO_DATE('20190901', 'YYYYMMDD') - ROWNUM, 'YYYYMMDD') orderDt
        FROM DUAL CONNECT BY LEVEL <= 100
       ) B,
       (SELECT ROWNUM R_NUM 
        FROM   DUAL
        CONNECT BY LEVEL <= 100
       )
ORDER BY DBMS_RANDOM.RANDOM();

--시노님생성
create public synonym t_order_km for test.t_order_km;

--인덱스생성
CREATE INDEX test.IX_t_order_km_01 ON test.t_order_km(orderLoc, orderDt, orderAmt);

--통계정보수집
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('test', 't_order_km');

--sort_area 조정
ALTER SESSION SET WORKAREA_SIZE_POLICY = AUTO;

 

[통계데이터 확인]

- 리프블록은 39202 와 depth는 2레벨

SELECT TABLE_NAME, INDEX_NAME, STATUS, NUM_ROWS, LEAF_BLOCKS, BLEVEL FROM
DBA_INDEXES
WHERE INDEX_NAME ='IX_T_ORDER_KM_01';

TABLE_NAME       INDEX_NAME               STATUS     NUM_ROWS LEAF_BLOCKS     BLEVEL
---------------- ------------------------ -------- ---------- ----------- ----------
T_ORDER_KM       IX_T_ORDER_KM_01         VALID      10000000       39202          2


select i.index_name, t.blocks, i.num_rows, i.clustering_factor
from dba_tables t, dba_indexes i
where t.table_name = 'T_ORDER_KM'
and i.table_name = t.table_name;

index_name         blocks        num_rows     clustering_factor
---------------- ------------- -----------    -----------------
IX_T_ORDER_KM_01   385488         10000000      9998767


* CF값이 블록에 가까울수록 좋고 num_rows에 가까울수록 나쁘다.

 

[AS-IS 쿼리]

- 드라이빙 조건 이후에 체크 조건이여서 filter 테이블 랜덤 액세스 발생

SELECT  /*+ no_index_ss (a IX_t_order_km_01) */
custno, orderAmt,
orderLoc, orderDt, C1, C2, C3
FROM t_order_km  A
WHERE orderAmt BETWEEN 80000 AND 81000  -- 체크조건
AND  orderLoc BETWEEN '01' AND '03'     -- 드라이빙 조건
AND orderDt     = '20190710';  -- 체크조건

Elapsed: 00:00:00.97

Execution Plan
----------------------------------------------------------
Plan hash value: 3045225975

--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |   919 |   131K| 28456   (1)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_ORDER_KM       |   919 |   131K| 28456   (1)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN                  | IX_T_ORDER_KM_01 |   919 |       | 27535   (1)| 00:00:02 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDERLOC">='01' AND "ORDERDT"='20190710' AND "ORDERAMT">=80000 AND
              "ORDERLOC"<='03' AND "ORDERAMT"<=81000)
       filter("ORDERDT"='20190710' AND "ORDERAMT">=80000 AND "ORDERAMT"<=81000)

 

[TO-BE 쿼리]

- in-list 형식으로 변경하여 주문일자도 드라이빙조건으로 가도록 튜닝

SELECT  custno, orderAmt, orderLoc, orderDt, C1, C2, C3
FROM t_order_km  A
WHERE orderAmt     BETWEEN 80000 AND 81000 --드라이빙조건
AND  orderLoc in ('01','02','03')   --드라이빙조건 
AND  orderDt     = '20190710'    --드라이빙조건


Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 3334870416

---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |   788 |   112K|   796   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR                     |                  |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORDER_KM       |   788 |   112K|   796   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX_T_ORDER_KM_01 |   788 |       |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access(("ORDERLOC"='01' OR "ORDERLOC"='02' OR "ORDERLOC"='03') AND "ORDERDT"='20190710'
              AND "ORDERAMT">=80000 AND "ORDERAMT"<=81000)