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
Post Reply
User avatar
tfg
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 83
Joined: Mon, 03 May 2004 3:38 pm
Location: Novo Hamburgo - RS

Personal ... I launch a doubt of mine:

I have worked lately in some packages where it almost is not used SELECTS INTO ...
for anything, the guys opened a cursor, Even when the cursor should return only one line!

Do you have any special reason to use cursors instead of direct select?
guerton
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 11
Joined: Tue, 26 Oct 2004 12:44 pm
Location: Sapiranga - Rs

I believe it is for the reason when there is no record in the table that you are making Select give error from the Found data.
User avatar
MuLtAnI
Moderador
Moderador
Posts: 90
Joined: Tue, 01 Jun 2004 9:21 am
Location: Videira - SC
Contact:

I think it's because with the cursor you have the properties of the object that can be treated with conditions, type:

- cursor% rowcount
- cursor% found
- Cursor% isopen
- Cursor %% Notfound

Dae These for example return values ??and you can compare and tal, a SQL statement as the colleague spoke if it does not return anything She returns an Exception.

[] 's
User avatar
MuLtAnI
Moderador
Moderador
Posts: 90
Joined: Tue, 01 Jun 2004 9:21 am
Location: Videira - SC
Contact:

Taking advantage of the topic:

What is faster?

For example a SELECT INTO

We assume that for this SELECT INTO I have to create 10 variables for the clause, it would be no faster to create a record type that Contain the field definitions and atibuir in the Into Clause.

I would like to know what you tell me about it.

[] 's
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

When there are many fields, I like to open a cursor just for this - not to need to declare all variables.
This is an example of what I am talking about: Guardar Egyistro in a variable


]s
User avatar
MuLtAnI
Moderador
Moderador
Posts: 90
Joined: Tue, 01 Jun 2004 9:21 am
Location: Videira - SC
Contact:

I particularly think it's best to work with a Record DQ N variable variable for a cursor. I think it gets more efficient access by reference and then if you need to pass the values ??as a parameter for some method, it passes only a variable instead of N.

The code beyond the faster ends more elegant

[] 's
Kaizzaer
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Thu, 17 Jun 2004 12:15 pm
Location: Rio de Janeiro - RJ

Taking advantage of the discussion about cursors ...

:? I have an atrocious problem in one of the systems in which I do maintenance.
I have a cursor that is locking at the time of FETCH.
This cursor returns two fields and about fifty records.

The most disturbing is that when I run the same SELECT that generates the cursor on the outside of the PL,
it returns all rows in less than 2 seconds.
I have already evaluated the indexes and everything is ok, but when the debugging to PL using the PL / SQL Developer,
the Fetch line generates more than 1 billion and 800 million "Logical Reads" That is, it catches everything.

Some of the colleagues of the forum have any idea what might be happening? Because neither the company's DBA knows what it is.

Thanks to all ...
Marlon Pasquali
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 28
Joined: Mon, 20 Mar 2006 3:40 pm
Location: Erechim - RS

Kaizzaer, it would be interesting if you could publish your routine so we can evaluate better and try to help you.
Kaizzaer
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Thu, 17 Jun 2004 12:15 pm
Location: Rio de Janeiro - RJ

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

Select all

CREATE OR REPLACE PROCEDURE CUSTEIO_PRODUCAO_F1(P_ANO_REF TOR501.ANO_REFERENCIA%TYPE, 
					        P_VERSAO TOR501.NR_VERSAO%TYPE, 
                            P_ANO_mês_BASE TOR000.ANO_mês_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_mês		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_mês		DATE; 
 
V_ANO_mês_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_mês		T_ANO_mês; 
 
-- 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_mês_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_mês_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_mês_ORCADO > P_ANO_mês_BASE 
GROUP BY ANO_mês_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_mês_ORCADO > P_ANO_mês_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_mês_BASE) + 1 
	INTO V_ULTIMO_mês, V_MESES 
	FROM DUAL; 
	 
	--DBMS_OUTPUT.PUT_LINE ('1 - QT_MESES '|| v_meses);	 
 
	-- MONTA ARRAY DE MESES PARA PESQ. DE INDICES 
	I_ANO_mês(1) := TO_CHAR(P_ANO_mês_BASE, 'YYYYMM'); 
	FOR IX IN 2..V_MESES LOOP 
		I_ANO_mês(IX) := TO_CHAR(ADD_MONTHS(P_ANO_mês_BASE, IX-1),'YYYYMM');	 
		--DBMS_OUTPUT.PUT_LINE ('1.1 - i_ano_mês '|| i_ano_mês(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_mês(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_mês(2) );	 
					GOTO ERRO; 
 
				ELSE 
 
					FOR IZ IN 1..V_MESES LOOP	 
					 
						IF V_ANOMES = I_ANO_mês(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_mês_C2, V_CONTA, V_CD_MOEDA, V_VL_ORCADO; 
		 
		WHILE C2%FOUND LOOP		 
 
			FOR IX IN 1..V_MESES LOOP 
			 
				IF V_ANO_mês_C2 = I_ANO_mês(IX) THEN 
					IZ := IX; 
					--DBMS_OUTPUT.PUT_LINE ('6 - PESQ MESES '|| IZ || ' ' || V_ANO_mês_C2 || ' ' || I_ANO_mês(IX));	 
				END IF; 
				 
				--DBMS_OUTPUT.PUT_LINE ('6 - PESQ MESES '|| IZ || ' ' || V_ANO_mês_C2 || ' ' || I_ANO_mês(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_mês_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_mês_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 all .. .
Marlon Pasquali
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 28
Joined: Mon, 20 Mar 2006 3:40 pm
Location: Erechim - RS

Try using the for loop structure instead of fetch. I know it seems bullshit, but I find it easier to track the cursor's data. Put nickname in the columns of your SELECT and use the example below to see what the routine is bringing from the cursor.

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;
Kaizzaer
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Thu, 17 Jun 2004 12:15 pm
Location: Rio de Janeiro - RJ

Hello Marlon, I tested the tip that you passed, but I continue with the problem, the lock is still occurring at the time the procedure tries to read the cursor. : Cry:

I will try to create the environment in another instance of Oracle and verify that the problem continues.

In any case, thanks for the tip.

ABS ...
User avatar
CharlieHorse
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Thu, 01 Jun 2006 3:28 pm
Location: PORTO ALEGRE - RS
Analista/Desenvolvedor Oracle há 12 anos.


Explicit (previously stated) cursors will always be more performable than the implicit (SELECT INTO) because once (declared), the compiler can already predict and create temporary areas that will contain the data that the queries They will return, size the effort and reserve the required memory.

Never use "SELECT INTO", they preferred to use explicit cursors
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Charliehorse wrote: Explicit (previously stated) cursors will always be more performable than implied
I found this week something in an Oracle manual that explains this phenomenon of the cursors Explicit are faster than a direct SELECT. (Of course, this if the user wants to return only 1 line)
All Select Statements Should Use an Explicit Cursor. Implicit
Select Statements Actually Cause 2 Fetches 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! One to search for information and another to test too_many_rows - if one line comes.

Source: http://download.oracle.com/docs/cd/A911 ... 15devg.pdf
nunes
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 2
Joined: Thu, 28 Oct 2010 2:17 pm
Location: Parobe - RS
Andre Nunes

Hello everyone,

Has anyone ever tested in practice if there is any performance difference between the 2 cases (Select-Into and Cursor) ??

I did some tests in the 9i, 10g and 11g versions and in both cases obtained better response time with SELECT-INTO.

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

In the tip asks to be done precisely the opposite of what has been said here: "Prefer implicit cursor".
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Tom Kyte also talked about using cursor implicit whenever he can on his visit to Brazil in the Guob Tech Day 2010. (this for cursors, as he still prefers that a direct insert is done).

He spoke a lot on ease of use (no need to be declaring, nor remembering closing, nor testing if he was not found, etc.).

In summary: we have a manual saying that explicit is better because he does not do 2 fetch, and we have Tom Kyte saying it is good to do select into ...

99] Topic still without solution :-(
gfkauer
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 389
Joined: Tue, 27 Jul 2010 1:34 pm
Location: Sapiranga - RS
Contact:
Quem falou que programar era fácil??

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

Acessem: www.gfkauer.com.br

Well, I always wonder, I'vê read in two or three manuals / books that cursor would be the best option, but Master Tom has already commented the contraction sometimes. For reasons of reusability and better control of exceptions I conclude to use the cursor. Until it provides that it has lower performance I do not see reasons to change ...

But always will have doubts in the air
nunes
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 2
Joined: Thu, 28 Oct 2010 2:17 pm
Location: Parobe - RS
Andre Nunes

Personnel,

to increase a little doubt, I created a test scenario to simulate, maybe it was biased. But I believe it is valid.

Below the code, if you want to test.

I created a table:

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 I performed a PL / SQL block to load it with random values ??and also some exclusions to Force the error in_data_found with SELECT-INTO.

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 will use Select-Into and another Select-Cursor.

I used an example where there will be the occurrence of TOO_MANY_ROWS, since I will be using PK to identify the registry, I believe that if the query can return more than 1 registry, the Select should not be used "Right, for a matter of logic.

First procedure:

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:

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 this I executed several times both:

Select all

Set serveroutput on size 10000 
Exec Prc_Select_Into 
Exec Prc_Select_Cursor 
and obtained the following times (I considered the cache to evaluate, I ignored the first execution):

Select all

Procedimento com SELECT-INTO..: 170 
Procedimento com SELECT-CURSOR: 235
I performed this test in Oracle Database 11g (11.2.0.1.0).
I did the same test in an Oracle 10G XE and also the advantage was from select-in.

If someone has any consideration as to the method used to evaluate.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 13 guests