Oracle not using Bitmap Join Index

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 +).
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 ...

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

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??

Thanks!!! Received on Sun Jan 08 2006 - 15:47:56 CST