Re: bitmap join index error

ageev_at_mail.cir.ru (Misha Ageev) wrote in message > > > (
> > > (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
> > >
> >
> > Hmm... You sure this is the query you want?
>
> Yes. I develop a text classification system where each category
> is represented by a boolean formulae. I need to select all documents
> that have a given stucture of features, and in reverse all features that
> have a given stucture of documents.
>
> This leads to a boolean queries like those I mentioned.
>
> Currently I use B+tree indexes and I sure the correct bitmap indexes
> can speed up the computation.
>
> Misha.

Either I do not totally understand your question or you have some serious misconception about the ORACLE bitmap index, and possibly the SQL language itself. Do you know that the query you posted does not make the slightest bit of sense? Assuming that is the actual query you are thinking of running against ORACLE, the first 3 selects is meaningless. You get nothing out of them. Is it typo thing or...

The bitmap concept is fairly simple. So is how to use ORACLE bitmap indexes.
1. If your table columns contain low cardinality data (few distinct values)
2. If your queries have a lot of "and", "or" equality constraints in the where clause.

Creating bitmap indexes on them might boost the performance. No gaurantee, but they might.

It seems to me as if you are trying to mannually manipulate the bitwise operation. You don't do that when running queries against ORACLE. You create the indexes, write your queries and ORACLE optimizer will, hopefully, use the indeses and apply the bitand, bitor on the indexes and bring back the data for you. The only thing you have to worry about is finding out if ORACLE is using them, and if ORACLE is not using them, how to use hints and session settings to force ORACLE to use them.

Just to clear things up on exactly what you are after, you should post the followings:

1. Your table structure
2. The actual queries you intend to run against them.

Once we have that, we can then decide whether or not bitmap indexes or BJI
make sense. No offense, but the stuff you posted so far is pretty confusing.

• Jusung Yang