Permalink
Find file
145716d Dec 30, 2015
172 lines (143 sloc) 5.69 KB
-- ----------------------------------------------------------------------------------------------
--
-- Script: merge_counter.sql
--
-- Author: Adrian Billington
-- www.oracle-developer.net
--
-- Description: A package to maintain separate INSERT and UPDATE counts from a MERGE.
--
-- I first devised this method for an article for Quest's PL/SQL Pipelines. This
-- article can be found on the oracle-developer.net website at
-- www.oracle-developer.net/display.php?id=220.
--
-- This utility will work for all versions of 9i and above (i.e. when MERGE was
-- introduced).
--
-- A current limitation is that it does not support parallel MERGE. I'm waiting for
-- a documented feature of Oracle 10g to start working so I can put a parallel-
-- enabled version together.
--
-- Usage: Usage is quite simple. We maintain an insert counter, update counter or both.
-- For efficiency, it makes sense to maintain a single counter on the operation that
-- is LEAST likely to occur. For example, if a MERGE is likely to UPDATE 200,000
-- records and INSERT just 5,000, then it makes sense to maintain an insert counter
-- only.
--
-- a) To maintain an insert counter
-- -----------------------------
--
-- MERGE
-- INTO target_table t
-- USING source_table s
-- ON (s.primary_key = t.primary_key)
-- WHEN MATCHED THEN
-- UPDATE
-- SET t.column_name = s.column_name
-- WHEN NOT MATCHED THEN
-- INSERT ( t.primary_key
-- , t.column_name )
-- VALUES ( DECODE(merge_counter.insert_counter,0,s.primary_key)
-- , s.column_name );
--
-- b) To maintain an update counter
-- -----------------------------
--
-- MERGE
-- INTO target_table t
-- USING source_table s
-- ON (s.primary_key = t.primary_key)
-- WHEN MATCHED THEN
-- UPDATE
-- SET t.column_name = DECODE(merge_counter.update_counter,0,s.column_name)
-- WHEN NOT MATCHED THEN
-- INSERT ( t.primary_key
-- , t.column_name )
-- VALUES ( s.primary_key
-- , s.column_name );
--
-- c) Reporting insert and update rowcounts
-- -------------------------------------
--
-- INSERT
-- => if insert counter used, merge_counter.get_insert_count
-- => if update counter used, merge_counter.get_insert_count(sql%rowcount)
--
-- UPDATE
-- => if update counter used, merge_counter.get_update_count
-- => if insert counter used, merge_counter.get_update_count(sql%rowcount)
--
-- To reset the counters, use merge_counter.reset_counters.
--
-- ----------------------------------------------------------------------------------------------
CREATE PACKAGE merge_counter AS
FUNCTION insert_counter
RETURN PLS_INTEGER;
FUNCTION update_counter
RETURN PLS_INTEGER;
FUNCTION get_update_count
RETURN PLS_INTEGER;
FUNCTION get_update_count (
merge_rowcount_in IN PLS_INTEGER
) RETURN PLS_INTEGER;
FUNCTION get_insert_count
RETURN PLS_INTEGER;
FUNCTION get_insert_count (
merge_rowcount_in in PLS_INTEGER
) RETURN PLS_INTEGER;
PROCEDURE reset_counters;
END merge_counter;
/
CREATE PACKAGE BODY merge_counter AS
g_update_counter PLS_INTEGER NOT NULL := 0;
g_insert_counter PLS_INTEGER NOT NULL := 0;
-------------------------------------------------------------------------
FUNCTION insert_counter
RETURN PLS_INTEGER IS
BEGIN
g_insert_counter := g_insert_counter + 1;
RETURN 0;
END insert_counter;
-------------------------------------------------------------------------
FUNCTION update_counter
RETURN PLS_INTEGER IS
BEGIN
g_update_counter := g_update_counter + 1;
RETURN 0;
END update_counter;
-------------------------------------------------------------------------
FUNCTION get_update_count
RETURN PLS_INTEGER is
BEGIN
RETURN g_update_counter;
END get_update_count;
-------------------------------------------------------------------------
FUNCTION get_update_count (
merge_rowcount_in IN PLS_INTEGER
) RETURN PLS_INTEGER IS
BEGIN
RETURN NVL( merge_rowcount_in - g_insert_counter, 0 );
END get_update_count;
-------------------------------------------------------------------------
FUNCTION get_insert_count
RETURN PLS_INTEGER IS
BEGIN
RETURN g_insert_counter;
END get_insert_count;
-------------------------------------------------------------------------
FUNCTION get_insert_count (
merge_rowcount_in IN PLS_INTEGER
) RETURN PLS_INTEGER IS
BEGIN
RETURN NVL( merge_rowcount_in - g_update_counter, 0 );
END get_insert_count;
-------------------------------------------------------------------------
PROCEDURE reset_counters IS
BEGIN
g_update_counter := 0;
g_insert_counter := 0;
END reset_counters;
END merge_counter;
/
CREATE OR REPLACE PUBLIC SYNONYM merge_counter FOR merge_counter;
GRANT EXECUTE ON merge_counter TO PUBLIC;