Q

Table comparison stored procedure very slow

I have written a stored procedure to compare data between two tables for all tables in the schema at one shot. The procedure works fine but it takes too long, as it is doing comparison of every field in every row. Here's my procedure. Please let me know what modification I can to make this procedure faster.

CREATE OR REPLACE PROCEDURE schema_compare
AS
	CURSOR cur_tab IS SELECT table_name FROM dba_constraints 
	WHERE table_name LIKE 'S%'
	AND table_name NOT LIKE '%$'
	AND owner = 'schema1'
	AND constraint_type = 'P';

--     This cursor is for all columns without primary key column for a table

	CURSOR cur_tab_col(p_tabname1 IN VARCHAR2, p_owner in VARCHAR2) IS SELECT column_name FROM
	dba_tab_columns dtc WHERE 
	column_name NOT IN(SELECT dcc.column_name FROM dba_constraints dc, dba_cons_columns dcc
	WHERE dc.constraint_TYPE = 'P'
	AND dcc.constraint_name = dc.constraint_name
	AND dcc.table_name = dc.table_name
	AND dcc.owner = dc.owner
	AND dc.OWNER = 'schema1'
	AND dc.table_name like 'S%'
	AND dc.table_name not like '%$'
	AND dcc.table_name like 'S%'
	AND dcc.table_name not like '%$')
	AND dtc.table_name like 'S%'
	AND dtc.table_name not like '%$'
	AND dtc.owner = 'schema1'
	AND dtc.table_name = p_tabname1
	AND dtc.owner = p_owner
	ORDER BY table_name, column_id;

--     This cursor fetches only priamry key columns for a table.

	CURSOR cur_pri(p_tabname3 IN VARCHAR2,p_owner in VARCHAR2 ) IS SELECT dcc.column_name
	FROM dba_constraints dc, dba_cons_columns dcc
	WHERE dc.constraint_TYPE = 'P'
	AND dcc.table_name = dc.table_name
	AND dcc.constraint_name = dc.constraint_name
	AND dcc.owner = dc.owner
	AND dcc.owner = 'schema1'
	AND dcc.table_name = p_tabname3;

	ndummy		NUMBER := 0;
	vdummy		NUMBER := 0;
	v_WHERE		VARCHAR2(4000);
	v_select1	VARCHAR2(4000);
	v_select2	VARCHAR2(4000);
	l_query		VARCHAR2(4000);
	l_row		VARCHAR2(4000);	
	l_result1 	VARCHAR2(4000);
	l_result2	VARCHAR2(4000);
	TYPE rc 	IS REF CURSOR; 
	l_cursor   	rc; 

--  The idea is to built select statement for each row for one table in one schema and then compare the two statements
--  based on primary key.
BEGIN
	FOR rec_int IN cur_tab
	LOOP

		EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM schema1.'||rec_int.table_name INTO ndummy;
		EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM schema2.'||rec_int.table_name INTO vdummy;
   		DBMS_OUTPUT.PUT_LINE('Table Name: '||rec_int.table_name);
		IF ndummy <> vdummy THEN
			dbms_output.put_line('First schema has ' || ndummy ||' Rows Second schema has ' ||vdummy|| ' Rows');
		END IF;
		
		v_WHERE := '';
		
		FOR rec_pri IN cur_pri(rec_int.table_name, 'schema1')
		LOOP
			IF v_WHERE = '' OR v_where IS NULL THEN
				v_where := rec_pri.column_name;
			ELSE
				v_where := v_where || '||' || rec_pri.column_name;			
			END IF;
		END LOOP;
		
		v_select1 := '';
		v_select2 := '';

		l_query := ' SELECT ' || v_where || ' FROM schema1.' || rec_int.table_name;

		BEGIN 
			OPEN l_cursor FOR l_query; 
			LOOP 
				FETCH l_cursor INTO l_row; 
				EXIT WHEN l_cursor%NOTFOUND; 

				FOR rec_non_pri IN cur_tab_col(rec_int.table_name,'schema1')
				LOOP

					v_select1 := 'SELECT ' || CHR(39) || CHR(39) || '||' 										|| rec_non_pri.column_name || ' FROM schema1.'|| rec_int.table_name ||
					 ' WHERE '	|| v_where || ' = ' || CHR(39) || 										l_row || CHR(39);
					
					v_select2 := 'SELECT ' || CHR(39) || CHR(39) || '||' 										|| rec_non_pri.column_name || ' FROM schema2.'|| rec_int.table_name ||
					 ' WHERE '	|| v_where || ' = ' || CHR(39) || 										l_row || CHR(39);

					EXECUTE IMMEDIATE  v_select1 INTO l_result1;
					EXECUTE IMMEDIATE  v_select2 INTO l_result2;


					IF l_result1 <> l_result2 THEN
					   DBMS_OUTPUT.PUT_LINE('Columns value do not match '||l_result1||' -- '||l_result2); 
					END IF;
				END LOOP;

			END LOOP; 
			CLOSE l_cursor; 
		END; 

	END LOOP;	
END;
/

It seems to me you're doing an awful lot of work that is unnecessary. Why not just change your procedure to use an approach like this:

First, create a query where you end up with a select that looks something like this:

SELECT 'schema1.table', column_name
  FROM 
	(SELECT column_name  from schema1.table
	 MINUS
	 SELECT column_name FROM schema2.table ) t1
UNION
SELECT 'schema2.table', column_name
	(SELECT column_name  FROM schema2.table 
	 MINUS
	 SELECT column_name from schema1.table ) t2  ;
For every row that is returned, you have a row that exists in only one of the tables. You can then simply display the results. One query and you've got it. No need to loop through row by row to determine if there's a match. The query does it for you and gives you back only the rows that are different.

As you've already proven, your method works but is slow and time consuming. The key is to reduce the steps you have to take in order to get the result you want. This is one way to do that.

Hope that helps!

For More Information


This was first published in May 2003

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close