Re: Help! View performance

From: Suresh Easwar <sje_at_sigma-inc.com>
Date: 1998/04/11
Message-ID: <352F7A4F.E9D35DCB@sigma-inc.com>#1/1

Hi Michael,
Unfortunately I cannot use C in the select clause like you suggest. The reason
may become clear when I explain my problem in more detailed. C is a column that has sensitive data. The function F determines whether a user is allowed
to see the value of the column or not. Thus if the user is allowed to see the column, F(C) returns C
otherwise it returns null. F makes this determination by looking up certain privilege and security tables for the
current user.
Actually what you see is only a small part of my problem. The complete select statement is:

create or replace view V as
select

    F('T','C1', rowid,C1) C1,
    F('T','C2',rowid,C2) C2,

    .
    .
    .

    F('T','Cn',rowid,Cn) Cn
from T
where G('T', rowid) = 'Y'

Here G( ) is the row-level security function that returns 'Y' if the user is allowed to see the row and 'N' otherwise. F( ) is the column level security function.

Suresh

bialik_at_wis.weizmann.ac.il wrote:

> In article <352F04DB.A886CBC1_at_sigma-inc.com>,
> Suresh Easwar <sje_at_sigma-inc.com> wrote:
> >
> > I have a table T with a column C that is indexed on C.
> > Let F be some arbitrary function.
> >
> > select F(C) from T where C = some_value
> > /
> > uses the index just fine.
> >
> > However I cannot get it to use the index in the following case:
> > create view V as select F(C) D from T
> > /
> > select D from V where C = some_value
> > /
> > Oracle always does a full table scan in this case. I tried using hints
> > to no avail.
> >
> > Can someone please explain how I could make Oracle use the index? I
> > really do need to use the view, though.
> >
> > Thanks
> > Suresh
> >
> >
> Hi.
>
> Do you include the C fied in your view? It is not clear from your
> question.
> If not - try :
> Create viev V as select C, F(C) D from T;
>
> Good luck.
> Michael Bialik.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Sat Apr 11 1998 - 00:00:00 CDT