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.

Caution: 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 <null> 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 <null> 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 <null> values for version 1 statistics)
BUCKETCARD integer The number of values in the bucket. Applicable for Version 2 statistics (and will show <null> values for version 1 statistics)
BUCKETNDV integer The number of distinct values in the bucket. Applicable for Version 2 statistics (and will show <null> 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]