Re: SQL Filtering

Ben Heath wrote:

> I have a problem, that I'm sure someone has had to solve before. I have a
> client table and I want to allow users access to only certain classes of
> clients. In my example I have 'OrdinaryClients' and 'ImportantClients'. If
> I want to filter a query so that certain users can only see
> 'OrdinaryClients' what's the best way to do this.
>
> This is what I am currently doing, but the queries are very slow as the
> table sizes increase. When an index is added to Client.clientNumber it's
> not being used if the resultset returned from Userprofile is larger than
> around 10 to12 clients. Any ideas?
> Table:
>
> Client
> ------
> clientNumber varchar(6)
> clientName varchar(20)
> clientDesc varchar(60)
> otherClientInfo varchar(120)
> .
> .
> .
>
> UserProfile
> -----------
> profileName varchar(20)
> clientNumber varchar(6)
> .
> .
> .
>
> User
> ----
> userName varchar(20)
> password varchar(20)
> profileName varchar(20)
> .
> .
> .
>
> User
> ----
> 'ben', 'xxxx', 'OrdinaryClients'
>
> UserProfile
> -----------
> 'ImportantClients', '111111'
> 'ImportantClients', '222222'
> 'ImportantClients', '333333'
> 'ImportantClients', '444444'
> 'OrdinaryClients', '555555'
> 'OrdinaryClients', 666666'
> 'OrdinaryClients', '777777'
>
> Client
> ------
> '111111', 'abc Company', ...
> '222222', 'def Company', ...
> '333333', 'ghi Company', ...
> '444444', 'jkl Company', ...
> '555555', 'mno Company', ...
> '666666', 'pqr Company', ...
> '777777', 'stu Company', ...
>
> select clientName from Client where client.clientNumber in
> (select clientNumber from userProfile where profileName = 'OrdinaryClients')

SYS_CONTEXT. You can find information at http://tahiti.oracle.com

Daniel Morgan Received on Fri Aug 02 2002 - 10:46:29 CDT