Nutzlose und weniger nutzlose METHOD_OPT-Angaben

Da ich hier zuletzt fast nur noch Links kommentiert habe, zur Abwechslung noch mal ein bisschen was Praktisches. Im OTN-Forum wurde heute die Frage gestellt, wieso dbms_stats.gather_table_stats auf den nicht dokumentierten Parameter-Wert "FOR ALL INDEXES" nicht mit einem Fehler reagiert. Meine Antwort darauf lautet: keine Ahnung, aber er ist noch gefährlicher als "FOR ALL INDEXED COLUMNS":

drop table t;
create table t
as
select rownum id
     , mod(rownum, 2) col1
     , mod(rownum, 5) col2
     , mod(rownum, 10) col3
  from dual
 connect by level <= 10000;  
 
create index t_idx1 on t(id);

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                                 NONE
COL2                                                 NONE
COL3                                                 NONE
ID                                    10000 01.12.15 HEIGHT BALANCED

--> create column statistics (and histograms) just for indexed columns

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL INDEXES')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                                 NONE
COL2                                                 NONE
COL3                                                 NONE
ID                                                   NONE

--> creates no column statistics

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL COLUMNS')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                      2 01.12.15 FREQUENCY
COL2                                      5 01.12.15 FREQUENCY
COL3                                     10 01.12.15 FREQUENCY
ID                                    10000 01.12.15 HEIGHT BALANCED

--> creates column statistics (and histograms) for all columns

exec dbms_stats.delete_table_stats(user, 't')
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS COL3 SIZE 254')

select column_name, num_distinct, last_analyzed, histogram from user_tab_cols where table_name = 'T' order by 1;

COLUMN_NAME                    NUM_DISTINCT LAST_ANA HISTOGRAM
------------------------------ ------------ -------- ---------------
COL1                                      2 01.12.15 NONE
COL2                                      5 01.12.15 NONE
COL3                                     10 01.12.15 FREQUENCY
ID                                    10000 01.12.15 NONE

--> creates column statistics for all columns and a histogram for COL3

Warum gefährlicher als "FOR ALL INDEXED COLUMNS"? Weil man damit tatsächlich gar keine Spalten-Statistiken erhält, so dass der Optimizer bei der Bestimmung der Cardinalities für alle Spalten auf Schätzungen zurückgehen muss. Ganz ohne Statistiken hätte man da noch dynamic sampling (und dadurch brauchbare Cardinalities), aber wenn Tabellen-Statistiken vorliegen, geht der Optimizer davon aus, dass er auch mit den Angaben zu den Spalten etwas anfangen kann:

SQL> select count(*) from t where col2 = 1;

  COUNT(*)
----------
      2000

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

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

   2 - filter("COL2"=1)

SQL> exec dbms_stats.delete_table_stats(user, 't')

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select count(*) from t where col2 = 1;

  COUNT(*)
----------
      2000

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

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

   2 - filter("COL2"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)