SAP Knowledge Base Article - Preview

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

Symptom

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.

  1. 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.


=====


Read more...

Environment

SAP Adaptive Server Enterprise 16.0 SP03 PL10

Product

SAP Adaptive Server Enterprise 16.0

Keywords

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

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.