Search Results fnd_histogram_cols_u1

Overview

APPLSYS.FND_HISTOGRAM_COLS is a repository table in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 that stores the list of candidate columns on which the statistics gathering engine should construct histograms. The Cost-Based Optimizer (CBO) relies on histograms to produce accurate cardinality estimates when a column's data is unevenly distributed, and FND_HISTOGRAM_COLS is the mechanism by which Oracle EBS administrators declare those skewed columns. During a statistics collection run, the FND_STATS package reads this table and builds histograms on every column registered within it.

The object is a standalone table — it does not reference any other database object via foreign keys, and no other object depends on it through a declarative FK. In Data Vault terms, this object is best classified heuristically as a standalone reference (hub-like) entity: it captures a list of business keys (application, table, column, partition) that describe histogram candidates, without any dependent descriptive satellites. The heuristic classification is provided as a modeling suggestion only; the operative technical fact is that this is a standalone configuration table owned by APPLSYS and stored in the APPS_TS_ARCHIVE tablespace with PCTFREE 10.

Key Information Stored

The physical schema documents twelve columns in the 12.2.2 ETRM repository. The most significant are:

• APPLICATION_ID — Numeric application identifier of the application that owns the skewed table.
• TABLE_NAME — Name of the table that potentially contains skewed data.
• COLUMN_NAME — Name of the column that is a candidate for histogram generation.
• PARTITION — Partition name, populated when the target table is partitioned.
• HSIZE — Bucket size (number of buckets) requested for the histogram on that column.
• OWNER — Schema owner recorded for the histogram definition.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns tracking insert and update history.
• ZD_EDITION_NAME — Editioning column introduced by the 12.2 Online Patching (ADOP) architecture, enabling the table to participate in edition-based redefinition.

The documented unique index FND_HISTOGRAM_COLS_U1 spans TABLE_NAME, COLUMN_NAME, PARTITION, APPLICATION_ID, ZD_EDITION_NAME, making these the business-key candidate for identifying a histogram definition. The primary key documented as FND_HISTOGRAM_COLS_UK covers APPLICATION_ID, TABLE_NAME, COLUMN_NAME, PARTITION, which is the effective surrogate/business-key tuple enforced by the unique index.

Common Use Cases and Queries

DBAs and performance engineers consult this table to determine which columns are flagged for histogram creation and to troubleshoot CBO plans that depend on those statistics. A typical query retrieves all candidate columns for a specific application or table:

SELECT application_id, table_name, column_name, partition, hsize, owner
FROM   applsys.fnd_histogram_cols
WHERE  table_name = :p_table;

Administrators also use it to audit histogram coverage before running FND_STATS.GATHER_TABLE_STATS or the concurrent program "Gather Statistics" against an application schema. Because the table is edition-aware in 12.2.2, queries should filter on the current edition when investigating active definitions.

Related Objects

• FND_STATS — PL/SQL statistics-gathering package that reads FND_HISTOGRAM_COLS to build histograms.
• FND_HISTOGRAM_COLS_U1 — Unique index enforcing the business key.
• APPS.FND_HISTOGRAM_COLS — Application synonym exposing the APPLSYS base table.
• FND_STATS_HISTORY / FND_STATS_LOG — Companion tables recording the outcome of statistics runs initiated against histogram candidates.
• DBA_TAB_HISTOGRAMS / DBA_HISTOGRAMS — Data dictionary views used to verify that histograms were generated for the columns registered here.