Re: bitmap join index error

From: Misha Ageev <ageev_at_mail.cir.ru>
Date: 17 Jul 2003 06:17:10 -0700
Message-ID: <877c3c6c.0307170517.6841e6e0@posting.google.com>

> Well, just because you have an unique index on a column does not mean
> you have a unique constaint on it. Add a unique constraint to it and
> it will work. For the 2nd type of "intersect" query you listed, I
> don't think BJI can help. BJI helps by avoiding runtime join between
> the fact table and dimension tables. The dimension tables will not be
> accessed at all at runtime. Try regular bitmap indexes instead.

You are right.
Unique constraints helps to create the index. But, unfortunately, this index is not exactly what I want. Daniel's message and some docs helped me to understand that oracle's bitmap join indexes do not solve my task.

danielroy10junk_at_hotmail.com (Daniel Roy) wrote in message news:<3722db.0307150752.2b6ab9de_at_posting.google.com>...
> I did more testing on your case, and it seems to be a "star
> transformation" case, with ma14_docs and ma14_feats as the dimension
> tables, and ma14_doc_feats the fact table. My question is why would
> you want to create a bitmap join index on a dimension table
> (ma14_feats)? It seems that what you would want is:
>
> create bitmap index ma14_doc_feats_bitmap1
> on ma14_doc_feats(d.doc_id, f.feat_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;
>
> That worked for me, with the same tables and constraints' definitions
> that you defined (i.e. a unique constraint on (feat_id, doc) of table
> ma14_doc_feats).

1. Oracle's bitmap index (for one table and for join) consists of several bitmap strings of equal length, and each rowid of indexed table (ma14_feats in my case) maps to some position of a bit string.
2. Oracle requires for bitmap join indexes that for each row of the indexed table there exist only one row in the join.

But my goal is to build index for join that satisfy 1st condition and does not satisfy 2nd condition:

I want to create bitmap matrix (doc_id x feat_id) so that bit(i, j)=1 if and only if the row (doc_id_i, feat_id_i) is in table dfi.

So, for each doc_id there exist a string of bits, where each bit maps to some row of ma14_feats.

Such index allow to quickly run queries like (
  (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

and even more quickly:

SELECT COUNT(*)
  FROM
   (
    (

      (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    )

(The last query only requires to count number of 1-bits in the resulting bit string, without access to mapping between bit position and feat_id).

Unfortunately, now I think that it is not possible in current version of
oracle.
Maybe anyone have another ideas?

Misha. Received on Thu Jul 17 2003 - 08:17:10 CDT