1.索引跳跃扫描
在查询条件中不涉及到索引的前导列，但是前导列不同值很少的时候，数据库优化器在生成执行计划时可能会使用索引跳跃扫描的方法来对表进行访问
1.1.生成模拟数据
echo "CREATE TABLE Employees1 (
ID INT,
Name VARCHAR(100),
Department VARCHAR(100),
JobTitle VARCHAR(100)
)@
-- 创建一个复合索引，但将第一个列设置为非最左前缀
CREATE INDEX idx_dept_jobtitle1 ON Employees1 (Department, JobTitle)@
begin
DECLARE i INT DEFAULT 1;
WHILE i <= 15000 DO
INSERT INTO employees1 VALUES (i, 'hr' || i,'ms0','cmg'||i);
SET i = i + 1;
END WHILE;
COMMIT;
END@
begin
DECLARE i INT DEFAULT 15001;
WHILE i <= 30000 DO
INSERT INTO employees1 VALUES (i, 'hr' || i,'ms1','cmg'||i);
SET i = i + 1;
END WHILE;
COMMIT;
END@" | db2 -td@ +p
"
db2 runstats on table Employees1 and indexes all
1.2.生成执行计划
db2 explain plan for "SELECT * FROM Employees1 WHERE JobTitle = 'cmg100'" --表 EXPLAIN_ARGUMENT 中ARGUMENT_TYPE='JUMPSCAN' ,ARGUMENT_VALUE ='TRUE'
echo -e '\n'|db2exfmt -d enmo -o db2exfmt.out1
1.3.查看执行计划
关注执行计划中使用了索引 IDX_DEPT_JOBTITLE1 的 IXSCAN ，但是 JUMPSCAN: (Jump Scan Plan) 为 TRUE 表示使用了跳跃扫描
Optimized Statement:
-------------------
SELECT
Q1.ID AS "ID",
Q1.NAME AS "NAME",
Q1.DEPARTMENT AS "DEPARTMENT",
Q1.JOBTITLE AS "JOBTITLE"
FROM
DB2INST1.EMPLOYEES1 AS Q1
WHERE
(Q1.JOBTITLE = 'cmg100')
Access Plan:
-----------
Total Cost: 76.272
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1.07511
FETCH
( 2)
76.272
10.075
/----+-----\
1.07511 30000
IXSCAN TABLE: DB2INST1
( 3) EMPLOYEES1
68.1375 Q1
9
|
30000
INDEX: DB2INST1
IDX_DEPT_JOBTITLE1
Q1
Extended Diagnostic Information:
--------------------------------
No extended Diagnostic Information for this statement.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 76.272
Cumulative CPU Cost: 225823
Cumulative I/O Cost: 10.075
Cumulative Re-Total Cost: 0.0275836
Cumulative Re-CPU Cost: 59387
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 72.0004
Estimated Bufferpool Buffers: 361
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v10.5.0.9 : s170908
HEAPUSE : (Maximum Statement Heap Usage)
80 Pages
PLANID : (Access plan identifier)
9d60e99c9d1526c6
PREPTIME: (Statement prepare time)
3 milliseconds
SEMEVID : (Semantic environment identifier)
431f78d03d9bb07e
STMTHEAP: (Statement heap size)
8192
STMTID : (Normalized statement identifier)
6a037cae9c4aeec5
Input Streams:
-------------
4) From Operator #2
Estimated number of rows: 1.07511
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.JOBTITLE+Q2.DEPARTMENT+Q2.NAME+Q2.ID
2) FETCH : (Fetch)
Cumulative Total Cost: 76.272
Cumulative CPU Cost: 225823
Cumulative I/O Cost: 10.075
Cumulative Re-Total Cost: 0.0275836
Cumulative Re-CPU Cost: 59387
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 72.0004
Estimated Bufferpool Buffers: 361
Arguments:
---------
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
SEQUENTIAL,READAHEAD
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SPEED : (Assumed speed of scan, in sharing structures)
SLOW
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
FALSE
VISIBLE : (May be included in scan sharing structures)
FALSE
WRAPPING: (Scan may start anywhere and wrap)
FALSE
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 1.07511
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.DEPARTMENT(A)+Q1.$RID$+Q1.JOBTITLE
3) From Object DB2INST1.EMPLOYEES1
Estimated number of rows: 30000
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.NAME+Q1.ID
Output Streams:
--------------
4) To Operator #1
Estimated number of rows: 1.07511
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.JOBTITLE+Q2.DEPARTMENT+Q2.NAME+Q2.ID
3) IXSCAN: (Index Scan)
Cumulative Total Cost: 68.1375
Cumulative CPU Cost: 209960
Cumulative I/O Cost: 9
Cumulative Re-Total Cost: 0.0267065
Cumulative Re-CPU Cost: 57498.7
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 64.4339
Estimated Bufferpool Buffers: 5
Arguments:
---------
JUMPSCAN: (Jump Scan Plan)
TRUE
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
3
PREFETCH: (Type of Prefetch)
SEQUENTIAL,READAHEAD
PROWLOCK: (Position Scan Row Lock)
NONE
PTABLOCK: (Position Scan Table Lock)
INTENT NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
2) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.58372e-05
Predicate Text:
--------------
(Q1.JOBTITLE = 'cmg100')
2) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.58372e-05
Predicate Text:
--------------
(Q1.JOBTITLE = 'cmg100')
Gap Info: Status
--------- ------
Index Column 1: Gap
Index Column 2: No Gap
Input Streams:
-------------
1) From Object DB2INST1.IDX_DEPT_JOBTITLE1
Estimated number of rows: 30000
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.DEPARTMENT(A)+Q1.$RID$+Q1.JOBTITLE
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 1.07511
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.DEPARTMENT(A)+Q1.$RID$+Q1.JOBTITLE
Objects Used in Access Plan:
---------------------------
Schema: DB2INST1
Name: IDX_DEPT_JOBTITLE1
Type: Index
Time of creation: 2024-10-30-12.58.21.091868
Last statistics update: 2024-10-30-13.00.17.986246
Number of columns: 2
Number of rows: 30000
Width of rows: -1
Number of buffer pool pages: 356
Distinct row values: No
Tablespace name: CGM
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 64
Container extent page count: 32
Index clustering statistic: 99.000000
Index leaf pages: 326
Index tree levels: 3
Index full key cardinality: 30000
Index first key cardinality: 2
Index first 2 keys cardinality: 30000
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 300
Index page density: 81
Index avg sequential pages: 100
Index avg gap between sequences:10
Index avg random pages: 6
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 30000
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: DB2INST1
Base Table Name: EMPLOYEES1
Columns in index:
DEPARTMENT(A)
JOBTITLE(A)
Null keys: Yes
Schema: DB2INST1
Name: EMPLOYEES1
Type: Table
Time of creation: 2024-10-30-12.58.21.048841
Last statistics update: 2024-10-30-13.00.17.986246
Number of columns: 4
Number of rows: 30000
Width of rows: 44
Number of buffer pool pages: 356
Number of data partitions: 1
Distinct row values: No
Tablespace name: CGM
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 64
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
2.RIDSCAN
RIDSCAN
Scans a list of row identifiers (RIDs) obtained from one or more indexes
表中有多个索引时，并且where条件中使用到了多个索引的过滤条件进行 or 连接，优化器会考虑把多个索引的结果通过排序获取到RIDSCAN，然后从表中去获取数据
2.1.生成模拟数据
echo "CREATE TABLE Employees2 (
ID INT,
Name VARCHAR(100),
Department VARCHAR(100),
JobTitle VARCHAR(100)
)@
-- 创建一个复合索引，但将第一个列设置为非最左前缀
CREATE INDEX idx_dept_jobtitle2 ON Employees2 (Department)@
CREATE INDEX idx_dept_jobtitle3 ON Employees2 (JobTitle)@
begin
DECLARE i INT DEFAULT 1;
WHILE i <= 30000 DO
INSERT INTO employees2 VALUES (i, 'hr' || i,'ms0'||i,'cmg'||i);
SET i = i + 1;
END WHILE;
COMMIT;
END@"|db2 -td@ +p
db2 runstats on table Employees2 and indexes all
2.2.生成执行计划
db2 explain plan for "SELECT * FROM Employees2 WHERE JobTitle = 'cmg100' or Department='ms01000'" --表 EXPLAIN_OPERATOR 中 OPERATOR_TYPE='RIDSCN'
echo -e '\n'|db2exfmt -d enmo -o db2exfmt.out2
2.3.查看执行计划
查询中使用到多个索引的条件进行 or 过滤，可以看到执行计划中用到了 RIDSCN: (Row Identifier Scan) 算子
Optimized Statement:
-------------------
SELECT
Q1.ID AS "ID",
Q1.NAME AS "NAME",
Q1.DEPARTMENT AS "DEPARTMENT",
Q1.JOBTITLE AS "JOBTITLE"
FROM
DB2INST1.EMPLOYEES2 AS Q1
WHERE
((Q1.JOBTITLE = 'cmg100') OR
(Q1.DEPARTMENT = 'ms01000'))
Access Plan:
-----------
Total Cost: 45.425
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1.99997
FETCH
( 2)
45.425
5.99742
/----+-----\
1.99997 30000
RIDSCN TABLE: DB2INST1
( 3) EMPLOYEES2
30.31 Q1
4
/-------+-------\
1 1
SORT SORT
( 4) ( 6)
15.1553 15.1553
2 2
| |
1 1
IXSCAN IXSCAN
( 5) ( 7)
15.1541 15.1541
2 2
| |
30000 30000
INDEX: DB2INST1 INDEX: DB2INST1
IDX_DEPT_JOBTITLE3 IDX_DEPT_JOBTITLE2
Q1 Q1
Extended Diagnostic Information:
--------------------------------
No extended Diagnostic Information for this statement.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 45.425
Cumulative CPU Cost: 181955
Cumulative I/O Cost: 5.99742
Cumulative Re-Total Cost: 0.0144526
Cumulative Re-CPU Cost: 31116.2
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 37.8765
Estimated Bufferpool Buffers: 7.99742
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v10.5.0.9 : s170908
HEAPUSE : (Maximum Statement Heap Usage)
80 Pages
PLANID : (Access plan identifier)
889183c77ea9ea5b
PREPTIME: (Statement prepare time)
15 milliseconds
SEMEVID : (Semantic environment identifier)
431f78d03d9bb07e
STMTHEAP: (Statement heap size)
8192
STMTID : (Normalized statement identifier)
a8315c84a7717111
Input Streams:
-------------
9) From Operator #2
Estimated number of rows: 1.99997
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.JOBTITLE+Q2.DEPARTMENT+Q2.NAME+Q2.ID
2) FETCH : (Fetch)
Cumulative Total Cost: 45.425
Cumulative CPU Cost: 181955
Cumulative I/O Cost: 5.99742
Cumulative Re-Total Cost: 0.0144526
Cumulative Re-CPU Cost: 31116.2
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 37.8765
Estimated Bufferpool Buffers: 7.99742
Arguments:
---------
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SPEED : (Assumed speed of scan, in sharing structures)
SLOW
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
FALSE
VISIBLE : (May be included in scan sharing structures)
FALSE
WRAPPING: (Scan may start anywhere and wrap)
FALSE
Predicates:
----------
2) Sargable Predicate,
Comparison Operator: Not Applicable
Subquery Input Required: No
Filter Factor: 6.66656e-05
Predicate Text:
--------------
((Q1.JOBTITLE = 'cmg100') OR
(Q1.DEPARTMENT = 'ms01000'))
Input Streams:
-------------
7) From Operator #3
Estimated number of rows: 1.99997
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$(A)
8) From Object DB2INST1.EMPLOYEES2
Estimated number of rows: 30000
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.NAME+Q1.ID+Q1.DEPARTMENT+Q1.JOBTITLE
Output Streams:
--------------
9) To Operator #1
Estimated number of rows: 1.99997
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.JOBTITLE+Q2.DEPARTMENT+Q2.NAME+Q2.ID
3) RIDSCN: (Row Identifier Scan)
Cumulative Total Cost: 30.31
Cumulative CPU Cost: 150778
Cumulative I/O Cost: 4
Cumulative Re-Total Cost: 0.0120328
Cumulative Re-CPU Cost: 25906.3
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 30.3094
Estimated Bufferpool Buffers: 6
Arguments:
---------
NUMROWS : (Estimated number of rows)
2
Input Streams:
-------------
3) From Operator #4
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$(A)
6) From Operator #6
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$(A)
Output Streams:
--------------
7) To Operator #2
Estimated number of rows: 1.99997
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$(A)
4) SORT : (Sort)
Cumulative Total Cost: 15.1553
Cumulative CPU Cost: 76095.7
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00554331
Cumulative Re-CPU Cost: 11934.7
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 15.1553
Estimated Bufferpool Buffers: 3
Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
TRUE
NUMROWS : (Estimated number of rows)
1
ROWWIDTH: (Estimated width of rows)
20
SORTKEY : (Sort Key column)
1: Q1.$RID$(A)
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
TRUE
Input Streams:
-------------
2) From Operator #5
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.JOBTITLE(A)+Q1.$RID$
Output Streams:
--------------
3) To Operator #3
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$(A)
5) IXSCAN: (Index Scan)
Cumulative Total Cost: 15.1541
Cumulative CPU Cost: 73395.7
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00554331
Cumulative Re-CPU Cost: 11934.7
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 15.1533
Estimated Bufferpool Buffers: 3
Arguments:
---------
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
SEQUENTIAL,READAHEAD
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
UR_EXTRA: (UR plus extra table lock and may upgrade to CS)
TRUE
Predicates:
----------
3) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.33333e-05
Predicate Text:
--------------
(Q1.JOBTITLE = 'cmg100')
3) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.33333e-05
Predicate Text:
--------------
(Q1.JOBTITLE = 'cmg100')
Input Streams:
-------------
1) From Object DB2INST1.IDX_DEPT_JOBTITLE3
Estimated number of rows: 30000
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.JOBTITLE(A)+Q1.$RID$
Output Streams:
--------------
2) To Operator #4
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.JOBTITLE(A)+Q1.$RID$
6) SORT : (Sort)
Cumulative Total Cost: 15.1553
Cumulative CPU Cost: 76095.7
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00554331
Cumulative Re-CPU Cost: 11934.7
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 15.1553
Estimated Bufferpool Buffers: 3
Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
TRUE
NUMROWS : (Estimated number of rows)
1
ROWWIDTH: (Estimated width of rows)
20
SORTKEY : (Sort Key column)
1: Q1.$RID$(A)
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
TRUE
Input Streams:
-------------
5) From Operator #7
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.DEPARTMENT(A)+Q1.$RID$
Output Streams:
--------------
6) To Operator #3
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$(A)
7) IXSCAN: (Index Scan)
Cumulative Total Cost: 15.1541
Cumulative CPU Cost: 73395.7
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00554331
Cumulative Re-CPU Cost: 11934.7
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 15.1533
Estimated Bufferpool Buffers: 3
Arguments:
---------
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
SEQUENTIAL,READAHEAD
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
UR_EXTRA: (UR plus extra table lock and may upgrade to CS)
TRUE
Predicates:
----------
4) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.33333e-05
Predicate Text:
--------------
(Q1.DEPARTMENT = 'ms01000')
4) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 3.33333e-05
Predicate Text:
--------------
(Q1.DEPARTMENT = 'ms01000')
Input Streams:
-------------
4) From Object DB2INST1.IDX_DEPT_JOBTITLE2
Estimated number of rows: 30000
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.DEPARTMENT(A)+Q1.$RID$
Output Streams:
--------------
5) To Operator #6
Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.DEPARTMENT(A)+Q1.$RID$
Objects Used in Access Plan:
---------------------------
Schema: DB2INST1
Name: IDX_DEPT_JOBTITLE2
Type: Index
Time of creation: 2024-10-30-14.25.22.423935
Last statistics update: 2024-10-30-14.26.14.356491
Number of columns: 1
Number of rows: 30000
Width of rows: -1
Number of buffer pool pages: 388
Distinct row values: No
Tablespace name: CGM
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 64
Container extent page count: 32
Index clustering statistic: 98.000000
Index leaf pages: 322
Index tree levels: 3
Index full key cardinality: 30000
Index first key cardinality: 30000
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 59
Index page density: 50
Index avg sequential pages: 59
Index avg gap between sequences:0
Index avg random pages: 261
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 30000
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: DB2INST1
Base Table Name: EMPLOYEES2
Columns in index:
DEPARTMENT(A)
Null keys: Yes
Schema: DB2INST1
Name: IDX_DEPT_JOBTITLE3
Type: Index
Time of creation: 2024-10-30-14.25.22.447166
Last statistics update: 2024-10-30-14.26.14.356491
Number of columns: 1
Number of rows: 30000
Width of rows: -1
Number of buffer pool pages: 388
Distinct row values: No
Tablespace name: CGM
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 64
Container extent page count: 32
Index clustering statistic: 98.000000
Index leaf pages: 322
Index tree levels: 3
Index full key cardinality: 30000
Index first key cardinality: 30000
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 218
Index page density: 46
Index avg sequential pages: 218
Index avg gap between sequences:0
Index avg random pages: 102
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 30000
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: DB2INST1
Base Table Name: EMPLOYEES2
Columns in index:
JOBTITLE(A)
Null keys: Yes
Schema: DB2INST1
Name: EMPLOYEES2
Type: Table
Time of creation: 2024-10-30-14.25.22.380527
Last statistics update: 2024-10-30-14.26.14.356491
Number of columns: 4
Number of rows: 30000
Width of rows: 49
Number of buffer pool pages: 388
Number of data partitions: 1
Distinct row values: No
Tablespace name: CGM
Tablespace overhead: 7.500000
Tablespace transfer rate: 0.060000
Source for statistics: Single Node
Prefetch page count: 64
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
最后修改时间：2024-10-30 15:29:57
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
