Personal, there is some performance difference between cast and functions to _... (to_date, to_char, to_number).
Which most usual?
Att.
Cast vs to _...
-
- 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
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 .
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 .
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 ...
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 ...

-
- 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.
I did various tests with to_char, to_date, to_number and in all cases the cast had A performance a little better.
Thanks.
- fsitja
- 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
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:
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:
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
- fsitja
- 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
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:
-
- Information
-
Who is online
Users browsing this forum: Bing [Bot] and 1 guest