bitmap join index error

From: Misha Ageev <ageev_at_mail.cir.ru>
Date: 14 Jul 2003 10:58:22 -0700
Message-ID: <877c3c6c.0307140525.514b909@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 - 12:58:22 CDT