 |
» |
|
|
|
SYSTEM.INDEX contains an entry for each index created by a user on a table.
This view is initially empty, but is updated whenever ALLBASE/SQL
processes a CREATE INDEX, DROP INDEX, DROP TABLE, TRANSFER
OWNERSHIP (of a table), or UPDATE STATISTICS statement. Table 8-17 System.Index |
Column Name | Type | Length | Description |
|---|
| INDEXNAME | CHAR | 20 | Name of the index | | TABLENAME | CHAR | 20 | Name of the table on which the
index or hash structure is defined | | OWNER | CHAR | 20 | Owner of the table on which the
index is defined | | UNIQUE | SMALLINT | 2 | Uniqueness indicator:
- 0
if duplicates are allowed, that is, the index is not unique - 1
if duplicates are not allowed, that is the index is unique
| | CLUSTER | SMALLINT | 2 | Clustering indicator:
- 0
if the index is not a clustering index
- 1
if the index is the clustering index for the table
| | NUMC | INTEGER | 4 | Number of columns in the index or hash key | | COLNUMS | BINARY | 64 | A vector of column numbers,
each of type SMALLINT, identifying
the columns the index is defined over.
In ISQL, each SMALLINIT (two-byte) entry is
displayed as a field of 4 hexadecimal digits. | | NPAGES | INTEGER | 4 | Number of index pages containing the index | | NLEVELS | INTEGER | 4 | Number of B-tree levels | | NLEAVES | INTEGER | 4 | Number of B-tree leaf pages | | NDISTINCT | INTEGER | 4 | Number of distinct keys | | NFIRST | INTEGER | 4 | Number of distinct first column
values of the B-tree key | | NPERKEY | INTEGER | 4 | Number of pages per B-tree key
| | CCOUNT | INTEGER | 4 | Cluster count; indicates how well the data of the
index is sorted
- 0
before first UPDATE STATISTICS statement is
processed - n
efficiency of clustering: best clustering if
n=NPATES of table indexed; worst if n=NROWS of
table indexed
| | CTIME | CHAR | 16 | Time of creation: yyyymmddhhsstt | | COLDIRS | BINARY | 64 | A vector of direction entries,
each of type SMALLINT indicating
the direction of the corresponding
column in the index definition.
In ISQL, each SMALLINIT (two-byte) entry is
displayed as a field of 4 hexadecimal digits.
- 5
ASC (Ascending)
- 6
DESC (Descending)
|
Example | |
SELECT * FROM System.Index;
--------------------+--------------------+--------------------+------+-----
INDEXNAME |TABLENAME |OWNER |UNIQUE|CLUST
--------------------+--------------------+--------------------+------+-----
PARTNUMINDEX |PARTS |PURCHDB | 1|
PARTTONUMINDEX |SUPPLYPRICE |PURCHDB | 0|
PARTTOVENDINDEX |SUPPLYPRICE |PURCHDB | 0|
VENDPARTINDEX |SUPPLYPRICE |PURCHDB | 1|
VENDORNUMINDEX |VENDORS |PURCHDB | 1|
ORDERNUMINDEX |ORDERS |PURCHDB | 1|
ORDERVENDINDEX |ORDERS |PURCHDB | 0|
ORDERITEMINDEX |ORDERITEMS |PURCHDB | 1|
INVPARTNUMINDEX |INVENTORY |PURCHDB | 1|
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
-+--------------------+------+-------+-----------+-------------------------
|OWNER |UNIQUE|CLUSTER|NUMC |COLNUMS
-+--------------------+------+-------+-----------+-------------------------
|PURCHDB | 1| 0| 1|0001000000000000000000000
|PURCHDB | 0| 1| 1|0001000000000000000000000
|PURCHDB | 0| 0| 1|0002000000000000000000000
|PURCHDB | 1| 0| 1|0003000000000000000000000
|PURCHDB | 1| 0| 1|0001000000000000000000000
|PURCHDB | 1| 1| 1|0001000000000000000000000
|PURCHDB | 0| 0| 1|0002000000000000000000000
|PURCHDB | 1| 1| 2|0001000200000000000000000
|PURCHDB | 1| 0| 1|0001000000000000000000000
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
---------+----------------------------------------------------------------+
MC |COLNUMS |
---------+----------------------------------------------------------------+
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0002000000000000000000000000000000000000000000000000000000000000|
1|0003000000000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0002000000000000000000000000000000000000000000000000000000000000|
2|0001000200000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
----------------------------------+-----------+-----------+-----------+----
|NPAGES |NLEVELS |NLEAVES |NDIS
----------------------------------+-----------+-----------+-----------+----
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 0| 0| 0|
0000000000000000000000000000000000| 1| 0| 1|
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
------+-----------+-----------+-----------+-----------+-----------+--------
S |NLEVELS |NLEAVES |NDISTINCT |NFIRST |NPERKEY |CCOUNT
------+-----------+-----------+-----------+-----------+-----------+--------
1| 0| 1| 22| 22| 1|
1| 0| 1| 22| 22| 1|
1| 0| 1| 15| 15| 1|
1| 0| 1| 69| 69| 1|
1| 0| 1| 18| 18| 1|
1| 0| 1| 17| 17| 1|
1| 0| 1| 12| 12| 1|
0| 0| 0| 0| 0| 0|
1| 0| 1| 22| 22| 1|
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
--+-----------+-----------+-----------+----------------+-------------------
|NFIRST |NPERKEY |CCOUNT |CTIME |COLDIRS
--+-----------+-----------+-----------+----------------+-------------------
22| 22| 1| 1|1996020600084900|0005000000000000000
22| 22| 1| 1|1996020600085000|0005000000000000000
15| 15| 1| 1|1996020600085000|0005000000000000000
69| 69| 1| 1|1996020600085000|0005000000000000000
18| 18| 1| 1|1996020600085100|0005000000000000000
17| 17| 1| 1|1996020600085100|0005000000000000000
12| 12| 1| 1|1996020600085100|0005000000000000000
0| 0| 0| 0|1996070805153400|0005000500000000000
22| 22| 1| 1|1996020600085200|0005000000000000000
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
---------------+-----------------------------------------------------------
TIME |COLDIRS
---------------+-----------------------------------------------------------
996020600084900|00050000000000000000000000000000000000000000000000000000000
996020600085000|00050000000000000000000000000000000000000000000000000000000
996020600085000|00050000000000000000000000000000000000000000000000000000000
996020600085000|00050000000000000000000000000000000000000000000000000000000
996020600085100|00050000000000000000000000000000000000000000000000000000000
996020600085100|00050000000000000000000000000000000000000000000000000000000
996020600085100|00050000000000000000000000000000000000000000000000000000000
996070805153400|00050005000000000000000000000000000000000000000000000000000
996020600085200|00050000000000000000000000000000000000000000000000000000000
---------------------------------------------------------------------------
Number of rows selected is 9
|
|
