Search Results fnd_histogram_cols




Overview
Explore in AI Chat

Overview

FND_HISTOGRAM_COLS is an Application Object Library (FND) repository table owned by the APPLSYS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the set of candidate columns for which the Oracle cost-based optimizer (CBO) may generate histograms on EBS application tables. Rather than relying on DBMS_STATS defaults for every column, EBS uses this table as a control list: it identifies the schema owner, table, column, and optional partition that should receive histogram statistics, together with the requested histogram size (HSIZE). This lets the EBS statistics-gathering routines target only those columns whose data distribution materially affects execution plans, avoiding the overhead of collecting histograms on every low-cardinality or uniformly distributed column.

From a dimensional modeling perspective, the documented metadata classifies this object heuristically as standalone. That is a modeling suggestion rather than a database-enforced relationship: the table carries no foreign keys to other EBS entities and functions as a reference or control list maintained by the DBA and statistics programs rather than as a transactional fact or a strictly modeled satellite.

Key Information Stored

The 12 documented columns fall into two groups: the business identifiers that define the histogram target, and the standard EBS audit columns maintained by the Applications framework.

  • APPLICATION_ID — the FND application owning the table; forms part of the primary key.
  • TABLE_NAME — the database table whose column is being configured for histogram collection.
  • COLUMN_NAME — the specific column within that table.
  • PARTITION — the optional partition name, allowing histogram targets to be scoped to a single partition of a partitioned table.
  • HSIZE — the requested histogram bucket count (the size passed to DBMS_STATS), which determines the granularity of the stored distribution.
  • OWNER — the schema that owns the target table, distinct from the APPLSYS owner of this configuration table.
  • CREATION_DATE, CREATED_BY — audit columns recording when and by whom the row was inserted.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns recording the most recent change.
  • ZD_EDITION_NAME — the editioning column that supports Edition-Based Redefinition (EBR), key in 12.2.x online patching.

The primary key is FND_HISTOGRAM_COLS_UK on (APPLICATION_ID, TABLE_NAME, COLUMN_NAME, PARTITION). A documented unique index, FND_HISTOGRAM_COLS_U1 on (TABLE_NAME, COLUMN_NAME, PARTITION, APPLICATION_ID, ZD_EDITION_NAME), serves as the business-key candidate and additionally guarantees that no duplicate histogram target exists within an edition.

Common Use Cases and Queries

The primary operational use is auditing and tuning which columns receive histograms. DBAs query this table to confirm the histogram configuration before or after running statistics gathering, and to compare it against actual data dictionary histograms.

  • List all configured histogram columns for a table: SELECT column_name, hsize, owner FROM applsys.fnd_histogram_cols WHERE table_name = :1;
  • Identify candidates for a specific application: SELECT table_name, column_name, hsize FROM applsys.fnd_histogram_cols WHERE application_id = :1;
  • Reconcile with the data dictionary: join to DBA_TAB_COL_STATISTICS or DBA_PART_COL_STATISTICS on TABLE_NAME and COLUMN_NAME to confirm collected histogram sizes match HSIZE.
  • Locate rows configured for partitioned objects: ... WHERE partition IS NOT NULL.

Reporting is typically limited to DBA and performance-tuning scripts; end users do not interact with this table directly.

Related Objects

Because the metadata classifies this object as standalone, it has no declared foreign keys. Its most significant logical relationships are established through the column values rather than constraints:

  • FND_APPLICATION — joined via APPLICATION_ID to resolve the owning application name.
  • DBA_TAB_COL_STATISTICS / DBA_PART_COL_STATISTICS — data dictionary views joined on OWNER, TABLE_NAME, COLUMN_NAME and PARTITION to compare configured HSIZE against collected statistics.
  • FND_TABLES — EBS-registered table metadata, matched on TABLE_NAME.
  • DBA_TAB_COLUMNS / DBA_TAB_PARTITIONS — used to validate that configured columns and partitions still exist.
  • DBMS_STATS — the PL/SQL package whose execution consumes these histogram targets.

Together these objects support statistics maintenance and CBO plan stability across the EBS 12.1.1 and 12.2.2 environments.