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

integer

The ID for the database object

CATALOGID

integer

The catalog Id for the database object

HISTOGRAMID

string

The ID for the histogram, unique to the table

BUCKETINDEX

string

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. Applicable for Version 2 statistics (and will show values for version 1 statistics)

BOUNDARYLOW CARD

integer

The number of times that the BOUNDARYLOW value appears in the index. Applicable for Version 2 statistics (and will show values for version 1 statistics)

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

integer

The number of times the BOUNDARY value appears in the index. Applicable for Version 2 statistics (and will show values for version 1 statistics)

BUCKETCARD

integer

The number of values in the bucket. Applicable for Version 2 statistics (and will show values for version 1 statistics)

BUCKETNDV

integer

The number of distinct values in the bucket. Applicable for Version 2 statistics (and will show values for version 1 statistics)

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]