Discussion :

[pandore1983]

Pour le deuxième exemple ça donne ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
>SELECT /*+INDEX( sales BIJ_Index8)*/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=1323 Card=1569 Bytes
          =34518)
 
   1    0   SORT (GROUP BY) (Cost=1323 Card=1569 Bytes=34518)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SALES' (Cost=1237 Card
          =25407 Bytes=558949)
 
   3    2       BITMAP CONVERSION (TO ROWIDS)
   4    3         BITMAP INDEX (SINGLE VALUE) OF 'BIJ_INDEX8'

[rouardg]

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

Bizarre, bizarre, car même si cette syntaxe est valable, en régle général, sur un modèle en étoile, on ne créé pas sur la table de fait un seul bitmap join index faisant la jointure sur les 4 tables, mais on créé un bitmap join index par table de dimension.

Donc pour ma part, j'aurais plutôt fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE bitmap INDEX bij_index1 ON sales(times.fiscal_year) 
  FROM times,sales WHERE times.time_id=sales.time_id;
 
CREATE bitmap INDEX bij_index2 ON sales(products.prod_category) 
  FROM products, sales WHERE sales.prod_id = products.prod_id;
 
CREATE bitmap INDEX bij_index3 ON sales(customers.cust_marital_status) 
  FROM customers, sales WHERE sales.cust_id = customers.cust_id ;

Peux-tu faire l'essai ?

D'autre part, pour que la requête ait son plan en étoile, tout est une question de sélectivité. Plus les critères d'interrogation sont sélectifs, plus Oracle privilégie les index.

[pandore1983]

Bonjour,

Même si cela vous semble bizarre, vous conviendrez cependant, qu'il ne pourrait pas y avoir un meilleur plan que celui où cet index sera utilisé.
Ce choix est donc lié à cette requête seulement, et si je l'ai créé c'est pour me rendre compte est-ce que l'optimiseur va l'utiliser ou non.
Finalement, ce n'était pas le cas!
Pour les trois index que vous me proposez,je suis convaincue moi aussi que pour plusieurs requêtes, il faut opter pour ça.

Donc pour les tests :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
> 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 utilisation d'un hint ça donne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
> SELECT /*+ INDEX_COMBINE (sales) STAR */sales.cust_id, avg(amount_sold) 
  2    FROM sales, customers, products, times 
  3    WHERE sales.cust_id = customers.cust_id 
  4    AND sales.prod_id = products.prod_id 
  5    AND sales.time_id = times.time_id 
  6    AND times.fiscal_year ='2000' AND 
  7    customers.cust_marital_status ='married' 
  8    AND products.prod_category ='Women' 
  9    GROUP BY sales.cust_id;
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1909 Card=1569 Bytes
          =34518)
 
   1    0   SORT (GROUP BY) (Cost=1909 Card=1569 Bytes=34518)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SALES' (Cost=1252 Card
          =203254 Bytes=4471592)
 
   3    2       BITMAP CONVERSION (TO ROWIDS)
   4    3         BITMAP AND
   5    4           BITMAP INDEX (SINGLE VALUE) OF 'BIJ_INDEX1'
   6    4           BITMAP INDEX (SINGLE VALUE) OF 'BIJ_INDEX2'
   7    4           BITMAP INDEX (SINGLE VALUE) OF 'BIJ_INDEX3'

Je ne comprends pas pourquoi ce n'est pas l'index bij_index8 qui est utilisé.

Quand je supprime les trois index, j'obtiens de nouveau ce plan :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
> SELECT /*+ INDEX_COMBINE (sales) STAR */sales.cust_id, avg(amount_sold) 
  2    FROM sales, customers, products, times 
  3    WHERE sales.cust_id = customers.cust_id 
  4    AND sales.prod_id = products.prod_id 
  5    AND sales.time_id = times.time_id 
  6    AND times.fiscal_year ='2000' AND 
  7    customers.cust_marital_status ='married' 
  8    AND products.prod_category ='Women' 
  9    GROUP BY sales.cust_id;
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1323 Card=1569 Bytes
          =34518)
 
   1    0   SORT (GROUP BY) (Cost=1323 Card=1569 Bytes=34518)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SALES' (Cost=1237 Card
          =25407 Bytes=558949)
 
   3    2       BITMAP CONVERSION (TO ROWIDS)
   4    3         BITMAP INDEX (SINGLE VALUE) OF 'BIJ_INDEX8'

Cordialement