efficient SQL for Oracle's CDC packages

From: <oxnard_at_carolina.rr.com>
Date: Tue, 29 Jan 2008 21:57:12 -0500
Message-ID: <4562073.83971201661832930.JavaMail.root@cdptpa-web08-z02>

db version 10.2.0.3.0

I am attempting to come up with an efficient process to process rows in the Oracle defined table created by package DBMS_CDC_PUBLISH. I do understand I will need to use DBMS_CDC_SUBSCRIDE to set up the view. For the purposes of building a good process I can test with the change table.

I am required to log only the end state of each DML operation on the table. So for example

Insert into t1 values (1,1);
update t1 set c1 = 2 where c1 = 1;
update t1 set c1 = 3 where c1 = 2;

I only should show the end state of c1 = 3 and c2 = 1

Additionally if there is a row already in T1 and a delete for that row occurs. I only need to capture the delete.

Here is my test setup:  

SQL>
SQL> conn cdc/cdc_at_to1
Connected.
SQL>
SQL> select * from v$version;

BANNER

---

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production

5 rows selected.

SQL>
SQL> grant connect,resource to u2;

Grant succeeded.

SQL>
SQL> conn u2/u2_at_to1
Connected.
SQL>
SQL> create table t1(
  2 c1 number,
  3 c2 number);

Table created.

SQL>
SQL> conn cdc/cdc_at_to1
Connected.
SQL>
SQL> begin
  2 dbms_cdc_publish.create_change_set(   3 change_set_name => 'U2_CS'
  4 ,description => 'change set'
  5 ,change_source_name => 'SYNC_SOURCE' -- < must be sync_source   6 );
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> begin

  2 dbms_cdc_publish.create_change_table(   3 owner => 'U2'

  4    ,change_table_name => 'T1_CHNG_TBL'
  5    ,change_set_name => 'U2_CS'
  6    ,source_schema => 'U2'
  7    ,source_table => 'T1'
  8    ,column_type_list => '
  9  	       c1 number,
 10  	       c2 number
 11  	       '
 12    ,capture_values => 'both'
 13    ,rs_id => 'y'
 14    ,row_id => 'y'
 15    ,user_id => 'n'
 16    ,timestamp => 'y'
 17    ,object_id => 'n'
 18    ,source_colmap => 'n'

 19 ,target_colmap => 'n'
 20 ,options_string => 'TABLESPACE USERS'  21 );
 22 end;
 23 /

PL/SQL procedure successfully completed.

SQL>
SQL> spool off

So now I connect as u2
SQL> Insert into t1 values (1,1);

1 row created.

SQL> update t1 set c1 = 2 where c1 = 1;

1 row updated.

SQL> update t1 set c1 = 3 where c1 = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> So now I go to find the end state of the rows in the table:

select * from

   (select

     operation$


     ,cscn$

     ,rsid$

     ,row_id$

     ,timestamp$

     ,c1

     ,c2

     ,rank() over (partition by row_id$ order by cscn$ desc,rsid$ desc) rk

    from
     t1_chng_tbl)

  where
   rk = 1
order by row_id$,cscn$,rsid$
;

This shows the last update ... which is what I want. The SQL works fine for deletes if and only if there is not another insert which happens to use the same rowid  

Since a delete is the last operation on a row I need to capture that info

But what if another insert comes in which happens to use the same rowid. Then the sql skips over the delete.

I need to do something like:

get the max value of cscn$ and rsid$ if the opertion is a delete return that and continue on partitioning on the same rowid if one happens to occur.

I cannot see how to do that in a single SQL. Any ideas on how to do this would be great. I would like to avoid as much as possible to loop through each record using PL/SQL.

Thanks

Oxnard

oxnard_at_carolina.rr.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 29 2008 - 20:57:12 CST