Index range accesses in Database Analyzer

[former_member219338]

Dear all,

there are a lot of index range acesses in DB Analyzer:

x index range accessess, selectivity x.xx %: xxxxx rows read, xxxxx rows qualified.

These messages are displayed as W2 alert.

What does the message means and how can it be solved?

[Former Member]

Hi,

index range is an optimizer strategy which tells you that an index is used but the indexed columns are not completely qualified or we have like or range conditions in the where condition. So a range has to be executed on the index ( start and stop key) and all rows (Primary keys) which are affected in the index range have to be read on the table.
The optimizer must read more rows to find the qualified records which belong to the result.
So we have rows read > rows qualified.

This could be a hint that with an additional index the access could be optimized.

But it could be as well the best strategy if eg. you have a like or a range condition in the where condition on an indexed column then always a range has to be executed - which is fine in this case.

If there is a huge difference between rows read > and rows qual then a warning is sent in the DB-Analyzer.

SQL Optimization is a very complex issue - to get familiar with the ooptimizer I recommend to use Expert Session

16 (1) and 16(2) about SQL Optimizer - you 'll find the recording and slides here: SAP MaxDB: The SAP Database - Training for download.

You can use the Command Monitor to catch those SQL commands which are executed via index range. Then you could analyze the where condition of these statements. May be you find a better index definition for those SQL commands.

Finally those messages in DB-Analyzer are a hint that a SQL Performance Analysis could reduce the workload of the system if suitable new indexes can be created.

Regards, Christiane

[former_member219338]

Hi,

thanks. And what means isolated index range accesses?

Regards,

Damian

[Former Member]

Hi,

Index Range accesses are ranges on indexes to find the rows to access the table afterwards.

Isolated Index Range accesses are used for Index Only strategies which means all data which is in the select list qualified can be fetched by the index only access - this means no additional access on the base table is necessary.

E..g select  key_column1,key_column2, Index_col1,Index_col2

where index_col2 > <value>

Because of the fact that primary key columns are stored as seperator in the index b* tree as well, you have all needed information on the index. No additional access on base table.

Regards, Christiane