Note in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st
"Vikas Agnihotri" <usenet_at_vikas.mailshell.com> wrote in message
news:2gfhphF286akU1_at_uni-berlin.de...
> The intended use of FGAC seems to be for securing data and providing
> different view of the data (at a row-level) depending on who is looking
> at it, etc.
>
> Would it be a appropriate use of FGAC to use it for history-enabling
tables?
>
It seems unlikely, but you have to figure out
exactly what you are trying to achieve, and
then see what features best match the requirements.
History is almost always a bigger pain than it
seems at first sight.
> For example, I have a CUST table with primary key of cust_no. There is a
> requirement to capture month-end history snapshots of the table. I was
> thinking of adding as_of_date to the primary key of the table. The
> "current" data would have a as_of_date of, say, 12/12/3000 or something
> (cant be null because it is part of the PK). Of course, depending on
> data volume, I would create this as a partitioned table with as_of_date
> as a partition key.
>
I can think of a few side-effects that you might have to worry
about if you did this. For example, can you guarantee that every
piece of code would set the date for new data correctly ? You
could protect it with a pre-row insert trigger (defining a default can
leave you with a risk), but then any batch loading would turn into
single row processing.
> Then, I would attach a FGAC policy to the table with a default predicate
> of "as_of_date=12/12/3000", so current users of the table wouldnt be
> affected. Then I would provide a function/procedure to change the
> as_of_date in the app. context. For example
>
> select * from cust; -- would return current data
>
> exec set_ctx('4/30/2004') -- would add a "as_of_date=4/30/2004" predicate
>
> select * from cust; -- would return 4/30/2004 data
>
If the users have to call a function to change the snapshot data,
why not simply clone the data into a separate schema (perhaps
with snapshot partition arrangement) and get them to change
schema ?
> Is this a good use of FGAC?
>
No.
> If not, why not?
>
Invisible predicates (even very simple ones) can result in changes
in access paths. And some access paths will become untunable -
for example, a simple outer join INTO a table with a security predicate
cannot be optimised properly in 8.1 unless you set one of the hidden
parameters relating to predicate pushing.
> Tom Kyte refered me to Workspace Manager for this, but I found WM to be
> a very immature offering with many bugs and restrictions. See my thread
> on this at
>
>
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4632007035731#18350492565844
>
My view of Workspace Manager is that it is a marketing
offering, that exists to allow a very limited set of functions
look easy to manage.
At best, it might be appropriate for running 'what if' scenarios
that make small changes to an otherwise fixed data set.
You've probably seen how much extra work goes on behind
the scenes to support WM. You probably won't want that to
happen all the time for an OLTP system.
>
> Oracle Support refuses to discuss this question, saying that it is a
> case for Oracle Consulting Services.
>
> If anyone here has any comments, experiences, I would greatly appreciate
it.
>
> Thanks
Received on Fri May 14 2004 - 07:05:04 CDT
