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