SEO

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

bob_monkhouse35@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...