INDEXHISTOGRAMBUCKETS System Table Description

Description

A pseudo table containing one row for each bucket in the Index’s histogram. So, if MAXRESOLUTION is 10, there will be ten rows per index.

Client applications should never contain code that accesses SYSTEM pseudo tables as they are subject to, and furthermore likely to, change from release to release.

Fields

Field Type Description

OBJECTID

bigint

The ID for the database object.

CATALOGID

bigint

The catalog Id for the database object.

HISTOGRAMID

integer

The ID for the histogram, unique to the table.

BUCKETINDEX

integer

The index of the bucket within the histogram. Values in this column are 0 to MAXRESOLUTION.

BOUNDARYLOW

string

This value is the lower most value for each field in the index, in that bucket.

BOUNDARYLOW CARD

bigint

The number of times that the BOUNDARYLOW value appears in the index.

BOUNDARY

string

For all but the first bucket, this value is the upper most value for each field in the index, in that bucket. The first bucket contains one key which is the lowest key in the index.

BOUNDARYCARD

bigint

The number of times the BOUNDARY value appears in the index.

BUCKETCARD

bigint

The number of values in the bucket.

BUCKETNDV

bigint

The number of distinct values in the bucket.

Indexes

None

Examples

Example 1

SQL> select * from system.indexhistogrambuckets where catalogid=6;

OBJECTID  CATALOGID  HISTOGRAMID  BUCKETINDEX          BOUNDARYLOW         BOUNDARYLOWCARD            BOUNDARY           BOUNDARYCARD  BUCKETCARD  BUCKETNDV

--------- ---------- ------------ ------------ --------------------------- ---------------- ---------------------------- ------------- ----------- ----------

   1         6           2            0       [ALLSYSTEMFIELDS], [SYSTEM]        1         [DOMAINS], [SYSTEM]                1            7          6
   1         6           2            1       [DUAL], [SYSTEM]                   1         [FORMATS], [SYSTEM]                1            4          4
   1         6           2            2       [FUNCTIONCOLUMNS], [SYSTEM]        1         [INDEXFIELDS], [SYSTEM]            1            4          4
   1         6           2            3       [JAVACLASSES], [SYSTEM]
   1         6           2            4       [PASSWORDS], [SYSTEM]              1         [PROCEDURECOLUMNS], [SYSTEM]       1            4          4
   1         6           2            5       [PROCEDURES], [SYSTEM]             1         [SCHEMAS], [SYSTEM]                1            4          4
   1         6           2            6       [SEQUENCES], [SYSTEM]              1         [TRIGGERCLASSES], [SYSTEM]         1            4          4
   1         6           2            7       [TRIGGERS], [SYSTEM]               1         [VIEW_TABLES], [SYSTEM]            1            5          5
   6         6           2            0       [SYSTEM], [ALLSYSTEMFIELDS]        1         [SYSTEM], [DOMAINS]                1            7          6
   6         6           2            1       [SYSTEM], [DUAL]                   1         [SYSTEM], [FORMATS]                1            4          4
   6         6           2            2       [SYSTEM], [FUNCTIONCOLUMNS]        1         [SYSTEM], [INDEXFIELDS]            1            4          4
   6         6           2            3       [SYSTEM], [JAVACLASSES]            1         [SYSTEM], [PARTITIONIDS]           1            4          4
   6         6           2            4       [SYSTEM], [PASSWORDS]              1         [SYSTEM], [PROCEDURECOLUMNS]       1            4          4
   6         6           2            5       [SYSTEM], [PROCEDURES]             1         [SYSTEM], [SCHEMAS]                1            4          4
   6         6           2            6       [SYSTEM], [SEQUENCES]              1         [SYSTEM], [TRIGGERCLASSES]         1            4          4
   6         6           2            7       [SYSTEM], [TRIGGERS]               1         [SYSTEM], [VIEW_TABLES]            1            5          5

Example 2

In the following example, we are referencing the table SCORING created by SQL QuickStart (see Running the SQL QuickStart). This table has a primary key, SCORING..PRIMARY_KEY, which is defined over five columns. This index was created using the default value of 10 for MAXRESOLUTION.

SELECT s.tablename,
    s.indexname,
    hb.objectid as OBJ,
    hb.catalogid as CAT,
    hb.histogramid as HIST,
    hb.bucketindex as BUCKET,
    hb.boundary FROM
        system.indexstatistics s,
        system.indexhistogrambuckets hb
WHERE s.tablename='SCORING' and
      s.objectid = hb.objectid and
      s.catalogid = hb.catalogid
ORDER BY s.tablename, s.indexname, hb.objectid,
    hb.catalogid, hb.histogramid, hb.bucketindex;
 TABLENAME       INDEXNAME       OBJ CAT HIST BUCKET                BOUNDARY
 ---------- -------------------- --- --- ---- ------- ------------------------------------

  SCORING   SCORING..PRIMARY_KEY  1  78   5     0     ZERO LENGTH KEY
  SCORING   SCORING..PRIMARY_KEY  1  78   5     1     [brickan01], [1991], [1], [BOS], [C]
  SCORING   SCORING..PRIMARY_KEY  1  78   5     2     [dandema01], [2007], [1], [MTL], [R]
  SCORING   SCORING..PRIMARY_KEY  1  78   5     3     [garpejo01], [1996], [1], [FLO], [L]
  SCORING   SCORING..PRIMARY_KEY  1  78   5     4     [houdado01], [1990], [2], [HAR], [D]
  SCORING   SCORING..PRIMARY_KEY  1  78   5     5     [lalonbo01], [1974], [1], [VAN], [C]
  SCORING   SCORING..PRIMARY_KEY  1  78   5     6     [mcbeawa01], [1989], [1], [NYI], [D]
  SCORING   SCORING..PRIMARY_KEY  1  78   5     7     [odwyebi01], [1989], [1], [BOS], [C]
  SCORING   SCORING..PRIMARY_KEY  1  78   5     8     [roberto01], [1985], [1], [HAR], [L]
  SCORING   SCORING..PRIMARY_KEY  1  78   5     9     [stevemi01], [1988], [1], [NYI], [L]
  SCORING   SCORING..PRIMARY_KEY  1  78   5    10     [willito01], [1974], [1], [WAS], [C]