CURSOR COM LOOP OU SELECT LIMITANDO NÚMERO DE LINHAS

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
vasilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 24
Registrado em: Qua, 28 Out 2009 9:58 am
Localização: RS

Bom dia pessoal,
Por gentileza, alguém pode me ajudar com a seguinte situação ?

Preciso fazer um select em uma tabela que contém milhões de registros, este SELECT deve ser executado "parceladamente" a cada 50 mil linhas em ordem decrescente ao valor de uma coluna. Ou seja.. roda o select e pega as última 50 milhas, depois as outras 50 mil, e 50 mil, até o final, do valor mais alto para o mais baixo.
Pensei em usar o "ROWNUM", mas o rownum não resolve meu problema, pois a cada vez que executo o SELECT, o banco está sofrendo inserções ao mesmo tempo então o resultado pode variar a cada SELECT.
O que preciso é considerar o estado da tabela no momento do primeiro SELECT, assim os select's na sequência considerarão sempre este valor inicial. Então, pesquisando na web vi alguns exemplos com cursores e tentei algo criando este abaixo.
Ah, a tabela é muito grande, então não posso ficar pegando "tudo"(*) a cada SELECT. Aí trava o processo.
Também preciso de um help para exibir o resultado na tela.
Estou utilizando o SQL Plus

Selecionar tudo

declare
  vCOLUNA1      			TB_TABELA.COLUNA1%type;                         
  vCOLUNA2                      	TB_TABELA.COLUNA2%type;
  vCOLUNA3                  		TB_TABELA.COLUNA3%type;
  vCOLUNA4                   		TB_TABELA.COLUNA4%type;
  vCOLUNA5                		TB_TABELA.COLUNA5%type;
  vCOLUNA6                		TB_TABELA.COLUNA6%type;
  vCOLUNA7              		TB_TABELA.COLUNA7%type;
  vCOLUNA8                   		TB_TABELA.COLUNA8%type;
  vCOLUNA9                   		TB_TABELA.COLUNA9%type;
  vCOLUNA10                  		TB_TABELA.COLUNA10%type;
  vCOLUNA11                   		TB_TABELA.COLUNA10%type;
  vCOLUNA12                   		TB_TABELA.COLUNA12%type;
  vCOLUNA13                		TB_TABELA.COLUNA13%type;
  vCOLUNA14                 		TB_TABELA.COLUNA14%type;
  vCOLUNA15              		TB_TABELA.COLUNA15%type;
  vCOLUNA16        			TB_TABELA.COLUNA16%type;
  vCOLUNA17                       	TB_TABELA.COLUNA17%type;
  vCOLUNA18                      	TB_TABELA.COLUNA18%type;
  vCOLUNA_UNIQUE_ID                   	TB_TABELA.COLUNA_UNIQUE_ID%type;
  vCOLUNA20             		TB_TABELA.COLUNA20%type;
  vCOLUNA21             		TB_TABELA.COLUNA21%type;
  vCOLUNA22              		TB_TABELA.COLUNA22%type;
  vCOLUNA23                        	TB_TABELA.COLUNA23%type;
  vCOLUNA24              		TB_TABELA.COLUNA24%type;
  vCOLUNA25                  		TB_TABELA.COLUNA25%type;
  vCOLUNA26                		TB_TABELA.COLUNA26%type;
  CURSOR cTABELA IS SELECT * FROM TB_TABELA
  WHERE vCOLUNA_UNIQUE_ID >= (SELECT max(vCOLUNA_UNIQUE_ID)FROM TB_TABELA)
  ORDER BY vCOLUNA_UNIQUE_ID DESC;
  type t_tb_TABELA_list is table of cTABELA%rowtype;
  t_Tb_TABELA_rec t_tb_TABELA_list;
Begin
  Open CTABELA;
Loop
  Fetch CTABELA BULK COLLECT into t_tb_TABELA_rec Limit 50000;
  Exit When CTABELA%NOTFOUND;
  for I in t_Tb_TABELA_rec.first .. t_Tb_TABELA_rec.last loop
	SELECT * 
  	INTO vCOLUNA1, vCOLUNA2, vCOLUNA3, vCOLUNA4, vCOLUNA5, vCOLUNA6, vCOLUNA7, vCOLUNA8, vCOLUNA9, vCOLUNA10, vCOLUNA11, vCOLUNA12,
  	vCOLUNA13, vCOLUNA14, vCOLUNA15, vCOLUNA16, vCOLUNA17, vCOLUNA18, vCOLUNA_UNIQUE_ID, vCOLUNA20, vCOLUNA21,
  	vCOLUNA22, vCOLUNA23, vCOLUNA24, vCOLUNA25, vCOLUNA26
  	FROM TB_TABELA
  	WHERE COLUNA_UNIQUE_ID =  vCOLUNA_UNIQUE_ID; 
commit; End Loop;
End loop;
Close CTABELA;
END;
/

Desde já agradeço a ajuda dos prezados.
Abraços.

VaSilva
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Você poderia usar um cursor com bulk collect para dentro de uma tabela em memória, definindo um limit mais ou menos como abaixo. O problema é que 50 mil linhas é muito, não dá para carregar isso tudo porque vai detonar a memória do seu servidor e seu DBA vai querer seu pescoço, então use no máximo de 100 a 1000 no limit (no lugar do 1 que coloquei ali para testar).

Selecionar tudo

declare
  cursor cur_teste is
    select 1 col from dual
    union all select 2 col from dual;
  type t_teste is table of cur_teste%rowtype;
  v_teste t_teste;
begin
  open cur_teste;
  loop
    fetch cur_teste bulk collect
      into v_teste limit 1;
    for i in 1 .. v_teste.count
    loop
      dbms_output.put_line(cur_teste%rowcount || ': ' || v_teste(i).col);
    end loop;
    exit when cur_teste%notfound;
  end loop;
  close cur_teste;
end;
Fiquei com uma dúvida: você tem que ter a imagem da tabela do momento que o select começou a rodar? Se isso for imprescindível, você pode ter que usar isolamento de transação "serializable".

O comando que seta a transação para serializable no sql*plus é:

Selecionar tudo

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Mais informações sobre serializable:
http://asktom.oracle.com/pls/asktom/f?p ... 3191441609
http://download.oracle.com/docs/cd/E118 ... ADFNS00204
vasilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 24
Registrado em: Qua, 28 Out 2009 9:58 am
Localização: RS

Meu caro,
Primeiramente, muito obrigado pela atenção ao meu problema.

Eu quero apenas ver o resultado final no ambiente de testes antes dde implementar na aplicação.
Primeiramente estou testando o código direto no SQL Plus, depois vou testar na aplicação.

Muito grato pela atenção!
VaSilva
vasilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 24
Registrado em: Qua, 28 Out 2009 9:58 am
Localização: RS
VaSilva

Ah..
onde você menciona a tabela neste seu exemplo ?

Obrigado.
Valério
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Dentro do select do cursor, na parte onde eu coloquei:

Selecionar tudo

cursor cur_teste is
    select 1 col from dual
    union all select 2 col from dual; 
Eram só dados fake, de exemplo.
Ali você substitui pelo seu select com sua tabela, retornando os dados que você precisa.
vasilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 24
Registrado em: Qua, 28 Out 2009 9:58 am
Localização: RS

Com base em sua ajuda, fiz o seguinte:

Selecionar tudo

declare
  cursor cur_teste is
    SELECT * FROM TB_EVENTS
    ORDER BY FD_UNIQUE_ID DESC;
--    union all select 2 col from dual;
  type t_teste is table of cur_teste%rowtype;
  v_teste t_teste;
begin
  open cur_teste;
  loop
    fetch cur_teste bulk collect
      into v_teste limit 10;
    for i in 1 .. v_teste.count
    loop
      dbms_output.put_line(cur_teste%rowcount || ': ' || v_teste(i).fd_unique_id);
    end loop;
    exit when cur_teste%notfound;
  end loop;
  close cur_teste;
end; 


Esta tabela têm milhões de registros, está travando o processo..
Eu ainda não compreendi como posso trazer o resultado "parcelado" de x em x linhas.


Se eu uso o SELECT(MAX), não traz todas, traz apenas a última, sendo que eu preciso do valor máximo para baixo. Mas se eu uso order BY, aí faz o SELECT FULL na tabela e demora muito.

Você têm alguma dica pra otimizar o meu processo ?

Muito obrigado.
Abraços.
VaSilva
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Umm está beleza, umas regras gerais que valem em 90% dos casos e dizem mais ou menos o seguinte:
1 - Se você pode fazer uma operação em um único comando (select, update, delete, merge, insert ou multi-table insert, etc...) faça, pois é preferível a escrever um programa em PL/SQL.
2 - Se SQL não tiver expressividade ou for tecnicamente inviável realizar o processamento todo num único comando, divida as operações em "lotes", seja por filtragem dos dados por domínio ou data em SQL ou através de bulk collect e forall statements num PL/SQL.
3 - Longas cadeias de transformações num dado podem ser feitas como numa "linha de montagem" através de pipelined table functions (útil em data warehousing mas de aplicação limitada no geral).

Agora de volta ao seu problema: você teria como esclarecer exatamente o que você quer com as 50 milhões de linhas dessa tabela? Alterar? Filtrar e inserir em outro lugar? Replicar integralmente? Analisar qualidade de dados, inconsistências e integridade?

Com mais detalhes deve dar para fugir da solução que não está funcionando e partir para algo que entregue o trabalho.
vasilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 24
Registrado em: Qua, 28 Out 2009 9:58 am
Localização: RS

Bom dia fsitja !

Então meu caro, o caso é o seguinte:

Nesta tabela, têm um processo que irá fazer um SELECT que irá retornar milhões de linhas, mas eu preciso que este SELECT me retorne o resultado a cada 50 mil linhas. Este resultado eu vou inserir em outra base que é igual, com o mesmo modelo de dados. É um processo de "EXPURGO", onde a base principal envia os dados antigos para uma base de backup.

Em resumo, a intenção é a seguinte:

1 - Faz o SELECT a trazendo o resultado a cada 50 mil linhas.
2 - Insere este resultado em outra base.
3 - Mata os dados da origem após inserir no outro banco.

Os passos 2 e 3 eu tenho prontos, através do programa que faz as operações, o meu problema maior é justamente "trazer" os dados da base de origem sem que demore muito. Hoje este processo leva muuuito tempo, muitas horas só pra fazer o SELECT que está pesado demais.

Muito obrigado.
Abraços.
Valério
vasilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 24
Registrado em: Qua, 28 Out 2009 9:58 am
Localização: RS

Meu caro,

Consegui algo bem perto daquilo que preciso..
O select está trazendo o resultado a partir do maior valor da coluna através do "ORDER BY".
Também consegui limitar o número de linhas através do "<= rownum". Consegui ordenar pelo número maior de linhas para baixo "ORDE BY rownum".

Até então está assim..

Selecionar tudo

declare
  cursor cur_teste is
    SELECT * FROM (SELECT * FROM TB_TABELA ORDER BY FD_COLUNA DESC)
    WHERE  rownum <= 5
    ORDER BY rownum;
--    union all select 2 col from dual;
  type t_teste is table of cur_teste%rowtype;
  v_teste t_teste;
begin
  open cur_teste;
  loop
    fetch cur_teste bulk collect
      into v_teste limit 5;
    for i in 1 .. v_teste.count
    select *
    into cur_teste
    from tb_events;
    loop
      dbms_output.put_line(cur_teste%rowcount  || ': ' || v_teste(i).FD_COLUNA);
     end loop;
    exit when cur_teste%notfound;
  end loop;
  close cur_teste;
end; 
Só me falta uma única coisa.
Conseguir encaixar este SELECT no loop para executar a cada limite de linhas, exemplo:

Tenho 15 linhas na tabela e a coluna FD_COLUNA que vai de 1 a 15..
Então, o meu select vai ordenar por esta coluna todos os registros..
Além disso, o rownum vai traze apenas 5 linhas da seguinte forma:

Selecionar tudo

FD_COLUNA
15
14
13
12
11
Até aqui perfeito..
Agora o meu problema é o seguinte..
Preciso que o "loop" faça automaticamente até início da tabela, ou seja, executaria 3 vezes o comando até não ter mais nada a recuperar.

Selecionar tudo

FD_COLUNA
15
14
13
12
11

FD_COLUNA
10
09
08
07
06

FD_COLUNA
05
04
03
02
01
Só me falta isto, fazer funcionar o loop a cada x linhas.

Valeu !
Abraço.
VaSilva
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Então Valério, não consegui responder seguindo o raciocínio que eu tinha começado no post anterior: Em vez de fazer o select primeiro e depois inserir, eu colocaria tudo num só comando, de uma só vez. Isso deve acelerar bastante o processo porque reduz o tráfego na rede desse volume imenso de dados e elimina os chaveamentos de contexto entre o PL/SQL engine e SQL, trabalhando tudo no próprio servidor.

Tenta da seguinte forma:
1 - Fazer um insert .. select, num comando só selecionando e inserindo tudo na tabela nova.
2 - Eliminar os dados na origem.

Para inserir, seria basicamente um comando sql seguido de um commit ao final:

Selecionar tudo

  insert /*+ APPEND */ into tabela_nova &#40;col_nova1, col_nova2, col_nova3&#41;
select col1, col2, col3 from tabela_velha
Isso dessa forma não deveria, a princípio, demorar horas. Mesmo com uma tabela de 50 milhões de registros, a carga não é tão grande quanto parece. É claro que depende de quantas colunas e quais tipos de colunas ela tem.

Se mesmo assim der problema de simplesmente não rodar, eu faria a carga em "fatias" nos campos de data, por exemplo: no select, coloque por exemplo um:

Selecionar tudo

where data_registro between to_date('01/01/2005', 'DD/MM/YYYY') and to_date('31/12/2005', 'DD/MM/YYYY')
E vá commitando ano por ano.
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Eu tava escrevendo minha resposta ao mesmo tempo que você, só vi sua depois que postei.

Entendi o que você está tentando, mas estou bem confiante que a melhor solução para o que você quer seria evitar PL/SQL e especialmente loops com inserts ao máximo e jogar a maior parte do esforço num insert into xxx select ..., onde não tenho dúvida que o desempenho será melhor.
O hint /*+ APPEND */ que coloquei no exemplo agiliza a carga também.
É simples e rápido, eu iria por esse caminho primeiro, como postei acima.
vasilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 24
Registrado em: Qua, 28 Out 2009 9:58 am
Localização: RS

Certo..

Pra que serve o "/*+ APPEND */" ???

Muito obrigado !
VaSilva
vasilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 24
Registrado em: Qua, 28 Out 2009 9:58 am
Localização: RS

Ah, esqueci de um outro detalhe..
Eu preciso validar se na base de backup já não existe o registro que vou inserir lá. Esta base de backup só vai crescendo, então eu vou executando este processo a cada período. Tipo, uma vez por mês eu rodo o processo que "enxuta" a base original e move para a de backup. Mas eu tenho que validar que não estou tentando inserir um registo que já existe, pois aí pode violar uma constraint em determinada coluna que aceita somente valores únicos. O valor desta coluna é gerado através de uma sequence.
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

O append é um hint que diz ao Oracle para inserir as linhas no final da tabela sequencialmente.
Outra coisa, se a tabela destino tem índices ou check constraints, pode acelerar bastante se você desabilitá-los durante a carga e habilitar após finalizá-la.
Quanto à validação que você mencionou, dá para fazer juntamente com a inserção através do comando MERGE, como no exemplo abaixo:

Selecionar tudo

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*.01)
     WHERE (S.salary <= 8000);
Exemplo retirado da documentação da Oracle
http://download.oracle.com/docs/cd/E118 ... SQLRF01606

O que ele faz é inserir na tabela BONUSES (alias D) quando não existir o registro (WHEN NOT MATCHED ..), e se existir já (WHEN MATCHED ..) ele dá um update. É claro, se você não quiser fazer o update, mas sim não fazer nada, apenas remova a parte do when matched, ou modifique de acordo com seu requisito.

Francisco.
Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Imagino que no seu caso possa ser algo mais ou menos assim:

Selecionar tudo

MERGE INTO tab_destino t
   USING (SELECT * FROM TB_TABELA ) o
   ON (t.id = o.id)
   WHEN NOT MATCHED THEN INSERT (t.id, t.col_nova1, t.col_nova2, t.col_nova3)
     VALUES (o.id, o.col1, o.col2, o.col3);
vasilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 24
Registrado em: Qua, 28 Out 2009 9:58 am
Localização: RS
VaSilva

Bom dia Francisco !

Muito obrigado pela ajuda !!!
Abraços e uma boa semana.

Valério
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 7 visitantes