efficient SQL for Oracle's CDC packages

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@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@to1
Connected.
SQL>
SQL> create table t1(
  2   c1 number,
  3   c2 number);

Table created.

SQL>
SQL> conn cdc/cdc@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@xxxxxxxxxxxxxxx

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

Other related posts: