Tr : Re : AQ dequeue question
- From: Bernard Polarski <bpolarsk@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 26 May 2006 06:20:41 -0700 (PDT)
<!-- DIV {margin:0px;}--> nah, none of them will do. I read in the doc :
(Oracle Streams Advanced Queuing User's Guide and Reference 10g Release 2
(10.2) Part Number B14257-01)
message grouping : This optional parameter specifies whether messages are
grouped or not. The default NONE means each message is treated individually.
TRANSACTIONAL means all messages enqueued in one transaction are considered
part of the same group and can be dequeued as a group of related messages.
So it speaks only of the way the messages are enqueued, nothing about the
content of data that are stored into the any_data.
Sort_list : it deals with enqueue in the propagation queues after the
capture. I read in the doc :
The sort_list parameter determines the order in which messages are dequeued.
You cannot change the message sort order after you have created the queue
table. Your choices are:
ENQ_TIME
ENQ_TIME,PRIORITY
PRIORITY
PRIORITY,ENQ_TIME
PRIORITY,COMMIT_TIME
COMMIT_TIME
I don't think it is possible at all. By definition user_data hold anonymous
content with only a schema address. It will be distributed by the reader
process to the applier processe that will execute the command over the right
object
Marius want to identifiy the content of these anonymous before they are
distributed and skip all duplicated of a row to only extract the last. So he
want the anonymous to have some kind of tag which their anonymous property
forbid. Another problem is defining 'mutated' in streams.
Since user_data does not recognize rowid, then it relies only on full column
comparison. So in fact he will never find his mutated rows
if we create a row with PK A and a colum value 'B' then mutate the colume
value to 'C' we still update in the table the same row
<rowid> A B
the rows becomes
<rowid> A C
You stil know that it is the same row due to the row_id and that row has
mutated. It is perfectly possible that A C is not the mutation of A B. For
instance you can create 'A B' ,then delete 'A B' and creatre 'A C'. In this
case you could not say that A C is the mutation of 'A B'
It is the row_id that tells you it is the mutation.
In streams you do not have the row id :
row 'A B' and 'A C' will be considedred different.
There is no way to tell that user_data 'A C' is in fact user_data 'A B'
mutated or if row 'A C' is a new row that appeared after the deletion of 'A
B'.
B. Polarski
http://www.smenu.org
----- Message d'origine ----
De : Marius Raicu <mariu200@xxxxxxxxx>
Ãâ : bpolarsk@xxxxxxxxx; rjamya@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
EnvoyÃÂ le : Vendredi, 26 Mai 2006, 2h18mn 23s
Objetà : RE: AQ dequeue question
Hi,
What about using SORT_LIST and/or MESSAGE_GROUPING parameters of
dbms_aqadm.create_queue_table? Otherwise, defining a user message type where
you declare a column like PRIORITY/SEQUENCE and getting max from this column
after dequeuing or using correlation_id with message enqueuing may be an area
to explore.
regards,
Bernard Polarski <bpolarsk@xxxxxxxxx> a Ãcrit : <!-- DIV {margin:0px;}-->Do
you mean you want the apply process to skip rows in the queue table and jump
directly to the last occurence of this row? You can alway write your own dml
handler but rows are compared with the full value of every columns, that's why
you activated the suplemental logs. I doubt you will earn something with your
dml handler. The heaviest part in streams are the update and you want to detect
them each.
As of attibute that could palys in ordered for a single column you could use
the SCN of t he user_data with something like that :
declare
lcr sys.LCR$._ROW_RECORD
rc PLS_INTEGER;
object_scb number;
trn_id varchar2 ;
begin
for p_x in (select user_data from AQ$_<QUEUE_RECEIVER>
loop
rc:= p_x.user_data.getobject(lcr) # load the user_data into LCR
-- use scn
object_scn := lcr.get_scn();
-- or use the trancaction id is a varchar - which is the sequentiel id
of the user_data
trn_id=lcr_get_trancation_id();
-- now you check if there is already another another one and decide to
delete this user_data or
execute it. with lcr.execute;
end loop;
end;
all this will take you far in time development
B.Polarski
http://www.smenu.org
-----Original Message-----
From: rjamya [mailto:rjamya@xxxxxxxxx]
Sent: Thursday, 25 May, 2006 9:32 PM
To: Oracle Discussion List
Subject: AQ dequeue question
We have a process that tracks changes to a table, whenever a column is
changes, pk is inserted in a AQ. There is a daemon process that does
some processing based on this value.
It may happen that in a short burst, one row may get modified multiple
times. Now we want to avoid re-processing the same row based on
entries in AQ.
Is there a way to get a ordered (rather distinct) set for dequeue by
specifying an attribute in the USERDATA part of the payload when
dequeuing?
Oracle is 9.2
Regards,
Marius
Yahoo! Mail rÃinvente le mail ! DÃcouvrez le nouveau Yahoo! Mail et son
interface rÃvolutionnaire.
Other related posts:
