BitMap Join Indexes -- extracting the DDL

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 25 Mar 2004 23:13:13 +0800



A consultant at my site has begun using BitMap Join Indexes [9.2.0.4 on Solaris8]
We have no complaints with that -- in fact we do get good performance.

However, we find that we are unable to get the BMJI definitions out, either by
querying USER_INDEXES or USER_IND_COLUMNS or using DBMS_METADATA.GET_DDL

e.g.
for the index definition
create bitmap index facts_cycle_count_bm on t_chart_facts
(t_time_dim.cycle_count)
from t_chart_facts,t_time_dim where t_chart_facts.time_id =
t_time_dim.time_id;

For Table T_CHART_FACTS, I see the Index in DBA_INDEXES but not in DBA_IND_COLUMNS.
for Table T_TIME_DIM, I do not see the Index in DBA_INDEXES but see it in DBA_IND_COLUMNS.
Furthermore, running select
dbms_metadata.get_ddl('INDEX','FACTS_CYCLE_COUNT_BM','DWH') FROM DUAL,
I do not get the column names --- I get only
CREATE BITMAP INDEX "DWH"."FACTS_CYCLE_COUNT_BM" ON "DWH"."T_CHART_FACTS" (
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DWH"


The Support Analyst has identified potential Bugs# 2944274 and 2977027.
I cannot see 2944274 and 2977027 does not seem to be relevant [from what I can see
of the publicly visible portions of the Bug Text] --> DBMS_METADATA generates INCORRECT DDL ORDER FOR LOCAL INDEXES


Has anyone use BMJIs and been able to extract the DDL out ?


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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2004