Introduction to NonStop SQL/MP
Physical Database Structure
NonStop SQL Architecture
3–6 113425 Tandem Computers Incorporated
Figure 3-2. An Index for Faster Sorted Access
010
EMPLOYEE Table
EMPNUM FIRST_NAME LAST_NAME ••• DEPTNUM
ROGER
JERRY
JANE
•••
ERIC
•••
RICHARD
•••
MARLENE
•••
HERB
•••
GREEN
HOWARD
RAYMOND
•••
BROWN
•••
BARTON
•••
BONNY
•••
ALBERT
•••
XEMPNAME Index
231
210
217
87
•••
•••
ALBERT
BARTON
BONNY
BROWN
•••
•••
LAST_NAME FIRST_NAME EMPNUM
HERB
RICHARD
MARLENE
ERIC
•••
•••
This clause orders the index by LAST_NAME, FIRST_NAME.
CREATE INDEX XEMPNAME
ON EMPLOYEE
( LAST_NAME, FIRST_NAME )
CATALOG PERSNL ;
SELECT EMPNUM, FIRST_NAME,
LAST_NAME, DEPTNUM
FROM EMPLOYEE
ORDER BY LAST_NAME, FIRST_NAME
EMPNUM
------
231
210
217
87
•••
•••
FIRST_NAME
----------
HERB
RICHARD
MARLENE
ERIC
•••
•••
LAST_NAME
---------
ALBERT
BARTON
BONNY
BROWN
•••
•••
DEPTNUM
-------
3300
1000
4000
4000
•••
•••
1
23
29
•••
87
•••
210
•••
217
•••
231
•••
9000
1000
3000
•••
4000
•••
1000
•••
4000
•••
3300
•••
•••
•••
•••
•••
•••
•••
•••
•••
•••
•••
•••
•••
Index definitions are stored separately from base table definitions. Each index has a
name and is stored in a key-sequenced file of the same name as the index. Index files
are not tables, and you cannot query an index directly. NonStop SQL/MP uses
indexes to provide faster access to tables. Each index is an access path to a table. Even
when you expect the system to use index-only access, your SQL query must refer to
the base table, not the index.