Fehlende Column Statistics

Die nachträgliche Ergänzung neuer Spalten in bestehenden Tabellen kann einige unerwünschte Effekte mit sich bringen - in erster Linie besteht die Gefahr, dass neu hinzu kommende Werte die Satzlänge vergrößern, was dann unter Umständen zu row migration führen kann. Ein Aspekt, über den ich bisher nicht intensiver nachgedacht hatte, ist das Fehlen von column statistics, das sich in diesem Fall ergibt, wenn man im Anschluss an die Ergänzung der Spalte keine Aktualisierung der Statistiken durchführt. Dazu ein Beispiel:

-- 11.2.0.1
create table test_new_col
as
select rownum id
  from dual
connect by level <= 10000;

exec dbms_stats.gather_table_stats(user, 'test_new_col');

alter table test_new_col add col1 number;

update test_new_col set col1 = 1 ;

commit;

Ein Blick in user_tab_cols zeigt, dass in diesem Fall (natürlich) keine Statistiken für col1 vorliegen.

select column_name
     , num_distinct
     , num_nulls
     , last_analyzed
  from user_tab_cols
 where table_name = upper('test_new_col');

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS LAST_ANALYZED
------------------------------ ------------ ---------- -------------------
ID                                    10000          0 12.12.2012 08:06:04
COL1

Was macht der CBO aus dieser Information - bzw. aus ihrem Fehlen:

explain plan for
select count(*)
  from test_new_col
 where col1 = 0;

select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |     4 |    12   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_NEW_COL |   100 |   400 |    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("COL1"=0)

explain plan for
select count(*)
  from test_new_col
 where col1 = 1

select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |     4 |    12   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |              |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_NEW_COL |   100 |   400 |    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - filter("COL1"=1)

Offensichtlich verwendet der CBO hier eine standard selectivity von 1%, was im Beispiel weder für Fall col1 = 0 (=> 0 rows) noch für Fall col1 = 1 (=> 10000 rows) passend ist. Der passende dbms_stats-Aufruf sollte folglich nach der Ergänzung einer neuen Spalte obligatorisch sein - besonders dann, wenn man über diese Spalte Einschränkungen durchführen möchte.