Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Re: BitMap Join Indexes -- extracting the DDL

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Sat, 27 Mar 2004 15:09:08 +0800
Message-Id: <5.1.1.6.0.20040327150757.00adb720@pop.singnet.com.sg>

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. 10.1.0.2, 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.
Example:
$ 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. 9.2.0.5, 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
>
>Hemant
>
>--- Michael Möller <m2_at_email.dk> 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: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional http://hkchital.tripod.com {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: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Sat Mar 27 2004 - 01:08:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US