Ask the Expert

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.
	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);
	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.
	FOR rec_int IN cur_tab

		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');
		v_WHERE := '';
		FOR rec_pri IN cur_pri(rec_int.table_name, 'schema1')
			IF v_WHERE = '' OR v_where IS NULL THEN
				v_where := rec_pri.column_name;
				v_where := v_where || '||' || rec_pri.column_name;			
			END IF;
		v_select1 := '';
		v_select2 := '';

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

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

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

					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;

			CLOSE l_cursor; 


    Requires Free Membership to View

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
	(SELECT column_name  from schema1.table
	 SELECT column_name FROM schema2.table ) t1
SELECT 'schema2.table', column_name
	(SELECT column_name  FROM schema2.table 
	 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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: