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.
Oracle ha divulgato i risultati di un test del MySQL performance team in cui sono state messe a confronto le prestazioni dei motori MyISAM e InnoDB di MySQL.
