L’optimiseur de base de données Oracle analyse votre instruction SQL, s’il existe une table partitionnée et « appropriée » where condition, il décide quelles partitions seront ignorées et quelles partitions seront consultées et lues. C’est ce qu’on appelle l’élagage des partitions. Il s’agit d’une fonctionnalité de performance essentielle pour les entrepôts de données.
Comme vous pouvez le deviner, lorsque vous élaguez certaines partitions, les données que vous devez lire à partir des disques sont considérablement réduites, ce qui entraîne des temps de traitement plus courts.
Types d’élagage de partition
Il existe deux types d’élagage de partition :
- Élagage des partitions statiques : si la
wherecondition a des valeurs constantes. (Temps de compilation) - Élagage dynamique des partitions : si la
wherela condition a des calculs, des fonctions, etc. (Durée d’exécution)
Conditions applicables
Type de partition
Tout d’abord, le type de partition détermine si l’élagage de partition peut être utilisé ou non.
Vous trouverez ci-dessous quels prédicats peuvent être utilisés dans quel type de partition.
Partitions de plage ou de liste :
- COMME
- égalité (=)
- plage (entre .. et ..)
- dans la liste (dans)
Partitions de hachage :
- égalité (=)
- dans la liste (dans)
Fonctions utilisées ou non
Deuxièmement, les fonctions utilisées sur les colonnes de partition annulent l’élagage de la partition.
Toutes les fonctions, y compris les conversions de type (fonctions cachées), annulent l’élagage de la partition. Par exemple:
where trunc(my_column) between sysdate - 30 and sysdate
Manifestation
Voyons comment cela fonctionne dans les coulisses ;
Tout d’abord, nous créons une table partitionnée par plage appelée MY_TABLE. Cette table a 29 partitions initialement. La clé de partition s’appelle PERIOD et son type de données est NUMBER.
create table ODS.MY_TABLE
(PERIOD NUMBER,
STATUS VARCHAR2(4000),
PORTFOLIO_DEALER_NAME VARCHAR2(4000),
PORTFOLIO_DEALER_CODE VARCHAR2(4000),
SALES_REGION VARCHAR2(4000),
SALES_SUB_REGION VARCHAR2(4000),
SALES_CHANNEL_TYPE VARCHAR2(4000)
)
PARTITION BY RANGE (PERIOD)
(PARTITION p201801 VALUES LESS THAN (201802),
PARTITION p201802 VALUES LESS THAN (201803),
PARTITION p201803 VALUES LESS THAN (201804),
PARTITION p201804 VALUES LESS THAN (201805),
PARTITION p201805 VALUES LESS THAN (201806),
PARTITION p201806 VALUES LESS THAN (201807),
PARTITION p201807 VALUES LESS THAN (201808),
PARTITION p201808 VALUES LESS THAN (201809),
PARTITION p201809 VALUES LESS THAN (201810),
PARTITION p201810 VALUES LESS THAN (201811),
PARTITION p201811 VALUES LESS THAN (201812),
PARTITION p201812 VALUES LESS THAN (201901),
PARTITION p201901 VALUES LESS THAN (201902),
PARTITION p201902 VALUES LESS THAN (201903),
PARTITION p201903 VALUES LESS THAN (201904),
PARTITION p201904 VALUES LESS THAN (201905),
PARTITION p201905 VALUES LESS THAN (201906),
PARTITION p201906 VALUES LESS THAN (201907),
PARTITION p201907 VALUES LESS THAN (201908),
PARTITION p201908 VALUES LESS THAN (201909),
PARTITION p201909 VALUES LESS THAN (201910),
PARTITION p201910 VALUES LESS THAN (201911),
PARTITION p201911 VALUES LESS THAN (201912),
PARTITION p201912 VALUES LESS THAN (202001),
PARTITION p202001 VALUES LESS THAN (202002),
PARTITION p202002 VALUES LESS THAN (202003),
PARTITION p202003 VALUES LESS THAN (202004),
PARTITION p202004 VALUES LESS THAN (202005),
PARTITION p202005 VALUES LESS THAN (202006))
nologging
parallel 16
compress for query high;Maintenant, nous exécutons une simple instruction select et examinons le plan d’exécution.
EXPLAIN PLAN FOR
select /*+ parallel */ *
from ODS.MY_TABLE;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12025 | 2 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 12025 | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1 | 12025 | 2 (0)| 00:00:01 | 1 | 29 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS STORAGE FULL| MY_TABLE | 1 | 12025 | 2 (0)| 00:00:01 | 1 | 29 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------La partie sur laquelle nous devons nous concentrer sur ce plan d’exécution est Pstart et Pstop Colonnes. Comme vous pouvez le voir, lorsque nous n’appliquons aucun where condition (c’est-à-dire en filtrant les données), il lit les 29 partitions.
Et si on appliquait un where condition et filtrer les données à l’aide de la clé de partition. Voyons comment le plan d’exécution change.
EXPLAIN PLAN FOR
select /*+ parallel */ *
from ODS.MY_TABLE
where period between 201901 and 201905;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12025 | 2 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 12025 | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1 | 12025 | 2 (0)| 00:00:01 | 13 | 17 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS STORAGE FULL| MY_TABLE | 1 | 12025 | 2 (0)| 00:00:01 | 13 | 17 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------Nous vérifions le Pstart et Pstop colonnes et voyez clairement que maintenant il ne lit que les partitions entre les 13e et 17e partitions. En d’autres termes, il élague les autres partitions et empêche de lire inutilement des partitions supplémentaires.
Maintenant, nous avons une idée de base de ce qu’est l’élagage de partition. Créons donc un scénario sur la façon dont une fonction annule l’élagage. Nous appliquerons un simple to_char et to_number fonction et vérifiez ce qui se passera dans le plan d’exécution.
EXPLAIN PLAN FOR
select /*+ parallel */ *
from ODS.MY_TABLE
where to_number(to_char(period)) between 201901 and 201905;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12025 | 2 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 12025 | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1 | 12025 | 2 (0)| 00:00:01 | 1 | 29 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS STORAGE FULL| MY_TABLE | 1 | 12025 | 2 (0)| 00:00:01 | 1 | 29 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------Comme nous pouvons le voir, l’utilisation de la fonction annule l’élagage de la partition et lit à nouveau toutes les partitions. Vous devez garder à l’esprit que s’il y a un casting de type caché dans le where condition, il annule toujours l’élagage de la partition.
Conclusion
En résumé, l’élagage de partition est un excellent moyen d’augmenter les performances de vos requêtes dans les entrepôts de données. Nous avons ignoré l’élagage des partitions d’une manière simple et j’espère que cela crée une compréhension de base du fonctionnement de l’élagage.