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$

mardi 29 mars 2011

ORA-01031: insufficient privileges

Si vous utilisez une base oracle sous windows et que vous obtenez l'erreur ORA-01031 lorsque vous vous connectez en SYSDBA sans préciser de mot de passe, il y'a de fortes chances que le problème soit lié à la configuration de votre fichier SQLNET.ORA
C:\HOMEWARE>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 28 13:22:53 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

En effet lorsque le paramètre SQLNET.AUTHENTICATION_SERVICES de votre fichier SQLNET.ora est positionné à NONE ou pas positionné du tout (NONE est la valeur par défaut), alors vous ne pouvez pas vous connecter en SYSDBA sans préciser de mot de passe.
Pour préciser à oracle que l'authentification se fait par l'OS windows il faut mettre ce paramètre à NTS:
SQLNET.AUTHENTICATION_SERVICES = (NTS)

Ainsi, vous n'êtes plus obligé de préciser le mot de passe:
C:\HOMEWARE>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 29 15:04:51 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

jeudi 24 mars 2011

ORA-00932: inconsistent datatypes: expected - got CLOB

Si vous tombez sur l’erreur ORA-00932 c’est que vous avez exécuté une requête contenant une colonne de type LOB dans la clause GROUP BY ce qui n’est absolument pas possible dans Oracle.

Voici un petit exemple très simple :
SQL> create table emp2 as select * from emp;

Table created.

SQL> alter table emp2 add (job_desc clob);

Table altered.

SQL> update emp2 set job_desc=to_clob(job);

14 rows updated.

SQL> commit;

Commit complete.

SQL> select job, count(1) from emp2 group by job order by 2 desc;

JOB         COUNT(1)
--------- ----------
CLERK              4
SALESMAN           4
MANAGER            3
ANALYST            2
PRESIDENT          1

SQL> select job_desc, count(1) from emp2 group by job_desc order by 2 desc;
select job_desc, count(1) from emp2 group by job_desc order by 2 desc
                                             *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

Dans mon exemple je pourrais m’en sortir en convertissant mon champ CLOB en VARCHAR2. Ceci n’est possible bien sûr que si mon CLOB ne fait pas plus de 4000 octects :
SQL> col jd for A20
SQL> select to_char(job_desc) jd, count(1) from emp2 group by to_char(job_desc) order by 2 desc;

JD                     COUNT(1)
-------------------- ----------
CLERK                         4
SALESMAN                      4
MANAGER                       3
ANALYST                       2
PRESIDENT                     1

Cette restriction fait partie d’un ensemble de restrictions d’utilisation de la clause GROUP BY. Ces restrictions sont listées dans la doc oracle « SQL Language reference » (chapitre 19) :
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2065646

This clause is subject to the following restrictions:
• You cannot specify LOB columns, nested tables, or varrays as part of expr.
• The expressions can be of any form except scalar subquery expressions.
• If the group_by_clause references any object type columns, then the query will not be parallelized.

mercredi 9 mars 2011

Ignorer les hints ( _OPTIMIZER_IGNORE_HINTS)

A la question "est-il possible de dire au CBO d’ignorer les hints ? " la réponse est OUI.
Il existe en effet un paramètre caché nommé _OPTIMZER_IGNORE_HINTS qui par défaut est à FALSE et qui peut être mis à TRUE si l’on veut que l’optimiseur ne prenne pas en considération lors du parsing les hints contenus dans les requêtes SQL.

Cette astuce peut être utile notamment après un upgrade de votre base Oracle pour déterminer si les hints présents dans la version précédente de la base sont toujours nécessaires dans la nouvelle version. En combinant cette astuce avec SQL Performance Analyzer (fonctionnalité du Real Application Testing disponible en 11g seulement) vous pouvez très rapidement déterminer si en supprimant les hints les requêtes s’exécutent mieux ou moins bien après le passage en 11g.

Exemple:

Voici un petit test case très simple permettant de constater l' efficacité du paramètre _OPTIMZER_IGNORE_HINTS :

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index t1_idx on t1(object_id);

Index created.

SQL> select count(1) from t1;

  COUNT(1)
----------
     50574

SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from t1 where object_id=20;

Explained.

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

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=20)


SQL> explain plan for select /*+ full(t1) */ * from t1 where object_id=20;

Explained.

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   157   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   157   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=20)

13 rows selected.


L’exemple précédent montre que le hint FULL(T1) force le Full Table Scan.
Sans le hint le CBO choisit un accès indexé.

Activons maintenant le paramètre _OPTIMZER_IGNORE_HINTS au niveau de la session:
SQL> alter session set "_optimizer_ignore_hints"=TRUE;

Session altered.

SQL> explain plan for select /*+ full(t1) */ * from t1 where object_id=20;

Explained.

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

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=20)

On s’aperçoit que le CBO a bien ignoré le hint qui pourtant fonctionnait bien lorsque le paramètre caché _OPTIMZER_IGNORE_HINTS était à sa valeur par défaut à savoir FALSE.

On peut aussi désactiver ce paramètre uniquement au niveau de la requête via un hint OPT_PARAM.
On a donc là un hint permettant de désactiver les hints. N’est-ce pas magnifique ?

SQL> explain plan for select /*+ opt_param('_optimizer_ignore_hints' 'TRUE') full(t1) */ * from t1 where object_id=20;

Explained.

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


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=20)


Si on met le OPT_PARAM après le hint FULL, le hint n’est alors pas ignoré. J’avoue avoir été surpris lorsque j’ai fait le test :

SQL> explain plan for select /*+ full(t1) opt_param('_optimizer_ignore_hints' 'TRUE') */ * from t1 where object_id=20;

Explained.

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   157   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   157   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=20)