Oracle Partitioning, c’est le nec plus ultra des fonctions d’Oracle ! Ça peut multiplier vos performances par 500 ou rendre possible ce qui ne le pourrait pas…

Pour faire court, le partitioning c’est diviser pour régner; vous avez une table de 3To, découpez en 10000 morceaux de 300Mo et c’est plus simple à sauvegarder, plus simple à alimenter, plus simple à effacer, plus simple à compresser ou plus rapide pour lire (au moins un morceau !).

Seulement, c’est comme tout, ce n’est pas parce qu’on a une théorie qu’elle est bonne ! « Benchmarker » ses idées, c’est bien plus efficace que de faire appel à quelqu’un pour tenter de pallier à une théorie finalement fumeuse. Surtout si le quelqu’un c’est moi et surtout si la théorie est complètement fausse…

Pourquoi un post ?

En fait il y a plusieurs raisons pour ce post. Bien sur, j’ai déjà promis cent fois d’écrire sur le partitioning. Bien sur, vous aurez constaté que, mal utilisé, le partitioning peut aussi tuer votre application…

Mais finalement, c’est un choc qui me laisse sans voix, et que vous connaissez peut-être qui me sort de mon mutisme : « Il n’est pas possible de sous-partitionner un index qui n’est pas partitionné comme la table (i.e un Index Global) ». Remarquez qu’on peut vivre des années avec une technologie et passer à coté d’un aspect fondamental ! Je cite le passage concerné de la documentation d’Oracle 11.1 :

You can partition a global index by range or by hash. In both cases, you can specify up to 32 columns as partitioning key columns.

Vous direz, c’est écrit « you can » et pas « you cannot ». Alors j’ai ouvert une SR et, comme attendu… C’est bien dommage pour moi, soit dit en passant.

Un mauvais exemple

Prenons l’exemple d’une table partitionnée par une clé de HASH sur 2 colonnes x et y :

create table t(x number, y number)
 partition by hash(x,y) partitions 128;

begin
for i in 10000..20000 loop
 for j in 1..10 loop
   insert into t(x,y) values (i,i*10+j);
 end loop;
end loop;
end;
/
commit;

create index t_idx on t(x,y) local;

Dans ce premier cas, exécutons la requête qui suit plusieurs fois et observons le plan d’exécution et le nombre de consistent gets :

select * from t where x=10000;

Execution Plan
---------------------------------------------------
Plan hash value: 3357014883

---------------------------------------------------
| Id  | Operation    | Name  |Pstart| Pstop |
---------------------------------------------------
|   0 | SELECT STATEMENT   |    |      |       |
|   1 |  PARTITION HASH ALL|    |     1|   128 |
|*  2 |   INDEX RANGE SCAN | T_IDX |     1|   128 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access("X"=10000)


Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
 257  consistent gets
   0  physical reads
   0  redo size
 588  bytes sent via SQL*Net to client
 416  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
  10  rows processed

On voit d’après le plan que les 128 partitions sont accédées et que l’exécution de la requête nécessite 257 consistent gets. Faisons le test avec la même table non partitionnée…

drop table t purge;

create table t(x number, y number);

begin
for i in 10000..20000 loop
 for j in 1..10 loop
   insert into t(x,y) values (i,i*10+j);
 end loop;
end loop;
end;
/
commit;

create index t_idx on t(x,y);

Execution Plan
----------------------------------
Plan hash value: 2946670127

----------------------------------
| Id  | Operation  | Name  |
----------------------------------
|   0 | SELECT STATEMENT |  |
|*  1 |  INDEX RANGE SCAN| T_IDX |
----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("X"=10000)

Statistics
----------------------------------------------------------
   0  recursive calls
   0  db block gets
   3  consistent gets
   0  physical reads
   0  redo size
 588  bytes sent via SQL*Net to client
 416  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
  10  rows processed

Dans ce second cas, l’exécution de la requête ne nécessite que 3 consistent gets; Elle est donc probablement 80 fois plus efficace.

Conclusion

Vous direz : vous avez 1000 exemples du cas contraire ou vous pouvez créer un index global pour ce cas… quoique si le cas est un peu plus complexe et que vous avez de gros volumes et plusieurs niveaux de partitioning, les limites des index globaux et du partitioning pourraient vous vous perturber.

Vous direz aussi : ça ne vous arrivera pas parce que vous savez que le partitioning dépend de l’application ou vous testez votre application… en charge

Je dis : vous avez raison… moi aussi ! et je dis aussi : supprimez la table d’exemple.

drop table t purge;