Tr : Re : AQ dequeue question

<!-- 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: