cursoe e BULK COLLECT melhor performace??

[marcelo0906]

Bom dia pessoal! sou iniciante em oracle, e me surgiu uma dúvida!

tenho um cursor e faço um fetch normal nele abrindo e fechando tal...

se eu usar uma bulk nesse cursor o desempnho será melhor???

se alguém tiver alguma maneira melhor, em questão de performace, para usar cursor me ajude por favor! hehehe
abraços

[fsitja]

Está correto, bulk collect + forall é mais rápido que row-by-row no cursor, por que há menos chaveamento de contexto entre os engines de SQL e de PL/SQL.
Entretanto, fazer em SQL puro num único comando é, como regra geral, a melhor alternativa em desempenho. PL/SQL só quando não for possível em SQL. E é possível muita coisa em SQL, principalmente com as extensões que a Oracle oferece.

[marcelo0906]

Hmn!!

vou incomodar mais um pouquinho, hehehe.
entenda o que está sendo feito e me diga no que estou errando eheheh:

assim é só um exemplo, não é o codigo verdadeiro, mas qual maneira de melhorar a performace no que está sendo feito, não conheço muito ai fico meio na dúvida de como fazer!!


tenho os cursores

Selecionar tudo

--Declarando o Cursor (apenas exemplo)
CURSOR c_Cur1(v_Ini DATE, v_Fim DATE) IS
          SELECT campo1, campo2, campo3, campo4, campo5
           FROM  T_TABELA
           WHERE campoTab > v_Ini AND campoTab <= v_Fim)  ;


CURSOR c_Curs2(v_Cod NUMBER) IS
		SELECT a.campo1 ,a.campo2 ,b.campo1 ,b.campo2 
		FROM  tabela1 a, tabela2 b
		WHERE a.campo1 = v_cod AND b.campo1 = v_cod ;

Selecionar tudo

--corpo do código
FOR r_Test1 IN c_Cur1(v_IniProcess, v_FimProcess) LOOP

   		
   			OPEN c_Curs2(r_Test1.campo1);
			FETCH c_Curs2 INTO v_var1, v_var2, var3, var4;
			CLOSE c_Curs2;


--Aqui faz um insert dos dados obtidos no c_curs2



	END LOOP;

[fsitja]

Eu tenho a impressão que daria para resolver tudo num único comando de insert.
Tipo:

Selecionar tudo

insert into tabela_destino (col1, col2, col3, etc)
select col1, col2, col3, etc -- conforme a lista acima
  from t_tabela t
  join tabela1 a on a.campo1 = t.campo1
  join tabela2 b on b.campo1 = t.campo1
 where t.campoTab > :v_Ini AND t.campoTab <= :v_Fim

Depende da cardinalidade do relacionamento entre t_tabela, tabela1 e tabela2. Se houver mais de um registro em t_tabela para cada em tabela1 e tabela2, pode precisar de um group by numa subquery do from, ou um exists no where, para evitar linhas duplicadas.

Caso eu tenha palpitado errado no modelo de dados, com a estrutura das tabelas (colunas, pk, fk) mais dados de exemplo dá para ter uma noção melhor.

[marcelo0906]

mas assim, e a questao do loop ?

tipo pra cada registro encontrado na tabela do cursor 1 ele teria que abrir o cursor 2 buscar alguns dados e dpois fazer o insert...

outra coisa no seu código você coloca :v_ini por exemplo o " : " antes da variavel melhora o desempenho??

[fsitja]

O que eu quis dizer é que não precisaria de loop. Com um select você produz o mesmo resultado através de joins (operações sobre conjuntos). A menos que você tenha alguma outra operação feita no loop, além de inserir. Nesse caso teria que reavaliar.

O :v_ini indica que é criado uma "bind variable" (variável de ligação). Basicamente é um parâmetro, que diz para o Oracle que aquele SQL pode ser reaproveitado apenas indicando o parâmetro novo.
Se você implementar numa procedure, você pode substituir por uma variável PL/SQL qualquer, se chegar à conclusão que a procedure não vai ser executada centenas de vezes e não haverá reaproveitamento do SQL.

[dr_gori]

Só pra complementar: o "motivo" dos bulks serem rápidos é que eles ficam na memória. Então, você até consegue acelerar um cursor, mas com um preço alto! (vai gastar memória). E as vezes não se tem muito sobrando.

Por isso que deve sempre ser bem avaliado o uso dos bulks, pois nem sempre é a solução. Como foi falado pelo fsitja, o ideal é tentar fazer num comando SQL. Se não dá, dai sim vai pro PL.

[fsitja]

Citando o mestre Tom Kyte, usar um collection de 100 registros em memória é razoável, 1000 já é extremo.

http://asktom.oracle.com/pls/asktom/f?p ... 8938803188

and we said...

good gosh -- you aren't serious are you???


Use the LIMIT clause, bulk collect say 100 to 1000 rows -- process them, bulk insert
them, get the next 100/1000 rows.


You blew process memory -- not SGA. Your process got bigger then your OS would allow you
(you hit an OS limit, might be ulimit related or whatever).

do something like this:

Selecionar tudo

   open cursor;
   loop
       fetch c bulk collect into l_c1, l_c2, ....... LIMIT 1000;
       for i in 1 .. l_c1.count
       loop
            process....
       end loop;
       forall i in 1 .. l_c1.count
            insert into ..... values ( L_c1(i), .... );
       end loop;
       exit when c%notfound;
   end loop;

   close cursor

1000 would be extreme, 100 is reasonable.

[bernoulthy]

Ola pessoal estou com uma dúvida referente a Bulk collect com for all
segue um pequeno exemplo:

Selecionar tudo

CREATE OR replace PROCEDURE Test
IS
  CURSOR c1 IS
    SELECT *
    FROM   cliente;
  TYPE c_collect
    IS TABLE OF cliente2%ROWTYPE;
  colecao C_COLLECT;
BEGIN
    OPEN c1;

    LOOP
        FETCH c1 bulk collect INTO colecao;

        exit WHEN colecao.count = 0;

        forall i IN 1..colecao.count
          INSERT INTO cliente2
          VALUES Colecao(i);
    END LOOP;
END; 

dessa maneira funciona perfeitamente, o problema é quando tento especificar apenas alguns campos no select do cursor ao invés de fazer um select com *, por exemplo:

Selecionar tudo

create or replace procedure test is 
CURSOR c1 IS
  SELECT id,
         nome,
         sobrenome
  FROM   cliente;type c_collect
IS
  TABLE OF cliente2%ROWTYPE;colecao c_collect;BEGIN
  OPEN c1;
  LOOP
    FETCH c1 bulk collect
    INTO  colecao;
    
    EXIT
  WHEN colecao.count = 0;
    forall i IN 1..colecao.count
    INSERT INTO cliente2 VALUES Colecao
                (
                            i
                );
  
  END LOOP;
END;

aparece um erro dizendo que número incorreto de valores na lista até ai eu entendi, porque a minha coleção está pegando a estrutura de uma tabela.
ai então eu faço a coleção pegar a estrutura do cursor:

Selecionar tudo

cursor c1 is select id, nome, sobrenome from cliente; 
type c_collect is table of c1%rowtype; 

ai o erro cai para a linha do insert, dizendo que não valores suficientes

Selecionar tudo

forall i in 1..colecao.count 
insert into cliente2 values colecao(i);

erro nessa linha

pois minha intenção é utilizar o forall para fazer o insert de uma massa de dados, porem não será em todos os campos da tabela

será se alguém pode me ajudar;

[fsitja]

Olá,

Provavelmente porque ele está esperando que cheguem todas as colunas tabela CLIENTE2. Tente especificar as colunas assim:

Selecionar tudo

insert into cliente2 (id, nome, sobrenome) values colecao(i);

[bernoulthy]

Consegui resolver, na verdade é necessário criar os types relativos as colunas, ex se for selecionar 3 colunas, e necessário criar 3 types e tb informa-los no insert

Selecionar tudo

insert into tabela (col1, col2, col3) values(type1(), type2(), type4())