Re: Using FGAC for implementing history

Vikas Agnihotri wrote:
> 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?
>
> 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.
>
> 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
>
> Is this a good use of FGAC?

Who can say? Sorry, that's a bit vague, but put it this way. You'll be using FGAC to do something it was never designed to do, really. You'll be adding a column to your tables. Your execution plans will be going bonkers because of the new where clause appended to every single piece of SQL issued against the table. Because you'll probably have to slap an index on the date column to try and improve performance.... and so it goes on.

> If not, why not?

See above.

> Tom Kyte refered me to Workspace Manager for this,

Funnily enough, as I was reading your post, I was thinking "Why doesn't he use WM, because it's perfect fot this". Then I get here and discover Tom beat me to it.

All my comments about FGAC above probably apply to WM with knobs on, however (lots of new columns, views, indexes, etc).

> but I found WM to be
> a very immature offering

I'd strongly suggest not saying to Tom, or anyone else, that a feature is "immature" when all that is really at issue is that it has functional constraints which may hinder its use in your particular environment! Such phrases tend to put people's backs up.

>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

The one about table and columns names seems to me to be a bit over-blown, because I can't imagine that people really want 28-character columns names on a regular basis anyway (no doubt there will be a flood of posts saying I'm an idiot for thinking that way, but there you go).

Export and import do become messy, I agree. And yes, triggers can be a show stopper.

But you design around those things, I suppose, if you want to make use of the WM features (incidentally, if 10g is an option, there have been some improvements to WM, though not ones which, I think, address your particular concerns).

In short, if export and triggers and column name lengths are critical issues for you, that is enough to rule out WM (which Jonathan Lewis dislikes as a feature of a proper database anyway, which should be enough to give most people pause for thought). Your FGAC is do-able, but will affect every query that hits the table, so performance tuning it is going to be critical. And the fact that Tom Kyte suggested WM instead should be enough, er, to give most people pause for thought.

Either way, then, seems plausible in theory, with your specific design criteria as your sole guide through the Scylla and Charybdis of the two technologies.

Not much help, really, was I?!

Regards
HJR Received on Wed May 12 2004 - 16:35:43 CDT