SAP Knowledge Base Article - Preview

2678697 - Optimizer is not choosing right index - SAP ASE

Symptom

Optimizer is not choosing the right index:

STEP 1
The type of query is SELECT.

5 operator(s) under root

|ROOT:EMIT Operator (VA = 5)
|
| |N-ARY NESTED LOOP JOIN Operator (VA = 4) has 3 children.
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | tb01
| | | Index : tb01_ix02
| | | Forward Scan.
| | | Positioning by key.
| | | Keys are:
| | | cola ASC
| | | Using I/O Size 2 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.
| |
| | |SCAN Operator (VA = 1)
| | | FROM TABLE
| | | tb02
| | | Index : tb02_ix01
| | | Forward Scan.
| | | Positioning by key.
| | | Keys are:
| | | colb ASC
| | | Using I/O Size 2 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.
| |
| | |RESTRICT Operator (VA = 3)(0)(0)(0)(13)(0)
| | |
| | | |SCAN Operator (VA = 2)
| | | | FROM TABLE
| | | | tb03
| | | | Index : Tb03_ix01
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Keys are:
| | | | colc ASC
| | | | Using I/O Size 2 Kbytes for index leaf pages.
| | | | With LRU Buffer Replacement Strategy for index leaf pages.
| | | | Using I/O Size 2 Kbytes for data pages.
| | | | With LRU Buffer Replacement Strategy for data pages.

=====

Notes: the correct index would be tb01.ix01 and not tb01.ix02


Read more...

Environment

  •  SAP Adaptive Server Enterprise (ASE) 16.0

Product

SAP Adaptive Server Enterprise 16.0

Keywords

Optimizer not choosing choose right index 811966 , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem

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.