Link to home
Start Free TrialLog in
Avatar of nskmore
nskmore

asked on

Oracle cached query result set

I need to write a trigger on a table that uses some data from other tables. This data is coming from a select statement on a table and is almost always constant. Please let me know if there is a way to cache the query result set in memory and access this data from a trigger.

Thanks.
Avatar of DrSQL - Scott Anderson
DrSQL - Scott Anderson
Flag of United States of America image

Well, you can make the table a cached table:

alter table MyTable cache;

Which says that it stays in the small table cache until the buffers absolutley need to be flushed.

But, if you want the RESULTS to be cached, you would need to create another object (probably a table) that could be cached.  A package can also maintain data across calls to entry point within it, but that is usually session specific (you write an addition block os pl/sql code that initializes your variables and the first time a procedure from that package is called the block is run and any global variables retain their values).  But, if you were to set up a service that ran as a daemon of scheduled job you could use a package that would keep the results in a cursor and then use advanced queue or messaging (which Oracle has built into  supplied pl/sql packages) to transmit the data to the trigger.

Another approach is to use an index that contains all of the column, with the most selective columns listed first.  If all of the columns that the trigger might use (in any way, not just in a where clause), then there's no need for the table to be read.

Good luck!
Avatar of nskmore
nskmore

ASKER

Can you please eloborate on the part where you mentioned about caching the result  set in a temporary table or object. I want a trigger on a table XXX to read data from a table YYY every time it gets triggered. This will prove to be expensive without any caching. Please give your suggestions with some code templates if possible.
Thanks in advance.
Sure, but can you tell me a little more about this select from "YYY"?  

1) How many records is it?
2) Is there a where clause?
3) How many columns?

And, for the trigger - does it have to fire for each row? And, is it more likely that there will be multiple records in a triggering event (insert/update/delete) or just one at a time even though there many be multiple users generating triggering events throughout the day.

This would help me to better structure the objects involved.

Good luck!
ASKER CERTIFIED SOLUTION
Avatar of KICUSek
KICUSek
Flag of Poland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nskmore

ASKER

DrSQL,
Please see my comments below.
1) How many records is it? Table YYY is a table of constant codes. It may have 100 recs atmost.
2) Is there a where clause? Trigger may need to read only 1 row from this cached table (YYY)... Yes there will be a where clause.
3) How many columns? Reads 2 columns.

Table YYY is to be used on every time it is triggered; you only want to load it once, not to read table every time trigger is called on the table XXX.
I think You should hardcode Your values in trigger.
Maybe write some code to generate trigger code based on content of table YYY (if table will be changed time to time).