|
안녕하세요.
오라클 Execution Plan에 관한 질문입니다.
인덱스 정보는 아래와 같습니다.
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
OPS$RKTIBG2 IDX_FTIOIR_02 OPS$RKTIBG2 FTIOIR REMT_PROC_DATE 1 8 8 ASC
OPS$RKTIBG2 IDX_FTIOIR_02 OPS$RKTIBG2 FTIOIR BRN_NO 2 3 3 ASC
OPS$RKTIBG2 IDX_FTIOIR_02 OPS$RKTIBG2 FTIOIR INST_NO 3 3 3 ASC
테이를 FTIOIR의 전체데이터 건수는 아래와 같습니다.
SQL> select count(*) from FTIOIR ;
COUNT(*)
----------
384853
Elapsed: 00:00:00.03
총 4가지의 경우의 query에 대한 질문입니다.
SQL> SELECT
2 ID_NO,
3 CUST_NAME,
4 STA_TYPE,
5 USD_AMT,
6 REF_NO,
7 REMT_PROC_DATE VALUE_DATE,
8 REMT_CCY,
9 TO_CHAR(REMT_AMT,'99,999,999,990.99'),
10 TO_CHAR(USD_AMT, '99,999,999,990.99'),
11 RSN_CODE1,
12 DESIG_BANK_CODE,
13 DECODE(REMT_TYPE, 'O', DECODE(STA_TYPE,'1','취결','3','등록','5','정리','6','퇴결','8','진행','9','취소',' '),
14 'I', DECODE(STA_TYPE,'1','등록','3','진행','5','정리','7','거절','8','진행','9','취소',' ')),
15 TO_CHAR(REMT_CHRG,'9,999,999,999,999'),
16 TO_CHAR(BANK_CHRG,'9,999,999,999,999'),
17 TO_CHAR(CABL_CHRG,'9,999,999,999,999'),
18 DECODE(REMT_TYPE,'I',APCT_DETL1,BENE_DETL1),
19 ACCT_NO1,
20 DECODE(NVL(CERT_ISSUE_FLAG,0),0,'미발급','발급'),
21 REMT_CANC_DATE,
22 REMT_SETL_DATE,
23 TRIM(TO_CHAR(REMT_AMT, '00000000000000.99')),
24 TRIM(TO_CHAR(REMT_CHRG,'00000000000000000')),
25 TRIM(TO_CHAR(BANK_CHRG,'00000000000000000')),
26 TRIM(TO_CHAR(CABL_CHRG,'00000000000000000'))
27 FROM FTIOIR
28 WHERE REMT_PROC_DATE BETWEEN '20120101' AND '20130101'
29 ORDER BY VALUE_DATE, REF_NO;
77652 rows selected.
Elapsed: 00:00:09.81
Execution Plan
----------------------------------------------------------
Plan hash value: 1710270931
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77060 | 17M| | 10815 (4)| 00:03:15 |
| 1 | SORT ORDER BY | | 77060 | 17M| 20M| 10815 (4)| 00:03:15 |
|* 2 | TABLE ACCESS FULL| FTIOIR | 77060 | 17M| | 9246 (4)| 00:02:47 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("REMT_PROC_DATE">='20120101' AND "REMT_PROC_DATE"<='20130101')
Statistics
----------------------------------------------------------
29 recursive calls
6 db block gets
19616 consistent gets
20515 physical reads
3736 redo size
15218140 bytes sent via SQL*Net to client
57456 bytes received via SQL*Net from client
5178 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
77652 rows processed
1) where 절을 REMT_PROC_DATE을 between을 이용하여 1년의 데이터를 조회하였습니다.
조회건수는 77652 건이므로 분포도가 좋지 않고 대량의 데이터임으로 full scan으로 처리함을 이해 하였습니다.
SQL> SELECT /*+ INDEX(FTIOIR IDX_FTIOIR_02) */
2 ID_NO,
3 CUST_NAME,
4 STA_TYPE,
5 USD_AMT,
6 REF_NO,
7 REMT_PROC_DATE VALUE_DATE,
8 REMT_CCY,
9 TO_CHAR(REMT_AMT,'99,999,999,990.99'),
10 TO_CHAR(USD_AMT, '99,999,999,990.99'),
11 RSN_CODE1,
12 DESIG_BANK_CODE,
13 DECODE(REMT_TYPE, 'O', DECODE(STA_TYPE,'1','취결','3','등록','5','정리','6','퇴결','8','진행','9','취소',' '),
14 'I', DECODE(STA_TYPE,'1','등록','3','진행','5','정리','7','거절','8','진행','9','취소',' ')),
15 TO_CHAR(REMT_CHRG,'9,999,999,999,999'),
16 TO_CHAR(BANK_CHRG,'9,999,999,999,999'),
17 TO_CHAR(CABL_CHRG,'9,999,999,999,999'),
18 DECODE(REMT_TYPE,'I',APCT_DETL1,BENE_DETL1),
19 ACCT_NO1,
20 DECODE(NVL(CERT_ISSUE_FLAG,0),0,'미발급','발급'),
21 REMT_CANC_DATE,
22 REMT_SETL_DATE,
23 TRIM(TO_CHAR(REMT_AMT, '00000000000000.99')),
24 TRIM(TO_CHAR(REMT_CHRG,'00000000000000000')),
25 TRIM(TO_CHAR(BANK_CHRG,'00000000000000000')),
26 TRIM(TO_CHAR(CABL_CHRG,'00000000000000000'))
27 FROM FTIOIR
28 WHERE REMT_PROC_DATE BETWEEN '20120101' AND '20130101'
29 ORDER BY VALUE_DATE, REF_NO;
77652 rows selected.
Elapsed: 00:00:08.37
Execution Plan
----------------------------------------------------------
Plan hash value: 465296239
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77060 | 17M| | 41035 (1)| 00:12:19 |
| 1 | SORT ORDER BY | | 77060 | 17M| 20M| 41035 (1)| 00:12:19 |
| 2 | TABLE ACCESS BY INDEX ROWID| FTIOIR | 77060 | 17M| | 39466 (1)| 00:11:51 |
|* 3 | INDEX RANGE SCAN | IDX_FTIOIR_02 | 77060 | | | 150 (4)| 00:00:03 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("REMT_PROC_DATE">='20120101' AND "REMT_PROC_DATE"<='20130101')
Statistics
----------------------------------------------------------
30 recursive calls
6 db block gets
51411 consistent gets
4880 physical reads
0 redo size
15218140 bytes sent via SQL*Net to client
57456 bytes received via SQL*Net from client
5178 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
77652 rows processed
2) where절은 동일하고 index hint(/*+ INDEX(FTIOIR IDX_FTIOIR_02) */) 사용하여 처리한 경우
index rage scan으로 조회가 되었고 full scan보다 느리다는 것을 확인하였습니다.
SQL> SELECT
2 ID_NO,
CUST_NAME,
3 4 STA_TYPE,
5 USD_AMT,
6 REF_NO,
7 REMT_PROC_DATE VALUE_DATE,
8 REMT_CCY,
9 TO_CHAR(REMT_AMT,'99,999,999,990.99'),
10 TO_CHAR(USD_AMT, '99,999,999,990.99'),
11 RSN_CODE1,
12 DESIG_BANK_CODE,
13 DECODE(REMT_TYPE, 'O', DECODE(STA_TYPE,'1','취결','3','등록','5','정리','6','퇴결','8','진행','9','취소',' '),
14 'I', DECODE(STA_TYPE,'1','등록','3','진행','5','정리','7','거절','8','진행','9','취소',' ')),
15 TO_CHAR(REMT_CHRG,'9,999,999,999,999'),
16 TO_CHAR(BANK_CHRG,'9,999,999,999,999'),
17 TO_CHAR(CABL_CHRG,'9,999,999,999,999'),
18 DECODE(REMT_TYPE,'I',APCT_DETL1,BENE_DETL1),
19 ACCT_NO1,
20 DECODE(NVL(CERT_ISSUE_FLAG,0),0,'미발급','발급'),
21 REMT_CANC_DATE,
22 REMT_SETL_DATE,
23 TRIM(TO_CHAR(REMT_AMT, '00000000000000.99')),
24 TRIM(TO_CHAR(REMT_CHRG,'00000000000000000')),
25 TRIM(TO_CHAR(BANK_CHRG,'00000000000000000')),
26 TRIM(TO_CHAR(CABL_CHRG,'00000000000000000'))
27 FROM FTIOIR
28 WHERE REMT_PROC_DATE BETWEEN '20120101' AND '20130101'
29 AND (REMT_PROC_DATE LIKE '2012%' OR REMT_PROC_DATE LIKE '2013%')
30 ORDER BY VALUE_DATE, REF_NO
; 31
77652 rows selected.
Elapsed: 00:00:09.23
Execution Plan
----------------------------------------------------------
Plan hash value: 465296239
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 391 | 90712 | 355 (4)| 00:00:07 |
| 1 | SORT ORDER BY | | 391 | 90712 | 355 (4)| 00:00:07 |
| 2 | TABLE ACCESS BY INDEX ROWID| FTIOIR | 391 | 90712 | 354 (3)| 00:00:07 |
|* 3 | INDEX RANGE SCAN | IDX_FTIOIR_02 | 391 | | 154 (6)| 00:00:03 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("REMT_PROC_DATE">='20120101' AND "REMT_PROC_DATE"<='20130101')
filter("REMT_PROC_DATE" LIKE '2012%' OR "REMT_PROC_DATE" LIKE '2013%')
Statistics
----------------------------------------------------------
29 recursive calls
5 db block gets
51411 consistent gets
4311 physical reads
0 redo size
15218140 bytes sent via SQL*Net to client
57456 bytes received via SQL*Net from client
5178 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
77652 rows processed
3) 문제는 3)번 case입니다. 이경우 index 칼럼의 where조건으로 (REMT_PROC_DATE LIKE '2012%' OR REMT_PROC_DATE LIKE '2013%') 을 사용한경우
1) 번과 같은 full scan으로 처리되어야 맞다고 생각되나 index range scan으로 조회가 되었고 Execution Plan의 row값이 391 밖에 나오지 않음을 확인하였습니다.
SQL> SELECT
2 ID_NO,
3 CUST_NAME,
4 STA_TYPE,
5 USD_AMT,
6 REF_NO,
7 REMT_PROC_DATE VALUE_DATE,
8 REMT_CCY,
9 TO_CHAR(REMT_AMT,'99,999,999,990.99'),
10 TO_CHAR(USD_AMT, '99,999,999,990.99'),
11 RSN_CODE1,
12 DESIG_BANK_CODE,
13 DECODE(REMT_TYPE, 'O', DECODE(STA_TYPE,'1','취결','3','등록','5','정리','6','퇴결','8','진행','9','취소',' '),
14 'I', DECODE(STA_TYPE,'1','등록','3','진행','5','정리','7','거절','8','진행','9','취소',' ')),
15 TO_CHAR(REMT_CHRG,'9,999,999,999,999'),
16 TO_CHAR(BANK_CHRG,'9,999,999,999,999'),
17 TO_CHAR(CABL_CHRG,'9,999,999,999,999'),
18 DECODE(REMT_TYPE,'I',APCT_DETL1,BENE_DETL1),
19 ACCT_NO1,
20 DECODE(NVL(CERT_ISSUE_FLAG,0),0,'미발급','발급'),
21 REMT_CANC_DATE,
22 REMT_SETL_DATE,
23 TRIM(TO_CHAR(REMT_AMT, '00000000000000.99')),
24 TRIM(TO_CHAR(REMT_CHRG,'00000000000000000')),
25 TRIM(TO_CHAR(BANK_CHRG,'00000000000000000')),
26 TRIM(TO_CHAR(CABL_CHRG,'00000000000000000'))
27 FROM FTIOIR
28 WHERE REMT_PROC_DATE BETWEEN '20121231' AND '20130101'
29 ORDER BY VALUE_DATE, REF_NO;
423 rows selected.
Elapsed: 00:00:00.93
Execution Plan
----------------------------------------------------------
Plan hash value: 1710270931
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68425 | 15M| | 10634 (4)| 00:03:12 |
| 1 | SORT ORDER BY | | 68425 | 15M| 18M| 10634 (4)| 00:03:12 |
|* 2 | TABLE ACCESS FULL| FTIOIR | 68425 | 15M| | 9244 (4)| 00:02:47 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("REMT_PROC_DATE">='20121231' AND "REMT_PROC_DATE"<='20130101')
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
19616 consistent gets
19608 physical reads
0 redo size
83234 bytes sent via SQL*Net to client
828 bytes received via SQL*Net from client
30 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
423 rows processed
4) 그리고 4)의 경우 이틀 가량의 data를 index에서 추출할경우 423 건으로 굉장히 적은 데이터이므로 index rage scan으로 조회가 되야 하나
full scan으로 조회되었습니다.
질문은 아래와 같습니다.
3)번의 rows값이 감소된 이유와 rows 값이 무엇을 가리키는지요?
4)번의 경우 full scan으로 조회되는 이유를 알고 싶습니다.
여러 고수님의 조언부탁드립니다. |
