Index bitmap de jointure

[pandore1983]

Bonjour
Je suis débutant et je travaille sous oracle 9i Personal Edition V9.2.0.1. Afin d’optimiser le temps d’exécution de certaines requêtes comme :

Code :

1
2
3
4
SELECT sales.time_id, sum(quantity_sold), sum (amount_sold)
 FROM sales, times 
WHERE sales.time_id = times.time_id AND times.fiscal_year ='2000' 
GROUP BY sales.time_id

j’ai créé un index bitmap de jointure avec la requête suivante :

Code :

1
2
3
4
CREATE bitmap INDEX BIJ_Index1
ON sales( times.fiscal_year)
FROM times, sales
WHERE times.time_id=sales.time_id;

Après sa création, et en exécution ma requête SELECT j’ai remarqué que cet index ne figure pas sur son plan d’exécution. Est-ce que quelqu’un pourrait me dire ce que je dois faire pour que l’index soit utilisé dans la résolution de cette requête.
Merci .

[orafrance]

un bitmap de jointure ??? Mais c'est n'importe quoi... pourquoi tu fais pas un index classique ?

[McM]

Je ne connaissais pas ce genre d'index

[pandore1983]

Bonjour
Les index bitmap de jointure (appelés aussi index de jointure binaires) sont les plus appropriés aux entrepôts de données, c'est ceux là que je dois créer; je n'ai pas le choix.
Merci quand même!

[pandore1983]

J'ajoute un petit quelque chose.
Les index bitmap de jointure sont intersessants car ils accelèrent
l'exécution des requêtes qui font intervenir des opérations de jointure
qui sont les plus coûteuses dans les entrepôts car elles opèrent
sur une table faits très volumineuse et des tables dimensions de taille moins
importante.
Dernière chose, vous pouvez aller voir ça
http://articles.techrepublic.com.com...2-1051931.html
Cordialement.

[pifor]

Pour analyser complètement un problème de performance, il faudrait avoir le maximum d'éléments de la liste suivante:

• le code SQL de création de la table et des index sur la table
• la cardinalité de la table
• la façon dont les statistiques sont calculées sur la table et les index
• le plan d'exécution de la requête avec EXPLAIN PLAN
• le plan d'exécution de la requête avec la trace SQL et TKPROF ou le résultat de SET AUTOTRACE
• quel est le temps d'exécution souhaité

Je ne connaissais pas non plus les bitmap join index qui existent depuis la version 9 et qui sont très peu référencés dans les forums OTN. (idem sur AskTom).

[pifor]

Envoyé par pandore1983

J'ajoute un petit quelque chose.
Les index bitmap de jointure sont intersessants car ils accelèrent
l'exécution des requêtes qui font intervenir des opérations de jointure
qui sont les plus coûteuses dans les entrepôts car elles opèrent
sur une table faits très volumineuse et des tables dimensions de taille moins
importante.
Dernière chose, vous pouvez aller voir ça
http://articles.techrepublic.com.com...2-1051931.html
Cordialement.

Sans doute, mais combien de lignes avez-vous dans vos tables sur votre base ? Si vous avez des petits volumes, Oracle va faire des full tables scan tout simplement parce que s'il y a peu de données, c'est la méthode la plus rapide (quelques soient les index qui peuvent exister)...

Avez-vous lu certains chapitres du Concepts Guide et du Performance Tuning Guide ?

[pandore1983]

Ce que j'utilise n'est qu'une base de données tests.
Au fait je suis en train de travailler sur un outil de sélection d'index.
Mon application exploite le fichier log d'une base pour en extraire les attributs les plus fréquemment
utilisés dans les clauses: where, group by et having pour proposer des index.
Ces attributs sont obtenus en faisant appel à une technique de data mining qui est l'extraction des itemset fermés fréquents.
Ce travail a déjà été fait mais dans un cadre statique. Mon application utilise un algorithme d'extraction incrémentale d'itemset,
ce qui rend mon application dynamique (c'est à dire qu'elle prend en compte l'évolution du log).
Arrivée à la phase expérimentale, et je me rends compte que les index bitmap de jointure ( c'est ceux -là que je dois créer) ne sont pas utilisés
pour la résolution de mes requêtes.Pourtant, lorsque je crée d'autres types d'index, je remarque qu'ils sont utilisés!!!

[jmguiche]

Bonjour,

Tu n’es jamais "obligé" de faire quoi que ce soit.

Ta requête est une requête particulièrement simple qui met 2 tables en jointure. Bref, typique d'une optimisation avec des boucles sur un index btree ou du hash join ou autre.

Des index bitmap sont utiles quand tu as une table de "faits" autour de laquelle gravitent des tas de dimensions peu discriminantes prises isoléments.
Quand une requête porte sur plusieurs dimensions "bitmapées" , l'optimiser peut effectuer des opérations de sélection sur les bitmap.
D'ailleurs, le lien que tu donne utilise une requete avec 2 critères de recherche.
Remarque : la maintenance des index bitmap, c'est lourd...

Bref, c'est tout sauf le cas que tu présente me semble t il. Les index bitmap ne sont pas adaptés à ta requête. Tu n'est donc pas "obligé" d'utiliser des index bitmap, il me semble plutôt qu'il est souhaitable que tu utilise des index btree.



Si ta base est une base de test avec des volumes et des « discriminances » de données non significative, le plan d'exécution n'est pas forcement représentatif de ce qui se passera en prod.

@+

[Scual]

Bonjour,

Juste une question, as-tu essayé d'utiliser des hints dans la requête SQL ?

++

[orafrance]

Un index de jointure bitmap ne peut être intéressant que si 2 conditions sont réunies :
- Jointure avec une table référenciel qui ne bouge JAMAIS (et à mon avis, c'est même mieux qu'aucune des 2 tables n'évoluent parce que les mises à jour doivent faire très mal )
- tu as une cardinalité très faible (peu de lignes dans une table par rapport à l'autre).

Si tu ne remplies pas ces conditions, tu fais un index de jointure B-Tree.

En plus, tu violes la restriction de l'article que tu nous proposes puisque tu ajoutes une condition au WHERE. C'est pour ça que tu ne peux pas utiliser l'index

Exclusions for bitmap join indexes
There are also restrictions on when the SQL optimizer is allowed to invoke a bitmap join index. For queries that have additional criteria in the WHERE clause that doesn't appear in the bitmap join index, Oracle9i will be unable to use this index to service the query.

Il faut rappeler que le Bitmap doit être utilisé que dans des cas TRES particulier... essaye donc en B-tree, j'suis sûr que ça suffira

Conclusion : il NE FAUT PAS que tu utilises le bitmap... CQFD

[laurentschneider]

l'exemple est tiré de la doc, c'est le genre d'exemple typique en DW où l'on employe un bitmap index de jointure.

Est-ce que tu as les droits QUERY REWRITE?

Sinon

Code :

1
2
 
GRANT QUERY REWRITE TO scott;

ensuite quels sont tes paramètres query_rewrite_enabled et query_rewrite_integrity ?

A+
Laurent

[laurentschneider]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE TABLE time_id (time_id number, fiscal_year varchar2(4));
CREATE TABLE times (time_id number PRIMARY KEY, fiscal_year varchar2(4));
CREATE bitmap INDEX bij_index1 ON sales(times.fiscal_year) 
  FROM times,sales WHERE times.time_id=sales.time_id;
 
SELECT sales.time_id, sum(quantity_sold), sum (amount_sold) 
FROM sales, times 
WHERE sales.time_id = times.time_id AND times.fiscal_year ='2000' 
GROUP BY sales.time_id;
 
 
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     1 |    39 |     2  (50)| 00:00:01 |
|   1 |  HASH GROUP BY                |            |     1 |    39 |     2  (50)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | SALES      |     1 |    39 |     1   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS|            |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | BIJ_INDEX1 |       |       |            |          |
--------------------------------------------------------------------------------------------

la table TIMES n'est pas scannée, donc l'index fonctionne parfaitement

[pifor]

C'est testé en 9.2.0.1 ?

[pandore1983]

Bonjour,
La restriction dont il est question dans l'article que je vous ai proposé porte sur les attributs qui figurent dans la clause where.
Ma requête ne fait intervenir dans celle-ci que des attributs figurant dans l'index .
Le plan d'exécution que j'obtiens m'informe que les tables Sales et times ont été utilisées ainsi que 2 index B-tree que le système a crés pour les deux table sur leurs clés primaires.
Je conviens sur le fait que l'exemple que j'ai pris est simple et qu'il nécessite pas un index bitmap de jointure, les autres requêtes de ma charge sont plus compliquées que ça

Code :

1
2
3
4
5
6
7
8
9
SELECT sales.cust_id, avg(amount_sold)
 FROM sales, customers, products, times 
WHERE sales.cust_id = customers.cust_id 
AND sales.prod_id = products.prod_id 
AND sales.time_id = times.time_id 
AND times.fiscal_year ='2000' 
AND customers.cust_marital_status ='married' 
AND products.prod_category ='Women' 
GROUP BY sales.cust_id

Je vais essayé avec les index que vous me proposez.
Merci beaucoup.
cordialement,
Pandore

[rouardg]

Bonjour à tous,

Pandore1983, as-tu généré les statistiques dans ton exemple ?

En effet, si je créée la table TIMES avec sa PK, et la table SALES avec son bitmap join index, voici ce que j'obtiens sans les stats (test réalisé sur une base Enterprise Edition 9.2.0.6.0) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
>CREATE TABLE times (time_id number PRIMARY KEY, fiscal_year varchar2(4));
 
TABLE créée.
 
>
>CREATE TABLE sales (time_id number, quantity_sold number, amount_sold number);
 
TABLE créée.
 
>
>CREATE bitmap INDEX bij_index1 ON sales(times.fiscal_year) 
  2    FROM times,sales WHERE times.time_id=sales.time_id;
 
INDEX créé.
 
 
>SET autotrace ON
 
>SELECT sales.time_id, sum(quantity_sold), sum (amount_sold) 
  2  FROM sales, times 
  3  WHERE sales.time_id = times.time_id AND times.fiscal_year ='2000' 
  4  GROUP BY sales.time_id;
 
aucune ligne sélectionnée
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'SALES'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'TIMES'
   5    4         INDEX (UNIQUE SCAN) OF 'SYS_C00213102' (UNIQUE)

Maintenant, si je génère les statistiques, et que je regarde à nouveau le plan d'exécution :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
>exec dbms_stats.gather_table_stats (user, 'times', estimate_percent => NULL, cascade => true) ;
 
Procédure PL/SQL terminée avec succès.
 
>exec dbms_stats.gather_table_stats (user, 'sales', estimate_percent => NULL, cascade => true) ;
 
Procédure PL/SQL terminée avec succès.
 
>SELECT sales.time_id, sum(quantity_sold), sum (amount_sold) 
  2  FROM sales, times 
  3  WHERE sales.time_id = times.time_id AND times.fiscal_year ='2000' 
  4  GROUP BY sales.time_id;
 
aucune ligne sélectionnée
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=39)
   1    0   SORT (GROUP BY) (Cost=8 Card=1 Bytes=39)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SALES' (Cost=1 Card=1
          Bytes=39)
 
   3    2       BITMAP CONVERSION (TO ROWIDS)
   4    3         BITMAP INDEX (SINGLE VALUE) OF 'BIJ_INDEX1'

Pour finir, je pense personnellement que Pandore1983 a raison d'utiliser les bitmap join index (ou au moins les bitmap index).

N'oublions pas qu'il travaille sur un entrepôt de données, sur un modèle en étoile (au moins 3 tables de dimension CUSTOMERS, PRODUCTS, TIMES) et SALES comme table de fait.

Sur ce genre de modélisation, les requêtes, qui sont souvent des aggrégats comme le prouve le dernier exemple de Pandore1983, sont performantes si elles ont un plan d'exécution en Star Query (requête étoile).

Cela veut dire :
1) des tables de dimension possédant des PK,
2) une table de fait sur laquelle on pose des index bitmap sur les ID. Voir aussi selon ses besoins si l'on pose des bitmaps join index,
3) des contraintes de FK posées de préférence en VALIDATE, ENABLE et RELY. Maintenant, si la vérification des FK est trop contraignante, ou trop lente, on peut les passer en DISABLE et NOVALIDATE,
4) des statistiques calculées sur les tables et ses index,
5) un plan d'exécution en Star Query. Il faut alors peut-être faire un :

ALTER SESSION SET STAR_TRANSFORMATION_ENABLED = TRUE ;

[laurentschneider]

Envoyé par rouardg

je pense personnellement que Pandore1983 a raison d'utiliser les bitmap join index [...]
N'oublions pas qu'il travaille sur un entrepôt de données

idem

[pandore1983]

Bonjour tout le monde

Oui j'ai du générer les statistiques pour affichier le coût dans mon plan d'exécution.
J'ai aussi activé les transformations en étoile car sans ça, l'index bitmap ne sera pas utilisé.
Mais j'obtiens toujours le même plan.

[rouardg]

Pandore1983,

Ce serait bien que tu nous donnes ton plan d'exécution, et que tu nous donnes qq idées sur la cardinalité des lignes attendus.

En effet, ce n'est pas parce que tu as activé l'option des requêtes étoiles, que le plan d'exécution va coller à cette option.

En effet, l'optimiseur Oracle calcule d'une part le coût pour un plan en étoile, et d'autre part calcule aussi le coût pour un plan classique. Au final, il retiend le meilleur des 2.

Si tu ne vois pas l'utilisation de ton bitmap join index, c'est peut-être parce qu'il n'est pas assez sélectif.

En fait, il faudrait savoir combien il y a de lignes au total dans SALES, et combien de lignes répondent au critère Année Fiscale = 2000.

Idem pour la table TIME : combien de lignes au total, et combien de lignes répondant au critère Année Fiscale = 2000 ?

[pandore1983]

Bonjour

Code :

1
2
3
4
5
6
 
 TABLE      |Nb total de lignes     |Nb de lignes vérifiant fiscal_year ='2000'
 -----------------------------------------------------------------
  Sales     |    1016271             |          328                              
 -----------------------------------------------------------------
  Times     |       1461              |          371

Le plan d'exécution (pour le premier exemple) que j'obtiens lorsque je force l'utilisation de mon index est

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
>SELECT /*+ INDEX_COMBINE (sales) STAR */ sales.time_id,
sum(quantity_sold), sum (amount_sold)
FROM sales, times
WHERE sales.time_id = times.time_id AND times.fiscal_year ='2000'
GROUP BY sales.time_id;
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2928 Card=1030 Bytes
          =15450)
 
   1    0   SORT (GROUP BY) (Cost=2928 Card=1030 Bytes=15450)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SALES' (Cost=2421 Card
          =203254 Bytes=3048813)
 
   3    2       BITMAP CONVERSION (TO ROWIDS)
   4    3         BITMAP INDEX (SINGLE VALUE) OF 'BIJ_INDEX1'

Sans forçage j'obtiens

Code :

1
2
3
4
5
6
7
8
9
10
11
12
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1341 Card=1030 Bytes
          =26780)
 
   1    0   SORT (GROUP BY) (Cost=1341 Card=1030 Bytes=26780)
   2    1     HASH JOIN (Cost=305 Card=288305 Bytes=7495930)
   3    2       TABLE ACCESS (FULL) OF 'TIMES' (Cost=2 Card=292 Bytes=
          3212)
 
   4    2       TABLE ACCESS (FULL) OF 'SALES' (Cost=299 Card=1016271
          Bytes=15244065)

Pour le dernier exemple , j'ai construit l'index et j'ai obtenu ce plan:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
> CREATE bitmap INDEX BIJ_Index8
  ON sales( times.fiscal_year, products.prod_category, customers.cust_marital_status)
  FROM sales, times, customers, products
  WHERE sales.time_id=times.time_id 
  AND products.prod_id = sales.prod_id 
  AND customers.cust_id = sales.cust_id ;
INDEX créé.
 
> SELECT sales.cust_id, avg(amount_sold) 
  FROM sales, customers, products, times 
  WHERE sales.cust_id = customers.cust_id 
  AND sales.prod_id = products.prod_id 
  AND sales.time_id = times.time_id 
  AND times.fiscal_year ='2000' AND 
  customers.cust_marital_status ='married' 
  AND products.prod_category ='Women' 
  GROUP BY sales.cust_id
 
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1496 Card=1569 Bytes
          =86295)
 
   1    0   SORT (GROUP BY) (Cost=1496 Card=1569 Bytes=86295)
   2    1     HASH JOIN (Cost=553 Card=143521 Bytes=7893655)
   3    2       TABLE ACCESS (FULL) OF 'CUSTOMERS' (Cost=9 Card=25000
          Bytes=275000)
 
   4    2       HASH JOIN (Cost=332 Card=143521 Bytes=6314924)
   5    4         TABLE ACCESS (FULL) OF 'PRODUCTS' (Cost=12 Card=2500
           Bytes=27500)
 
   6    4         HASH JOIN (Cost=306 Card=288305 Bytes=9514065)
   7    6           TABLE ACCESS (FULL) OF 'TIMES' (Cost=2 Card=292 By
          tes=3212)
 
   8    6           TABLE ACCESS (FULL) OF 'SALES' (Cost=299 Card=1016
          271 Bytes=22357962)

Avec forçage

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
> SELECT /*+ INDEX_COMBINE (sales) STAR */sales.cust_id, avg(amount_sold) 
  FROM sales, customers, products, times 
  WHERE sales.cust_id = customers.cust_id 
  AND sales.prod_id = products.prod_id 
  AND sales.time_id = times.time_id 
  AND times.fiscal_year ='2000' AND 
  customers.cust_marital_status ='married' 
  AND products.prod_category ='Women' 
  GROUP BY sales.cust_id;
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5420 Card=1569 Bytes
          =69036)
 
   1    0   SORT (GROUP BY) (Cost=5420 Card=1569 Bytes=69036)
   2    1     HASH JOIN (Cost=2673 Card=505909 Bytes=22259996)
   3    2       TABLE ACCESS (FULL) OF 'PRODUCTS' (Cost=12 Card=2500 B
          ytes=27500)
 
   4    2       HASH JOIN (Cost=2615 Card=1016271 Bytes=33536943)
   5    4         TABLE ACCESS (FULL) OF 'CUSTOMERS' (Cost=9 Card=2500
          0 Bytes=275000)
 
   6    4         TABLE ACCESS (BY INDEX ROWID) OF 'SALES' (Cost=2421
          Card=203254 Bytes=4471592)
 
   7    6           BITMAP CONVERSION (TO ROWIDS)
   8    7             BITMAP INDEX (SINGLE VALUE) OF 'BIJ_INDEX1'

Je n'ai jamais pensé qu'une requête comme cette dernière pourrait être résolue plus efficacement en utilisant autre chose qu'un index bitmap de jointure.
Merci beaucoup