efficient SQL for Oracle's CDC packages
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-lReceived on Tue Jan 29 2008 - 20:57:12 CST