3289046 - Different query plan after applying SP03 PL12 - SAP ASE

• After applying  ASE 16.0 SP03 PL12 then it was noted procedures with different plans from ASE 16.0 SP03 PL10.

• The following query:

          ======

           select col1
           from tab01 a (index ix01)
           where a.status in ('a', 'vi b', 'vi')
           and a.col01 >= '20221017 00:00:00'
           and a.col02 <= '20221017 23:59:59'

           ======

• PLAN ASE 16.0 SP03 PL10

ROOT:EMIT Operator (VA = 4)
|
| |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
| |
| | |SCAN Operator (VA = 0)
| | | FROM OR List
| | | OR List has up to 3 rows of OR/IN values.
| |
| | |RESTRICT Operator (VA = 2)(5)(0)(0)(0)(0)
| | |
| | | |SCAN Operator (VA = 1)
| | | | FROM TABLE
| | | | tab01
| | | | a
| | | | Index : ix01
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Keys are:
| | | | col01 ASC
| | | | col02 ASC
| | | | col03 ASC
| | | | Using I/O Size 16 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | Using I/O Size 16 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.

=======

• PLAN ASE 16.0 SP03 PL12

QUERY PLAN FOR STATEMENT 2 (at line 1).
Optimized using Serial Mode
Optimized using the forced options (internally generated Abstract Plan).

STEP 1
The type of query is SELECT.

2 operator(s) under root

|ROOT:EMIT Operator (VA = 2)
|
|   |RESTRICT Operator (VA = 1)(5)(0)(0)(10)(0)
|   |
|   |   |SCAN Operator (VA = 0)
|   |   |  FROM TABLE
|   |   |  tab01
|   |   |  a
|   |   |  Index : ix01
|   |   |  Forward Scan.
|   |   |  Positioning by key.
|   |   |  Keys are:
|   |   |    col01 ASC
|   |   |    col02 ASC
|   |   |  Using I/O Size 16 Kbytes for index leaf pages.
|   |   |  With LRU Buffer Replacement Strategy for index leaf pages.
|   |   |  Using I/O Size 16 Kbytes for data pages.
|   |   |  With LRU Buffer Replacement Strategy for data pages.

=======

• The data is the same and the index is: create nonclustered index ix01 on tab01 (col01, col02, col03).
• In SP03 PL12, we can see that optimizer is not using all columns defined on index and it uses much more logical IO's.

"Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental "

• SAP Adaptive Server Enterprise 16.0 SP03 PL10
• SAP Adaptive Server Enterprise 16.0 SP03 PL12

bad different query plan optimizer more logical IOS ios performance index covered all columns NESTED LOOP JOIN inner join FROM OR LIST parameter cost of a physical io 827583 CR 827583 CR827583 CR#827583 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem