Cast vs to _...

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
diegobarr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 23 Nov 2010 9:34 pm
Location: Florianopolis - SC

Personal, there is some performance difference between cast and functions to _... (to_date, to_char, to_number).

Which most usual?

Att.
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

In my humble opinion the construction of to _ is simpler, accelerating development.

and I may be mistaken, but I believe that the cast is more used for non-basic types, as per examples a cast type date for a type meutiparticular .
burga
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Thu, 26 Nov 2009 1:05 pm
Location: SP
Ricardo H. Tajiri

The cast if I am not mistaken is SQL standard, which can be a hand on the wheel in independent applications of the technology used for BD.

Looking for a little Google about the performance of the two I found this message on a list of discussions:
http://www.freelists.org/post/oracle-l/ ... -DATE-or,7
You want to make your own tests feel at ease, then post to us the result ... :)
diegobarr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 23 Nov 2010 9:34 pm
Location: Florianopolis - SC

After reading the post sent I did some tests and the cast is a little faster than the to _....

I did various tests with to_char, to_date, to_number and in all cases the cast had A performance a little better.

Thanks.


User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
Location: 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

As the burgida has already said, the main use of the cast is in the conversion of User Defined Types, Object Types and Collection Types. In these cases CASTS are often required.

for a simple number conversion to Varchar2, for example, both ... The performance difference is insignificant. But the main is, for most conversions it is only possible to do using to_char, because the cast does not accept formatting masks ...

being so, the best is or using cast in Simple conversions, because the implicit conversion , this yes, has significantly superior performance. See below:

Select all

 
SQL> SET SERVEROUTPUT ON 
SQL> -- CONVERSÃO IMPLÍCITA 
SQL> DECLARE 
  2    v_teste VARCHAR2(10); 
  3    t1      NUMBER; 
  4    t2      NUMBER; 
  5  BEGIN 
  6    t1 := dbms_utility.get_time; 
  7    FOR cur IN (SELECT LEVEL INTO v_teste FROM dual CONNECT BY LEVEL <= 1000000) 
  8    LOOP 
  9      NULL; 
 10    END LOOP; 
 11    t2 := dbms_utility.get_time; 
 12    dbms_output.put_line(sql%rowcount || ' registros em ' || to_char((t2 - t1) / 100, '990.000') || ' segundos'); 
 13  END; 
 14  / 
  
 registros em    3.000 segundos 
  
PL/SQL procedure successfully completed 
SQL> -- CAST 
SQL> DECLARE 
  2    v_teste VARCHAR2(10); 
  3    t1      NUMBER; 
  4    t2      NUMBER; 
  5  BEGIN 
  6    t1 := dbms_utility.get_time; 
  7    FOR cur IN (SELECT CAST(LEVEL AS VARCHAR2(10)) INTO v_teste FROM dual CONNECT BY LEVEL <= 1000000) 
  8    LOOP 
  9      NULL; 
 10    END LOOP; 
 11    t2 := dbms_utility.get_time; 
 12    dbms_output.put_line(sql%rowcount || ' registros em ' || to_char((t2 - t1) / 100, '990.000') || ' segundos'); 
 13  END; 
 14  / 
  
 registros em    3.770 segundos 
  
PL/SQL procedure successfully completed 
SQL> -- TO_CHAR 
SQL> DECLARE 
  2    v_teste VARCHAR2(10); 
  3    t1      NUMBER; 
  4    t2      NUMBER; 
  5  BEGIN 
  6    t1 := dbms_utility.get_time; 
  7    FOR cur IN (SELECT TO_CHAR(LEVEL) INTO v_teste FROM dual CONNECT BY LEVEL <= 1000000) 
  8    LOOP 
  9      NULL; 
 10    END LOOP; 
 11    t2 := dbms_utility.get_time; 
 12    dbms_output.put_line(sql%rowcount || ' registros em ' || to_char((t2 - t1) / 100, '990.000') || ' segundos'); 
 13  END; 
 14  / 
  
 registros em    3.810 segundos 
  
PL/SQL procedure successfully completed 
burga
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 232
Joined: Thu, 26 Nov 2009 1:05 pm
Location: SP
Ricardo H. Tajiri

As you have already spoken, the main use of the cast is in the conversion of User Defined Types, Object Types and Collection Types
In fact the credit is from gfkauer. . :-O
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
Location: 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

Oh yes! Exact, a thousand excuses: oops:
diegobarr
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Tue, 23 Nov 2010 9:34 pm
Location: Florianopolis - SC

Valeu boys ...

[] S
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 1 guest