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.
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

Indexes

None

Example

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]