Re: Oracle not using Bitmap Join Index

bob_monkhouse35_at_hotmail.com wrote:
> I'm trying to speed up a query that is being generated from an
> application. The application (there the sql itself) cannot be
> modified. Therefore I was investigating using bitmap join indexes to
> help speed things up as there are some joins on large tables (700k rows
> +).

Why not? Many applications have the possibility to store the query, instead of (re-)generating it over and over again.

> Here's the sql generated by the app:
>
> SELECT COUNT(DISTINCT(OBJ.OBJECTID))
> FROM REVS OBJ, CLASS CLS,ATTRS ATT_C1,CATTRS
> ATTC_C1,OBJATTR REL_C1
> WHERE (OBJ.UNIQUEKEY LIKE 'D%'
> AND OBJ.CLSOBJECTID = CLS.OBJECTID
> AND OBJ.NAME LIKE '30%'
> AND (ATTC_C1.CATTRSNAME = 'SECURELEVEL'
> AND ATT_C1.CATTRSOBJECTID = ATTC_C1.OBJECTID
> AND REL_C1.RIGHTOBJECTID = ATT_C1.OBJECTID
> AND REL_C1.LEFTOBJECTID = OBJ.OBJECTID
> AND ATT_C1.VALUE = 'secure')
>
> The OBJATTR table is big and is being used in the join
> ATT_C1.CATTRSOBJECTID = ATTC_C1.OBJECTID so this is what I wanted use
> in the bitmap join index ...

Eh? Objectid seems like a unique identifier; bitmap indexes are usually better when low-cardinality data (GENDER: M/F, MARRIED: Y/N, etc) comes into play.
Not knowing your data, could you elaborate on what observations led you to decide a bitmap index is in place? Besides, I fail to see how OBJATTR (or: REL_C1) is used to join ATT_C1 (Table: ATTR) with ATTC_C1 (Table: CATTRS)

>
> CREATE BITMAP INDEX revs_attrs
> ON objattr(revs.objectid)
> FROM objattr, revs
> WHERE objattr.rightobjectid=revs.objectid;

See above - why would this index speed up the join on attr and cattr?
>
> Oracle doesn't use the bitmap join though, instead using the index on
> the OBJATTR.RIGHTOBJECTID instead.
> Any ideas as to why Oracle won't use the bitmap index? Is a bitmap
> index even the correct thing to use here? Is there any other way that
> I can speed things up??
>

More hardware. More statistics (did you analyze the index?)

Comments inline...
And, apart from always posting platform and Oracle version, post explain plan, generated from trace files with performance questions.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...