SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Accessing SQL/MX Data

HP NonStop SQL/MX Query Guide—523728-003

2-3

Index-Only Access

Index-only access is not used if any of these conditions are true:

•

The columns required by the query are not all included in the index.

•

The query performs an update. Even if the index contains the column, the base

table column (and any other index containing the column) also needs to be

updated.

The next figure shows a query that contains an indexed item (deptnum) in the WHERE

clause. The empnum column is the clustering key for the table and is also a column of

the index. (Remember that an index contains one or more columns plus the columns

that make up the clustering key of the table.) The compiler goes directly to the index to

retrieve the information because:

•

The cost is much less than using a full table scan.

•

The query requests information that is contained in the index.

The query execution plan shows the index-only access.

empnum first_name last_name deptnum jobcode salary

TABLE employee

Base Table

SELECT empnum FROM employee

WHERE deptnum = 3100;

ROOT

PARTITION_ACCESS

INDEX_SCAN

QUERY PLAN

deptnum empnum

3100

228

229

993

994

995

INDEX xempdept

vst022.vsd

Index on deptnum