As you can tell, I have no idea on a name for what I am about to describe. So let me start from the beginning, and set the scene for an idea I have to utilize a cool new 18c feature.
Often in a transactional-style system the busiest table (let us call it SALES for the sake of this discussion) is also
- the biggest table, after all, it has all of our sales in it,
- the most demanded for table, in that, almost every query in our application wants to access it in some way shape or form.
This is in effect the database version of the Pareto Principle. Everyone wants a slice of that SALES “pie”, and the piece of that pie that is in most demand is typically the most recent data. Your application may have pages that will be showing:
- sales in the past hour,
- sales in the past day,
- products sold in the past hour,
- largest purchase amount for sales in the past few hours,
- suspicious activity for today,
- A customer will want their sales for the last few days
The list goes on and on, the common theme being that the data being requested is bound by a range of time in the recent past. Our SALES table already will have a primary key, probably being some sort of unique transaction ID for each purchase, but to the satisfy the style of requests in the list above, we will probably need:
- an index on the time(stamp) of the sale,
- an index on the product ID that was sold,
- an index on the customer ID who made the purchase,
- potentially even an index on the sale amount
You can see the troubled waters into which we are sailing here. It is our biggest and busiest table, and here we are, adding index after index after index to improve query performance, whilst at the same time:
- adding overhead/contention to DML operations on the SALES table,
- increasing the size of the database,
- increasing the duration of the backups,
- increasing time for maintenance and copies to Development and Test
None of this is looking great but we might be thinking “What choice do we have?”
Here is perhaps an alternate strategy: Let’s not create any indexes. The challenges (1) through (4) above evaporate to non-existence. But of course, now we have a remaining challenge in getting those queries to run efficiently.
I’ll tackle that in a different way – I will partition the SALES table very “aggressively”, hence my blog post title “hyper-partitioning”. Considering the typical query requirements I listed above, I will partition my SALES down to as small as a partition for every hour.
SQL> create table sales
2 ( ts timestamp,
3 id int,
4 amt number,
5 product int,
6 customer int,
7 item_cnt int,
8 terminal int,
9 operator int,
10 credit_card int,
...
...
17 )
18 partition by range ( ts )
19 interval ( numtodsinterval(1,'HOUR') )
20 (
21 partition p1 values less than ( timestamp '2018-07-01 00:00:00' )
22 );
Table created.
SQL>
SQL> insert /*+ APPEND */ into sales (ts,id,amt,product,customer)
2 select date '2018-07-01' + rownum / 240, rownum, dbms_random.value(1,100),
3 dbms_random.value(1,100),dbms_random.value(1,100)
4 from dual
5 connect by level
SQL> set serverout on
SQL> declare
2 h varchar2(1000);
3 begin
4 for i in (
5 select partition_name, high_value
6 from user_tab_partitions
7 where table_name = 'SALES'
8 and interval = 'YES'
9 and partition_name like 'SYS_P%'
10 order by partition_position
11 ) loop
12 h := i.high_value;
13 execute immediate 'select to_char('||h||'-0.01,''yyyymmdd_hh24'') from dual' into h;
14 execute immediate 'alter table sales rename partition '||i.partition_name||' to p'||h;
15 end loop;
16 end;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> col high_value format a60
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------------------------------------
P1 TIMESTAMP' 2018-07-01 00:00:00'
P20180701_00 TIMESTAMP' 2018-07-01 01:00:00'
P20180701_01 TIMESTAMP' 2018-07-01 02:00:00'
P20180701_02 TIMESTAMP' 2018-07-01 03:00:00'
P20180701_03 TIMESTAMP' 2018-07-01 04:00:00'
P20180701_04 TIMESTAMP' 2018-07-01 05:00:00'
P20180701_05 TIMESTAMP' 2018-07-01 06:00:00'
P20180701_06 TIMESTAMP' 2018-07-01 07:00:00'
P20180701_07 TIMESTAMP' 2018-07-01 08:00:00'
P20180701_08 TIMESTAMP' 2018-07-01 09:00:00'
P20180701_09 TIMESTAMP' 2018-07-01 10:00:00'
P20180701_10 TIMESTAMP' 2018-07-01 11:00:00'
P20180701_11 TIMESTAMP' 2018-07-01 12:00:00'
P20180701_12 TIMESTAMP' 2018-07-01 13:00:00'
P20180701_13 TIMESTAMP' 2018-07-01 14:00:00'
P20180701_14 TIMESTAMP' 2018-07-01 15:00:00'
P20180701_15 TIMESTAMP' 2018-07-01 16:00:00'
P20180701_16 TIMESTAMP' 2018-07-01 17:00:00'
P20180701_17 TIMESTAMP' 2018-07-01 18:00:00'
P20180701_18 TIMESTAMP' 2018-07-01 19:00:00'
P20180701_19 TIMESTAMP' 2018-07-01 20:00:00'
P20180701_20 TIMESTAMP' 2018-07-01 21:00:00'
P20180701_21 TIMESTAMP' 2018-07-01 22:00:00'
P20180701_22 TIMESTAMP' 2018-07-01 23:00:00'
P20180701_23 TIMESTAMP' 2018-07-02 00:00:00'
P20180702_00 TIMESTAMP' 2018-07-02 01:00:00'
P20180702_01 TIMESTAMP' 2018-07-02 02:00:00'
P20180702_02 TIMESTAMP' 2018-07-02 03:00:00'
P20180702_03 TIMESTAMP' 2018-07-02 04:00:00'
P20180702_04 TIMESTAMP' 2018-07-02 05:00:00'
P20180702_05 TIMESTAMP' 2018-07-02 06:00:00'
P20180702_06 TIMESTAMP' 2018-07-02 07:00:00'
P20180702_07 TIMESTAMP' 2018-07-02 08:00:00'
P20180702_08 TIMESTAMP' 2018-07-02 09:00:00'
P20180702_09 TIMESTAMP' 2018-07-02 10:00:00'
P20180702_10 TIMESTAMP' 2018-07-02 11:00:00'
P20180702_11 TIMESTAMP' 2018-07-02 12:00:00'
P20180702_12 TIMESTAMP' 2018-07-02 13:00:00'
P20180702_13 TIMESTAMP' 2018-07-02 14:00:00'
P20180702_14 TIMESTAMP' 2018-07-02 15:00:00'
P20180702_15 TIMESTAMP' 2018-07-02 16:00:00'
41 rows selected.
I’ve run a small anonymous block to rename the (system-named) interval partitions into some sensible names to reflect the date range the partition covers. Let’s now look at the typical queries we will now be performing on the SALES table:
SQL> set autotrace traceonly explain
SQL> select * from sales
2 where ts > timestamp '2018-07-02 15:00:00';
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 387 | 7 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 9 | 387 | 7 (0)| 00:00:01 | 41 |1048575|
|* 2 | TABLE ACCESS FULL | SALES | 9 | 387 | 7 (0)| 00:00:01 | 41 |1048575|
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')
SQL> select * from sales
2 where ts > timestamp '2018-07-02 15:00:00'
3 and product = 12;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 7 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 1 | 43 | 7 (0)| 00:00:01 | 41 |1048575|
|* 2 | TABLE ACCESS FULL | SALES | 1 | 43 | 7 (0)| 00:00:01 | 41 |1048575|
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PRODUCT"=12 AND "TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')
SQL>
SQL> select * from sales
2 where ts > timestamp '2018-07-02 15:00:00'
3 and customer = 25;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 7 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 1 | 43 | 7 (0)| 00:00:01 | 41 |1048575|
|* 2 | TABLE ACCESS FULL | SALES | 1 | 43 | 7 (0)| 00:00:01 | 41 |1048575|
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUSTOMER"=25 AND "TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')
SQL>
SQL> select max(amt) from sales
2 where ts > timestamp '2018-07-02 15:00:00';
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 7 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 33 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 9 | 297 | 7 (0)| 00:00:01 | 41 |1048575|
|* 3 | TABLE ACCESS FULL | SALES | 9 | 297 | 7 (0)| 00:00:01 | 41 |1048575|
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')
SQL> set autotrace off
All of them scan a tiny portion of the data, namely, just the hours of sales data relevant to the query, and the query response times will be relatively consistent for all cases no matter which customer, product or other predicate will be passed because the data to be scanned is a fixed number of hours.
But there’s a problem here. If I am partitioning to the hour, or even to the minute…then it won’t be long before I have a lot of partitions. In the latter case (minutes) I will be up to over 500,000 partitions in the first year of SALES alone! That is a lot of database metadata to store. There is the partitions themselves, plus optimizer statistics on them, plus historical optimizer statistics, plus potentially histograms on every column. A lot of optimizer data might lead to expensive parse times because there is just so much information to wade through when optimizing queries.
But we only need the extreme granularity of partitions for the SALES table for today. Once today ticks over and becomes “yesterday”, then we might only need a partition for the entire day. And once “yesterday” ticks over to “last week”, then maybe only weekly partitions are needed and so forth.
One of the cool things in 18c is ability to do this style of maintenance with negligible disruption to service. Because I have named my partitions in a logical fashion, here is a simple routine to merge “yesterdays” hourly partitions into a single one for the day.
SQL> set serverout on
SQL> declare
2 d date := date '2018-07-01';
3 ddl varchar2(4000);
4 begin
5 select listagg(partition_name||chr(10),',') within group ( order by partition_position )
6 into ddl
7 from user_tab_partitions
8 where table_name = 'SALES'
9 and partition_name like 'P'||to_char(d,'yyyymmdd')||'%';
10
11 ddl := 'alter table sales merge partitions '||ddl||' into partition p'||to_char(d,'yyyymmdd')||' online';
12
13 dbms_output.put_line(ddl);
14 execute immediate ddl;
15 end;
16 /
alter table sales merge partitions
P20180701_00
,P20180701_01
,P20180701_02
,P20180701_03
,P20180701_04
,P20180701_05
,P20180701_06
,P20180701_07
,P20180701_08
,P20180701_09
,P20180701_10
,P20180701_11
,P20180701_12
,P20180701_13
,P20180701_14
,P20180701_15
,P20180701_16
,P20180701_17
,P20180701_18
,P20180701_19
,P20180701_20
,P20180701_21
,P20180701_22
,P20180701_23
into partition p20180701 online
PL/SQL procedure successfully completed.
Now I have a single partition for yesterday’s data, and hourly partitions for today’s data.
SQL> col high_value format a60
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES'
4 order by partition_position;
PARTITION_NAME HIGH_VALUE
------------------------------ -------------------------------
P1 TIMESTAMP' 2018-07-01 00:00:00'
P20180701 TIMESTAMP' 2018-07-02 00:00:00'
P20180702_00 TIMESTAMP' 2018-07-02 01:00:00'
P20180702_01 TIMESTAMP' 2018-07-02 02:00:00'
P20180702_02 TIMESTAMP' 2018-07-02 03:00:00'
P20180702_03 TIMESTAMP' 2018-07-02 04:00:00'
P20180702_04 TIMESTAMP' 2018-07-02 05:00:00'
P20180702_05 TIMESTAMP' 2018-07-02 06:00:00'
P20180702_06 TIMESTAMP' 2018-07-02 07:00:00'
P20180702_07 TIMESTAMP' 2018-07-02 08:00:00'
P20180702_08 TIMESTAMP' 2018-07-02 09:00:00'
P20180702_09 TIMESTAMP' 2018-07-02 10:00:00'
P20180702_10 TIMESTAMP' 2018-07-02 11:00:00'
P20180702_11 TIMESTAMP' 2018-07-02 12:00:00'
P20180702_12 TIMESTAMP' 2018-07-02 13:00:00'
P20180702_13 TIMESTAMP' 2018-07-02 14:00:00'
P20180702_14 TIMESTAMP' 2018-07-02 15:00:00'
P20180702_15 TIMESTAMP' 2018-07-02 16:00:00'
18 rows selected.
I stress – this is not my recommendation to race out and partition every transactional table you have, and drop all of the indexes 
. But it with so many online partitioning operations in 18c, it raises some exciting new opportunities there were not available in previous releases. So start thinking about how you can exploit this to get advantages with the partitioning option that might sit “outside the box” of the standard usage.
Learning is not a spectator sport 
Got some thoughts? Leave a comment