Re: Implementation design advice needed

On Fri, 14 Oct 2005 16:39:56 GMT, "Jack Addington" <jaddington_at_shaw.ca> wrote:

>1) Spawning an I/D statement for each header update isn't that bad.
>Performance wise the queries are quick and I'm not sure I would save that
>much over grouping all the statements via an IN clause. Also keeps
>everything extrememly simple and creates a realtime environment. I'm a
>touch leary how this will scale up though.
>

It won't scale
>2) What about calling a dbms_jobs for each header row update? That would
>result in a tiny bit more overhead for the database but less impact on the
>user. The data would still be updated in near real time.
>

That would be yet another nightmare. Remember there is a limit to how often Orcacle can check a job queue. Your header updates are going to queue up, and you would need to queue a series of 1-time jobs. That was not what dbms_job was designed for. If you really insist on queuing, use the Advanced Queuing facility. Won't scale either in your situation, but it would be better to manage.
>3) Change my BUS/AUS trigger logic and use a temp global table to store each
>header key in the row trigger and then process the temp table in the AUS
>trigger. This creates a slightly simpler structure that I can debug a bit
>easier - although session based tables can be a bit of a pain.
>

You don't need temporary tables. A pl/sql table (or collection) will do just fine and is easy to implement. If you search for 'mutating tables' on http://asktom.oracle.com you will find a working demo in a trigger situation.

>4) Same as three but spawn a single dbms_jobs to process the temp table of
>keys.

Yet another DBA nightmare.

--
Sybrand Bakker, Senior Oracle DBA