Re: Using FGAC for implementing history

Vikas Agnihotri wrote:
> Howard J. Rogers wrote:
>

[snip]

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

It is indeed the point. And my point was that implementing FGAC is therefore not free of cost, and that you need to be prepared to tune for that change in execution plan. Rather more than someone whose where clauses changes from 'where invoice#=8567' to 'where invoice#=2545' would have to do, for example.

Perhaps I missed the point too: perhaps you are in development stage and can muck around with your design and tune for the addition of that FGAC-produced where clause in advance. But it sounded as if you already had a design and code, and were proposing to *now* add FGAC on top of it in response to a changed business requirement. I was merely trying to point out that retrofitting FGAC might mean having to re-tune an entire production system. That's all. Just a simple matter for you, I'm sure.

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

The optimiser is a funny thing. The point is, you don't know what it will do, do you? Who can say whether it will be "drastic" or not unless you've tested it beforehand and tuned accordingly.

None of which need be particularly difficult, and I didn't say it would be. But there *will* be issues and you *will* have to address them. And it is invariably easier to address them in the design and testing stage than to implement them and then try and patch things up to deal with the consequences.

>Yes, the as_of_date column might have to be
> indexed, but thats about it. I dont see the "and it goes on" part.

How can you possibly say "but that's about it" when you haven't got the faintest idea of what the effect of adding a predicate to a SQL statement will be? It might be trivial and "about it", and it might not be. They are issues that need to be considered, tested and tuned for. That's all.

And even if "that's about it", are you quite sure that having a new index on a column won't slow down DML unacceptably? That your buffer cache is sized appropriately to cope with the extra blocks being loaded? That your redo logs are sized sufficiently for the additional index maintenance redo not to be a problem?

Silly me. Of course you will have anticipated all of that, won't you? Either that or have some pat answer like "but that's about it".

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

That's what the phrase "with knobs on" means, actually.

>Have you
> looked under the covers at what WM does when you version-enable a table?

Er, yes I have. A lot.

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

But that is my precise point. Yes, if you've already got a view created and users have created their own views, then clearly you're already in production, and WM could be a nightmare to retrofit. Design it in from the outset, however, and those sorts of problems wouldn't be a problem, would they?

> I find it very hard to believe that I simply do
> 'dbms_wm.EnableVersioning('T') and go about my merry way.

Did I say that? No I didn't.

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

Uh huh. Since you *know* so much, you don't need any further contributions from here then, surely.

(Tip: try not to look gift horses in the mouth).

[snip]

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

Try and get a grip. If they are show stoppers for you, as I said, then don't use WM. It's a simple enough equation. They might not be for others, and they may take account of the limitations in their design in any case, and they may therefore be very happy with Workspace Manager. For you, with your particular concerns, to brand an entire technology 'immature' just because it doesn't fit those particular concerns is, er, immature, actually.

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

Perhaps you might try looking harder. The phrases multi-parent workspaces mean nothing to you I suppose? Nor dbms_wm.setsystemparameter? Nor workspace events? Nor dbms_wm.export? Dbms_wm.import draws a complete blank with you does it? How about workspace continuous refresh? How about the fact that FGAC now works with WM?

So apart from the roads, the aqueucts, the sewers, the villas and the central heating, what have the Romans ever done for us, eh?

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

Oh, you mean you *don't* know everything?

I could offer a precis of my understanding of why he doesn't particularly relish it, and I could point you to a URL or two, but frankly your attitude sucks and I don't think I'll bother.

You could just ask him instead. He isn't a hermit.

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

As per me, what? Did I say use WM?? Did I say use FGAC??

No. I said it was a matter to be determined by you having worked out what your operational and functional requirements were, and seeing which technology best provided the combination of features which matched those requirements. And that it was going to be for you, with your specific circumstances in mind, that would best be able to determine the course to steer. Decision-making like that, however, requires that you have an open mind on the choices involved, which I now realise was a bit of an ambitious assumption on my part.

Don't have a go at me for pointing out that FGAC may well alter your execution plans in ways you don't anticipate. Or that the need to build an additional index might itself cause performance or scalability problems. And don't make out I said WM would be trivial to implement either. I didn't even suggest that one would be easier to implement than the other.

So you know, I like both technologies, and both could be employed to do what you asked, but what you asked falls more 'naturally' into the WM sphere than FGAC. IMHO, of course.

The strange thing is, your mind is clearly already made up, if not actually closed on the matter, and yet you ask for comments and advice here. I wish I could work out why you bothered.

Why don't you just go and implement FGAC and come back and tell us all about how trivial it was to do?

HJR Received on Wed May 12 2004 - 23:01:18 CDT