access plan 分析

有一个query如下。

  select *   from alt.debit_request   where debit_request_status_id =6 and debit_request_type_id =5 and         amount=0

分别有两个index在debit_request_status_id 和debit_request_type_id上时的access plan 是这样的，cost是36824,

Section Code Page = 1208

Estimated Cost = 6824.998047

Estimated Cardinality = 0.032630

Access Table Name = ALT.DEBIT_REQUEST  ID = 23,2315

|  Index Scan:  Name = ALT.IX_DB_REQ_STAT_ID  ID = 2

|  |  Regular Index (Not Clustered)

|  |  Index Columns:

|  |  |  1: DEBIT_REQUEST_STATUS_ID (Ascending)

|  #Columns = 13

|  Skip Inserted Rows

|  Avoid Locking Committed Data

|  Currently Committed for Cursor Stability

|  Evaluate Predicates Before Locking for Key

|  #Key Columns = 1

|  |  Start Key: Inclusive Value

|  |  |  1: 6

|  |  Stop Key: Inclusive Value

|  |  |  1: 6

|  Data Prefetch: Sequential(5709), Readahead

|  Index Prefetch: Sequential(287), Readahead

|  Lock Intents

|  |  Table: Intent Share

|  |  Row  : Next Key Share

|  Sargable Predicate(s)

|  |  #Predicates = 2

|  |  Return Data to Application

|  |  |  #Columns = 14

Return Data Completion

但是我建了个composite index on 2 columns之后, access plan却提高到了138964，不知道大家有什么看法？

Estimated Cost = 138964.015625

Estimated Cardinality = 20.239891

Access Table Name = ALT.DEBIT_REQUEST  ID = 23,2315

|  Index Scan:  Name = ALT.IX_DB_REQ_STAT_ID222  ID = 7

|  |  Regular Index (Not Clustered)

|  |  Index Columns:

|  |  |  1: DEBIT_REQUEST_TYPE_ID (Ascending)

|  |  |  2: DEBIT_REQUEST_STATUS_ID (Ascending)

|  #Columns = 13

|  Skip Inserted Rows

|  Avoid Locking Committed Data

|  Currently Committed for Cursor Stability

|  Evaluate Predicates Before Locking for Key

|  #Key Columns = 2

|  |  Start Key: Inclusive Value

|  |  |  1: 5

|  |  |  2: 6

|  |  Stop Key: Inclusive Value

|  |  |  1: 5

|  |  |  2: 6

|  Data Prefetch: Sequential(131300), Readahead

|  Index Prefetch: Sequential(5682), Readahead

|  Lock Intents

|  |  Table: Intent Share

|  |  Row  : Next Key Share

|  Sargable Predicate(s)

|  |  #Predicates = 1

|  |  Return Data to Application

|  |  |  #Columns = 14

Return Data Completion

End of section

Index creation syntax:

2 Separate indexes

CREATE INDEX "ALT     "."IX_DB_REQ_TYPE_ID" ON "ALT     "."DEBIT_REQUEST"

                ("DEBIT_REQUEST_TYPE_ID" ASC)

                PCTFREE 10 MINPCTUSED 10

                COMPRESS NO

                INCLUDE NULL KEYS ALLOW REVERSE SCANS;

CREATE INDEX "ALT     "."IX_DB_REQ_STAT_ID" ON "ALT     "."DEBIT_REQUEST"

                ("DEBIT_REQUEST_STATUS_ID" ASC)

                PCTFREE 10 MINPCTUSED 10

                COMPRESS NO

                INCLUDE NULL KEYS ALLOW REVERSE SCANS;

Composite index:

CREATE INDEX "ALT     "."IX_DB_REQ_STAT_ID222" ON "ALT     "."DEBIT_REQUEST"

                ("DEBIT_REQUEST_TYPE_ID" ASC,

                 "DEBIT_REQUEST_STATUS_ID" ASC)

                PCTFREE 10 MINPCTUSED 10

                COMPRESS NO

                INCLUDE NULL KEYS ALLOW REVERSE SCANS;