Re: Using FGAC for implementing history

Jonathan Lewis wrote:

> 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.

Agreed

> 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.

Good point. Thanks

> 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 ?

Yes, I was thinking about this and it does seem like the best option. But even if I clone the data into a separate schema, I would still need to add the new as_of_date column to the tables. And change all my views to add this predicate. To avoid this and let FGAC do this transparently seemed very attractive to me.

> 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.

Hm, this is where I get puzzled. Adding invisible predicates, however simple or complex they may be, is the very essence of FGAC. I agree that this needs extensive testing when deployed into existing systems.

But, if we are so concerned about the risk of adding even seemingly simple predicates into existing queries/systems, then it almost seems like FGAC documentation should say that "Oracle strongly recommends that FGAC be used only for new systems, adding it to existing systems needs extensive testing, you have been warned!".

Out of curiosity, what is your opinion on the FGAC/VPD technology in general?

> 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.

Exactly! That is precisely what I was trying to say in my discussion with Tom above. I just cant see it working with large amounts of data, adding large data to existing "version enabled tables", etc.

> 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.

Heck, forget about OLTP, I wouldnt even want it to happen for a DSS system. It is just too complex. It is perfect for the kind of simple examples provided in the documentation, working with a handful of independent, stand-alone tables, with no views, stored procs on the tables, but nothing more. Again, its fine for new projects that would be designed with its unique restrictions in mind, but I cant see it "retrofitting" into an existing non-trivial system.

Thanks for your time Received on Fri May 14 2004 - 10:01:03 CDT