Dans les articles précédents consacrés à « mon petit Data Mart », nous nous sommes concentrés sur le partitioning et les mécanismes de ré-écritures de requêtes qui permettent d’utiliser des agrégats lorsque ceux-ci sont stockés dans des vues matérialisées. C’est assez basic mais ça permettra à quelques-uns, je le souhaite, de mettre du concret sur ce qui est présenté dans la documentation.
C’est un substance ce que je vous propose de faire, une fois encore, avec 2 exemples d’utilisation d’une « star transformation » puis de « bitmap join index ». Si ce que vous cherchez est l’explication de ces 2 mécanismes, je ne saurais trop vous conseiller de lire les sections à propos des requêtes en étoile et des bitmap join indexes dans le « Data Warehousing Guide 11.2 ». Lisez également ces 2 articles de Jonathan Lewis, un peu ancien mais toujours aussi pertinents :
Schéma d’exemple
La star transformation et les index bitmap join permettent d’accélérer les accès aux données de détail lorsque celles-ci sont nécessaires. Nous allons donc re-créer un schema d’exemple, sans vue matérialisée cette fois-ci, pour vous permettre d’illustrer des plans et des temps de réponses associés.
Note:
J’ai testé cet exemple sur Oracle 11.2.0.1 sur Linux x86 32bits mais il devrait fonctionner avec Oracle 10g.
Le script ci-dessous crée les tables, données, statistiques et les indices pour nos requêtes :
drop table time purge;
drop table dim2 purge;
drop table dim3 purge;
drop table dim4 purge;
drop table d1 purge;
drop table d3 purge;
drop table d2 purge;
drop table d4 purge;
drop table fact purge;
create table fact
(d1 number not null,
d2 number not null,
d3 number not null,
d4 number not null,
x number);
begin
for i in 1..100 loop
insert into fact(d1, d2, d3, d4, x)
select d1, d2, d3, i, d1+d2+d3+i
from (select rownum d1 from dual
connect by level<=100) d1,
(select rownum d2 from dual
connect by level<=100) d2,
(select rownum d3 from dual
connect by level<=100) d3;
commit;
end loop;
end;
/
select count(*) from fact;
exec dbms_stats.gather_table_stats(user, 'FACT', -
method_opt=>'for all columns size 254')
create bitmap index fact_d1_bix on fact(d1);
create bitmap index fact_d2_bix on fact(d2);
create bitmap index fact_d3_bix on fact(d3);
create bitmap index fact_d4_bix on fact(d4);
create table d1
(d1 number,
d1_l number);
insert into d1
select rownum d1, mod(rownum,10)
from dual
connect by level<=100000;
exec dbms_stats.gather_table_stats(user, 'D1', -
method_opt=>'for all columns size 254')
alter table d1 add constraint d1_pk primary key(d1);
create table d2
(d2 number,
d2_l number);
insert into d2
select rownum d2, mod(rownum,10)
from dual
connect by level<=100000;
exec dbms_stats.gather_table_stats(user, 'D2', -
method_opt=>'for all columns size 254')
alter table d2 add constraint d2_pk primary key(d2);
create table d3
(d3 number,
d3_l number);
insert into d3
select rownum d3, mod(rownum,10)
from dual
connect by level<=100000;
exec dbms_stats.gather_table_stats(user, 'D3', -
method_opt=>'for all columns size 254')
alter table d3 add constraint d3_pk primary key(d3);
create table d4
(d4 number,
d4_l number);
insert into d4
select rownum d4, mod(rownum,10)
from dual
connect by level<=100000;
exec dbms_stats.gather_table_stats(user, 'D4', -
method_opt=>'for all columns size 254')
alter table d4 add constraint d4_pk primary key(d4);
Une requête simple
Voici une requête simple qui consiste à compter le nombre de lignes correspondant à un cluster de données dans notre table de fait segmenté selon certaines valeurs prises dans les tables de dimension:
alter session set star_transformation_enabled=false;
set timing on
select /*GG1*/ count(*)
from fact f, d1, d2, d3, d4
where f.d1=d1.d1
and f.d2=d2.d2
and f.d3=d3.d3
and f.d4=d4.d4
and d2_l=1
and d1_l=1
and d3_l=1
and d4_l=1;
COUNT(*)
----------
10000
Elapsed: 00:00:43.54
select * from table(dbms_xplan.display_cursor(format=>'basic note'));
EXPLAINED SQL STATEMENT:
------------------------
select /*GG1*/ count(*) from fact f, d1, d2, d3, d4 where f.d1=d1.d1
and f.d2=d2.d2 and f.d3=d3.d3 and f.d4=d4.d4 and d2_l=1
and d1_l=1 and d3_l=1 and d4_l=1
Plan hash value: 4147546651
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL | D1 |
| 4 | HASH JOIN | |
| 5 | TABLE ACCESS FULL | D3 |
| 6 | HASH JOIN | |
| 7 | TABLE ACCESS FULL | D2 |
| 8 | HASH JOIN | |
| 9 | TABLE ACCESS FULL| D4 |
| 10 | TABLE ACCESS FULL| FACT |
---------------------------------------
Note:
La valeur par défaut du paramètrestar_transformation_enableden 11.2 estfalse
La même requête avec une star transformation
Nous allons maintenant exécutez la même requête avec une star transformation. Vous remarquerez en particulier les opérations BITMAP KEY ITERATION puis BITMAP MERGE entre les tables de dimension et les index bitmap ainsi que l’opération de BITMAP AND entre tous les segments bitmap résultant des étapes préalables; le temps de réponse est significativement amélioré (sur mon laptop) :
alter session set star_transformation_enabled=true;
set timing on
select /*GG2*/ count(*)
from fact f, d1, d2, d3, d4
where f.d1=d1.d1
and f.d2=d2.d2
and f.d3=d3.d3
and f.d4=d4.d4
and d2_l=1
and d1_l=1
and d3_l=1
and d4_l=1;
COUNT(*)
----------
10000
Elapsed: 00:00:02.02
select * from table(dbms_xplan.display_cursor(format=>'basic note'));
EXPLAINED SQL STATEMENT:
------------------------
select /*GG1*/ count(*) from fact f, d1, d2, d3, d4 where f.d1=d1.d1
and f.d2=d2.d2 and f.d3=d3.d3 and f.d4=d4.d4 and d2_l=1
and d1_l=1 and d3_l=1 and d4_l=1
Plan hash value: 2278764636
----------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | BITMAP CONVERSION COUNT | |
| 3 | BITMAP AND | |
| 4 | BITMAP MERGE | |
| 5 | BITMAP KEY ITERATION | |
| 6 | TABLE ACCESS FULL | D4 |
| 7 | BITMAP INDEX RANGE SCAN| FACT_D4_BIX |
| 8 | BITMAP MERGE | |
| 9 | BITMAP KEY ITERATION | |
| 10 | TABLE ACCESS FULL | D2 |
| 11 | BITMAP INDEX RANGE SCAN| FACT_D2_BIX |
| 12 | BITMAP MERGE | |
| 13 | BITMAP KEY ITERATION | |
| 14 | TABLE ACCESS FULL | D1 |
| 15 | BITMAP INDEX RANGE SCAN| FACT_D1_BIX |
| 16 | BITMAP MERGE | |
| 17 | BITMAP KEY ITERATION | |
| 18 | TABLE ACCESS FULL | D3 |
| 19 | BITMAP INDEX RANGE SCAN| FACT_D3_BIX |
----------------------------------------------------
Note
-----
- star transformation used for this statement
La même requête avec des Bitmap Join Indexes
Nous allons maintenant prendre du temps pour calculer des bitmap join indexes entre la table de faits et les colonnes des dimensions utilisées dans la clause where de notre requête :
drop index fact_d1_bix;
drop index fact_d2_bix;
drop index fact_d3_bix;
drop index fact_d4_bix;
create bitmap index fact_d1_bjix
on fact(d1.d1_l)
from d1, fact f
where d1.d1=f.d1;
create bitmap index fact_d2_bjix
on fact(d2.d2_l)
from d2, fact f
where d2.d2=f.d2;
create bitmap index fact_d3_bjix
on fact(d3.d3_l)
from d3, fact f
where d3.d3=f.d3;
create bitmap index fact_d4_bjix
on fact(d4.d4_l)
from d4, fact f
where d4.d4=f.d4;
La requête, sans star transformation, a un plan et des temps d’exécution significativement améliorés :
alter session set star_transformation_enabled=false;
set timing on
select /*GG2*/ count(*)
from fact f, d1, d2, d3, d4
where f.d1=d1.d1
and f.d2=d2.d2
and f.d3=d3.d3
and f.d4=d4.d4
and d2_l=1
and d1_l=1
and d3_l=1
and d4_l=1;
COUNT(*)
----------
10000
Elapsed: 00:00:00.63
select * from table(dbms_xplan.display_cursor(format=>'basic note'));
EXPLAINED SQL STATEMENT:
------------------------
select /*GG2*/ count(*) from fact f, d1, d2, d3, d4 where f.d1=d1.d1
and f.d2=d2.d2 and f.d3=d3.d3 and f.d4=d4.d4 and d2_l=1
and d1_l=1 and d3_l=1 and d4_l=1
Plan hash value: 2920980726
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | BITMAP CONVERSION COUNT | |
| 3 | BITMAP AND | |
| 4 | BITMAP INDEX SINGLE VALUE| FACT_D4_BJIX |
| 5 | BITMAP INDEX SINGLE VALUE| FACT_D1_BJIX |
| 6 | BITMAP INDEX SINGLE VALUE| FACT_D2_BJIX |
| 7 | BITMAP INDEX SINGLE VALUE| FACT_D3_BJIX |
-----------------------------------------------------
Conclusion
Evidemment, le résultat dépend du travail préalable réalisé lors des chargements. Ces outils peuvent néanmoins permettre de changer de plusieurs facteurs d’échelle les temps de réponse de vos requêtes.
1 réflexion sur “Mon petit Data Mart (Partie 5) : Star Transformation et Bitmap Join Index”
excellent article
Les commentaires sont fermés.