Colonne correlate in 11g

In passato avevo già affrontato il problema delle colonne correlate e il sampling dinamico come soluzione alternativa.

In 11g, mediante una nuova modalità di raccolta delle statistiche, è possibile specificare dei gruppi di colonne su cui misurare la correlazione, in qualche modo un'estensione del concetto di distribuzione poco uniforme dei dati (skewness).

Facciamo un esempio più generale di quello fatto in passato, dove la correlazione è meno evidente. Nell'esempio del post sul sampling dinamico avevo utilizzato due colonne con valori uguali per ogni riga, mentre ora utilizzo un caso più reale: immaginiamo di avere tre gruppi di valori, il primo di valori attorno a 1000, il secondo attorno a 2000, il terzo attorno a 3000:

SQL> desc correl
 Name                Null?    Type
 ------------------- -------- --------------
 TAG                 NOT NULL VARCHAR2(16)
 VAL                 NOT NULL NUMBER(38)

SQL> insert into correl select 'TIPO1', 1000+TRUNC(DBMS_RANDOM.VALUE(0,100)) from all_objects;

61122 rows created.

SQL> select * from correl where rownum < 10;

TAG                     VAL
---------------- ----------
TIPO1                  1035
TIPO1                  1012
TIPO1                  1003
TIPO1                  1090
TIPO1                  1070
TIPO1                  1061
TIPO1                  1004
TIPO1                  1020
TIPO1                  1032

9 rows selected.

SQL> insert into correl select 'TIPO2', 2000+TRUNC(DBMS_RANDOM.VALUE(0,100)) from all_objects;

61122 rows created.

SQL> insert into correl select 'TIPO3', 3000+TRUNC(DBMS_RANDOM.VALUE(0,100)) from all_objects;

61122 rows created.

SQL> commit;

Commit complete.

A questo punto prendiamo le statistiche standard e vediamo che l'optimizer, per un valore qualsiasi (e non esistente) all'interno del range max-min della colonna, prevede che ci siano 204 righe:

SQL> exec dbms_stats.gather_table_stats(user, 'CORREL', estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> explain plan for select * from correl where tag = 'TIPO2' and val = 1200;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 469411154

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   204 |  2040 |   141   (4)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| CORREL |   204 |  2040 |   141   (4)| 00:00:02 |
----------------------------------------------------------------------------

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

   1 - filter("VAL"=1200 AND "TAG"='TIPO2')

13 rows selected.

Ma ora prendiamo le statistiche utilizzando i gruppi di colonne:

SQL> exec dbms_stats.gather_table_stats(user, 'CORREL', method_opt => 'FOR COLUMNS (TAG,VAL) SIZE SKEWONLY', estimate_percent => 100);

PL/SQL procedure successfully completed.

SQL> explain plan for select * from correl where tag = 'TIPO2' and val = 1201;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 469411154

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   611 |  6110 |   141   (4)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| CORREL |   611 |  6110 |   141   (4)| 00:00:02 |
----------------------------------------------------------------------------

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

   1 - filter("VAL"=1201 AND "TAG"='TIPO2')

13 rows selected.

È evidente che ora le cose vanno molto meglio: la stima dell'optimizer è di 611 righe, anche se non esistono righe per cui il valore val è 1201.

Ma il grande vantaggio è che ora la stima di circa 600 righe è valida per i numeri esistenti:

SQL> explain plan for select * from correl where tag = 'TIPO2' and val = 2023;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 469411154

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |   611 |  6110 |   141   (4)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| CORREL |   611 |  6110 |   141   (4)| 00:00:02 |
----------------------------------------------------------------------------

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

   1 - filter("VAL"=2023 AND "TAG"='TIPO2')

13 rows selected.

SQL> select count(*) from correl where tag = 'TIPO2' and val = 2023;

  COUNT(*)
----------
       645

1 row selected.

Probabilmente con qualche intervento sull'istogramma del gruppo di colonne è possibile correggere anche la stima errata dovuta alla distribuzione molto disuniforme dei numeri nella colonna val.