I had a requirement where for some promotions event client wanted to capture changed data from last 1 hour or 1 day. The table size was huge (750M rows) and this table is getting used for transactions so creating trigger is also not a good option as it may impact performance for application. Also creating materialized views etc. also involve a certain level of changes and still we need to compare.
As making changes in application code is not so quick so we identified that if we create a backup table and compare by using compare utility package, it will be quick.
below code is tested in 5.5 M records in each table and it completed comparison and generating delta in just 1 minute.
CREATE OR REPLACE PACKAGE pkg_comparision
AS
-- ****************************************************************************
-- Name: pkg_comparision
-- What:
-- Collection of procedures used as utilities to compare tables. This is draft version and latter it can be enhance to compare tables over db link or compare tables from multiple schema's
-- Also in draft version it will not store historical comparision but it can be enhanced to do the same.
-- dbms_comparison and DBA_COMPARISON_ROW_DIF is not having direct permissions assigned to users but via roles
-- so if we want to call it via package
-- we need to execute below statements from SYS user
-- grant execute on dbms_comparison to SCPOUSER;
-- grant select on DBA_COMPARISON_ROW_DIF to SCPOUSER;
-- Final output will be written to output_table
-- Changes
-- When Who What
-- ---------- ---------------------- ----------------------------------------
-- 22/06/2017 Anand Yadav, XYZ v1.0 Initial draft
-- ****************************************************************************
comparision_name VARCHAR2 (30);
-- *********************************************************
-- function: get_comparision_name
-- Purpose:
-- Create a new comparision name
--
-- Input:
--
-- *********************************************************
FUNCTION get_comparision_name (orig_table_name IN VARCHAR2,
bk_table_name IN VARCHAR2)
RETURN VARCHAR2;
-- *********************************************************
-- Procedure: drop_comparision
-- Purpose:
-- To drop comparision before next iteration
--
-- Input:
--
-- *********************************************************
PROCEDURE drop_comparision (comparision_name IN VARCHAR2);
-- *********************************************************
-- Procedure: create_comparision
-- Purpose:
-- Create a new comparision
--
-- Input:
--
-- *********************************************************
PROCEDURE create_comparision (orig_table_name IN VARCHAR2,
bk_table_name IN VARCHAR2,
orig_index_name IN VARCHAR2);
-- *********************************************************
-- Procedure: compare
-- Purpose:
-- compare tables data based on comparision created
--
-- Input:
--
-- *********************************************************
PROCEDURE compare (comparison_name IN VARCHAR2);
-- *********************************************************
-- Procedure: generate_delta
-- Purpose:
-- generate difference of data and write it to output table
--
-- Input:
--
--
-- *********************************************************
PROCEDURE generate_delta (orig_table_name IN VARCHAR2,
bk_table_name IN VARCHAR2,
output_table IN VARCHAR2,
comparision_name IN VARCHAR2);
-- *********************************************************
-- Procedure: execute_comparision
-- Purpose:
-- This is the procedure external system will invoke
--
-- Input:
--
--
-- *********************************************************
PROCEDURE execute_comparision (orig_table_name IN VARCHAR2,
bk_table_name IN VARCHAR2,
orig_index_name IN VARCHAR2,
output_table IN VARCHAR2);
END pkg_comparision;
/
CREATE OR REPLACE PACKAGE BODY pkg_comparision
AS
-- ****************************************************************************
-- Name: pkg_comparision
-- What:
-- Collection of procedures used as utilities to compare tables. This is draft version and latter it can be enhance to compare tables over db link or compare tables from multiple schema's
-- Also in draft version it will not store historical comparision but it can be enhanced to do the same.
-- dbms_comparison and DBA_COMPARISON_ROW_DIF is not having direct permissions assigned to users but via roles
-- so if we want to call it via package
-- we need to execute below statements from SYS user
-- grant execute on dbms_comparison to SCPOUSER;
-- grant select on DBA_COMPARISON_ROW_DIF to SCPOUSER;
-- Final output will be written to output_table
-- Changes
-- When Who What
-- ---------- ---------------------- ----------------------------------------
-- 22/06/2017 Anand Yadav, XYZ v1.0 Initial draft
-- ****************************************************************************
-- ---------- ---------------------- ----------------------------------------
-- Usage exec PKG_COMPARISION.EXECUTE_COMPARISION('original table name', 'backup table name', 'original table index', 'output table');
-- Example exec PKG_COMPARISION.EXECUTE_COMPARISION('ALL_OBJECTS_TEMP1','ALL_OBJECTS_TEMP','ALL_OBJECTS_TEMP1_IDX','MY_OUTPUT1');
-- Output table can be given anything as per users convinience
-- ****************************************************************************
-- Function: get_comparision_name
-- Purpose:
-- Create a new comparision name
--
--
-- Input:
--
-- *********************************************************
FUNCTION get_comparision_name (orig_table_name IN VARCHAR2,
bk_table_name IN VARCHAR2)
RETURN VARCHAR2
IS
-- comparision_name VARCHAR2 (30) := NULL;
BEGIN
SELECT 'COMP_'
|| SUBSTR (UPPER (LTRIM (RTRIM (orig_table_name))), 1, 12)
|| '_'
|| SUBSTR (UPPER (LTRIM (RTRIM (bk_table_name))), 1, 12)
INTO comparision_name
FROM DUAL;
RETURN comparision_name;
END;
-- *********************************************************
-- Procedure: drop_comparision
-- Purpose:
-- To drop comparision before next iteration
--
-- Input:
--
-- *********************************************************
PROCEDURE drop_comparision (comparision_name IN VARCHAR2)
IS
BEGIN
DBMS_COMPARISON.drop_comparison (comparision_name);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Comparision object ' || comparision_name || 'does not exists');
--raise;
END;
-- *********************************************************
-- Procedure: create_comparision
-- Purpose:
-- Create a new comparision
--
-- Input:
--
-- *********************************************************
PROCEDURE create_comparision (orig_table_name IN VARCHAR2,
bk_table_name IN VARCHAR2,
orig_index_name IN VARCHAR2)
IS
BEGIN
DBMS_COMPARISON.create_comparison (
comparison_name => comparision_name,
schema_name => USER,
object_name => orig_table_name,
index_schema_name => USER,
index_name => orig_index_name,
dblink_name => '',
remote_schema_name => USER,
remote_object_name => bk_table_name,
column_list => '*',
scan_mode => DBMS_COMPARISON.cmp_scan_mode_full);
END;
-- *********************************************************
-- Procedure: compare
-- Purpose:
-- compare tables data based on comparision created
--
-- Input:
--
-- *********************************************************
PROCEDURE compare (comparison_name IN VARCHAR2)
IS
v_scan_info DBMS_COMPARISON.comparison_type;
v_compare_result BOOLEAN;
BEGIN
v_compare_result :=
DBMS_COMPARISON.compare (comparison_name => comparison_name,
scan_info => v_scan_info,
perform_row_dif => TRUE);
IF v_compare_result = TRUE
THEN
DBMS_OUTPUT.put_line (a => 'Tables are synchronized!');
ELSE
DBMS_OUTPUT.put_line (
a => 'Warning! Data divergence found!'
|| CHR (10)
|| 'Scan id differences: '
|| v_scan_info.scan_id);
END IF;
END;
-- *********************************************************
-- Procedure: generate_delta
-- Purpose:
-- generate difference of data and write it to output table
--
-- Input:
--
--
-- *********************************************************
PROCEDURE generate_delta (orig_table_name IN VARCHAR2,
bk_table_name IN VARCHAR2,
output_table IN VARCHAR2,
comparision_name IN VARCHAR2)
AS
cnt NUMBER := 0;
BEGIN
--DBMS_OUTPUT.PUT_LINE('CREATE TABLE '||comparision_name|| ' AS select * from DBA_COMPARISON_ROW_DIF where COMPARISON_NAME='''||comparision_name||'''');
SELECT COUNT (1)
INTO cnt
FROM user_tables
WHERE table_name = comparision_name;
IF cnt > 0
THEN
EXECUTE IMMEDIATE 'DROP TABLE ' || comparision_name;
END IF;
EXECUTE IMMEDIATE
'CREATE TABLE '
|| comparision_name
|| ' AS select * from DBA_COMPARISON_ROW_DIF where COMPARISON_NAME='''
|| comparision_name
|| '''';
EXECUTE IMMEDIATE
'CREATE INDEX '
|| SUBSTR (comparision_name, 1, 15)
|| 'IDX1 ON '
|| comparision_name
|| '(LOCAL_ROWID)';
EXECUTE IMMEDIATE
'CREATE INDEX '
|| SUBSTR (comparision_name, 1, 15)
|| 'IDX2 ON '
|| comparision_name
|| '(REMOTE_ROWID)';
cnt := 0;
SELECT COUNT (1)
INTO cnt
FROM user_tables
WHERE table_name = output_table;
IF cnt > 0
THEN
EXECUTE IMMEDIATE 'DROP TABLE ' || output_table;
END IF;
EXECUTE IMMEDIATE
'creAte table '
|| output_table
|| ' as
select ''INSERT'' AS operation, b.* from '
|| comparision_name
|| ' a, '
|| orig_table_name
|| ' b where a.local_rowid=b.rowid and a.remote_rowid is null
UNION ALL
select ''DELETE'' AS operation, b.* from '
|| comparision_name
|| ' a, '
|| bk_table_name
|| ' b where a.remote_rowid=b.rowid and a.local_rowid is null
UNION ALL
select ''UPDATE'' AS operation, b.* from '
|| comparision_name
|| ' a, '
|| orig_table_name
|| ' b where a.local_rowid=b.rowid and a.local_rowid is not null and a.remote_rowid is not null';
END;
-- *********************************************************
-- Procedure: execute_comparision
-- Purpose:
-- This is the procedure external system will invoke
--
-- Input:actual table name, backup table name, actual table index, output table
--
--
-- *********************************************************
PROCEDURE execute_comparision (orig_table_name IN VARCHAR2,
bk_table_name IN VARCHAR2,
orig_index_name IN VARCHAR2,
output_table IN VARCHAR2)
IS
comparision_name VARCHAR2 (30) := NULL;
BEGIN
comparision_name :=
get_comparision_name (orig_table_name, bk_table_name);
drop_comparision (comparision_name);
create_comparision (orig_table_name, bk_table_name, orig_index_name);
compare (comparision_name);
generate_delta (orig_table_name,
bk_table_name,
output_table,
comparision_name);
END;
END pkg_comparision;
/
SHOW ERRORS;
No comments:
Post a Comment