You are here: Reference Information > SQL Reference Information > SQL System Tables > INDEXHISTOGRAMS System Table Description

INDEXHISTOGRAMS System Table Description

Description

A pseudo table containing one row for every index in your database. This table represents data related to the index histogram. It is currently almost an exact subset of INDEXSTATISTICS with the exception of FieldCount, but this is subject to change in the future.

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 bigint The catalog ID for the database object
HISTOGRAMID string The ID for the histogram, unique to the table
FIELDCOUNT integer The number of fields in the index
MAXRESOLUTION integer The maximum number of histogram buckets. Index histograms are one of the most important parts of index statistics. By default, the number of histogram buckets created is 10 for Version 1 statistics and 256 for Version 2 statistics. Raising this number (see CREATE INDEX) will make more precise statistics available to the query optimizer.

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.

SELECT s.tablename, 
    s.indexname, 
    h.fieldcount,
    h.maxresolution as MAXRES, 
    h.histogramid as HIST, 
    h.objectid as OBJ,
    h.catalogid as CAT
        FROM system.indexstatistics s, system.indexhistograms h 
WHERE s.tablename='SCORING' and    
    s.objectid = h.objectid and 
    s.catalogid = h.catalogid 
ORDER BY s.tablename, s.indexname;
 TABLENAME       INDEXNAME       FIELDCOUNT  MAXRES HIST OBJ CAT
 ---------- -------------------- ----------- ------ ---- --- ---
  SCORING   SCORING..PRIMARY_KEY      5        10    5    1  78