Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tr : Re : AQ dequeue question

RE: Tr : Re : AQ dequeue question

From: Marius Raicu <mariu200_at_yahoo.com>
Date: Fri, 26 May 2006 19:18:20 +0200 (CEST)
Message-ID: <20060526171820.8407.qmail@web60925.mail.yahoo.com>


But you can create a queue table with an OBJECT (create type myType as object) which may contain the unique columns identifying your updated row. Then, on DEQUEUE, you can use dequeue_mode=browse or even DBMS_AQ.DEQUEUE_ARRAY. Anyway I think it may be possible but not by dequeuing in a FIFO but dequeuing by batch of messages and analyzing the content of your payload columns - if one of these columns can be a timestamp, I think you can get the latest update based on a set of columns making your PK.

Regards,  

Bernard Polarski <bpolarsk_at_yahoo.com> a écrit :  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

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_at_yahoo.com> a écrit : 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();
    

   end loop;
 end;  

 all this will take you far in time development  

 B.Polarski
 http://www.smenu.org    

 -----Original Message-----
 From: rjamya [mailto:rjamya_at_gmail.com]
 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.

Regards,
Marius                 



 Yahoo! Mail réinvente le mail ! Découvrez le nouveau Yahoo! Mail et son interface révolutionnaire.
--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 26 2006 - 12:18:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US