Re: Supporting multiple oracle versions in a trigger

> WHen you get the book, read the chapter on locks and latches.  Then you
> will see exactly where the scalability problem will occur.  Until then I
> will not be able to explain it properly.

Ok well spent the morning googling and reading, and will come back to this after my book arrives, but I can't drop this yet for a few days. I'm going to assume for now that all the scalability issues are with the 'client tables' that I am dropping/creating on the fly and not with my raw data storage.

Given my constraints:

• need an indexed data dump (in MSAccess format as many of my clients put it) of the underlying data collection
• would like sync to be as close to real time as possible
• should be able to be done by clients /wo IT/DBA
• least amount of impact on concurrant database activities

Is this a better solution given the worse case scenario?:

Before dataCollection change:

ViewClientPrivateT = select data in transpose format. TableClientPrivateT = indexed table essentially CTAS from viewT viewClientPublicT = select * from TableT DataCollection col links data collection to TableClientPrivateT

So when the underlying collection changes to require a rebuild:

• create secondary queue of any data keys processed from data after update trigger
• create ViewClientPrivateT2;
• create TableClientPrivateT2;
• insert into table TableClientPrivateT2 from ViewClientPrivateT2 /w no logging /no rollback
• create indexes on TableClientPrivateT2 / no logging
• modify col in data collection to link to ClientTablePrivateT2 ;
• create/replace viewClientPublicT to select from new TableClientPriavteT2;
• re-process queue of data keys that were modified during the process

Then have a cleanup process at 2am to drop any objects no longer linked to data collections (TableClientPrivateT/ViewClientPrivateT);

Apart from the workload on the database to populate the new table, I see the main concurrancy issues as:
- A select is being processed from viewClientPublicT while I attempt to
create/replace it. (would a drop/create of a synonym to the viewClientPublicT be better?)

Just some thoughts...

thx

JAck Received on Fri Oct 21 2005 - 13:04:39 CDT