mercredi 20 avril 2011

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 ».

mercredi 13 avril 2011

SEMI-JOINS : IN vs EXISTS

Une semi-jointure est une jointure entre 2 tables où les lignes de la première table sont retournées s’il existe au moins une correspondance dans la 2ème table.

Les semi-jointures peuvent notamment s’écrire soit à l’aide de la clause EXISTS soit à l’aide de la clause IN.

Voyons ces 2 manières d’écrire les semi-jointures avec un exemple utilisant les tables COUNTRIES et CUSTOMERS du schéma SH (un des schémas DEMO d’Oracle) :

SQL> select /* EXISTS_SEMI_JOIN */ country_name from countries co where exists (select 1 from customers cu where co.COUNTRY_ID=cu.COUNTRY_ID);

COUNTRY_NAME
----------------------------------------
Italy
Brazil
Japan
United Kingdom
Germany
United States of America
France
Canada
Spain
China
Singapore
New Zealand
Poland
Australia
Argentina
Denmark
South Africa
Saudi Arabia
Turkey

19 rows selected.

SQL>  select /* IN_SEMI_JOIN */ country_name from countries co where country_id in (select country_id from customers cu );

COUNTRY_NAME
----------------------------------------
Italy
Brazil
Japan
United Kingdom
Germany
United States of America
France
Canada
Spain
China
Singapore
New Zealand
Poland
Australia
Argentina
Denmark
South Africa
Saudi Arabia
Turkey

19 rows selected.

Ces requêtes retournent la liste des pays pour lesquels on a au moins un client qui y réside.
On constate que les 2 requêtes retournent le même nombre de lignes ce qui veut dire que ces 2 manières d’écrire les semi-jointures sont identiques d’un point de vu fonctionnel.
Mais qu’en est-il d’un point de vu performances ?

Sur ce sujet j’ai souvent entendu des développeurs (et même des DBAs) dire que la clause EXISTS était à privilégier par rapport à la clause IN.
Ceci était vrai jusqu’à la version 8i mais ce n’est plus le cas aujourd’hui. En effet, depuis la 9i l’optimiseur d’Oracle lors du parsing transforme « à sa sauce » la requête (que ce soit celle avec la clause IN ou celle avec la clause EXISTS) en « mergeant » la sous-requête. On dit qu’Oracle UNNEST la sous requête. Ce merge permet à Oracle d’utiliser la technique de semi-jointure (NESTED LOOP SEMI ou HASH JOIN SEMI) plus performante que l’opération FILTER.

Voici la preuve que les 2 requêtes conduisent au même plan d’exécution :

SQL> explain plan for
  2  select /* EXISTS_SEMI_JOIN */ country_name from countries co where exists (select 1 from customers cu where co.COUNTRY_ID=cu.COUNTRY_ID);
SQL> select * from table(dbms_xplan.display);

Plan hash value: 1942541467

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |    19 |   380 |    26   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |               |    19 |   380 |    26   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| COUNTRIES     |    23 |   345 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IDX_COUTRY_ID | 45848 |   223K|     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   3 - access("CO"."COUNTRY_ID"="CU"."COUNTRY_ID")
SQL> explain plan for
  2  select /* IN_SEMI_JOIN */ country_name from countries co where country_id in (select country_id from customers cu );
SQL> select * from table(dbms_xplan.display);

Plan hash value: 1942541467

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |    19 |   380 |    26   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |               |    19 |   380 |    26   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| COUNTRIES     |    23 |   345 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IDX_COUTRY_ID | 45848 |   223K|     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   3 - access("COUNTRY_ID"="COUNTRY_ID")

Ici le plan consiste à rechercher pour chaque ligne de la table COUNTRIES s’il existe une correspondance dans la table CUSTOMERS via l’index IDX_COUNTRY_ID. L’avantage du NESTED LOOP SEMI par rapport au NESTED LOOP classique (en gros de la semi jointure par rapport à une equi-jointure classique) c’est que dans la semi-jointure la recherche pour un COUNTRY_ID s’arrête dès lors qu’on a trouvé une correspondance dans la table CUSTOMERS permettant à Oracle de passer au COUNTRY_ID suivant. C’est comme si on avait une clause EXIT dans le code d'une boucle imbriquée.

Voyons maintenant ce que donne le plan lorsqu’on empêche l’optimiseur de faire sa transformation. Pour ce faire j'utilise le hint NO_QUERY_TRANSFORMATION (le hint parle de lui-même) :

SQL> explain plan for
  2  select /*+ NO_QUERY_TRANSFORMATION */ country_name from countries co where country_id in (select country_id from customers cu );
SQL> select * from table(dbms_xplan.display);

Plan hash value: 3262839326

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    15 |    15   (0)| 00:00:01 |
|*  1 |  FILTER            |               |       |       |            |          |
|   2 |   TABLE ACCESS FULL| COUNTRIES     |    23 |   345 |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IDX_COUTRY_ID |     2 |    10 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "CUSTOMERS" "CU" WHERE
              "COUNTRY_ID"=:B1))
   3 - access("COUNTRY_ID"=:B1)

On voit que la sous-requête n’est plus « unnestée » et que le CBO n’a d’autre choix que d’utiliser l’opérateur FILTER qui est beaucoup moins performant.


Voici également une preuve qu’en 8i les plans et donc les performances étaient bien différents selon qu’on utilisait la clause IN ou la clause EXISTS. Il est possible de passer l’optimiseur en mode 8i en modifiant le paramètre OPTIMIZER_FEATURES_ENABLE :

SQL> alter session set optimizer_features_enable='8.0.6';

Session altered.

SQL> explain plan for
  2  select /* IN_SEMI_JOIN */ country_name from countries co where country_id in (select country_id from customers cu );

Explained.

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

Plan hash value: 214911130

---------------------------------------------------------------------------------
| Id  | Operation               | Name          | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |    19 |   912 |       |   228 |
|*  1 |  HASH JOIN              |               |    19 |   912 |       |   228 |
|   2 |   VIEW                  | VW_NSO_1      |    19 |   247 |       |   226 |
|   3 |    SORT UNIQUE          |               |    19 |   247 |  1112K|   226 |
|   4 |     INDEX FAST FULL SCAN| IDX_COUTRY_ID | 55500 |   704K|       |    13 |
|   5 |   TABLE ACCESS FULL     | COUNTRIES     |    23 |   805 |       |     1 |
---------------------------------------------------------------------------------

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

   1 - access("COUNTRY_ID"="$nso_col_1")

Note
-----
   - cpu costing is off (consider enabling it)

21 rows selected.

SQL> explain plan for
  2  select /* EXISTS_SEMI_JOIN */ country_name from countries co where exists (select 1 from customers cu where co.COUNTRY_ID=cu.COUNTRY_ID);

Explained.

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

Plan hash value: 3262839326

--------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    35 |     1 |
|*  1 |  FILTER            |               |       |       |       |
|   2 |   TABLE ACCESS FULL| COUNTRIES     |     1 |    35 |     1 |
|*  3 |   INDEX RANGE SCAN | IDX_COUTRY_ID |  2921 | 37973 |     7 |
--------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "CUSTOMERS" "CU" WHERE
              "CU"."COUNTRY_ID"=:B1))
   3 - access("CU"."COUNTRY_ID"=:B1)

Note
-----
   - cpu costing is off (consider enabling it)

21 rows selected. 

Maintenant que nous sommes en 11g (et bientôt en 12c) ne dites plus que EXISTS c’est mieux que IN.

A lire aussi:
ANTI-JOINS: NOT IN vs NOT EXISTS

vendredi 8 avril 2011

SQLPLUS: NEW_VALUE

Lorsque vous écrivez des scripts sous SQLPLUS vous pouvez utiliser la commande NEW_VALUE pour stocker la valeur d'une colonne dans une variable SQLPLUS.
La commande de raccourci est NEW_V.

Exemple:
SQL> col instance_name NEW_V inst_name
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl11

SQL> select instance_number from v$instance where instance_name='&inst_name';
old   1: select instance_number from v$instance where instance_name='&inst_name'
new   1: select instance_number from v$instance where instance_name='orcl11'

INSTANCE_NUMBER
---------------
              1

Si la requête retourne plusieurs lignes alors c'est la dernière valeur qui sera sauvergardée dans la variable:
SQL> col table_name NEW_V t_name
SQL> select table_name from user_tables where rownum<=3;

TABLE_NAME
------------------------------
ICOL$
CON$
UNDO$

SQL> prompt &t_name
UNDO$