Cursor is better than SELECT INTO?

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Tue, 30 Nov 2004 9:51 am

Personal ... Throw a doubt my: I'vê been working lately on some Packages of almost no is used select statements INTO ...
For anything, the guys opened a cursor, even when the cursor should return only one row.

Do you have any special reason to use cursors instead of SELECT direct?
tfg
Location: Novo Hamburgo - RS

Poston Wed, 01 Dec 2004 8:56 am

I believe that's the reason when there is no record in the table that you're doing select give DATA error FOUND.
guerton
Location: Sapiranga - Rs

Poston Wed, 19 Jan 2005 10:47 am

I think its because with the cursor you have object properties that can be treated with conditions, type:% ROWCOUNT CURSOR-CURSOR-% FOUND CURSOR% ISOPEN CURSOR% NOTFOUND dae for example return values and you can compare and tal, an SQL statement as the colleague said, if she does not return anything it returns an EXCEPTION.

[] 's
MuLtAnI
Location: Videira - SC


Poston Wed, 19 Jan 2005 1:33 pm

Building on the topic: what is faster?

for example a select into we assume that for this select into to create 10 variables to the clause, wouldn't it be faster to create a record that contains the definitions of the fields and allocate in the into clause.

I wonder qualquer you guys tell me about it.

[] 's
MuLtAnI
Location: Videira - SC


Poston Wed, 19 Jan 2005 1:42 pm

When you have many fields, I like to open a cursor precisely for that-so you don't need to declare all variables.

This is an example of what I'm talking about: [url = ://glufke http . net/oracle/viewtopic php? t = 51 . & highlight = cursor] save a registry variable [/url] [] ´ s
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 19 Jan 2005 1:50 pm

I personally think it's best to work with a variable of type Record doq N variables for a cursor. I think it looks more efficient access by reference, and then if you need to pass the values as a parameter to a method, it passes only a variable instead of N.

the code in addition to faster gets more stylish [] 's
MuLtAnI
Location: Videira - SC


Poston Wed, 12 Apr 2006 12:26 pm

Taking advantage of the discussion about cursors ...:? I have a problem in one of the systems in atrocious I do maintenance.
I have a cursor that is locking at the time of execution of the FETCH.
This cursor returns two fields and some fifty records.

The most troubling thing is that when I run the same select statement that generates the cursor outside of the PL, the same returns all rows in less than 2 seconds.
I evaluated the indexes and everything is OK, only in debug PL using the PL/SQL Developer, the FETCH line generates more than 1 billion and 800 million \"Logical Reads\", IE crashes everything.

Some of the members of the Forum have any idea what could be happening? Because neither the company DBA knows what it is.

Thank you all ...
Kaizzaer
Location: Rio de Janeiro - RJ

Poston Wed, 12 Apr 2006 4:58 pm

Kaizzaer, it would be interesting if you could publish your routine so that we can better evaluate and try to help him.
Marlon Pasquali
Location: Erechim - RS

Poston Wed, 12 Apr 2006 5:16 pm

This is my procedure where I am with the problem reported above, it occurs the cursor C0 ...

Code: Select all
CREATE OR REPLACE PROCEDURE CUSTEIO_PRODUCAO_F1(P_ANO_REF TOR501.ANO_REFERENCIA%TYPE,
                       P_VERSAO TOR501.NR_VERSAO%TYPE,
                            P_ANO_MES_BASE TOR000.ANO_MES_BASE%TYPE,
                      P_RESPOSTA IN OUT VARCHAR2,
                     P_QTD_ANOS_ORC IN TOR000.QTD_ANOS_ORC%TYPE)
AS

--p_resposta      varchar2(200);

TYPE T_PERCENT_RATEIO   IS TABLE OF TOR053.VL_PERCENTUAL_RATEIO%TYPE   INDEX BY BINARY_INTEGER;
TYPE T_ANO_MES      IS TABLE OF CHAR(6)            INDEX BY BINARY_INTEGER;

V_DIMENSAO      CHAR(6);
V_DESTINO      TOR053.CD_PROCESSO_DESTINO%TYPE;
V_ANOMES      CHAR(6);
V_PERC_RATEIO      TOR053.VL_PERCENTUAL_RATEIO%TYPE;
V_MESES         NUMBER;
V_ULTIMO_MES      DATE;

V_ANO_MES_C2      CHAR(6);
V_CONTA         CHAR(6);
V_VL_ORCADO      NUMBER;

V_CD_MOEDA      NUMBER;

V_VL_RATEADO      NUMBER;

IX         NUMBER;
IZ         NUMBER;

I_PERCENT_RATEIO   T_PERCENT_RATEIO;
I_ANO_MES      T_ANO_MES;

-- SELECIONA CENTRO DE CUSTOS DE MANUTENCAO DISTRIBUIDA (MD) /ORIGEM E DESTINO/
CURSOR C0 IS
SELECT DISTINCT A.CD_DIMENSAO_CR, B.CD_PROCESSO_DESTINO
FROM TOR501 A, TOR053 B, TOR025 C
WHERE A.ANO_REFERENCIA = B.ANO_REFERENCIA AND
A.ANO_REFERENCIA = P_ANO_REF AND
A.NR_VERSAO = P_VERSAO AND
B.ANO_REFERENCIA = A.ANO_REFERENCIA AND
B.NR_VERSAO = A.NR_VERSAO AND
B.CD_DIMENSAO_ORIGEM = A.CD_DIMENSAO_CR AND
C.ANO_REFERENCIA = A.ANO_REFERENCIA AND
C.NR_VERSAO = A.NR_VERSAO AND
C.CD_DIMENSAO = A.CD_DIMENSAO_CR AND
C.TP_DIMENSAO = 'MD'
ORDER BY A.CD_DIMENSAO_CR;

-- OBTEM PERCENTUAIS E CR (DIMENSAO) A DISTRIBUIR
CURSOR C1 IS
SELECT TO_CHAR(P_ANO_MES_BASE,'YYYYMM'), VL_PERCENTUAL_RATEIO
FROM TOR053
WHERE ANO_REFERENCIA = P_ANO_REF AND
CD_DIMENSAO_ORIGEM = V_DIMENSAO AND
CD_PROCESSO_DESTINO = V_DESTINO
ORDER BY 1;

-- CALCULA TOTAL DE GASTOS CR MANUTENCAO DISTRIBUIDA
CURSOR C2 IS
SELECT TO_CHAR(ANO_MES_ORCADO,'YYYYMM'), CD_CONTA, CD_MOEDA, SUM(VL_CORTE)
FROM TOR501
WHERE ANO_REFERENCIA = P_ANO_REF AND
NR_VERSAO = P_VERSAO AND
CD_DIMENSAO_CR = V_DIMENSAO AND
ANO_MES_ORCADO > P_ANO_MES_BASE
GROUP BY ANO_MES_ORCADO, TOR501.CD_CONTA, CD_MOEDA
ORDER BY 1;

BEGIN
--select to_char(sysdate,'hh:mi:ss') into v_hora from dual;
--dbms_output.put_line(v_hora);

   P_RESPOSTA := ' ';

   SELECT COUNT(*)
   INTO IX
   FROM TOR501
   WHERE ANO_REFERENCIA = P_ANO_REF AND
   NR_VERSAO = P_VERSAO AND
   CT_UTILIZADO NOT IN('0','9');

   IF IX > 0 THEN
      P_RESPOSTA := 'ATENÇÃO!! CUSTEIO DA PRODUÇÃO JÁ CALCULADO';
      GOTO ERRO;
   END IF;
 
  SELECT COUNT(DISTINCT T.CD_PRODUTO)
  INTO IX
  FROM TOR302 T
  WHERE T.ANO_REFERENCIA = P_ANO_REF
  AND   T.NR_VERSAO = P_VERSAO
  AND   T.ANO_MES_ORCADO > P_ANO_MES_BASE
  AND   T.QT_PRODUCAO > 0
  AND   NOT EXISTS (SELECT S.NR_SEQUENCIA
                    FROM TOR122 S
                    WHERE S.ANO_REFERENCIA = T.ANO_REFERENCIA
                    AND   S.NR_VERSAO      = T.NR_VERSAO
                    AND   S.CD_PRODUTO     = T.CD_PRODUTO);

   IF IX > 0 THEN
      P_RESPOSTA := 'ATENÇÃO!! EXISTEM PRODUTOS FORA DA SEQUENCIA DE CUSTEIO';
      GOTO ERRO;
   END IF;
 
   DELETE FROM TOR504 WHERE ANO_REFERENCIA= P_ANO_REF AND NR_VERSAO = P_VERSAO;
   COMMIT;

   -- CALCULA QUANTIDADE DE MESES
   SELECT TO_DATE(TO_CHAR(P_ANO_REF + P_QTD_ANOS_ORC,'0000')||'12','YYYYMM'),
   MONTHS_BETWEEN (TO_DATE(TO_CHAR(P_ANO_REF + P_QTD_ANOS_ORC,'0000')||'12','YYYYMM'), P_ANO_MES_BASE) + 1
   INTO V_ULTIMO_MES, V_MESES
   FROM DUAL;
   
   --DBMS_OUTPUT.PUT_LINE ('1 - QT_MESES '|| v_meses);   

   -- MONTA ARRAY DE MESES PARA PESQ. DE INDICES
   I_ANO_MES(1) := TO_CHAR(P_ANO_MES_BASE, 'YYYYMM');
   FOR IX IN 2..V_MESES LOOP
      I_ANO_MES(IX) := TO_CHAR(ADD_MONTHS(P_ANO_MES_BASE, IX-1),'YYYYMM');   
      --DBMS_OUTPUT.PUT_LINE ('1.1 - i_ano_mes '|| i_ano_mes(ix));      
   END LOOP;   

   --INICIA ARRAY I_PERCENT_RATEIO COM ZEROS
   FOR IX IN 1..V_MESES LOOP
      I_PERCENT_RATEIO(IX) := 0;
   END LOOP;

   OPEN C0;
   FETCH C0 INTO V_DIMENSAO, V_DESTINO;[/color][/b] [b][color=green]AQUI OCORRE O PROBLEMA[/color][/b]

   [b][color=red]WHILE C0%FOUND LOOP
      
      --DBMS_OUTPUT.PUT_LINE ('2 - ORIGEM '|| V_DIMENSAO || ' DESTINO ' || V_DESTINO);   

      -- MONTA I_PERCENT_RATEIO
      OPEN C1;
      FETCH C1 INTO V_ANOMES, V_PERC_RATEIO;
      IX := 1;
      WHILE C1%FOUND LOOP
      
         IF V_ANOMES = I_ANO_MES(1) THEN
            I_PERCENT_RATEIO(IX) := V_PERC_RATEIO;            
         ELSE
            
            IF IX = 1 THEN
            
               --DBMS_OUTPUT.PUT_LINE ('3 - erro '|| ix || ' ' || V_ANOMES || ' ' || I_ANO_MES(2) );   
               GOTO ERRO;

            ELSE

               FOR IZ IN 1..V_MESES LOOP   
               
                  IF V_ANOMES = I_ANO_MES(IZ) THEN
                     IX := IZ;
                  END IF;
               
               END LOOP;

               I_PERCENT_RATEIO(IX) := V_PERC_RATEIO;
            
            END IF;
                                    
         END IF;

         --DBMS_OUTPUT.PUT_LINE ('4 - rateio '|| I_PERCENT_RATEIO(ix));   
      
         FETCH C1 INTO V_ANOMES, V_PERC_RATEIO;
      
         --DBMS_OUTPUT.PUT_LINE ('5 - rateio '|| v_PERC_RATEIO);   

         IX := IX + 1;
      END LOOP;
      CLOSE C1;
      
      -- PREENCHE ARRAY DE RATEIO OS MESES EM BRANCO      
      FOR IX IN 2..V_MESES LOOP

         --DBMS_OUTPUT.PUT_LINE ('5 - ZERO '|| ix);   

         IF I_PERCENT_RATEIO(IX) = 0 THEN
            I_PERCENT_RATEIO(IX) := I_PERCENT_RATEIO(IX - 1);
         END IF;
      
      END LOOP;

      --DBMS_OUTPUT.PUT_LINE ('5 - ZERO FIM');

      -- MONTA I_VALOR_ORCADO      
      OPEN C2;
      FETCH C2 INTO V_ANO_MES_C2, V_CONTA, V_CD_MOEDA, V_VL_ORCADO;
      
      WHILE C2%FOUND LOOP      

         FOR IX IN 1..V_MESES LOOP
         
            IF V_ANO_MES_C2 = I_ANO_MES(IX) THEN
               IZ := IX;
               --DBMS_OUTPUT.PUT_LINE ('6 - PESQ MESES '|| IZ || ' ' || V_ANO_MES_C2 || ' ' || I_ANO_MES(IX));   
            END IF;
            
            --DBMS_OUTPUT.PUT_LINE ('6 - PESQ MESES '|| IZ || ' ' || V_ANO_MES_C2 || ' ' || I_ANO_MES(IX));   
            
         END LOOP;

         -- INSERE NA TABELA TOR504
               
         V_VL_RATEADO := V_VL_ORCADO * I_PERCENT_RATEIO(IZ);

         --DBMS_OUTPUT.PUT_LINE ('7 - RATEIO '|| V_VL_RATEADO || ' ' || V_VL_ORCADO || ' ' || I_PERCENT_RATEIO(IZ));

         INSERT INTO TOR504 VALUES (P_ANO_REF, P_VERSAO, TO_DATE(V_ANO_MES_C2,'YYYYMM'), V_DESTINO, V_DIMENSAO, V_CD_MOEDA,
                     V_CONTA, 'M', V_VL_RATEADO);   

         --DBMS_OUTPUT.PUT_LINE ('8 - INSERT NA TOR504 '|| ix);      
         FETCH C2 INTO V_ANO_MES_C2, V_CONTA, V_CD_MOEDA, V_VL_ORCADO;
      
      END LOOP;            
      
      CLOSE C2;
      COMMIT;

      UPDATE TOR501
      SET CT_UTILIZADO = '1'
      WHERE ANO_REFERENCIA = P_ANO_REF AND
      NR_VERSAO = P_VERSAO AND
      CD_DIMENSAO_CR = V_DIMENSAO;

      -- INICIA ARRAY I_PERCENT_RATEIO COM ZEROS
      FOR IX IN 1..V_MESES LOOP
         I_PERCENT_RATEIO(IX) := 0;
      END LOOP;

      FETCH C0 INTO V_DIMENSAO, V_DESTINO;
   END LOOP;

   CLOSE C0;

   COMMIT;
   GOTO FIM;
<<ERRO>>
   ROLLBACK;
<<FIM>>
--CORRIGE_VALORES_TOR504(P_ANO_REF, P_VERSAO);
NULL;
EXCEPTION
WHEN OTHERS THEN
P_RESPOSTA := SQLERRM;
END CUSTEIO_PRODUCAO_F1;
thanks to everyone ...
Kaizzaer
Location: Rio de Janeiro - RJ

Poston Wed, 12 Apr 2006 5:42 pm

try using the structure For Loop instead of Fetch. I know it sounds silly, but I find it more easy to track the cursor data. Place name in the columns of your select and use the example below to see what the routine is bringing the cursor.

Code: Select all
FOR r_CO in CO LOOP
   V_DIMENSAO := r_CO.CD_DIMENSAO;
   V_DESTINO    := r_CO.CD_DESTINO;
   DBMS_OUTPUT.PUT_LINE ('2 - ORIGEM '|| V_DIMENSAO || ' DESTINO ' || V_DESTINO);   
END LOOP;
Marlon Pasquali
Location: Erechim - RS

Poston Thu, 13 Apr 2006 10:57 am

Hi Marlon, tested the tip that you spent, but still with the problem, the crash is still occurring at the time the procedure attempts to read the cursor. : cry: I will try to create the environment in another Oracle instance and check if the problem continues.

Anyway thanks for the tip.

Abs ...
Kaizzaer
Location: Rio de Janeiro - RJ

Poston Thu, 01 Jun 2006 3:33 pm

Explicit Cursors personnel (previously reported) will always be more performant than the implicit (select into) why once expected (declared), the compiler can already predict and create the temporary areas to hold the data that the queries will return, size the effort and reserve the memory required.

Never use \"SELECT into\", prefer using explicit cursors
CharlieHorse
Location: PORTO ALEGRE - RS

Analista/Desenvolvedor Oracle há 12 anos.

Poston Thu, 10 Jul 2008 5:32 pm

[quote = CharlieHorse " "]Explicit cursors (that has previously been declared) will always be more performant than the implicit [/quote] I found this week in a ORACLE manual which explains this phenomenon of EXPLICIT cursors are faster than a direct select. (of course, that if the user wants to return only 1 line)

All SELECT statements should use an explicit cursor. Implicit SELECT statements actually cause re-fetches that 2 to run: one to get the date, and one to check for the TOO_MANY_ROWS exception. You can avoid this by FETCHing just a single record from an explicit cursor.


That is, it makes two searches! A to get the information and another to test the TOO_MANY_ROWS-if another line.

Source: http://download.oracle.com/docs/cd/A911 ... 15devg.pdf
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 28 Oct 2010 2:28 pm

Hello guys, did anyone ever test in practice if there are any performance difference between the 2 cases (SELECT-INTO and CURSOR)?

Did some testing in versions 9i, 10 g and 11 g and in both cases I got better response time with SELECT-INTO.

I decided to make this test after I read in a presentation of good practices of company that provides consulting in the area.

In tip asks if it's done the opposite of what's being said here: \"Prefer implicit cursor\".
nunes
Location: Parobe - RS

Andre Nunes

Poston Thu, 28 Oct 2010 4:06 pm

TOM KYTE also talked about using IMPLICIT cursor whenever you can in his visit to Brazil on TECH DAY 2010 GUOB. (this for cursors, for he still prefers to be done a SELECT INTO direct).

He spoke a lot in ease of use (you don't need to be declared, or remembering to close, or testing if it was NOT FOUND, etc).

In summary: we have a manual about how explicit is better because it does fetch 2, and we have TOM KYTE saying it's good to do SELECT INTO ...

TOPIC STILL UNSOLVED:-(
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 28 Oct 2010 4:15 pm

Yeah I'm always asking myself, I'vê read two or three manuals/books that Cursor would be the best option, but already the master Tom have commented sometimes contrátio. By reusability issues and better control of exceptions I constumo use the Cursor. Until proven that he has underperformed not reason to change ...

But I always had the doubt in the air
gfkauer
Location: Sapiranga - RS

Quem falou que programar era fácil??

Quanto mais dificil for a implementação mais valorizado seu trabalho será!

Acessem: www.gfkauer.com.br

Poston Fri, 29 Oct 2010 1:18 pm

Staff, to increase a little doubt, create a test scenario to simulate, might have been biased. But I believe it is valid.

Under the code, if you want to test.

I created a table:
Code: Select all
create table Empregado
( IDEmpregado    INTEGER      NOT NULL,
  Nome           VARCHAR2(50) NOT NULL,
  DataNascimento DATE         NOT NULL,
  CodigoTeste    INTEGER      NOT NULL,
  NomeMae        VARCHAR2(50),
  NomePai        VARCHAR2(50) );
then ran a PL/SQL block to load it with random values and also some exclusions, to force the NO_DATA_FOUND error with Select-Into.
Code: Select all
begin
  for i in 1..70000 loop
     Insert into Empregado
         (IDEmpregado, Nome, DataNascimento, CodigoTeste, NomeMae, NomePai)
     Values
         (i,
         dbms_random.string('X', TRUNC(DBMS_RANDOM.value(25,45)) ),
         add_months(sysdate,-200) + DBMS_RANDOM.value(-50,50),
         dbms_random.value(1,70000),
         dbms_random.string('X', TRUNC(DBMS_RANDOM.value(25,45)) ),
         dbms_random.string('X', TRUNC(DBMS_RANDOM.value(25,45)) )
         );
  end loop;
  Commit;

  -- Cria chave primária para a tabela!
  Execute immediate 'alter table Empregado add constraint PK_Empregado primary key (IDEmpregado)';

  -- FORÇAR A OCORRENCIA DE ERROS (NO_DATA_FOUND) --
  for i in 1..8000 loop
     Delete Empregado Where IDEmpregado = TRUNC( DBMS_RANDOM.VALUE(1,70000) );
  end loop;
  Commit;
end;
and then I created 2 procedures, one that uses the SELECT-INTO and other SELECT-CURSOR.

I used an example where there won't be the occurrence of TOO_MANY_ROWS, since I will be using the PK to identify the record, believe that if the query can return more than 1 record shouldn't really be used SELECT-INTO, as a matter of logic.

First steps:
Code: Select all
-- PROCEDIMENTO QUE FARÁ O SELECT INTO PARA TODOS OS REGISTROS DA TABELA EMPREGADO
create procedure Prc_Select_Into is
  vTimeStart number;
  vTimeEnd   number;
  v_nome empregado.nome%type;
begin
  vTimeStart := dbms_utility.get_time;
 
  for i in 1..70000 loop
     begin
        select nome
        into   v_nome
        from   Empregado
        Where  IDEmpregado = i;
     exception
        when no_data_found then null;
     end;
  end loop;
 
  vTimeEnd := dbms_utility.get_time;
  dbms_output.put_line('Tempo processamento: '|| to_char(vTimeEnd-vTimeStart) );
end;
second procedure:
Code: Select all
-- PROCEDIMENTO QUE UTILIZARÁ O CURSOR PARA TODOS OS REGISTROS DA TABELA EMPREGADO
create procedure Prc_Select_Cursor is
  vTimeStart number;
  vTimeEnd   number;
  v_nome     Empregado.nome%type;
  cursor c_nome (pIDEmpregado in integer) is
     Select nome
     from   Empregado
     Where  IDEmpregado = pIDEmpregado;
begin
  vTimeStart := dbms_utility.get_time;
 
  for i in 1..70000 loop
     for w in c_nome(i) loop
       v_nome := w.nome;
     end loop;
  end loop;
 
  vTimeEnd := dbms_utility.get_time;
  dbms_output.put_line('Tempo processamento: '||to_char(vTimeEnd-vTimeStart));
end;
After it ran several times both:
Code: Select all
Set serveroutput on size 10000
Exec Prc_Select_Into
Exec Prc_Select_Cursor
and got the following times (considered the cache to evaluate, ignored the first run):
Code: Select all
Procedimento com SELECT-INTO..: 170
Procedimento com SELECT-CURSOR: 235
Performed this test on Oracle Database 11 g (11.2.0.1.0).
I did the same test on a Oracle 10 g XE and the advantage was the SELECT-INTO.

If anyone has any consideration as to the method used to evaluate.
nunes
Location: Parobe - RS

Andre Nunes


  • See also
    Replies
    Views
    Last Post


      Return to PL/SQL

      Who is online

      Users browsing this forum: Bing [Bot] and 6 guests