Nouveautés 11g : les colonnes virtuelles

Avec la 11g il est possible de créer des colonnes virtuelles dans une table sauf que le contenu de cette colonne n’est pas stocké dans le segment mais calculé lorsqu’on requête la colonne.
Ainsi je peux avoir dans une table CLIENT une colonne nommée UPPER_NAME qui contiendra le résultat de UPPER(NAME). A chaque fois que je requêterai la colonne UPPER_NAME, Oracle appliquera la fonction UPPER à la colonne NAME.

La syntaxe pour ajouter une colonne virtuelle est très simple :

ALTER TABLE CLIENT ADD UPPER_NAME GENERATED
 ALWAYS AS (UPPER (NAME) ) VIRTUAL;

Les clauses GENERATED ALWAYS et VIRTUAL ne sont pas obligatoires. Elles servent juste à rendre la syntaxe plus parlante.

L’un des avantages des colonnes virtuelles c’est qu’elles peuvent être prises en compte lors du calcul des statistiques de la table. L’optimiseur possède ainsi des informations sur la colonne lui permettant de choisir le plan d’exécution adéquat.

Voyons tout ça avec un exemple :

SQL> create table t1 as select table_name,last_analyzed from user_tables;

Table created.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 LAST_ANALYZED                                      DATE

SQL> alter table t1 add lower_table_name generated always as (lower(table_name)) VIRTUAL;

Table altered.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 LAST_ANALYZED                                      DATE
 LOWER_TABLE_NAME                                   VARCHAR2(30)

La colonne virtuelle a été ajoutée et est présente dans le descriptif de la table.
Si on requête la table on se rend compte que la colonne virtuelle contient bien le contenu de TABLE_NAME en miniscule :

SQL> select * from t1 where rownum<=5;

TABLE_NAME                     LAST_ANAL LOWER_TABLE_NAME
------------------------------ --------- ------------------------------
ICOL$                          23-FEB-11 icol$
IND$                           17-APR-11 ind$
COL$                           23-FEB-11 col$
CLU$                           17-APR-11 clu$
TAB$                           18-APR-11 tab$

Calculons maintenant des statistiques sur la table :

SQL> exec DBMS_STATS.gather_table_stats(USER,'T1');

PL/SQL procedure successfully completed.

SQL> select a.column_name, a.num_distinct, a.avg_col_len, a.density
  2  from user_tab_col_statistics a where table_name='T1';

COLUMN_NAME                    NUM_DISTINCT AVG_COL_LEN    DENSITY
------------------------------ ------------ ----------- ----------
TABLE_NAME                              942          18 .001061571
LAST_ANALYZED                           213           8 .004694836
LOWER_TABLE_NAME                        942          18 .001061571

On se rend compte que des statistiques ont bien été calculées pour la colonne virtuelle. Du coup, lorsque j’effectue une requête avec un filter predicate sur la colonne virtuelle le CBO estime la bonne cardinalité :

SQL> select /*+ gather_plan_statistics */ * from t1 where LOWER_TABLE_NAME='tab$';

TABLE_NAME                     LAST_ANAL LOWER_TABLE_NAME
------------------------------ --------- ------------------------------
TAB$                           18-APR-11 tab$

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Plan hash value: 3617692013

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------

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

   1 - filter(LOWER("TABLE_NAME")='tab$')

Il est bien sûr possible de créer un index sur cette colonne virtuelle comme s’il s’agissait d’une colonne classique :

SQL> create index idx_lower_name on T1(LOWER_TABLE_NAME);

Index created.

SQL> select /*+ gather_plan_statistics */ * from t1 where LOWER_TABLE_NAME='tab$';

TABLE_NAME                     LAST_ANAL LOWER_TABLE_NAME
------------------------------ --------- ------------------------------
TAB$                           18-APR-11 tab$

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));


Plan hash value: 579975285

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |      1 |00:00:00.01 |       4 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1             |      1 |      1 |      1 |00:00:00.01 |       4 |      1 |
|*  2 |   INDEX RANGE SCAN          | IDX_LOWER_NAME |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |
-----------------------------------------------------------------------------------------------------------------

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

   2 - access("LOWER_TABLE_NAME"='tab$')

Les colonnes virtuelles sont donc plus pratiques que les indexes fonctions car elles évitent notamment le fait d’avoir à calculer des statistiques étendues sur les fonctions « maison ».