?????. 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