Results for “fnd_index_columns”
50+ results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
FND_INDEX_COLUMNS is a registration table in the APPLSYS schema of Oracle E-Business Suite (documented here for 12.1.1 and 12.2.2), owned by the FND — Application Object Library product. It stores the columns that make up each database index registered with Oracle Application Object Library. Where FND_INDEXES records index-level metadata (index name, uniqueness, index type), FND_INDEX_COLUMNS resolves the ordered list of columns participating in each index, providing the positional definition that EBS uses for metadata-driven index management, such as the "Create Index" and "Drop Index" concurrent programs and the underlying validation that governs index registration.
From a Data Vault modeling perspective, the heuristic classification of this object is link. It does not represent a business entity in isolation; rather, it resolves many-to-many associations between an index (identified by APPLICATION_ID, TABLE_ID, INDEX_ID) and the columns it comprises (identified by COLUMN_ID), with COLUMN_SEQUENCE capturing ordinal position within the index.
Key Information Stored
The table holds 11 documented columns. The most significant are:
- APPLICATION_ID — the application owning the table and index; part of every key and foreign key on the table.
- TABLE_ID — surrogate identifier of the FND_TABLES registration for the base table.
- INDEX_ID — surrogate identifier of the parent index record in FND_INDEXES. This is the column most commonly searched on when resolving index-to-column mappings.
- COLUMN_SEQUENCE — ordinal position of the column within the index, defining column order for composite indexes.
- COLUMN_ID — surrogate identifier of the column, joining to FND_COLUMNS.
- LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS WHO columns used for audit and concurrency tracking (e.g., optimistic locking via LAST_UPDATE_DATE).
- ZD_EDITION_NAME — editioning support column (present in 12.2.x Online Patching), included in the unique indexes to distinguish edition-specific rows.
The surrogate primary key is FND_INDEX_COLUMNS_PK (APPLICATION_ID, TABLE_ID, INDEX_ID, COLUMN_SEQUENCE). Documented business-key candidates are the unique indexes FND_INDEX_COLUMNS_U1 (APPLICATION_ID, TABLE_ID, INDEX_ID, COLUMN_SEQUENCE, ZD_EDITION_NAME) and FND_INDEX_COLUMNS_U2 (APPLICATION_ID, TABLE_ID, COLUMN_ID, INDEX_ID, ZD_EDITION_NAME), the latter enforcing that a given column appears at most once per index.
Common Use Cases and Queries
Typical uses include resolving which columns form a given index, reconstructing DDL, and auditing index definitions across development, test, and production instances.
- List columns of an index by INDEX_ID, in index order:
SELECT ic.COLUMN_SEQUENCE, ic.COLUMN_ID, fx.INDEX_NAME, fc.COLUMN_NAME
FROM FND_INDEX_COLUMNS ic, FND_INDEXES fx, FND_COLUMNS fc
WHERE ic.APPLICATION_ID = fx.APPLICATION_ID AND ic.TABLE_ID = fx.TABLE_ID
AND ic.INDEX_ID = fx.INDEX_ID
AND ic.APPLICATION_ID = fc.APPLICATION_ID AND ic.TABLE_ID = fc.TABLE_ID
AND ic.COLUMN_ID = fc.COLUMN_ID
AND ic.INDEX_ID = :index_id
ORDER BY ic.COLUMN_SEQUENCE; - Find all indexes in which a particular column participates (reverse lookup for impact analysis before dropping or altering a column).
- Validate that registered index column counts match the physical database catalog (USER_IND_COLUMNS / ALL_IND_COLUMNS), a common post-clone or post-patch verification.
- Report orphaned or missing index definitions by outer-joining to FND_INDEXES and FND_COLUMNS.
Because the table is metadata, queries are almost always read-only reports; there is no supported direct DML path.
Related Objects
- FND_INDEXES — parent index registry; joined on APPLICATION_ID, TABLE_ID, INDEX_ID. Supplies index name, uniqueness, and physical attributes.
- FND_COLUMNS — column registry; joined on APPLICATION_ID, TABLE_ID, COLUMN_ID. Supplies column name and datatype.
- FND_TABLES — base table registry referenced through APPLICATION_ID and TABLE_ID; the parent of both FND_INDEXES and FND_COLUMNS.
- FND_INDEX_COLUMNS_PK / U1 / U2 — the primary key and unique indexes that enforce uniqueness and support lookups.
- Related FND dictionary tables and the AD_ZD editioning views in 12.2.x that expose edition-aware metadata.
-
Index columns registered with Oracle Application Object Library
-
Index columns registered with Oracle Application Object Library
-
12.2.2 DBA Data 12.2.2
-
12.2.2 DBA Data 12.2.2
-
PACKAGE: APPS.ETRM_FNDNAV 12.1.1
-
PACKAGE: APPS.ETRM_FNDNAV 12.2.2
-
12.2.2 DBA Data 12.2.2
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 FND Design Data 12.2.2