Estou fazendo o select abaixo e gostaria de melhorar a performnce, pois estou utilizando-o em uma package e esta demorando um absurdo. alguém tem uma dica para eu melhorar a performance do select abaixo?
SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'USER_1'
AND CONSTRAINT_TYPE = 'P'
AND TABLE_NAME LIKE ('TABLE_CUST_%'));
SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE (owner, table_name, CONSTRAINT_NAME) IN (SELECT owner, table_name, CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'USER_1'
AND CONSTRAINT_TYPE = 'P'
AND TABLE_NAME LIKE ('TABLE_CUST_%'));
declare
queryresult number(38) := 0;
cursor C_1 is
SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'USER1'
AND CONSTRAINT_TYPE = 'P'
AND TABLE_NAME LIKE ('TABLE_CUST%'));
begin
for rec in c_1 loop
execute immediate 'SELECT COUNT(1) FROM USER1.'||rec.TABLE_NAME||' a WHERE NOT EXISTS (SELECT 1 FROM USER1.'||TABLE_NAME||'@DB_LINK b WHERE a.'||rec.COLUMN_NAME||' = b.'||rec.COLUMN_NAME) into queryresult;
end loop;
end;
Editado pela última vez por cleberz em Qui, 31 Ago 2017 1:39 pm, em um total de 1 vez.
declare
queryresult number(38) := 0;
cursor C_1 is
SELECT TABLE_NAME, COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE OWNER = 'USER1'
AND CONSTRAINT_TYPE = 'P'
AND TABLE_NAME LIKE ('TABLE_CUST%'));
begin
for rec in c_1 loop
execute immediate 'SELECT COUNT(1) FROM USER1.'||rec.TABLE_NAME||' a WHERE NOT EXISTS (SELECT 1 FROM USER1.'||TABLE_NAME||'@DB_LINK b WHERE a.'||rec.COLUMN_NAME||' = b.'||rec.COLUMN_NAME) into queryresult;
end loop;
end;
execute immediate 'SELECT COUNT(1) FROM USER1.'||rec.TABLE_NAME||' a WHERE NOT EXISTS (SELECT /*+ parallel */ 1 FROM USER1.'||TABLE_NAME||'@DB_LINK b WHERE a.'||rec.COLUMN_NAME||' = b.'||rec.COLUMN_NAME) into queryresult;
declare
--
v_owner varchar2(40) := 'USER1';
v_link varchar2(40) := '@dblink_siv001';
v_table_name varchar2(40) := 'TABLE_CUST';
queryresult number(38) := 0;
--
cursor c is
select constraint_name, table_name from all_constraints
where owner = v_owner
and table_name like v_table_name||'%';
and constraint_type = 'P';
c_r c%rowtype;
--
cursor C1 is
select column_name, count(*) over (partition by table_name) qt_colu
from all_cons_columns
where owner = v_owner
and table_name = c_r.table_name
and constraint_name = c_r.constraint_name
order by position ;
c1_r c1%rowtype;
--
v_comando varchar2(2000);
v_colunas varchar2(2000);
v_contador number := 0;
--
begin
open c;
loop
fetch c into c_r;
exit when c%notfound;
--
v_Comando := null;
v_contador := 1;
v_colunas := null;
open c1;
loop
fetch c1 into c1_r;
exit when c1%notfound;
--
if v_contador = c1_r.qt_colu then
v_colunas := v_colunas||c1_r.column_name;
else
v_colunas := v_colunas||c1_r.column_name||',';
end if;
v_contador := nvl(v_contador,0) + 1;
--
end loop;
close c1;
--
v_Comando := 'Select count(1) from ( ';
v_comando := v_Comando||'Select '||v_Colunas||' from '||c_r.table_name||' minus '||'Select '||v_Colunas||' from '||c_r.table_name||v_link||')';
--v_comando := v_Comando||'Select '||v_Colunas||' from '||c_r.table_name||v_link||' minus '||'Select '||v_Colunas||' from '||c_r.table_name||')';
--
dbms_output.put_line(v_Comando);
execute immediate v_Comando into queryresult;
dbms_output.put_line(queryresult);
end loop;
close c;
end;
/
Brother, da forma como você colocou esta demorando 11.000 segundos e com o hint /*+ parallel */ que coloquei esta demorando 9.000 segundos, um pouco mais rapido. Ainda assim não tem como a gente melhorar?
declare
--
v_owner varchar2(40) := 'USER_1';
v_link varchar2(40) := '@DB_LINK';
v_table_name varchar2(40) := 'TABLE_CUST_';
--
queryresult number(38) := 0;
v_qt_regi_base_loca number(38) := 0;
v_qt_regi_base_remo number(38) := 0;
--
cursor c is
select constraint_name, table_name from all_constraints
where owner = v_owner
and table_name like v_table_name||'%'
and constraint_type = 'P';
c_r c%rowtype;
--
cursor C1 is
select column_name, count(*) over (partition by table_name) qt_colu
from all_cons_columns
where owner = v_owner
and table_name = c_r.table_name
and constraint_name = c_r.constraint_name
order by position ;
c1_r c1%rowtype;
--
v_comando varchar2(2000);
v_colunas varchar2(2000);
v_contador number := 0;
--
begin
open c;
loop
fetch c into c_r;
exit when c%notfound;
--
-- pesquisa a quantidade de registros na base local
--
v_qt_regi_base_loca := 0;
execute immediate 'select count(1) from '||c_r.table_name into v_qt_regi_base_loca;
--
v_contador := 1;
v_colunas := null;
v_Comando := 'Select count(1) from '||c_r.table_name||' a where exists (select 1 from '||c_r.table_name||v_link||' b ';
open c1;
loop
fetch c1 into c1_r;
exit when c1%notfound;
--
if v_contador = 1 then
v_Comando := v_Comando||' where ';
else
v_Comando := v_Comando||' and ';
end if;
--
v_Comando := v_Comando||' a.'||c1_r.column_name||' = b.'||c1_r.column_name;
v_contador := nvl(v_contador,0) + 1;
--
end loop;
close c1;
--
v_Comando := v_Comando||')';
--
dbms_output.put_line(v_Comando);
execute immediate v_Comando into v_qt_regi_base_remo;
--
dbms_output.put_line('Local :'||nvl(v_qt_regi_base_loca,0));
dbms_output.put_line('Remota :'||nvl(v_qt_regi_base_Remo,0));
dbms_output.put_line( nvl(v_qt_regi_base_loca,0) - nvl(v_qt_regi_base_remo,0) );
end loop;
close c;
end;
/