Re: bitmap join index error

From: Daniel Roy <danielroy10junk_at_hotmail.com>
Date: 15 Jul 2003 08:02:45 -0700
Message-ID: <3722db.0307150702.6db5b3e3@posting.google.com>

I certainly can't explain why yet (I don't have any experience before today with bitmap indexes), but I was able to get it working:

SQL> create table ma14_feats (feat_id number(10) primary key);

Table created.

SQL> create table ma14_docs (doc_id number(10) primary key);

Table created.

SQL> create table ma14_doc_feats(doc_id number(10) not null references ma14_docs,
  2 feat_id number(10) not null references ma14_feats,   3 constraint ma14_doc_feats_un unique (doc_id, feat_id));

Table created.

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

SQL> drop table ma14_doc_feats;

Table dropped.

SQL> create table ma14_doc_feats(doc_id number(10) not null references ma14_docs(doc_id),
  2 feat_id number(10) not null references ma14_feats(feat_id),   3 constraint ma14_doc_feats_un unique (feat_id));

Table created.

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;

Index created.

Note that the unique constraint is on field feat_id only (for table ma14_doc_feats), as opposed to both doc_id and feat_id. But it doesn't seem realistic to me to enforce uniqueness of feat_id only (this seems too strong a requirement)!

Daniel Received on Tue Jul 15 2003 - 10:02:45 CDT