안녕하세요. DB2 및 XpertMon 사용자여러분. ㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
현재, 저희는 Monitoring 뿐만 아니라, Admin 업무를 XpertMon을 이용하여 수행하실 수 있도록
하기 위하여 Admin Component 설계작업을 진행하고 있습니다. 추가되거나, 변경되는
기능들에 대해서는 앞으로 계속적으로 소개해 드리도록 하겠습니다.
이번주 뉴스레터 주제는 Include Index 기능에 대한 내용입니다.
추가적으로 뉴스레터를 전달하고 싶은 고객이 있으시다면, 성명과 연락처, 메일주소를
알려주시면 감사하겠습니다.
DB2 UDB - non-index data retrieval
DB2 UDB에는 데이터 페이지를 읽지 않고도, Index를 이용하여 Non-Key Data를 검색하는
방법이 있습니다.
이 나머지 데이터는 Index의 고유성을 체크하는데는 포함되지 않으면서 Index에 포함되어,
Data Page를 읽지 않고도 데이터를 처리할 수 있도록 하여, Performance 효과를 높여줍니다.
AIX 머신에 DB2 UDB For Aix 8.1 FP3를 설치합니다.
SAMPLE DB의 EMPLOYEE 테이블을 이용하여 아래와 같이 테스트하도록 합니다.
EMPLOYEE Table에는 empno, firstnme, midinit, lastname, workdept, phoneno, hiredate,
job, edlevel, sex, birthdate, salary, bonus, and comm의 Column이 있습니다
여기서는 EMPLOYEE 테이블의 EMPNO와 PHONENO 컬럼을 Select 하고자 합니다.
다음의 두개의 시나리오를 가지고 결과를 비교해보도록 하겠습니다.
1) EMPNO 로 INDEX 를 만들고 Select
2) EMPNO 로 INDEX 를 만들되, PHONENO 컬럼을 non-key part로 구성하여 Select
1번 TEST
1) EMPNO로 INDEX를 만듭니다.
> db2 connect to sample
> db2 create unique index emp_ix1 on employee (empno)
2) db2expln을 사용하여 SQL을 실행합니다.
>db2expln -d sample -q "select phoneno from employee where empno = `000010`" -t
3) OUTPUT
SQL Statement:
select phoneno
from employee
where empno = `000010`
Section Code Page = 970
Estimated Cost = 296231.312
Estimated Cardinality = 1.000000
Access Table Name = UDBEEE.EMPLOYEE ID = 2,5
| #Columns = 2
| Single Record
| Index Scan: Name = UDBEEE.EMP_IX1 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: EMPNO (Ascending)
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | 1: `000010`
| | | Stop Key: Inclusive Value
| | | | 1: `000010`
| | Data Prefetch: None
| | Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Return Data to Application
| | #Columns = 1
Return Data Completion
End of section
이 결과의 COST는
296231.312입니다.
여기서 잠깐 !!!
XpertMon에서 SQL RUNNER를 이용하면 INDEX를 생성할 수 있습니다.
그 다음 EXPLAIN을 이용하면 Explain OUTPUT을 볼 수 있습니다.
설명 >
2) Index Scan 을 수행하였습니다. ( UDBEEE.EMP_IX1 이라는 인덱스를 이용하였고,
약 19387.140 정도의 cost가 소모되었습니다.)
1) Fetch Table Data 를 수행하였습니다. (테이블의 데이터를 Fetch 하였습니다.
그리고, 여기까지 296231.312 정도의 Cost가 소모되었습니다. ).
Fetch Table Data를 수행한 이유는 Index만으로 SQL을 처리할 수 없으므로, 필요한 데이터를
읽기 위해 테이블로 가서 검색했다는 의미입니다. PHONENO라는 컬럼이 EMP_IX1이라는
인덱스에 존재하지 않았기 때문에 발생한 것입니다. Fetch Table Data를 함으로써 Cost가
기하급수적으로 많아졌음을 볼 수 있습니다.
2번 TEST
1) INCLUDE문을 사용하여 PHONENO를 포함한 EMPNO로 INDEX를 만듭니다
>db2 create unique index emp_ix2 on employee (empno) include (phoneno)
2) DB에 다시 Connect를 합니다
>db2 connect reset
>db2 connect to sample
3) db2expln을 사용하여 SQL을 실행합니다.
>db2expln -d sample -q "select phoneno from employee where empno =`000010`" -t
4) OUTPUT
SQL Statement:
select phoneno
from employee
where empno = `000010`
Section Code Page = 970
Estimated Cost = 26128.138
Estimated Cardinality = 1.000000
Access Table Name = UDBEEE.EMPLOYEE ID = 2,5
| #Columns = 1
| Single Record
| Index Scan: Name = UDBEEE.EMP_IX2 ID = 2
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: EMPNO (Ascending)
| | | 2: PHONENO (Include Column)
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | 1: `000010`
| | | Stop Key: Inclusive Value
| | | | 1: `000010`
| | Index-Only Access
| | Index Prefetch: None
| | Return Data to Application
| | | #Columns = 1
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Return Data Completion
End of section
XpertMon을 이용하여 EXPLAIN 을 수행하면 아래와 같이 나타납니다.
설명>
1) Index Scan을 수행하였습니다. 인덱스이름은 UDBEEE.EMP_IX2 입니다. Cost는 26128.138 입니다.
다음에 Data Operation Complete 이므로, Index 만 읽어서 SQL을 처리하였습니다.
이러한 Index Scan을 Index Only Access 라고 합니다.
이 결과의 COST는
26128.138이고 INDEX - ONLY Access하는 것을 알 수 있습니다.
즉 INDEX를 사용한 COST를 비교해 보면 296231.312에서 26128.138으로 감소 했음을 알 수 있습니다.
만약 Query에 다른 Column을 추가하여 TEST 해 보면 (예를 들어 firstnme)
select
firstnme, phoneno from employee where empno = `000010`
결과는 COST가
313008.375로 1번 TEST보다 더 높음을 알 수 있습니다.
그 이유는 Firstnme이란 정보를 얻기 위하여 DATA PAGE에 가기 때문입니다.
자 여기까지 INDEX에서 NON-KEY DATA를 사용하는 이점을 살펴 보았습니다
단, 검색할 Column 정보가 확실하지 않아서, 여러 개의 INDEX를 작성하거나,
조합(Combination) 으로 INDEX 를 작성하는 것은 오버헤드가 있으므로 피해야 합니다.
XpertMon for DB2 UDB V2
DB 사업부
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail :
hjlee@iteg.co.kr
URL :
http://iteg.co.kr
