Oracle Implicit datatype convertion

Isto é uma coisa supostamente antiga mas parece que muita gente ainda não sabe. Parece que o  Oracle faz conversão implicita de datatype durante a fase de “parsing”
[Fase designada quando o CBO verifica se o statement está na shared pool, verifica a sintaxe e privilégios nos objectos e determina de um modo geral o plano de execução]:

 

O exemplo vai ser o seguinte:

1 – Criar uma tabela com uma coluna do tipo NUMBER

2 – Verificar o plano de execução

3 – ??

4 – Profit!

 

SQL> create table a_test as select 1 as A from dual;

Table created.


SQL> desc a_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

 

SQL>  explain plan for 

select * from a_test where A= '1' || RPAD('0', '3', '0');

Explained.

 


SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3679270240

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| A_TEST | 1 | 13 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter("A"=1000)

Note
-----
- dynamic sampling used for this statement

17 rows selected.

 

É fácil reparar que o PLAN_TABLE_OUTPUT refere “A” = 1000, sendo que o valor 1000 já representa um inteiro, inteiro esse convertido durante

a fase de “parsing” do CBO. Se isto não acontecesse provavelmente veriamos um TO_NUMBER(), para permitir a comparação contra o datatype

definido na coluna.

Para provar que isto realmente é assim basta adicionar na query um TO_CHAR() para perceber que agora o valor para comparação será um varchar:

 

SQL> explain plan for select * from a_test 

where to_char(a) = '1' || RPAD('0','3','0');


Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3679270240

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| A_TEST | 1 | 13 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - filter(TO_CHAR("A")='1000')

Note
-----
- dynamic sampling used for this statement

17 rows selected.

 

Neste exemplo não há conversa implicita pois pretendemos comparar uma

coluna do tipo char com um varchar ‘1000’.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s