Récuperer le DDL d’une table avec DBMS_METADATA

Lorsque vous devez récupérer le script SQL de définition d’une table je suppose que la majorité d’entre vous utilisent des outils graphiques tels que TOAD ou SQL Developper.

Sachez toutefois que depuis la 9i il est possible de récupérer facilement ces scripts DDL et juste avec une fenêtre SQLPLUS grâce à la fonction GET_DDL du package DBMS_METADATA.

Voyons par exemple comment récupérer le DDL de la table CUSTOMERS du schéma SH d’Oracle :

SQL> SET LONG 50000
SQL> SET PAGES 500
SQL> SET LINES 200
SQL>  SELECT dbms_metadata.get_ddl('TABLE', table_name)
  2  from user_tables
  3  WHERE table_name='CUSTOMERS';
 
DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME)
--------------------------------------------------------------------------------
  CREATE TABLE "SH"."CUSTOMERS"
   (    "CUST_ID" NUMBER NOT NULL ENABLE,
        "CUST_FIRST_NAME" VARCHAR2(20) NOT NULL ENABLE,
        "CUST_LAST_NAME" VARCHAR2(40) NOT NULL ENABLE,
        "CUST_GENDER" CHAR(1) NOT NULL ENABLE,
        "CUST_YEAR_OF_BIRTH" NUMBER(4,0) NOT NULL ENABLE,
        "CUST_MARITAL_STATUS" VARCHAR2(20),
        "CUST_STREET_ADDRESS" VARCHAR2(40) NOT NULL ENABLE,
        "CUST_POSTAL_CODE" VARCHAR2(10) NOT NULL ENABLE,
        "CUST_CITY" VARCHAR2(30) NOT NULL ENABLE,
        "CUST_CITY_ID" NUMBER NOT NULL ENABLE,
        "CUST_STATE_PROVINCE" VARCHAR2(40) NOT NULL ENABLE,
        "CUST_STATE_PROVINCE_ID" NUMBER NOT NULL ENABLE,
        "COUNTRY_ID" NUMBER NOT NULL ENABLE,
        "CUST_MAIN_PHONE_NUMBER" VARCHAR2(25) NOT NULL ENABLE,
        "CUST_INCOME_LEVEL" VARCHAR2(30),
        "CUST_CREDIT_LIMIT" NUMBER,
        "CUST_EMAIL" VARCHAR2(30),
        "CUST_TOTAL" VARCHAR2(14) NOT NULL ENABLE,
        "CUST_TOTAL_ID" NUMBER NOT NULL ENABLE,
        "CUST_SRC_ID" NUMBER,
        "CUST_EFF_FROM" DATE,
        "CUST_EFF_TO" DATE,
        "CUST_VALID" VARCHAR2(1),
         CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY ("CUST_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"  ENABLE NOVALIDATE,
         CONSTRAINT "CUSTOMERS_COUNTRY_FK" FOREIGN KEY ("COUNTRY_ID")
          REFERENCES "SH"."COUNTRIES" ("COUNTRY_ID") ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"

Pour récupérer le script de création de l’index CUSTOMERS_GENDER_BIX on utilise la même logique :

SQL> SELECT dbms_metadata.get_ddl('INDEX', index_name)
  2  from user_indexes
  3  WHERE index_name='CUSTOMERS_GENDER_BIX';

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
-------------------------------------------------------------------------------
 CREATE BITMAP INDEX "SH"."CUSTOMERS_GENDER_BIX" ON "SH"."CUSTOMERS" ("CUST_GEN

DER")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"

En utilisant un sympathique petit SPOOL il vous est possible de créer un script qui va générer par exemple l’ensemble des DDL des objets d’un schéma donné.

Le package DBMS_METADATA fournit bien d’autres procédures et fonctions que je vous invite à découvrir dans la doc Oracle Database Database PL/SQL Packages and Types Reference

FOREIGN KEY et ORA-00054

Un développeur m’a demandé de l’aide aujourd’hui car il ne comprenait pas pourquoi il obtenait l’erreur ORA-00054 alors qu’il souhaitait simplement créer une nouvelle table:

SQL> CREATE TABLE ETF_TRACKING_ERROR_POINT
  2  (
  3    PTR_ID                   NUMBER                NOT NULL,
  4    PTR_FUNDSHARE            NUMBER(10)            NOT NULL,
  5    PTR_DATE                 DATE                  NOT NULL,
  6    PTR_INDEXID              NUMBER(10)            NOT NULL,
  7    PTR_FUNDSHARENAV         FLOAT(126)            NOT NULL,
  8    PTR_INDEXVALUE           FLOAT(126)            NOT NULL,
  9    PTR_BENCHMARKVALUE       FLOAT(126)            NOT NULL,
 10    PTR_INDEXDIV             FLOAT(126)            NOT NULL,
 11    PTR_SPOT                 FLOAT(126)            NOT NULL,
 12    PTR_CALCVALUE            FLOAT(126)            NOT NULL,
 13    PTR_USEDVALUE            FLOAT(126)            NOT NULL,
 14    PTR_TOBEUSEDFLAG         NUMBER(1)   DEFAULT 1 NOT NULL,
 15    PTR_USER                 VARCHAR2(200),
 16    PTR_COMMENT              VARCHAR2(250),
 17    CONSTRAINT PK_ETFTERRORPOINT PRIMARY KEY (PTR_ID),
 18    CONSTRAINT UK_ETFTERRORPOINT UNIQUE (PTR_FUNDSHARE, PTR_DATE) using index tablespace INDXLNS_M01,
 19    CONSTRAINT FK_ETFTERRORPOINT_OBJECT Foreign KEY (PTR_ID) References MUT_OBJECTS (OBJ_ID)
 20  );
 
CREATE TABLE ETF_TRACKING_ERROR_POINT
(
  PTR_ID                   NUMBER                NOT NULL,
  PTR_FUNDSHARE            NUMBER(10)            NOT NULL,
  PTR_DATE                 DATE                  NOT NULL,
  PTR_INDEXID              NUMBER(10)            NOT NULL,
  PTR_FUNDSHARENAV         FLOAT(126)            NOT NULL,
  PTR_INDEXVALUE           FLOAT(126)            NOT NULL,
  PTR_BENCHMARKVALUE       FLOAT(126)            NOT NULL,
  PTR_INDEXDIV             FLOAT(126)            NOT NULL,
  PTR_SPOT                 FLOAT(126)            NOT NULL,
  PTR_CALCVALUE            FLOAT(126)            NOT NULL,
  PTR_USEDVALUE            FLOAT(126)            NOT NULL,
  PTR_TOBEUSEDFLAG         NUMBER(1)   DEFAULT 1 NOT NULL,
  PTR_USER                 VARCHAR2(200),
  PTR_COMMENT              VARCHAR2(250),
  CONSTRAINT PK_ETFTERRORPOINT PRIMARY KEY (PTR_ID),
  CONSTRAINT UK_ETFTERRORPOINT UNIQUE (PTR_FUNDSHARE, PTR_DATE) using index tablespace INDXLNS_M01,
  CONSTRAINT FK_ETFTERRORPOINT_OBJECT Foreign KEY (PTR_ID) References MUT_OBJECTS (OBJ_ID)
)
 
ORA-00054: resource busy and acquire with NOWAIT specified

Le problème ici était forcément dû à une des 2 FK que le développeur souhaitait créer.
En testant séparément la création des 2 FK, j’ai pu conclure que le problème était lié à la table MUT_OBJECTS et que celle-ci devait surement être lockée par une autre session.

Voyons comment résoudre ce problème avec un exemple.

Test Case:

Session 1 :

SQL> create table t1 as select * from user_objects;

Table created.

SQL> alter table t1 add constraint pk_t1 primary key (object_id);

Table altered.

SQL> update t1 set STATUS='OK';

30755 rows updated.

La table T1 est donc lockée par la session 1.


Session 2:

SQL> create table t2 as select * from user_objects where rownum<=10;

Table created.

SQL> alter table t2 add constraint fk_t1 foreign key (object_id) references t1;
alter table t2 add constraint fk_t1 foreign key (object_id) references t1
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Je reproduis donc le même problème que mon développeur aujourd’hui.
Dans mon exemple je sais qu’on a un lock sur T1 dans la session 1 puisque c’est moi qui l’ait généré, mais comment peut-on trouver « Oraclement parlant » la session qui lock la table ?

La solution que j’ai utilisé consiste à requêter la vue v$LOCKED_OBJECT grâce à l’OBEJCT_ID que j’ai récupéré dans USER_OBJECTS. La vue v$LOCKED_OBJECT me donne la liste des locks de type DML en cours et notamment la session bloquante :

SQL> select object_id from user_objects where object_name='T1';

 OBJECT_ID
----------
     70929

SQL> select SESSION_ID from v$locked_object where object_id=70929;

SESSION_ID
----------
        17

Le coupable est donc la session 17.
On peut ensuite requêter la vue V$SESSION pour avoir plus d’informations sur celui ou celle qui lock la table :

SQL> select OSUSER,machine,PROGRAM from v$session where sid=17;

OSUSER                         MACHINE                                                          PROGRAM
------------------------------ ---------------------------------------------------------------- --------------------------------------
EUR\aaangour012711             EUR\FR9124662D                                                   sqlplus.exe

J’ai ainsi le nom du coupable (moi), le nom de la machine cliente et le programme utilisé.
Je peux ensuite prendre mon téléphone et appeler le LOCKEUR pour lui dire qu’il a intérêt à committer ou rollbacker sa transaction sur le champ sinon je lui kill sa session. Bien sûr je ne lui dis pas ça comme ça, mais ça traduit en tout cas ma pensée ;-).

Et effectivement en committant la transaction de la session 1 je peux créer la FK sur la table T2 :

Session 1 :

SQL> commit;

Commit complete.

Session 2 :

SQL> alter table t2 add constraint fk_t1 foreign key (object_id) references t1;

Table altered.

Voilà comment en utilisant les vues dynamiques de performance qu’Oracle met à notre disposition vous pouvez régler des problèmes Oracle de tous les jours.
N’hésitez pas à faire des DESCRIBE sur ces vues pour voir les infos disponibles.
La doc Oracle Database Reference est là pour vous aider à y voir plus claire.

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

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

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$