RE: "Re-sequencing" a pseudo-key

From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Thu, 9 Oct 2014 13:10:28 -0700
Message-ID: <BLU179-W89E5EB84810109E7BF9A55EBA00_at_phx.gbl>

Yes, lots of issues to consider--such as complexity, redo, risk, and downtime--before you pick the solution that works best for you and finalize the details. Automation will mitigate the complexity. For example, you could write a script to generate dynamic SQL. [oracle_at_localhost ~]$ cat script.sqlset echo on create table test_table (recid integer not null primary key, ref_recid_1 integer not null, ref_recid_2 integer not null); insert into test_table values (1, 2, 3);insert into test_table values (11, 12, 13); alter table test_table add (new_recid integer null, new_ref_recid_1 integer null, new_ref_recid_2 integer null); select * from test_table;
define lower_limit=1;define upper_limit=20; create table translation (recid integer primary key, new_recid integer not null); truncate table translation;insert into translationselect recid, rownum from( select recid from test_table where recid between &&lower_limit and &&upper_limit union select ref_recid_1 from test_table where ref_recid_1 between &&lower_limit and &&upper_limit union select ref_recid_2 from test_table where ref_recid_2 between &&lower_limit and &&upper_limit order by 1); select * from translation;
set serveroutput on size 1000000;
declare
  procedure translate2( p_original in varchar2, p_primary_key in varchar2, p_col_X in varchar2, p_translated_col_X in varchar2, p_lower_limit in integer, p_upper_limit in integer ) is   dml_command varchar2(2048);
  begin
    dml_command := 'update' || ' ( ' || ' select ' || p_original || '.' || p_primary_key || ' as pk, ' || p_original || '.' || p_translated_col_X || ' as translated_col_X, translation.new_recid as new_recid' || ' from ' || p_original || ', translation' || ' where ' || p_original || '.' || p_col_X || ' between ' || p_lower_limit || ' and ' || p_upper_limit || ' and translation.recid between ' || p_lower_limit || ' and ' || p_upper_limit || ' and ' || p_original || '.' || p_col_X || ' = translation.recid' || ' )' || ' set translated_col_X = new_recid';     dbms_output.put_line(dml_command);
    begin execute immediate dml_command; dbms_output.put_line(SQL%ROWCOUNT || ' row(s) updated'); exception when others then dbms_output.put_line('sqlcode = ' || sqlcode || ', sqlerrm = ' || sqlerrm); end;   end;
begin
  translate2('test_table', 'recid', 'recid', 'new_recid', 1, 10); translate2('test_table', 'recid', 'ref_recid_1', 'new_ref_recid_1', 1, 10); translate2('test_table', 'recid', 'ref_recid_2', 'new_ref_recid_2', 1, 10);   translate2('test_table', 'recid', 'recid', 'new_recid', 11, 20); translate2('test_table', 'recid', 'ref_recid_1', 'new_ref_recid_1', 11, 20); translate2('test_table', 'recid', 'ref_recid_2', 'new_ref_recid_2', 11, 20); end;/
select * from test_table;[oracle_at_localhost ~]$ sqlplus SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 9 13:03:06 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter user-name: hrEnter password:
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> _at_scriptSQL> SQL> create table test_table (recid integer not null primary key, ref_recid_1 integer not null, ref_recid_2 integer not null); Table created.
SQL> SQL> insert into test_table values (1, 2, 3); 1 row created.
SQL> insert into test_table values (11, 12, 13); 1 row created.
SQL> SQL> alter table test_table add (new_recid integer null, new_ref_recid_1 integer null, new_ref_recid_2 integer null); Table altered.
SQL> SQL> select * from test_table;

RECID REF_RECID_1 REF_RECID_2 NEW_RECID NEW_REF_RECID_1 NEW_REF_RECID_2---------- ----------- ----------- ---------- --------------- --------------- 1 2 3 11 12 13 SQL> SQL> define lower_limit=1;SQL> define upper_limit=20;SQL> SQL> create table translation (recid integer primary key, new_recid integer not null); Table created.
SQL> SQL> truncate table translation;
Table truncated.
SQL> insert into translation 2 select recid, rownum from 3 ( 4 select recid from test_table 5 where recid between &&lower_limit and &&upper_limit 6 union select ref_recid_1 from test_table 7 where ref_recid_1 between &&lower_limit and &&upper_limit 8 union select ref_recid_2 from test_table 9 where ref_recid_2 between &&lower_limit and &&upper_limit 10 order by 1 11 );old 5: where recid between &&lower_limit and &&upper_limitnew 5: where recid between 1 and 20old 7: where ref_recid_1 between &&lower_limit and &&upper_limitnew 7: where ref_recid_1 between 1 and 20old 9: where ref_recid_2 between &&lower_limit and &&upper_limitnew 9: where ref_recid_2 between 1 and 20 6 rows created.
SQL> SQL> select * from translation;

RECID NEW_RECID---------- ---------- 1 1 2 2 3 3 11 4 12 5 13 6 6 rows selected.
SQL> SQL> set serveroutput on size 1000000;SQL> SQL> declare 2 3 procedure translate2( 4 p_original in varchar2, 5 p_primary_key in varchar2, 6 p_col_X in varchar2, 7 p_translated_col_X in varchar2, 8 p_lower_limit in integer, 9 p_upper_limit in integer 10 ) is 11 12 dml_command varchar2(2048); 13 14 begin 15 16 dml_command := 'update' || 17 ' ( ' || 18 ' select ' || p_original || '.' || p_primary_key || ' as pk, ' || p_original || '.' || p_translated_col_X || ' as translated_col_X, translation.new_recid as new_recid' || 19 ' from ' || p_original || ', translation' || 20 ' where ' || p_original || '.' || p_col_X || ' between ' || p_lower_limit || ' and ' || p_upper_limit || 21 ' and translation.recid between ' || p_lower_limit || ' and ' || p_upper_limit || 22 ' and ' || p_original || '.' || p_col_X || ' = translation.recid' || 23 ' )' || 24 ' set translated_col_X = new_recid'; 25 26 dbms_output.put_line(dml_command); 27 28 begin 29 execute immediate dml_command; 30 dbms_output.put_line(SQL%ROWCOUNT || ' row(s) updated'); 31 exception 32 when others then dbms_output.put_line('sqlcode = ' || sqlcode || ', sqlerrm = ' || sqlerrm); 33 end; 34 35 end; 36 37 begin 38 39 translate2('test_table', 'recid', 'recid', 'new_recid', 1, 10); 40 translate2('test_table', 'recid', 'ref_recid_1', 'new_ref_recid_1', 1, 10); 41 translate2('test_table', 'recid', 'ref_recid_2', 'new_ref_recid_2', 1, 10); 42 43 translate2('test_table', 'recid', 'recid', 'new_recid', 11, 20); 44 translate2('test_table', 'recid', 'ref_recid_1', 'new_ref_recid_1', 11, 20); 45 translate2('test_table', 'recid', 'ref_recid_2', 'new_ref_recid_2', 11, 20); 46 47 end; 48 /update ( select test_table.recid as pk, test_table.new_recid astranslated_col_X, translation.new_recid as new_recid from test_table,translation where test_table.recid between 1 and 10 and translation.recidbetween 1 and 10 and test_table.recid = translation.recid ) settranslated_col_X = new_recid1 row(s) updatedupdate ( select test_table.recid as pk, test_table.new_ref_recid_1 astranslated_col_X, translation.new_recid as new_recid from test_table,translation where test_table.ref_recid_1 between 1 and 10 andtranslation.recid between 1 and 10 and test_table.ref_recid_1 =translation.recid ) set translated_col_X = new_recid1 row(s) updatedupdate ( select test_table.recid as pk, test_table.new_ref_recid_2 astranslated_col_X, translation.new_recid as new_recid from test_table,translation where test_table.ref_recid_2 between 1 and 10 andtranslation.recid between 1 and 10 and test_table.ref_recid_2 =translation.recid ) set translated_col_X = new_recid1 row(s) updatedupdate ( select test_table.recid as pk, test_table.new_recid astranslated_col_X, translation.new_recid as new_recid from test_table,translation where test_table.recid between 11 and 20 and translation.recidbetween 11 and 20 and test_table.recid = translation.recid ) settranslated_col_X = new_recid1 row(s) updatedupdate ( select test_table.recid as pk, test_table.new_ref_recid_1 astranslated_col_X, translation.new_recid as new_recid from test_table,translation where test_table.ref_recid_1 between 11 and 20 andtranslation.recid between 11 and 20 and test_table.ref_recid_1 =translation.recid ) set translated_col_X = new_recid1 row(s) updatedupdate ( select test_table.recid as pk, test_table.new_ref_recid_2 astranslated_col_X, translation.new_recid as new_recid from test_table,translation where test_table.ref_recid_2 between 11 and 20 andtranslation.recid between 11 and 20 and test_table.ref_recid_2 =translation.recid ) set translated_col_X = new_recid1 row(s) updated PL/SQL procedure successfully completed. SQL> SQL> select * from test_table;

RECID REF_RECID_1 REF_RECID_2 NEW_RECID NEW_REF_RECID_1 NEW_REF_RECID_2---------- ----------- ----------- ---------- --------------- --------------- 1 2 3 1 2 3 11 12 13 4 5 6 SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

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

Received on Thu Oct 09 2014 - 22:10:28 CEST

This message: [ Message body ]
Next message: Fergal Taheny: "RE: Logout system trigger"
Previous message: Jure Bratina: "Re: Logout system trigger"
Maybe in reply to: Kim Berg Hansen: ""Re-sequencing" a pseudo-key"
Next in thread: Kim Berg Hansen: "Re: "Re-sequencing" a pseudo-key"
Reply: Kim Berg Hansen: "Re: "Re-sequencing" a pseudo-key"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message