Re: bitmap join index error

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0307141639.6f242331_at_posting.google.com>...
> 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.

I already have primary key on d.doc_id and f.feat_id. But why I should have unique key on dfi.feat_id? My goal is to build index which contains one bitmap string for each feat_id. Each bitmap string should contain one bit for each doc_id.

My schema do this: for each pair (feat_id, doc_id) there exists 0 or 1 rows in dfi.

Using this schema I want to quickly run queries like this: SELECT feat_id /* and some properties for this feat_id */   FROM ma14_feats f, ma14_doc_feats dfi, ma14_docs d

  WHERE f.feat_id=dfi.feat_id
    AND d.doc_id=dfi.doc_id
    AND d.doc_id IN (1,2,3)

And like this:
(
  (SELECT feat_id FROM ma14_doc_feats dfi WHERE doc_id=1    INTERSECT
   SELECT feat_id FROM ma14_doc_feats dfi WHERE doc_id=2   )
  MINUS
  SELECT feat_id FROM ma14_doc_feats dfi WHERE doc_id=3 )
UNION
SELECT feat_id FROM ma14_doc_feats dfi WHERE doc_id=4

I can write a program in C that performs these operations quickly by using bitmap strings, but I want to made this on Oracle.

Misha.

> 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 Tue Jul 15 2003 - 05:40:32 CDT