Tout le monde connaît la puissance des tables externes, apparues avec la 9i.
Il suffit de lancer un create table particulier et les données fournies sous forme d’un simple fichier plat sont immédiatement accessibles en lecture via un ordre select. Le seul problème des tables externes, c’est que la notion d’index n’existe pas …
Prenons l’exemple d’une société Lambda possédant 3 filiales ABC, DEF et GHI. Régulièrement, la société reçoit un fichier plat comprenant 3 champs ID0, ID1, ID2. ID0 correspond au code filiale, ID1 au code service et ID2 à une valeur d’information.
Comme je souhaite simplement accéder rapidement à mes données en lecture seule, je décide de créer une table externe sur ce fichier plat. Seul hic, lorsque je veux effectuer des requêtes qui ne concernent qu’une filiale, je balaye l’ensemble des données du fichier. Et comme de juste, le fichier en question contient beaucoup de lignes.
Je décide donc de découper mon fichier en 3 au moyen d’une commande OS et de créer 3 tables externes, une par filiale. Je peux ainsi interroger chacune des tables pour ne plus balayer l’intégralité des données et gagner un peu de temps.
CREATE OR REPLACE DIRECTORY DATA_DIR AS '/home/oracle'; drop table ext_table_test1 purge; drop table ext_table_test2 purge; drop table ext_table_test3 purge; CREATE TABLE EXT_TABLE_TEST1 ( ID0 CHAR(3) , ID1 CHAR(10) , ID2 CHAR(10) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY DATA_DIR ACCESS PARAMETERS ( records delimited by newline nobadfile nodiscardfile logfile 'ext_table_test1.log' fields terminated by ";" ) LOCATION (DATA_DIR:'test1.txt') ) REJECT LIMIT UNLIMITED NOPARALLEL; CREATE TABLE EXT_TABLE_TEST2 ( ID0 CHAR(3) , ID1 CHAR(10) , ID2 CHAR(10) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY DATA_DIR ACCESS PARAMETERS ( records delimited by newline nobadfile nodiscardfile logfile 'ext_table_test2.log' fields terminated by ";" ) LOCATION (DATA_DIR:'test2.txt') ) REJECT LIMIT UNLIMITED NOPARALLEL; CREATE TABLE EXT_TABLE_TEST3 ( ID0 CHAR(3) , ID1 CHAR(10) , ID2 CHAR(10) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY DATA_DIR ACCESS PARAMETERS ( records delimited by newline nobadfile nodiscardfile logfile 'ext_table_test3.log' fields terminated by ";" ) LOCATION (DATA_DIR:'test3.txt') ) REJECT LIMIT UNLIMITED NOPARALLEL;
Je crée ensuite une vue sur les 3 tables au moyen d’un union all dans l’optique de pouvoir effectuer des requêtes indifféremment par filiale ou sur l’ensemble des données de l’entreprise.
-- On crée la vue qui donne une vue d'ensemble des tables externes create or replace view v_ext_table_2_all as select * from ext_table_test1 union all select * from ext_table_test2 union all select * from ext_table_test3; -- On execute l'interrogation de la "partition DEF" -- en utilisant la première vue basée sur les tables set timing on select count(*) from v_ext_table_2_all where id0='DEF'; COUNT(*) ---------- 10000000 Elapsed: 00:00:11.82
Je constate au moyen des logs de tables externes que malgré la clause where, les 3 tables sont bien accédées !
-rw-r--r-- 1 oracle asmadmin 1156 May 24 14:42 /home/oracle/ext_table_test1.log -rw-r--r-- 1 oracle asmadmin 2890 May 24 14:42 /home/oracle/ext_table_test2.log -rw-r--r-- 1 oracle asmadmin 1156 May 24 14:42 /home/oracle/ext_table_test3.log
Comme je m’y attendais, je dois me résigner à continuer d’interroger directement les tables externes si je souhaite n’accéder qu’aux données d’une seule filiale. Si seulement je pouvais indiquer à l’optimiseur de ne lire que la table externe concernée …
C’est chose faite avec une fonctionnalité antédiluvienne d’Oracle appelée partitionnement logique.
-- Maintenant, on crée une vue sur chacune des tables externe -- en valorisant la clause where avec la "clé de partitionnement" ... create or replace view v_ext_table_test1 as select * from ext_table_test1 where id0 = 'ABC'; create or replace view v_ext_table_test2 as select * from ext_table_test2 where id0 = 'DEF'; create or replace view v_ext_table_test3 as select * from ext_table_test3 where id0 = 'GHI'; -- ... et on crée une nouvelle vue mais cette fois basée sur les vues ci-desssus create or replace view v_ext_table_all as select * from v_ext_table_test1 union all select * from v_ext_table_test2 union all select * from v_ext_table_test3; -- On exécute l'interrogation de la "partition DEF" -- en utilisant l'autre vue basée sur les vues select count(*) from v_ext_table_all where id0='DEF'; COUNT(*) ---------- 10000000 Elapsed: 00:00:04.03
Je constate que seule la table des enregistrements ‘DEF’ a été accédée, avec au passage un gain substantiel de temps !
-rw-r--r-- 1 oracle asmadmin 578 May 24 14:42 /home/oracle/ext_table_test2.log
C’est cette différence qui fait que la première requête est 3 fois plus lente que la seconde. Explications …
explain plan for
select * from v_ext_table_2_all where ID0 = 'DEF';
@?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 491587757
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2002 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2002 | | |
| 2 | VIEW | V_EXT_TABLE_2_ALL | 1 | 2002 | 2 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | EXTERNAL TABLE ACCESS FULL| EXT_TABLE_TEST1 | 82 | 160K| 29 (0)| 00:00:01 |
|* 5 | EXTERNAL TABLE ACCESS FULL| EXT_TABLE_TEST2 | 82 | 160K| 29 (0)| 00:00:01 |
|* 6 | EXTERNAL TABLE ACCESS FULL| EXT_TABLE_TEST3 | 82 | 160K| 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID0"='DEF')
5 - filter("ID0"='DEF')
6 - filter("ID0"='DEF')
20 rows selected.
L’union all accède aux 3 tables.
explain plan for
select * from v_ext_table_all where id0 = 'DEF';
@?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 83388990
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2002 | 2 (0)| 00:00:01 |
| 1 | VIEW | V_EXT_TABLE_ALL | 1 | 2002 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | EXTERNAL TABLE ACCESS FULL| EXT_TABLE_TEST1 | 82 | 160K| 29 (0)| 00:00:01 |
|* 5 | EXTERNAL TABLE ACCESS FULL | EXT_TABLE_TEST2 | 82 | 160K| 29 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | EXTERNAL TABLE ACCESS FULL| EXT_TABLE_TEST3 | 82 | 160K| 29 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NULL IS NOT NULL)
4 - filter("ID0"='ABC')
5 - filter("ID0"='DEF')
6 - filter(NULL IS NOT NULL)
7 - filter("ID0"='GHI')
23 rows selected.
Le partitionnement logique (ou View Partition) de l’optimiseur reconnait que la clause where correspond à celle de la deuxième vue. Les 2 autres tables ne sont donc pas accédées, ce qui n’est pas sans rappeler la notion de pruning des tables partitionnées.
