3127034 - Index is not correctly used after upgrade to ASE 16.0 SP03 PL10 - SAP ASE

Index is not correctly used after upgrade to ASE 16.0 SP03 PL10. In the good plan, index is positioned by key while in the bad plan, index is positioned at index start. The join order is not the best too.

If we split, in the other words, "#tmp table" is created first followed by "go", then the index is correctly used. If the query is executed all in one "go", the index is not correctly used.

See:

1. BAD PLAN

create table #tmp_Counts (id int, currency char(3), reconciled_until numeric(12,0))
insert #tmp_Counts (id, currency, reconciled_until) values (26363,'XXX',143412772)
select t.id as id
from tab91 t (index tab91_ix5), #tmp_Counts la
where t.account_id = la.id
and t.id >= la.reconciled_until
and isnull (t.reconciled, 0) = 0
go

| |MERGE JOIN Operator (Join Type: Inner Join) (VA = 4)
| | Using Worktable2 for internal storage.
| | Key Count: 1
| | Key Ordering: ASC
| |
| | |RESTRICT Operator (VA = 1)(0)(0)(0)(8)(0)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | tab91
| | | | t
| | | | Index : tab91_ix5
| | | | Forward Scan.
| | | | Positioning at index start.
| | | | Using I/O Size 4 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | Using I/O Size 4 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.
| |
| | |SORT Operator (VA = 3)
| | | Using Worktable1 for internal storage.
| | |
| | | |SCAN Operator (VA = 2)
| | | | FROM TABLE
| | | | #tmp_Counts
| | | | la
| | | | Table Scan.
| | | | Forward Scan.
| | | | Positioning at start of table.
| | | | Using I/O Size 2 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.

2. GOOD PLAN

create table #tmp_Counts (id int, currency char(3), reconciled_until numeric(12,0))
go

insert #tmp_Counts (id, currency, reconciled_until) values (26363,'XXX',143412772)
select t.id as id
from tab91 t (index tab91_ix5), #tmp_Counts la
where t.account_id = la.id
and t.id >= la.reconciled_until
and isnull (t.reconciled, 0) = 0
go

|ROOT:EMIT Operator (VA = 4)
|
| |NESTED LOOP JOIN Operator (VA = 3) (Join Type: Inner Join)
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | #tmp_Counts
| | | la
| | | Table Scan.
| | | Forward Scan.
| | | Positioning at start of table.
| | | Using I/O Size 2 Kbytes for data pages.
| | | With LRU Buffer Replacement Strategy for data pages.
| |
| | |RESTRICT Operator (VA = 2)(0)(0)(0)(8)(0)
| | |
| | | |SCAN Operator (VA = 1)
| | | | FROM TABLE
| | | | tab91
| | | | t
| | | | Index : tab91_ix5
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Keys are:
| | | | account_id ASC
| | | | id ASC
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | Using I/O Size 4 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.

=====

SAP Adaptive Server Enterprise 16.0 SP03 PL10

bad plan Forward Scan MERGE JOIN Positioning at index start NESTED LOOP JOIN Positioning by key 825893 CR 825893 CR 825893 CR# 825893 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed