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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig deeper on Oracle stored procedures
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.