Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: BitMap Join Indexes -- extracting the DDL

Re: BitMap Join Indexes -- extracting the DDL

From: Hemant K Chitale <>
Date: Sat, 27 Mar 2004 15:09:08 +0800
Message-Id: <>

The last update from Oracle support [the analyst hasn't referred to USER_JOIN_IND_COLUMNS
in the response, though...] is

  1. This seems to be the same issue as the one reported in bug:2944274 "METADATA API DOES NOT SUPPORT BITMAP JOIN INDEXES". (The bug report is in unpublished status, and not available in Metalink.)

This bug is fixed in rel. 10g. I tested dbms_metadata.get_ddl in ver., and the correct DDL for bitmap join indexes is returned in this version.

As a workaround in ver. 9.2, I suggest you consider using export/import to get the DDL.
$ exp <user>/<password> rows=n tables=(T_CHART_FACTS,t_time_dim) $ imp <user>/<password> INDEXFILE=indexes.sql

-> The INDEXFILE option writes the DDL for indexes into the file specified ('indexes.sql'). No objects/data are imported into the database when using the INDEXFILE option.

If you need a fix for bug:2944274 in rel. 9.2, we could try to get a backport for ver., but it is not guaranteed that we will be able to do so. As part of the backport process, we will need a detailed business justification from you explaining why it is critical to get the backport for this bug.

Please let us know what you want to do.

2) The TABLE_NAME column of the DBA_INDEXES (USER_INDEXES) view shows the fact table (here T_CHART_FACTS).
The INDEX_TYPE column is BITMAP, and the JOIN_INDEX column is set to YES. Whereas the TABLE_NAME column of the DBA_IND_COLUMNS (USER_IND_COLUMNS) view shows the table containing the indexed column. Join indexes are special cases - the TABLE_NAME column in DBA_IND_COLUMNS may not match the TABLE_NAME column in other *_INDEXES views.

At 04:15 PM Friday, you wrote:
>Thanks ! We'll use USER_JOIN_IND_COLUMNS
>--- Michael Möller <> wrote:
> >
> >
><deleted because ECARTIS doesn't like too many
>quoetd lines>
>ALL/DBA/USER_JOIN_IND_COLUMNS describes the join conditions of bitmap join
>indexes to which you have access.
>Please see the official ORACLE-L FAQ:
>To unsubscribe send email to:
>put 'unsubscribe' in the subject line.
>Archives are at
>FAQ is at

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional {last updated 21-March-04} "If you wish to leave your footprints on the sand, do not drag your feet"

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Sat Mar 27 2004 - 01:08:12 CST

Original text of this message