There are quite a few restrictions on using BJI. One of them is that
dimension table columns participate in the join must all have primary
key or unique key. Yours looks like a snow flake schema. So you need
primary or uique key on dfi.feat_id and d.doc_id.
ageev_at_mail.cir.ru (Misha Ageev) wrote in message news:<877c3c6c.0307140525.514b909_at_posting.google.com>...
> Hi, all!
>
> I've read about new wonderful feature of oracle 9i - bitmap join indexes.
> I wrote the following script to test these type of indexes but oracle
> do not create the index and throws the following error:
>
> SQL> create bitmap index ma14_doc_feats_bitmap1
> 2 on ma14_feats(d.doc_id)
> 3 from ma14_feats f, ma14_docs d, ma14_doc_feats dfi
> 4 where f.feat_id=dfi.feat_id
> 5 and d.doc_id=dfi.doc_id;
> from ma14_feats f, ma14_docs d, ma14_doc_feats dfi
> *
> ERROR at line 3:
> ORA-25954: missing primary key or unique constraint on dimension
>
> What is wrong? Help, please.
>
> ------------------- script start -------------------------
>
> create table ma14_docs(doc_id number(10) not null primary key);
> insert into ma14_docs values(1);
> insert into ma14_docs values(2);
> insert into ma14_docs values(3);
>
> create table ma14_feats(feat_id number(10) not null primary key);
> insert into ma14_feats values(1001);
> insert into ma14_feats values(1002);
> insert into ma14_feats values(1003);
>
> create table ma14_doc_feats(
> doc_id number(10) not null references ma14_docs,
> feat_id number(10) not null references ma14_feats,
> constraint ma14_doc_feats_un unique (doc_id, feat_id)
> );
> insert into ma14_doc_feats(doc_id, feat_id) values(1, 1001);
> insert into ma14_doc_feats(doc_id, feat_id) values(1, 1002);
> insert into ma14_doc_feats(doc_id, feat_id) values(2, 1002);
> insert into ma14_doc_feats(doc_id, feat_id) values(2, 1003);
> insert into ma14_doc_feats(doc_id, feat_id) values(3, 1003);
> commit;
>
> create bitmap index ma14_doc_feats_bitmap1
> on ma14_feats(d.doc_id)
> from ma14_feats f, ma14_docs d, ma14_doc_feats dfi
> where f.feat_id=dfi.feat_id
> and d.doc_id=dfi.doc_id;
>
> ------------------- script end ---------------------------
>
> Misha.
Received on Mon Jul 14 2003 - 19:39:25 CDT
