Re: Using FGAC for implementing history

From: Vikas Agnihotri <usenet_at_vikas.mailshell.com>
Date: Wed, 12 May 2004 23:14:23 -0400
Message-ID: <2gg7gfF2gj21U1@uni-berlin.de>

Howard J. Rogers wrote:

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

Huh? The very essence of FGAC is to add a dynamic predicate to every query accessing the table, no matter if the how the table is accesed...as part of another view, subquery, whatever. So, execution plans _will_ change, thats the point, isnt it? I wouldnt say they will "go bonkers", that depends on the predicate that my policy_function returns. Of course, if I have a 100 line PL/SQL policy function doing all sorts of selects and stuff just to get to the predicate and this has to be evaluated for every table/row access, things would get slow. But for a numbingly simple predicate like 'column = value', why would anything drastic happen? Yes, the as_of_date column might have to be indexed, but thats about it. I dont see the "and it goes on" part.

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

Well, your comments above apply more so to WM out of the box. Have you looked under the covers at what WM does when you version-enable a table? Look at note 156963.1 on MetaLink. Suppose I version-enable a table T. WM creates a view T. Look at this view T. Look at the WHERE clause on that view (against the T_BASE *view* which is on the real table T_LT). Its pretty hairy. Now suppose that your original table T was used in another view, which users have gone and created additional views off of. It quickly gets pretty hairy.

I find it very hard to believe that I simply do 'dbms_wm.EnableVersioning('T') and go about my merry way. In contrast, with my FGAC approach above, I *know* that all I am doing is adding a simple 'column = value' predicate to my underlying table.

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

Hm, I guess this is the policitically correct age. "Functional constraint"...is that the PC term for a bug these days? :) Here are the list of restrictions that WM puts on you

1. Table *and* column names have to be under 25 characters. You might not think this is a big deal and I agree for tables that are hidden from end users, but when this is part of a end-user layer and used for report titles, adhoc querying, etc, having long descriptive column names is a absolute necessity. In fact, I think that even 30 characters is too small! (SQL Server has a limit of 256 or something!). I hate unnecessarily abbreviating names by chppng ff vwls!
2. Statement-level triggers are not supported on the table
3. If I have a row-level trigger for INSERT or UPDATE, this wont do. I have to now create 2 triggers, one for INSERT and another for UPDATE with the same body!
4. If I want to add a trigger on the table after I version-enable it, I get a PL/SQL numeric or value error. Is this a functional contraint or a bug?!
5. export/import become extremely messy. exp/imp are already pretty kludgy tools already up until the new expdp (Data Pump) incarnations in 10g and WM cripples them even more.
6. Here is another kicker I just discovered. The table to be version enabled cannot have any UNIQUE constraints on it! So, if my existing table has a few unique constraints on it, what am I to do? Drop them and risk corrupting my data?
7. Suppose I am in this version enabled mode for a few months and I find that my tables are getting pretty unmanagable and I want to explore partitioning the tables. How do I go about recreating my version enabled table as a partiotioned table? Can I use one of the internal WM infrastructure columns as the partition key? Why? Because I would to partition by, say savepoint, by date or something, something that is in not in my actual table, but is now part of the equation just because version enabling the table is making it grow larger. In other words, since WM can potentially grow a table (if used for this history purpose like I want to do), it should offer a way to manage that aspect using partitions, etc. But the documentation is silent about this.
8. I cannot do bulk loads, etc to my table, because it is not a table anymore, it is a view! Yes, WM puts INSTEAD OF triggers on the view but then I cant do stuff like INSERT /*+ APPEND */ and SQL Loader direct-path loads (since they bypass triggers).

> But you design around those things, I suppose, if you want to make use

How? Just by looking at WM for couple of hours, I discovered the above 8 show stoppers!

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

I dont see any improvements in 10g related to WM. I do see improvements in 10g relating to FGAC (now called VPD!). See

http://otn.oracle.com/pub/articles/10gdba/week14_10gdba.html

This optimizes some of the predicate calculation, making FGAC an even more attractive option, wouldnt you say?

> 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

Do you know why Jonathan dislikes WM? Can you point me to a URL where he discusses this?

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

Again, I dont understand this viewpoint. As I explain above, *in my specific example*, performance tuning is going to be more critical for the WM approach than the FGAC one.

I respect both Jonathan Lewis and Tom Kyte enormously for their knowledge, experience and service to the Oracle community. As per you, in this case, they have differing opinions. Why? If you are reading this thread, can you please chime in?

Thanks Received on Wed May 12 2004 - 22:14:23 CDT