我今天看了一个资料,资料上写的如果谓词是大于操作符,那么它不是范围界定谓词,如果谓词是大于等于操作符,那么它就是范围界定谓词。可是我实验发现不是这样子的啊!
语句1:select count(*) from test where id>500 and id<=1000
Access Table Name = DB2INST1.TEST ID = 2,4
| Index Scan: Name = DB2INST1.IDX_TEST2 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ID (Ascending)
| #Columns = 0
| Avoid Locking Committed Data
| #Key Columns = 1
| | Start Key: Exclusive Value
| | | 1: 500
| | Stop Key: Inclusive Value
| | | 1: 1000
| Index-Only Access
| Index Prefetch: Sequential(2), Readahead
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Predicate Aggregation
| | | Column Function(s)
Aggregation Completion
| Column Function(s)
Return Data to Application
| #Columns = 1
End of section
Optimizer Plan:
Operator
(ID)
RETURN
( 1)
|
GRPBY
( 2)
|
IXSCAN
( 3)
|
Index:
DB2INST1
IDX_TEST2
语句2:select count(*) from test where id>501 and id<=1000
Access Table Name = DB2INST1.TEST ID = 2,4
| Index Scan: Name = DB2INST1.IDX_TEST2 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ID (Ascending)
| #Columns = 0
| Avoid Locking Committed Data
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | 1: 501
| | Stop Key: Inclusive Value
| | | 1: 1000
| Index-Only Access
| Index Prefetch: Sequential(2), Readahead
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Predicate Aggregation
| | | Column Function(s)
Aggregation Completion
| Column Function(s)
Return Data to Application
| #Columns = 1
End of section
Optimizer Plan:
Operator
(ID)
RETURN
( 1)
|
GRPBY
( 2)
|
IXSCAN
( 3)
|
Index:
DB2INST1
IDX_TEST2
看上面两个语句没有什么问题啊?在start key和end key方面都是差不多的啊!
test表的表结构如下:
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID SYSIBM INTEGER 4 0 Yes
NAME SYSIBM VARCHAR 20 0 Yes
收起