Join Cardinality und explizite Typ-Konvertierung

Gerade habe ich im OTN-Forum einen Fall vorgestellt, bei dem der CBO zu einer seltsamen Fehleinschätzung der Join Cardinality kommt (und der den Kern eines Problems darstellt, dem ich gestern bei einer großen DWH-Query begegnet bin):

Zunächst der harmlose Versuchsaufbau:

drop table t1;
drop table t2;

create table t1
as
select rownum col1
  from dual
connect by level <= 1000;

create table t2
as
select to_char(rownum) col1
  from dual
connect by level <= 1000;

exec dbms_stats.gather_table_stats(user, 't1')
exec dbms_stats.gather_table_stats(user, 't2')

Also zwei Tabellen, die jeweils die Werte 1 bis 1000 enthalten und deren einziger Unterschied der Datentyp ist: einmal number, einmal varchar2. Wenn ich jetzt versuche, diesen Unterschied (man könnte es wohl auch Modellierungsfehler nennen) in einer inline View zu beheben, bekomme ich ein überraschendes Ergebnis:

explain plan for
select t.col1
     , t2.col1
  from (select /*+ no_merge */
               to_char(col1) col1
          from t1) t
     , t2
 where t.col1 = t2.col1;

select * from table(dbms_xplan.display('', ''));

Plan hash value: 1373845765

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    25 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |    25 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T2   |  1000 |  3000 |     3   (0)| 00:00:01 |
|   3 |   VIEW              |      |  1000 | 22000 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   |  1000 |  3000 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("T"."COL1"="T2"."COL1")

Der CBO erwartet in Step 1 also nur einen Satz! Mit der Standardformel für die Berechnung der Join Cardinality (ohne Berücksichtigung von NULL-Werten und Filterungen) bekomme ich Folgendes:

• join selectivity = 1 / greater(num_distinct(t1.col1), num_distinct(t2.col1))
• join cardinality = join selectivity * cardinality t1 * cardinality t2
• So I get 1/1000 * 1000 * 1000 = 1000

Und dieses - korrekte - Ergebnis von 1000 bekomme ich auch in nahezu jedem anderen Fall:

-- ohne no_merge
select t.col1
     , t2.col1
  from (select to_char(col1) col1
          from t1) t
     , t2
 where t.col1 = t2.col1

Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 |  6000 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  1000 |  6000 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |  1000 |  3000 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 |  3000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T2"."COL1"=TO_CHAR("COL1"))

-- ohne explizite Typ-Umwandlung in der inline View
select t.col1
     , t2.col1
  from (select /*+ no_merge */
               col1
          from t1) t
     , t2
 where t.col1 = t2.col1

Plan hash value: 1373845765

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1000 | 16000 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |  1000 | 16000 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T2   |  1000 |  3000 |     3   (0)| 00:00:01 |
|   3 |   VIEW              |      |  1000 | 13000 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   |  1000 |  3000 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("T"."COL1"=TO_NUMBER("T2"."COL1"))

-- mit zusätzlicher expliziter Typ-Umwandlung im Join
select t.col1
     , t2.col1
  from (select /*+ no_merge */
               to_char(col1) col1
          from t1) t
     , t2
 where to_char(t.col1) = t2.col1

Plan hash value: 1373845765

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1000 | 25000 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |  1000 | 25000 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T2   |  1000 |  3000 |     3   (0)| 00:00:01 |
|   3 |   VIEW              |      |  1000 | 22000 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   |  1000 |  3000 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("T2"."COL1"=INTERNAL_FUNCTION("T"."COL1"))

In allen diesen Fällen erfolgt (laut access-Prädikat der Join-Operation) eine zusätzlich Typ-Umwandlung, die den CBO offenbar von der Fehleinschätzung der Cardinality abbringt. Aber warum der CBO durch die explizite Typumwandlung so massiv gestört wird, bleibt mir zunächst ein Rätsel.

Nachtrag: ein CBO-Trace zeigt für die Varianten mit und ohne to_char in der inline-View sehr ähnliche Ergebnisse. Zu den signifikanten Unterschieden gehören die folgenden Angaben:

-- mit to_char
Join Card:  0.000000 = = outer (1000.000000) * inner (1000.000000) * sel (0.000000)
Join Card - Rounded: 1 Computed: 0.00

-- ohne to_char
Join Card:  1000.000000 = = outer (1000.000000) * inner (1000.000000) * sel (0.001000)
Join Card - Rounded: 1000 Computed: 1000.00

Aber woher die sel-Angabe (0.000000) im ersten Fall kommt, sehe ich nicht.

Nachtrag 14.09.2012: in der OTN-Diskussion wurde klar, dass das Problem der unbrauchbaren Join-Cardinality-Schätzungen bei Verwendung inadäquater Datentypen auch andere Join-Syntax-Varianten betreffen kann, was den Fall noch unerfreulicher macht - und die Bedeutung einer soliden Modellierung unterstreicht.