Re: View based on a procedure

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: mark_ai@xxxxxxxxx
  • Date: Mon, 19 Feb 2007 07:21:49 -0700

Look into using materialized views, possibly synchronously-updated (a.k.a. "ON COMMIT") in addition to asynchronous (a.k.a. "ON DEMAND").

Possibly it is a better solution to perform transformations/calculations when the data is updated rather than when it is queried.  If the underlying data is very volatile and is updated frequently, then this could hurt, but your solution will start to fail if the number of queries from the view is greater than the number of updates to the underlying data, especially if that rate increases.

You can include PL/SQL functions in a WHERE clause using the TABLE() clause, as well -- this technique is pretty well documented in the standard documentation as well as MetaLink.

M. Alroy Mascrenghe wrote:
Is there a way to base a view/materialised view on a procedure? Everytime the view is selected from the procedure must be run. The procedure does a lot of heavy calculations writes to a table. And the view should read from that table. I know DML in select wont work. Is there a workaround, can use some other structure instead of a table in the procedure maybe like (varrays)?


No need to miss a message. Get email on-the-go
with Yahoo! Mail for Mobile. Get started.

Other related posts: