Pickleball spielen

This is about how to write SQL in a way that supports Partition-Pruning – and what should be avoided.  The playing field looks as follows:

SQL> select * from v$version;
BANNER
 --------------------------------------------------------------------------------
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 PL/SQL Release 11.2.0.2.0 - Production
 CORE    11.2.0.2.0      Production
 TNS for Linux: Version 11.2.0.2.0 - Production
 NLSRTL Version 11.2.0.2.0 - Production

SQL> select table_name,partitioning_type,partition_count from user_part_tables where table_name='SALES_YEAR';
TABLE_NAME                     PARTITION PARTITION_COUNT
 ------------------------------ --------- ---------------
 SALES_YEAR                     RANGE             1048575

SQL> select segment_name,partition_name,sum(bytes)/1024/1024 as mb
 from user_segments where segment_name='SALES_YEAR'
 group by rollup (segment_name,partition_name)
 order by 1,2; 

SEGMENT_NAME                   PARTITION_NAME                         MB
 ------------------------------ ------------------------------ ----------
 SALES_YEAR                     P1                                     16
 SALES_YEAR                     SYS_P181                               32
 SALES_YEAR                     SYS_P182                               32
 SALES_YEAR                     SYS_P183                               32
 SALES_YEAR                     SYS_P184                               32
 SALES_YEAR                     SYS_P185                               32
 SALES_YEAR                     SYS_P186                               32
 SALES_YEAR                     SYS_P187                               32
 SALES_YEAR                     SYS_P188                               32
 SALES_YEAR                     SYS_P189                               32
 SALES_YEAR                     SYS_P190                               32
 SALES_YEAR                     SYS_P191                               32
 SALES_YEAR                     SYS_P192                               32
 SALES_YEAR                     SYS_P193                               32
 SALES_YEAR                     SYS_P194                               32
 SALES_YEAR                     SYS_P195                               32
 SALES_YEAR                     SYS_P196                               32
 SALES_YEAR                     SYS_P197                               32
 SALES_YEAR                     SYS_P198                               32
 SALES_YEAR                     SYS_P199                               32
 SALES_YEAR                     SYS_P200                               32
 SALES_YEAR                     SYS_P201                               32
 SALES_YEAR                     SYS_P202                               32
 SALES_YEAR                     SYS_P203                               32
 SALES_YEAR                     SYS_P204                               32
 SALES_YEAR                     SYS_P205                               32
 SALES_YEAR                     SYS_P206                               32
 SALES_YEAR                     SYS_P207                               24
 SALES_YEAR                                                           872
 872
30 rows selected.

SQL> select to_char(order_date,'yyyy'),count(*) from sales_year group by to_char(order_date,'yyyy') order by 1;


TO_C   COUNT(*)
 ---- ----------
 1985     158000
 1986     365000
 1987     365000
 1988     366000
 1989     365000
 1990     365000
 1991     365000
 1992     366000
 1993     365000
 1994     365000
 1995     365000
 1996     366000
 1997     365000
 1998     365000
 1999     365000
 2000     366000
 2001     365000
 2002     365000
 2003     365000
 2004     366000
 2005     365000
 2006     365000
 2007     365000
 2008     366000
 2009     365000
 2010     365000
 2011     365000
 2012     346000
28 rows selected.

My moderately sized table is Interval partitioned (therefore PARTITION_COUNT in USER_PART_TABLES shows the possible  maximum number)  by the year on ORDER_DATE with 28 partitions. Now imagine we want to have the summarized AMOUNT_SOLD of the year 2011. What about this statement?

SQL> set timing on
SQL> select sum(amount_sold) from sales_year where to_char(order_date,'yyyy')='2011';

SUM(AMOUNT_SOLD)
----------------
      1825000000

Elapsed: 00:00:05.15
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  cv54q4mt7ajjr, child number 0
-------------------------------------
select sum(amount_sold) from sales_year where
to_char(order_date,'yyyy')='2011'

Plan hash value: 3345868052

---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |       |       | 24384 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |            |     1 |    22 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|            |   287K|  6181K| 24384   (2)| 00:00:07 |     1 |1048575|
|*  3 |    TABLE ACCESS FULL | SALES_YEAR |   287K|  6181K| 24384   (2)| 00:00:07 |     1 |1048575|
---------------------------------------------------------------------------------------------------

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

   3 - filter(TO_CHAR(INTERNAL_FUNCTION("ORDER_DATE"),'yyyy')='2011')

It produces the required result, but using a Full Table Scan across all partitions. Very much better instead:

SQL> select sum(amount_sold) from sales_year where order_date between to_date('01.01.2011','dd.mm.yyyy') and to_date('31.12.2011','dd.mm.yyyy');

SUM(AMOUNT_SOLD)
----------------
      1825000000

Elapsed: 00:00:00.11
SQL> select plan_table_output from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  6rwm3z7rhgmd6, child number 0
-------------------------------------
select sum(amount_sold) from sales_year where order_date between
to_date('01.01.2011','dd.mm.yyyy') and
to_date('31.12.2011','dd.mm.yyyy')

Plan hash value: 767904852

------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |       |       |  1033 (100)|          |       |       |
|   1 |  SORT AGGREGATE         |            |     1 |    22 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|            |   378K|  8128K|  1033  (16)| 00:00:01 |    27 |    27 |
|*  3 |    TABLE ACCESS FULL    | SALES_YEAR |   378K|  8128K|  1033  (16)| 00:00:01 |    27 |    27 |
------------------------------------------------------------------------------------------------------

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

   3 - filter(("ORDER_DATE">=TO_DATE(' 2011-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORDER_DATE"<=TO_DATE(' 2011-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

The same result but much faster, scanning only one partition!

Conclusion: It is quite important to have no functions around the partition key in the WHERE-clause here. Personally, the first SQL looks easier to me and has less coding, but it is obviously not as good as the second. Might be worth to spend some time thinking and adding some more characters to the code to make Partition-Pruning possible. Don’t believe it, test it! With some big enough tables, I mean 🙂