| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> BitMap Join Indexes -- extracting the DDL
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"
 |
 |