Global Partitioned Index restent relativement contraints, y compris en 11g :

• ils ne supportent que le partitionnement par RANGE (avec un MAXVALUE) ou par HASH. 
• il n’est pas possible de créer de sous-partitions
• la clé de partition doit préfixer les colonnes de l’index
• il n’y a que très peu d’intérêt aux opérations de maintenance en ligne des partitions 

Si cette approche est parfois utilisée sur des tables partitionnées, je ne l’ai que rarement vu sur des tables non-partitionnées. Pourtant créer un index partitionné sur une table non-partitionnée fonctionne très bien. En outre, cela permet de mettre en oeuvre très rapidement du partitionnement dans une approche conservative du type « je partitionne mais pas trop »… Un bon moyen de régler rapidement un problème de performance sans tout chambouler, ni forcément, plusieurs jours d’une étude approfondie ; en attendant de faire mieux. Cet article présente des exemples de partitions Pruning et Partition-Wise Join qui mettent en oeuvre une table non partitionnée nommée INCIDENT…

Partition Pruning

Soit donc une table qui stocke des incidents relatifs à des voitures:

create table incident (licplate    varchar2(12), 
                       carcolor    varchar2(6) not null, 
                       inctype     number,
                       incdate     number,
                       kpi1        number,
                       kpi2        number,
                       inccomment  varchar2(4000));

explain plan for select count(carcolor) 
  from incident
 where carcolor='black';

set tab off
select * 
  from table(dbms_xplan.display(
                 format=>'basic +partition'));

PLAN_TABLE_OUTPUT
---------------------------------------
Plan hash value: 524677929

---------------------------------------
| Id  | Operation          | Name     |
---------------------------------------
|   0 | SELECT STATEMENT   |          |
|   1 |  SORT AGGREGATE    |          |
|   2 |   TABLE ACCESS FULL| INCIDENT |
---------------------------------------

Si on crée un index partitionné sur cette table non partitionnée, celui-ci peut être mis à contribution et les partitioning inutiles non mises à contribution comme le montre l’exemple ci-desssous :

create index incidx on incident(carcolor, licplate, kpi1) 
  global partition by range(carcolor) 
  (partition dblack values less than ('blackz'),
   partition dblue  values less than ('bluez'), 
   partition dgray  values less than ('grayz'),
   partition dother values less than ('otherz'),
   partition dred   values less than ('redz'),
   partition dwhite values less than ('whitez'),
   partition doflow values less than (MAXVALUE));

explain plan for select count(carcolor) 
  from incident
 where carcolor='black';

set tab off
select * 
  from table(dbms_xplan.display(
                 format=>'basic +partition'));

PLAN_TABLE_OUTPUT
-----------------
Plan hash value: 1658844147

----------------------------------------------------------
| Id  | Operation               | Name   | Pstart| Pstop |
----------------------------------------------------------
|   0 | SELECT STATEMENT        |        |       |       |
|   1 |  SORT AGGREGATE         |        |       |       |
|   2 |   PARTITION RANGE SINGLE|        |     1 |     1 |
|   3 |    INDEX RANGE SCAN     | INCIDX |     1 |     1 |
----------------------------------------------------------

Partition Wise Join

Le second exemple est un peu plus riche. Il s’appuie sur une table partitionnée selon le même critère pour illustrer la fonctionnalité de Partitioning Wise Join. Vous créerez un jeu de données pour que le plan utilisant le partitioning apparaisse comme apportant un bénéfice au temps d’exécution:

create table car (id       number, 
                  licplate varchar2(12), 
                  color    varchar2(6)  not null, 
                  attrib1  number)
  partition by range(color) 
  (partition dblack values less than ('blackz'),
   partition dblue  values less than ('bluez'), 
   partition dgray  values less than ('grayz'),
   partition dother values less than ('otherz'),
   partition dred   values less than ('redz'),
   partition dwhite values less than ('whitez'),
   partition doflow values less than (MAXVALUE));

insert into car
  select rownum, 
         to_char(rownum),
         decode(mod(rownum,6),
                   0,'black',
                   1,'blue',
                   2,'gray',
                   3,'other',
                   4,'red',
                   5,'black'),mod(rownum,17) 
    from dual 
 connect by level <=10000;

insert into incident
  select mod(rownum,10000)+1, 'black', mod(rownum,23),  
         mod(rownum,29), mod(rownum,3), mod(rownum,5), rpad('X',1000) 
    from dual d connect by level <=100000;

update incident i 
   set carcolor=(select color 
                   from car 
                  where id=i.licplate);
commit;

Comme le montre l’explain plan ci-dessous et, bien que la table INCIDENT ne soit pas partitionnée, la présence d’un Index Global Partitionné permet la mise en oeuvre, dans certaines circonstance, de l’algorithme de Partition Wise Join ; ici dans sa version complète :

explain plan for select sum(i.kpi1) 
from car c, incident i 
  where c.color=i.carcolor 
    and c.licplate=i.licplate
    and c.licplate like '1%';

select * 
  from table(dbms_xplan.display(
                 format=>'basic +partition +predicate'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------
Plan hash value: 2075022438

----------------------------------------------------------
| Id  | Operation               | Name   | Pstart| Pstop |
----------------------------------------------------------
|   0 | SELECT STATEMENT        |        |       |       |
|   1 |  SORT AGGREGATE         |        |       |       |
|   2 |   PARTITION RANGE ALL   |        |     1 |     7 |
|*  3 |    HASH JOIN            |        |       |       |
|*  4 |     TABLE ACCESS FULL   | CAR    |     1 |     7 |
|*  5 |     INDEX FAST FULL SCAN| INCIDX |     1 |     7 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("C"."COLOR"="I"."CARCOLOR" AND
              "C"."LICPLATE"="I"."LICPLATE")
   4 - filter("C"."LICPLATE" LIKE '1%')
   5 - filter("I"."LICPLATE" LIKE '1%')

L’opération PARTITION RANGE ALL se fait après la jointure, ce qui est caractéristique d’un « Full Partition Wise Join » ici.

Conclusion

Cet exemple montre qu’il est possible de tirer partie du partitioning, même sans partitionner une tables. Evidemment, cette approche reste minimaliste et, aucun doute que, vous arriverez vite à la conclusion qu’une vraie implémentation est beaucoup plus intéressante. N’oubliez pas que Oracle Partitioning est une option soumise à Licence d’Oracle Enterprise Edition et de supprimer les tables précédentes :

drop table incident purge;
drop table car purge;

]] >