Re: Tuning a SQL statement with expensive function calls

Hello,

The expensive, expensive function includes following query:

> select decode( count(*), 0,'f', 't') into exists_p
> from dual where exists (
> select /*+ ORDERED*/ 'x'
> from acs_object_grantee_priv_map ogpm,
> group_approved_member_map gmm
> where object_id = permission_p.object_id
> and gmm.member_id = permission_p.party_id
> and privilege = permission_p.privilege
> and ogpm.grantee_id = gmm.group_id);

I guess that the columns in the PK index on group_approved_member_map are (group_id , member_id). This is not very useful here.Try adding a secondary index on the same columns but in reverse order: (member_id, group_id).

> -- relational segment approved group
> select decode( count(*), 0,'f', 't') into exists_p
> from dual where exists (
> select /*+ FIRST_ROWS*/ 'x'
> from acs_object_grantee_priv_map ogpm,
> rel_seg_approved_member_map rsmm
> where object_id = permission_p.object_id
> and rsmm.member_id = permission_p.party_id
> and privilege = permission_p.privilege
> and ogpm.grantee_id = rsmm.segment_id);

Same: make sure there is an index on acs_object_grantee_priv_map (member_id, group_id) in that order.

Bye,

Edzard Received on Thu Jan 24 2002 - 10:42:05 CST