Implizite Typ-Konvertierung und Index-Zugriff

Das Thema wurde sicherlich schon in diversen Blog-Einträgen erläutert - ich war aber zu faul, um danach zu suchen. Außerdem wollte ich hier mal wieder ein kleines Code-Beispiel unterbringen.

Um was es geht, ist Folgendes: Oracle sieht großzügig über fehlerhafte Typ-Angaben bei den Einschränkungen von Abfragen hinweg und korrigiert sie stillschweigend via implizite Typ-Umwandlung. Grundsätzlich ist das eine nette Geste, aber natürlich bringt das Verhalten gelegentlich auch Probleme mit sich. Unter anderem gibt es Situationen, in denen eine solche implizite Konvertierung einen Index-Zugriff ausschalten kann. Dazu ein kleines Beispiel, das nur vier sehr einfache Fälle berücksichtigt: die Verwendung einer numerischen bzw. einer String-Bedingung für eine numerische bzw. eine String-Spalte:

-- 11.2.0.1
drop table t;

create table t
as 
select rownum num_col
     , to_char(rownum) char_col
  from dual 
connect by level <= 100000;

exec dbms_stats.gather_table_stats(user, 'T')

create unique index t_num_col_idx on t(num_col);

create unique index t_char_col_idx on t(char_col);

set autot trace

-- number column and number condition
select count(*) 
  from t 
 where num_col = 1;

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |               |     1 |     5 |            |          |
|*  2 |   INDEX UNIQUE SCAN| T_NUM_COL_IDX |     1 |     5 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("NUM_COL"=1)

-- number column and string condition 
select count(*) 
  from t 
 where num_col = '1';

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |               |     1 |     5 |            |          |
|*  2 |   INDEX UNIQUE SCAN| T_NUM_COL_IDX |     1 |     5 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("NUM_COL"=1)

-- string column and number condition 
select count(*) 
  from t 
 where char_col = 1;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |    11   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     6 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter(TO_NUMBER("CHAR_COL")=1)

-- string column and string condition 
select count(*) 
  from t 
 where char_col = '1';

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     6 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                |     1 |     6 |            |          |
|*  2 |   INDEX UNIQUE SCAN| T_CHAR_COL_IDX |     1 |     6 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("CHAR_COL"='1')

Das Verhalten ist demnach in den gegebenen Fällen recht übersichtlich:

• number column and number condition: keine Typumwandlung.
• number column and string condition: der String in der Bedingung wird in einen numerischen Wert umgewandelt, wobei diese Umwandlung im Plan nicht sichtbar ist. In diesem Fall ist die Umwandlung ungefährlich, weil der Vergleich nur für numerische Werte sinnvoll ist: würde als Vergleichswert ein nicht-numerischer Wert erscheinen, so ergäbe sich ein Parse-Fehler (ORA-01722: Ungültige Zahl).
• string column and number condition: dies ist der problematische Fall: der Zugriff erfolgt über Full Table Scan, weil aufgrund des Vergleichs ein Filter-Prädikat (TO_NUMBER("CHAR_COL")=1) eingeführt werden muss.
• string column and string condition: keine Typumwandlung.

Hier angekommen fällt mir auf, dass ich keine Lust habe, die komplette Matrix möglicher Konvertierungen auf ihre Behandlung der impliziten Konvertierung hin zu untersuchen, aber das hat Maxym Kharchenko dankenswerterweise gelegentlich bereits erledigt. Beim Blick auf diese Matrix fällt es mir schwer, eine allgemeingültige Regel zu formulieren, die das Verhalten allgemein beschreibt - aber für meine Zwecke genügt das Fazit, dass numerische Spalten, die als String-Typen abgelegt werden, Probleme hervorrufen können, was wieder ein Argument dafür wäre, Attribute in geeigneten Typen abzubilden - wenn man dafür noch Argumente bräuchte.