Le Query Cache fait partie intégrante de MySQL depuis la version 3.23 (si ce n'est plus tôt). Il aura fallu attendre jusqu'à la version 11G pour voir arriver cette fonctionnalité chez Oracle. Nous allons tester cette fonctionnalité chez MySQL et Oracle et voir si le fait que l'on utilise le partitionnement apporte quelque chose.
Pour MYSQL (5.1 requis) :CREATE TABLE table_mysql_partitionned(ident int unsigned, quand date, valeur int unsigned)
ENGINE=INNODB
PARTITION BY RANGE( YEAR(quand) )
(
PARTITION p2006 VALUES LESS THAN (2007),
PARTITION p2007 VALUES LESS THAN (2008),
PARTITION p2008 VALUES LESS THAN (2009),
PARTITION p2009 VALUES LESS THAN (2010)
);
INSERT INTO table_mysql_partitionned VALUES(1,'2008-06-04',20),(2,'2007-05-04',35),(3,'2008-07-04',3),(4,'2009-10-03',11),(1,'2009-12-25',46),
(2,'2008-10-12',43),(10,'2006-05-03',32),(5,'2007-06-12',56);
mysql> select * from table_mysql_partitionned where quand between '2008-01-01' and '2008-10-01';
+-------+------------+--------+
| ident | quand | valeur |
+-------+------------+--------+
| 1 | 2008-06-04 | 20 |
| 3 | 2008-07-04 | 3 |
+-------+------------+--------+
mysql> show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 20958592 |
| Qcache_hits | 4 || Qcache_inserts | 4 || Qcache_lowmem_prunes | 0 |
+-------------------------+----------+
8 rows in set (0,00 sec)
mysql> select * from table_mysql_partitionned where quand between '2008-01-01' and '2008-10-01';
+-------+------------+--------+
| ident | quand | valeur |
+-------+------------+--------+
| 1 | 2008-06-04 | 20 |
| 3 | 2008-07-04 | 3 |
+-------+------------+--------+
2 rows in set (0,00 sec)
mysql> show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 20958592 |
| Qcache_hits | 5 || Qcache_inserts | 4 || Qcache_lowmem_prunes | 0 |
+-------------------------+----------+
8 rows in set (0,01 sec)
mysql> INSERT INTO table_mysql_partitionned VALUES(1,'2009-06-04',34);
Query OK, 1 row affected (0,04 sec)
mysql> select * from table_mysql_partitionned where quand between '2008-01-01' and '2008-10-01';
+-------+------------+--------+
| ident | quand | valeur |
+-------+------------+--------+
| 1 | 2008-06-04 | 20 |
| 3 | 2008-07-04 | 3 |
+-------+------------+--------+
2 rows in set (0,00 sec)
mysql> show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 20959616 |
| Qcache_hits | 5 || Qcache_inserts | 5 || Qcache_lowmem_prunes | 0 |
+-------------------------+----------+
8 rows in set (0,00 sec)
La partition n'a pas été modifiée et pourtant le résultat du query cache a été invalidé :-(
Pour Oracle :SQLPLUS SCOTT/****
CREATE TABLE table_oracle_partitionned(ident NUMBER, quand date, valeur NUMBER)
PARTITION BY RANGE (quand)
INTERVAL (numtoyminterval(1,'YEAR'))
(
PARTITION p2006 VALUES LESS THAN (to_date('2007-01-01','yyyy-mm-dd'))
);
INSERT INTO table_oracle_partitionned VALUES(1,to_date('2008-06-04','yyyy-mm-dd'),20);
INSERT INTO table_oracle_partitionned VALUES(2,to_date('2007-05-04','yyyy-mm-dd'),35);
INSERT INTO table_oracle_partitionned VALUES(3,to_date('2008-07-04','yyyy-mm-dd'),3);
INSERT INTO table_oracle_partitionned VALUES(4,to_date('2009-10-03','yyyy-mm-dd'),11);
INSERT INTO table_oracle_partitionned VALUES(1,to_date('2009-12-25','yyyy-mm-dd'),46);
INSERT INTO table_oracle_partitionned VALUES(2,to_date('2008-10-12','yyyy-mm-dd'),43);
INSERT INTO table_oracle_partitionned VALUES(10,to_date('2006-05-03','yyyy-mm-dd'),32);
INSERT INTO table_oracle_partitionned VALUES(5,to_date('2007-06-12','yyyy-mm-dd'),56);
EXIT;
SQLPLUS / AS SYSDBA
SQL> EXECUTE dbms_result_cache.flush;
SQL> SELECT * FROM scott.table_oracle_partitionned WHERE quand BETWEEN to_date('2008-01-01','yyyy-mm-dd') AND to_date('2008-10-01','yyyy-mm-dd');
IDENT QUAND VALEUR
---------- -------- ----------
1 04/06/08 20
3 04/07/08 3
Si on lance plusieurs fois la requête et que l'on regarde les informations du result_cache :
SQL> select name, value from v$result_cache_statistics;
NAME VALUE
-------------------- ----------
Create Count Success 1Create Count Failure 0
Find Count 4Invalidation Count 0
Delete Count Invalid 0
On s'aperçoit que le résultat est bien récupéré à partir du result_cache.
J'effectue une mise à jour sur la table mais en prenant soin de ne pas intervenir sur les enregistrements de la partition contenant les données de l'année 2008 :
SQL> UPDATE scott.table_oracle_partitionned SET VALEUR=VALEUR+1 WHERE quand BETWEEN to_date('2007-01-01','yyyy-mm-dd') AND to_date('2007-12-30','yyyy-mm-dd');
2 rows updated.
Je relance la même requête pour récupérer les enregistrements de l'année 2008 et je regarde à nouveau les statistiques du result_cache :
SQL> SELECT name, value FROM v$result_cache_statistics;
NAME VALUE
-------------------- ----------
Create Count Success 2Create Count Failure 0
Find Count 4Invalidation Count 1Delete Count Invalid 0
En conclusion :
Le Result Cache d'Oracle se comporte comme le Query Cache de MySQL dans le sens où chaque modification d'une table engendre l'invalidation des SELECTs concernés. J'espérais qu'Oracle aurait su prendre en compte le partitionnement de la table pour obtenir une invalidation plus fine ce qui dans l'exemple plus haut aurait eu pour conséquence de ne pas recréer un enregistrement dans le Result Cache.
Articles
