Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Davide.

Asked: October 03, 2016 - 4:32 pm UTC

Last updated: October 04, 2016 - 11:32 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi,

I've a high-level question: i've a huge events table written by an external program and i want make a off-line elaboration for each modification affected by the table (insert, delete and update).

the best idea that's come to my mind is creating a trigger that writes a tracking table and then make a procedure that elaborates every occurs of the tracing table

my main pourpose is to not slow down the first loading process with any on-line elaborations

i wouldn't change the loading process because it's quite complex and formed by many sub-processes

tnkz

and Connor said...

Sounds like AQ might be a good option here.

Each change puts a message in the queue, and you have a background listener that dequeues messsages and does the processing.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

tnzk

Davide Golinelli, October 04, 2016 - 7:28 am UTC

i've done a quick view and it seems just what I need!

i've googled a lot about the topic but didn't foud anything

tnkz a lot

Davide Golinelli, October 04, 2016 - 8:17 am UTC

i've seen this question:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8760267539329

it seems that anyway i've to put in the queue the messages from a trigger, right?
the AQ structure helps me only to implement a client-server processs, that's correct?

tnkz again
Connor McDonald
October 04, 2016 - 11:32 am UTC

Here's what I am thinking

1) trigger on "huge event" table

fires off dbms_aq.enqueue, with sufficient details for downstream processing (might just be the primary key, with perhaps a flag for why we're enqueueing, eg ins/upd/del)

2) program running as a daemon (you would write this).

endless loop
- dbms_aq.dequeue;
- with msg, do the downstream processing
- commit;

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library