troubleshooting Question

Automatically rebuild a second table after data changes in first table

Avatar of Steve Wales
Steve WalesFlag for United States of America asked on
Oracle Database
8 Comments1 Solution485 ViewsLast Modified:
Environment:  HPUX 11.11 / Oracle 9207

I work with a vendor supplied app, so we are unable to modify table structures etc.

There is a table that defines a hierarchy of accounts that is in the format

PARENT_ACCOUNT
CHILD_ACCOUNT

A parent can have multiple children, each child can have one parent.  So it builds a tree.

A view was built that joins this table to itself 6 times so that it can build a hierarchy 6 levels deep and query transactions using these accounts for any level in the hierarchy (a level may represent a division, branch, department etc) so it's a way to summarize costs up the accounting structure.

Because the table is joined to itself so many times in the view it's fairly expensive.  It has to do a full table scan of itself several times every time it's referenced in a query and some of the queries reference the view 4 or 5 or more times.

The hierarchy is not updated very often, so in order to improve performance, I thought I'd create a table based upon the view definition.  It improves query performance significantly.

The problem is keeping it updated when the master table is updated.

I have a couple of thoughts so far, would appreciate it anyone else has any other bright ideas.

I had thought about an update trigger to drop and rebuild the table, but DDL performs an implicit commit and don't want to do that since if something goes wrong later, it can't rollback (option a)

When the master table is updated it throws a request out to the OS and a background process runs that perform some other updates.  I could add a table rebuild to the end of this process, however I'd have to connect to the database as the schema owner in a shell script that called SQLPlus (and I don't want the schema owner password in clear text in a script).  (option b)

One option to tie into the above could be to have this reference table in its own schema, grant select on it to the query user and create a synonym so that it's transparent to the query user. (option c)

My last thought was to have an after update trigger launch a job - but I don't know if that's counted as a separate transaction or would be included in the transaction that launches to job (and since the triggering update occurs in an OLTP system, can't have it waiting forever for a return). (option d)

If anyone has any other bright ideas (I'm leaning towards option c at the moment) or just wants to chime in on my thoughts so far, it would be appreciated.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros