- Use bind variables you are killing your scalability and it isn't going to
help your performance either.
2, Use tkprof
- post the explain plan.
Jim
"peter" <peterdnight_at_yahoo.com> wrote in message
news:401585d3.0204160838.1e42d59e_at_posting.google.com...
> Hi, I have 5 tables:
> - user(id/name),
> - group(id/name),
> - command(id,name, applicationid),
> - application(id, name),
> - usergroup(id,userid,groupid), and
> - groupcommand(id,groupid,commandid).
>
> I need: the unique set of commands a specified user has access to. The
> user can belong to multiple groups, a group is assigned 1 or more
> commands. Because different groups can be assigned different commands,
> I know I need either a distinct or exists item.... I figured out the
> SQL below with "distinct", but any ideas on what a corresponding SQL
> using "exists" looks like? I am assuming it would be faster ...
>
> Any other performance hints are also appreciated...
>
> Thanks,
> Peter
>
> select distinct command_name, user_name, app_name from
> crew_user, crew_group, crew_command, crew_application,
> crew_user_to_group, crew_group_to_command
>
> where
> user.USER_Name = 'testUser1'
> AND user_to_group.USER_ID = user.USER_ID
> AND group.GROUP_ID = user_to_group.GROUP_ID
> AND group_to_command.GROUP_ID = user_to_group.GROUP_ID
> AND command.COMMAND_ID = group_to_command.COMMAND_ID
> AND application.APPLICATION_ID = command.APPLICATION_ID
>
> )
Received on Tue Apr 16 2002 - 22:17:54 CDT
