Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Automatically rebuild a second table after data changes in first table

Posted on 2012-12-27
8
Medium Priority
?
457 Views
Last Modified: 2012-12-28
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.
0
Comment
Question by:Steve Wales
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 38724801
>>> I thought I'd create a table based upon the view definition.

This is called a materialized view.

You can build them with fast refresh on commit or with a periodic job.

http://docs.oracle.com/cd/B10501_01/server.920/a96520/mv.htm


http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_64a.htm
0
 
LVL 32

Expert Comment

by:awking00
ID: 38724825
Can you post a describe of your master table?
0
 
LVL 23

Author Comment

by:Steve Wales
ID: 38724827
I had completely forgotten about MV's - haven't had a need to use one in years.

Thanks, I'll dig into that and get back with you.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 23

Author Comment

by:Steve Wales
ID: 38725356
sdstuber:

My view of the data is a self join - it joins the table to itself a total of 6 times.

According to the documentation link, under the subheading "Materialized Views Containing Only Joins", A materialized view containing only joins can be defined to be refreshed ON COMMIT or ON DEMAND. If it is ON COMMIT, the refresh is performed at commit time of the transaction that does DML on the materialized view's detail table. Oracle does not allow self-joins in materialized join views.

I could create the MV as REFRESH FORCE but not as REFRESH FAST ON COMMIT or REFRESH FAST.

The former gives ORA-12054: cannot set OIN COMMIT refresh attribute for the materialized view

The latter gives ORA-12015: cannot create a fast refresh materialized view from a complex query

So I'm left with a less than "automatic" refresh since it's considered a complex view (doing 6 left outer joins to itself, since an account could have from 0 to 6 "parents" depending on the depth of the tree).

Are there any other options other than doing CREATE MV START sysdate NEXT sysdate+1/24 or something similar so that I have something resembling a more immediate rebuild that doesn't require me to constantly rebuild an entity that won't get changed very often ?

Related question:  If I specify REFRESH FORCE in the START / NEXT syntax, is Oracle clever enough to realized (thanks to the MV LOG) that even though I have a job scheduled to do it, that it doesn't really have to ?   Is there a way to test it (I can manually run the job, but I don't know if it actually performed any updates or not) ?
Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38725787
the dbms_job created can simply be dropped if you don't really want it to run.

or, set it to broken and you can run it as needed, or simply call the refresh procedure yourself
0
 
LVL 23

Author Comment

by:Steve Wales
ID: 38725871
Well, it's more that the rebuild needs to happen when the users make changes - which may be once a year or once a week during year end ... it's not set in stone.

So I need a way to trigger the rebuild to happen after a change in the table.  If calling the refresh job doesn't do any work if there's no indication of a change in the materialized view log, then that might be sufficient.

I don't want the materialized view to be consistently recreated for no reason - it may cause issues during reporting (year end is coming after all).

Does that make the issue clearer ?
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 38725951
How about a variation of your option a that just does an update (or insert) rather than a full recreate?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38726077
the materialized view logs will keep track of changes,  if there are no changes, then the refresh won't do anything.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question