SAP Knowledge Base Article - Preview

2816541 - Functional index using RIGHT function is not used when statement cache is enabled - SAP ASE

Symptom

Functional index using RIGHT function is not used when statement cache is ON:

CREATE INDEX idx2 ON teste_C (right(colb, 8))

======

1> select * from teste_X where (right(colb, 8)) = 'YYYYYYYYY'
2> go


The type of query is SELECT.
2 operator(s) under root
|ROOT:EMIT Operator (VA = 2)
|
| |RESTRICT Operator (VA = 1)(0)(0)(0)(6)(0)
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | teste_X
| | | Table Scan.
| | | Forward Scan.
| | | Positioning at start of table.
| | | Using I/O Size 4 Kbytes for data pages.
| | | With LRU Buffer Replacement Strategy for data pages.

======

When statement cache is disabled then index is used:

======

STEP 1
The type of query is SELECT.
1 operator(s) under root
|ROOT:EMIT Operator (VA = 1)
|| |SCAN Operator (VA = 0)
| | FROM TABLE
| | teste_X
| | Index : idx2
| | Forward Scan.
| | Positioning by key.
| | Keys are:
| | sybfi3_1 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.

=====


Read more...

Environment

  • SAP Adaptive Server Enterprise (ASE) 15.7 SP139
  • SAP Adaptive Server Enterprise (ASE) 16.0 SP03 PL07

Product

SAP Adaptive Server Enterprise 15.7

Keywords

table scan 819712 CR 819712 functional index RIGHT function statement cache ON enable enabled , 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.