Re: Help! View performance

This then explains why Oracle will not use an index on C. What it is seeing is

Select ...
From ...
Where F(C) = 'literal' (or bind variable);

ie a function around a column. This is therefore no different to something like

Select ...
from ...
where UPPER(C) = 'TIM';

You are going to have to re-think your requirements/implementation if performance is causing you problems.

Suresh Easwar <sje_at_sigma-inc.com> wrote in article <352F7A4F.E9D35DCB_at_sigma-inc.com>...
> 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