[Oracle] exploring partition pruning

[Oracle] exploring partition-partition pruning refers to partition table or partition index, the optimizer can automatically extract the partitions to be scanned FROM the FROM and WHERE statements based on the partition key. This avoids full table scanning, reduces the number of scanned data blocks, and improves performance. Partition pruning is divided into static and dynamic, static partition pruning occurs in the compilation phase, and dynamic partition pruning occurs in the execution phase. Let's take a look at the similarities and differences between the two partition pruning execution plans. Static partition pruning static partition pruning knows how many partitions need to be scanned in the parsing phase. Therefore, PSTART and PSTOP in the execution plan clearly show the number of scanned start and end partitions. For example:

SQL> explain plan for select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------Plan hash value: 3971874201----------------------------------------------------------------------------------------------| Id | Operation              | Name  | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |----------------------------------------------------------------------------------------------|  0 | SELECT STATEMENT       |       | 673  | 19517 | 27      (8)| 00:00:01 |       |       ||  1 |  PARTITION RANGE SINGLE|       | 673  | 19517 | 27      (8)| 00:00:01 | 17    | 17    ||* 2 |   TABLE ACCESS FULL    | SALES | 673  | 19517 | 27      (8)| 00:00:01 | 17    | 17    |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------    2 - filter("TIME_ID"=TO_DATE('2001-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

 

In the above example, PSTART and PSTOP are equal to 17, indicating that only 17th partitions need to be scanned. If you cannot know the number of partitions to be scanned during the parsing phase, you can only determine the number of partitions to be scanned at runtime. In this case, Oracle automatically uses the dynamic partition pruning technology, for example:

SQL> explain plan for select sum(amount_sold) from sales where time_id in     (select time_id from times where fiscal_year = 2000);Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUTPLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Plan hash value: 3827742054----------------------------------------------------------------------------------------------------| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |----------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT           |       |     1 |    25 |   523   (5)| 00:00:07 |       |       ||   1 |  SORT AGGREGATE            |       |     1 |    25 |            |          |       |       ||*  2 |   HASH JOIN                |       |   191K|  4676K|   523   (5)| 00:00:07 |       |       ||*  3 |    TABLE ACCESS FULL       | TIMES |   304 |  3648 |    18   (0)| 00:00:01 |       |       ||   4 |    PARTITION RANGE SUBQUERY|       |   918K|    11M|   498   (4)| 00:00:06 |KEY(SQ)|KEY(SQ)||   5 |     TABLE ACCESS FULL      | SALES |   918K|    11M|   498   (4)| 00:00:06 |KEY(SQ)|KEY(SQ)|----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("TIME_ID"="TIME_ID")   3 - filter("FISCAL_YEAR"=2000)

 

In the preceding example, the number of partitions to be scanned is only known after the subquery returns the result. Therefore, PSTART and PSTOP cannot display the exact number of partitions in the execution plan, instead of the keyword KEY, it indicates dynamic partition pruning. Partition pruning considerations 1. Data type conversion may result in static partition pruning becoming dynamic partition pruning, affecting performance, for example:

explain plan for SELECT SUM(amount_sold) total_revenueFROM sales,WHERE time_id between '01-JAN-00' and '31-DEC-00';----------------------------------------------------------------------------------------------------| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |----------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT           |       |     1 |    13 |   525   (8)| 00:00:07 |       |       ||   1 |  SORT AGGREGATE            |       |     1 |    13 |            |          |       |       ||*  2 |   FILTER                   |       |       |       |            |          |       |       ||   3 |    PARTITION RANGE ITERATOR|       |   230K|  2932K|   525   (8)| 00:00:07 |   KEY |   KEY ||*  4 |     TABLE ACCESS FULL      | SALES |   230K|  2932K|   525   (8)| 00:00:07 |   KEY |   KEY |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(TO_DATE('01-JAN-00')<=TO_DATE('31-DEC-00'))   4 - filter("TIME_ID">='01-JAN-00' AND "TIME_ID"<='31-DEC-00') 

 

In the preceding example, the partition key time_id is of the Date type, but the where clause is of the Char type. Therefore, an implicit type conversion is required, resulting in the transformation of static partition pruning to dynamic partition pruning, which affects performance.

----------------------------------------------------------------------------------------------------| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |----------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT           |       |     1 |    13 |   525   (8)| 00:00:07 |       |       ||   1 |  SORT AGGREGATE            |       |     1 |    13 |            |          |       |       ||*  2 |   FILTER                   |       |       |       |            |          |       |       ||   3 |    PARTITION RANGE ITERATOR|       |   230K|  2932K|   525   (8)| 00:00:07 |   KEY |   KEY ||*  4 |     TABLE ACCESS FULL      | SALES |   230K|  2932K|   525   (8)| 00:00:07 |   KEY |   KEY |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(TO_DATE('01-JAN-00')<=TO_DATE('31-DEC-00'))   4 - filter("TIME_ID">='01-JAN-00' AND "TIME_ID"<='31-DEC-00') 

 

2. If a function has a function (display or implicit) on the partition key, partition pruning will fail. For example:

EXPLAIN PLAN FORSELECT SUM(quantity_sold)FROM salesWHERE time_id = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy');

 

Because time_id is of the Date type, Oracle must upgrade it to Timestamp. Therefore, the statements actually executed by Oracle are as follows:

TO_TIMESTAMP(time_id) = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy')

 

Partition pruning fails due to functions on the partition key:

--------------------------------------------------------------------------------------------|Id | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT     |       |     1 |    11 |     6  (17)| 00:00:01 |       |       || 1 |  SORT AGGREGATE      |       |     1 |    11 |            |          |       |       || 2 |   PARTITION RANGE ALL|       |    10 |   110 |     6  (17)| 00:00:01 |     1 |    19 ||*3 |    TABLE ACCESS FULL | SALES |    10 |   110 |     6  (17)| 00:00:01 |     1 |    19 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - filter(INTERNAL_FUNCTION("TIME_ID")=TO_TIMESTAMP('1-jan-2000',:B1))