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:
Guys
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 19 2007 - 08:21:49 CST
