Search Results fnd_indexes_pk

Overview

The FND_INDEXES table is a core metadata repository within the Oracle E-Business Suite Application Object Library (AOL). It serves as the central registry for all unique and non-unique indexes that are registered with the Oracle Applications framework. This registration is a critical component of the EBS architecture, enabling the system to manage, reference, and validate database index structures in a controlled, application-aware manner. By maintaining this metadata, Oracle EBS ensures that indexes are recognized as formal application objects, which facilitates consistent administration, patching, and upgrade processes across modules in both the 12.1.1 and 12.2.2 releases.

Key Information Stored

The table stores essential identifying and descriptive attributes for each registered index. Its structure is defined by composite primary and unique keys that enforce data integrity within the application context. The primary key (FND_INDEXES_PK) consists of APPLICATION_ID, TABLE_ID, and INDEX_ID, providing a unique system identifier. A separate unique key (FND_INDEXES_UK1) ensures uniqueness of an index name within the context of a specific application and table, using APPLICATION_ID, TABLE_ID, and INDEX_NAME. While the provided metadata does not list all columns, the key relationships imply the table stores the index's internal ID, its physical database name, and foreign keys linking it to its parent application and table entities in the FND_TABLES repository.

Common Use Cases and Queries

This table is primarily queried for metadata discovery, impact analysis, and system validation. Common scenarios include identifying all indexes registered for a specific application table, verifying index registration status before applying patches, or generating reports on the indexing strategy for a module. A typical query would join FND_INDEXES with FND_TABLES and FND_APPLICATION to retrieve a human-readable list. For example:

• SELECT fa.application_short_name, ft.table_name, fi.index_name FROM apps.fnd_indexes fi JOIN apps.fnd_tables ft ON (fi.application_id = ft.application_id AND fi.table_id = ft.table_id) JOIN apps.fnd_application fa ON (fi.application_id = fa.application_id) WHERE ft.table_name = 'PO_HEADERS_ALL';

This query pattern is fundamental for technical consultants and DBAs performing environment audits or troubleshooting performance issues related to missing or unregistered indexes.

Related Objects

FND_INDEXES is a central node in the AOL metadata model with defined relationships to other key repositories. As per the documented foreign keys:

• FND_TABLES: The table has a foreign key relationship where FND_INDEXES.APPLICATION_ID and FND_INDEXES.TABLE_ID reference FND_TABLES. This links every registered index to its parent application table.
• FND_INDEX_COLUMNS: The table is referenced by FND_INDEX_COLUMNS via the columns APPLICATION_ID, TABLE_ID, and INDEX_ID. This creates a one-to-many relationship where FND_INDEX_COLUMNS stores the detailed column composition for each index defined in FND_INDEXES.

These relationships form a complete metadata chain from application (FND_APPLICATION) to table (FND_TABLES) to index (FND_INDEXES) to index column (FND_INDEX_COLUMNS), which is essential for full object dependency analysis.