I have a process of creating a table that is very slow and would like to improve it.
The view used in the process was created to use the CPF as a parameter, but when it is executed without the CPF or in join with another table it is extremely slow.
I'm trying to get the CPF's from a table and pass them as a parameter in insert / select, still unsuccessful. Can you help me?
declare
v_doc number(15);
cursor c1 is
select CPF from TABELA where cod = 1 and CPF is not null and CPF <> 0;
begin
open c1;
fetch c1 into v_doc;
close c1;
insert into teste_tab (CPFCNPJCLIENTE,EMPRESA,CODAGENCIA,OPERACAO,NOMECLIENTE,TIPOPESSOA,TIPOBDV,DESCRICAOBDV,TIPOVINCULO,DATAINICIO,DATAFINAL)
SELECT
CASE WHEN LENGTH(TO_CHAR(CPF_CNPJ_CLIENTE)) > 11 THEN
lpad(TO_CHAR(CPF_CNPJ_CLIENTE),14,'0') ELSE
lpad(TO_CHAR(CPF_CNPJ_CLIENTE),11,'0') END AS CPFCNPJCLIENTE,
'11111111' AS EMPRESA,
'00010' AS CODAGENCIA,
CASE WHEN LENGTH(TO_CHAR(CPF_CNPJ_CLIENTE)) > 11 THEN
lpad(TO_CHAR(CPF_CNPJ_CLIENTE),14,'0') ELSE
lpad(TO_CHAR(CPF_CNPJ_CLIENTE),11,'0') END || '_' || TO_CHAR(DATA_CADASTRO, 'yyyymmdd') || 'CAD' || TO_CHAR(DATA_ATIVACAO, 'yyyymmdd') AS OPERACAO,
SUBSTR(NOME_CLIENTE, 0, 50) AS NOMECLIENTE,
CASE WHEN LENGTH(TO_CHAR(CPF_CNPJ_CLIENTE)) <= 11 THEN
'F' ELSE
'J' END AS TIPOPESSOA,
'00' AS TIPOBDV,
'Sem BDV' AS DESCRICAOBDV,
'T' AS TIPOVINCULO,
CASE WHEN DATA_ATIVACAO IS NULL then
DATA_CADASTRO ELSE
DATA_ATIVACAO END AS DATAINICIO,
DATA_INATIVACAO AS DATAFINAL
FROM
VIEW
where VIEW.CPF_CNPJ_CLIENTE = v_doc;
commit;
end;