SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

Accessing SQL/MX Data

HP NonStop SQL/MX Release 3.2 Query Guide—663851-002

2-18

MDAM’s Use of DENSE and SPARSE Algorithms

followed by the executor. For example, if the optimizer chooses a DENSE algorithm

and the executor finds the DENSE algorithm is inefficient for accessing the column, the

executor adapts by switching to a SPARSE algorithm when it discovers that many

values are missing. The executor chooses the appropriate algorithm for the density or

sparseness of data.

When the compiler chooses a SPARSE algorithm, the executor executes only the

SPARSE algorithm and does not attempt to switch to a DENSE algorithm.

You can force the choice by specifying the SPARSE or DENSE option in the

CONTROL QUERY SHAPE statement. If you force a DENSE algorithm, the executor

does an adaptive DENSE or SPARSE and switches accordingly when it finds that the

chosen algorithm is not efficient for the column it is accessing. If you force a SPARSE

algorithm, the executor uses only the SPARSE algorithm.

In the next figure, Table A shows that Col. 1 has values that always increase by one. If

the compiler chooses MDAM DENSE, the executor starts with DENSE until it discovers

a gap in the useful information requested, and then it switches to SPARSE. With the

SPARSE algorithm, the executor must retrieve a row to determine the next actual value

in a column. If the compiler chooses SPARSE, the executor does not attempt DENSE.

TABLE A, Col 1

VST027.vsd

Dense

Sparse

Dense

TP663851.fm Page 18 Thursday, August 2, 2012 4:47 PM