L'index bitmap de jointure
Les bases de données décisionnelles sont structurées selon un modèle en étoile. Une étoile représente généralement un métier dans le cadre du système d’information décisionnel. Ainsi la table centrale est la table de métriques, toutes les informations quantitatives, la quantité des produits, le prix unitaire, etc. La table centrale, est jointe aux autres tables qui continents les sujets d’analyse, appelées les tables dimensions. Tous les sujets d’analyse sont stockés dans les tables de dimensions ; ainsi les libellés qui seront très souvent utilisés dans les clauses « WHERE » pour les filtres, se trouvent dans d’autres tables que celle interrogée. Si vous créez un index sur la table « INDICATEURS » et vous utilisez dans les clauses « WHERE » des champs des tables dimensions, l’index ne sera jamais utilisé.
L’index bitmap de jointure est un index de type bitmap qui permet d’indexer un ou plusieurs champs d’une ou plusieurs autres tables qui sont jointes à la table indexée. La table indexée est généralement la table des métriques et les tables qui fournissent les champs qui sont indexés sont les tables dimensions. Voici la syntaxe pour pouvoir créer un index bitmap de jointure :
CREATE BITMAP INDEX nom_index
ON [SCHEMA.]nom_table_indexée
( tj.champ_table_jointe [ASC | DESC].[,...])
FROM nom_table_indexée ti, nom_table_jointe tj[,...]
WHERE ti.clé_étrangère = tj.clé_primaire [and ...] ...;
La clause « FROM » et la clause « WHERE » dans l'instruction « CREATE » permettent au système de faire le lien entre les tables.
Les exemples de cette partie du module sont pris sur un ensemble des plusieurs tables structurées dans un modèle en étoile. La table des métriques dans notre exemple est la table « INDICATEURS », les autres tables étant les dimensions d’analyse. Voici dans l’image suivante la présentation graphique du modèle physique de données.
La table « INDICATEURS » est fréquemment interrogée avec plusieurs critères de filtre qui sont situés dans les tables : « DIM_PRODUIT », « DIM_GEOGRAPHIE » et « DIM_TEMPS ». Voici un exemple de requête avec les filtres dans la clause « WHERE ».
STAG@agate>select i.* from indicateurs i, dim_temps t,
2 dim_geographie g, dim_produits p
3 where i.commande = t.jour
4 and i.id_geographie = g.id_geographie
5 and i.ref_produit = p.ref_produit
6 and p.nom_categorie = 'Desserts'
7 and g.pays = 'France'
8 and t.mois = 'Septembre';
13917 ligne(s) sélectionnée(s).
Statistiques
----------------------------------------------------------
0 recursive calls
0 db block gets
56596 consistent gets
18888 physical reads
...
La requête est très consommatrice des ressources car il n’est pas possible en l’état de profiter des index de la table « INDICATEURS ». Voici la création de l’index bitmap de jointure qui peut résoudre le problème posé par cette situation.
STAG@agate>create bitmap index ind_cli_geo_prod
2 on indicateurs( t.mois, g.pays, p.nom_categorie)
3 from indicateurs i, dim_temps t,
4 dim_geographie g, dim_produits p
5 where i.commande = t.jour
6 and i.id_geographie = g.id_geographie
7 and i.ref_produit = p.ref_produit
8 tablespace itp_star;
Index créé.
STAG@agate>select i.* from indicateurs i, dim_temps t,
2 dim_geographie g, dim_produits p
3 where i.commande = t.jour
4 and i.id_geographie = g.id_geographie
5 and i.ref_produit = p.ref_produit
6 and p.nom_categorie = 'Desserts'
7 and g.pays = 'France'
8 and t.mois = 'Septembre' ;
13917 ligne(s) sélectionnée(s).
Statistiques
----------------------------------------------------------
0 recursive calls
0 db block gets
4144 consistent gets
2 physical reads
STAG@agate>select i.* from indicateurs i, dim_temps t,
2 dim_geographie g, dim_produits p
3 where i.commande = t.jour
4 and i.id_geographie = g.id_geographie
5 and i.ref_produit = p.ref_produit
6 and p.nom_categorie = 'Desserts'
7 and g.pays = 'France'
8 and t.mois = 'Septembre'
9 and acquitee = 0;
123 ligne(s) sélectionnée(s).
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID | INDICATEURS |
| 2 | BITMAP CONVERSION TO ROWIDS| |
| 3 | BITMAP AND | |
|* 4 | BITMAP INDEX SINGLE VALUE| IND_BMP_ACQUITEE |
|* 5 | BITMAP INDEX SINGLE VALUE| IND_CLI_GEO_PROD |
---------------------------------------------------------
Les index bitmap de jointure peuvent être combinés avec les autres index de type bitmap de la table comme vous pouvez l’observer dans l’exemple précédent.
L’index bitmap de jointure créé est un index qui joint trois tables à la table « INDICATEURS » principale. Comme il est complexe, l’optimiseur ne l’utilise pas très souvent si toutes les tables ne sont pas jointes. Ainsi il est peut-être préférable de créer plusieurs index joignant seulement deux tables à la fois, de la sorte l’optimiseur choisi les combinaisons les plus discriminantes. Voici la création des trois autres index et les requêtes qui mettent en évidence leur utilisation. Toujours entre la table des métriques « INDICATEURS » et les tables des dimensions.
STAG@agate>create bitmap index ind_geo
2 on indicateurs( g.pays, g.ville)
3 from indicateurs i, dim_geographie g
4 where i.id_geographie = g.id_geographie
5 tablespace itp_star;
Index créé.
STAG@agate>create bitmap index ind_prod
2 on indicateurs( p.nom_categorie)
3 from indicateurs i, dim_produits p
4 where i.ref_produit = p.ref_produit
5 tablespace itp_star;
Index créé.
STAG@agate>create bitmap index ind_annee_trim
2 on indicateurs( t.annee, t.trimestre)
3 from indicateurs i, dim_temps t
4 where i.commande = t.jour
5 tablespace itp_star;
Index créé.
STAG@agate>select i.* from indicateurs i, dim_geographie g,
2 dim_produits p, dim_employes e, dim_temps t
3 where i.id_geographie = g.id_geographie
4 and i.ref_produit = p.ref_produit
5 and i.no_employe = e.no_employe
6 and i.commande = t.jour
7 and g.pays = 'Allemagne'
8 and g.ville = 'Aachen'
9 and p.nom_categorie = 'Desserts'
10 and t.annee = 2009
11 and t.trimestre = 2;
270 ligne(s) sélectionnée(s).
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID | INDICATEURS |
| 2 | BITMAP CONVERSION TO ROWIDS| |
| 3 | BITMAP AND | |
|* 4 | BITMAP INDEX SINGLE VALUE| IND_ANNEE_TRIM |
|* 5 | BITMAP INDEX SINGLE VALUE| IND_GEO |
|* 6 | BITMAP INDEX SINGLE VALUE| IND_PROD |
--------------------------------------------------------
STAG@agate>select i.* from indicateurs i, dim_geographie g,
2 dim_produits p, dim_employes e, dim_temps t
3 where i.id_geographie = g.id_geographie
4 and i.ref_produit = p.ref_produit
5 and i.no_employe = e.no_employe and i.commande = t.jour
6 and g.pays in ('Argentine','Autriche')
7 and p.nom_categorie = 'Desserts' and t.annee = 2009;
3656 ligne(s) sélectionnée(s).
--------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | TABLE ACCESS FULL | DIM_GEOGRAPHIE |
| 3 | TABLE ACCESS BY INDEX ROWID | INDICATEURS |
| 4 | BITMAP CONVERSION TO ROWIDS| |
| 5 | BITMAP AND | |
| 6 | BITMAP MERGE | |
|* 7 | BITMAP INDEX RANGE SCAN | IND_ANNEE_TRIM |
| 8 | BITMAP OR | |
| 9 | BITMAP MERGE | |
|* 10 | BITMAP INDEX RANGE SCAN| IND_GEO |
| 11 | BITMAP MERGE | |
|* 12 | BITMAP INDEX RANGE SCAN| IND_GEO |
|* 13 | BITMAP INDEX SINGLE VALUE| IND_PROD |
--------------------------------------------------------
STAG@agate>select * from indicateurs i, dim_temps t,
2 dim_geographie g, dim_produits p
3 where i.commande = t.jour
4 and i.id_geographie = g.id_geographie
5 and i.ref_produit = p.ref_produit
6 and g.pays = 'France'
7 and p.nom_categorie = 'Desserts'
8 and acquitee = 0;
1830 ligne(s) sélectionnée(s).
------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | HASH JOIN | |
|* 3 | TABLE ACCESS FULL | DIM_PRODUITS |
|* 4 | HASH JOIN | |
|* 5 | TABLE ACCESS FULL | DIM_GEOGRAPHIE |
| 6 | TABLE ACCESS BY INDEX ROWID | INDICATEURS |
| 7 | BITMAP CONVERSION TO ROWIDS| |
| 8 | BITMAP AND | |
|* 9 | BITMAP INDEX SINGLE VALUE| IND_BMP_ACQUITEE |
| 10 | BITMAP MERGE | |
|* 11 | BITMAP INDEX RANGE SCAN | IND_GEO |
|* 12 | BITMAP INDEX SINGLE VALUE| IND_PROD |
| 13 | TABLE ACCESS FULL | DIM_TEMPS |
------------------------------------------------------------