BI Publisher report in project accounting fails due to missing index after table ALTER

ERP Oracle JD Edwards
, , , , , , , ,
1

We recently altered the F0311 table structure in project accounting to add custom fields for tracking project phases. The ALTER operation completed successfully through OMW, but now our BI Publisher reports for project cost analysis are failing intermittently.

The error suggests missing index references:


ORA-01502: index 'F0311_IDX3' unusable
BI Publisher: Data retrieval failed
Report: Project_Cost_Analysis_v2

The table ALTER was done to support new cost allocation requirements, but we didn’t anticipate the impact on BI Publisher data retrieval. The reports were working fine before the change. Some queries return partial data, others timeout completely. We’ve checked index regeneration in OMW but the status shows as complete.

Has anyone dealt with index issues after table modifications affecting BI Publisher reports? Specifically concerned about the project cost analysis reports that pull from multiple tables including F0311.

2

Adding to the previous comment - this is a known behavior with JDE 9.2.2 and BI Publisher integration. The table ALTER operation completes but doesn’t automatically trigger index regeneration for all dependent indexes. Your F0311_IDX3 is likely a composite index used by your project cost analysis queries. When BI Publisher tries to execute the data model, it hits the unusable index and either fails or does full table scans causing timeouts. You should also check if there are any materialized views dependent on F0311 that need refreshing.

3

For production environments, I recommend using OMW’s index rebuild with the ONLINE option if your Oracle version supports it. This allows concurrent DML operations during the rebuild. However, for critical indexes on large tables like F0311, schedule it during a maintenance window if possible. The rebuild can take 15-30 minutes depending on table size and will temporarily increase I/O load.

4

Thanks for the quick responses. I ran the query and confirmed F0311_IDX3 and F0311_IDX5 are both showing UNUSABLE status. That explains the intermittent failures - some reports don’t use those indexes. Should I rebuild through OMW or directly in Oracle? Also concerned about the impact on running transactions during the rebuild.

5

I’ve seen this exact scenario. When you ALTER a table that has existing indexes, Oracle marks those indexes as UNUSABLE even if OMW shows completion. The BI Publisher data retrieval engine can’t use unusable indexes, causing the failures you’re seeing.

You need to manually rebuild the indexes. Check the actual status in Oracle:


SELECT index_name, status FROM user_indexes
WHERE table_name = 'F0311';

If they show UNUSABLE, rebuild them through SQL or use OMW’s index rebuild utility.