Learning is not a spectator sport

The internet heralded a very common evolution in databases. You started with a database that backed a physical store front, or the data was created/entered by human beings who sat in a location near to the data centre because that was governed by the network technology of the time. Even so, you had a lot of data (or at least, what was considered a lot of data for the time) and so you might opt to partition your tables by date.

SQL> create table par_ts
  2  (
  3    a     number,
  4    b     number,
  5    c     date
  6  )
  7  partition by range(c)
  8  interval (numtoyminterval(1,'MONTH'))
  9  ( partition p1 values less than
 10      (date '2001-01-01')
 11  );

Table created.

And then boom, you had some success and the fledgling web meant that now you were accepting data with greater frequency, some times even sub-second. The next logical step was to take those dates and give them some sub-second precision as timestamps.

SQL> create table par_ts
  2  (
  3    a     number,
  4    b     number,
  5    c     timestamp(6)
  6  )
  7  partition by range(c)
  8  interval (numtoyminterval(1,'MONTH'))
  9  ( partition p1 values less than
 10      (timestamp '2008-01-01 00:00:00 EST')
 11  );

Table created.

And then an even bigger boom! Suddenly the dot.com explosion meant that people from all over the world could be customers to your database, and due to our little planet wanting to rotate on its own axis, we now had people in different time zones wanting to store data in your database.

“No problem” you think to yourself. “I’m using Oracle and they support timezones”, but then you get an unpleasant surprise when you try to recreate your partitioned table with time zone support.

SQL> create table par_ts
  2  (
  3    a     number,
  4    b     number,
  5    c     timestamp(6) with time zone
  6  )
  7  partition by range(c)
  8  interval (numtoyminterval(1,'MONTH'))
  9  ( partition p1 values less than
 10      (timestamp '2023-09-01 00:00:00 EST')
 11  );
partition by range(c)
                   *
ERROR at line 7:
ORA-03001: unimplemented feature

The fundamental issue here is the moment you introduce time zones into equation, the concept of “absolute time” becomes somewhat vague. For example, if my table is partitioned by month, and its 6am on February 1st 2024, then my data should go into the February partition right? Maybe not, because I’m in Perth and someone entering the exact same transaction in San Francisco is looking at the clock reading 2pm on January 31st. For them, it is the previous month!

Throw in the fact that the damn planet wants to go around the sun each year, combined with people wanting more time in the evening to the go the beach in summer 😀, and things get complicated quickly.

Returning to the concept of “absolute time”, if we can get everyone in our database to agree to use UTC then life would be a lot easier, but lets face it, people live and breathe in their local time zone. However, with partitioning in the Oracle Database, we can take advantage of virtual columns to achieve both goals.

SQL> create table par_ts
  2  (
  3    a     number,
  4    b     number,
  5    c     timestamp(6) with time zone,
  6    c_utc timestamp(6) generated always as (sys_extract_utc(c))
  7  )
  8  partition by range(c_utc)
  9  interval (numtoyminterval(1,'MONTH'))
 10  ( partition p1 values less than
 11      (timestamp '2023-09-01 00:00:00 EST')
 12  );

Table created.

Of course, the downside here is that no-one is going the query your database on the column called C_UTC, because they will be dealing solely in their local time zone and will query on the column C, which is not the partition key.

But check out this nifty optimization. When your predicate is referring to a time stamp with a time zone, we are going to convert that to UTC in order to do the comparison. (This is unrelated to partitioning). The optimizer then sees we have a virtual column that matches this expression and will perform partition pruning in the normal way.

SQL> insert into par_ts (a, b, c )
  2  select rownum, rownum, systimestamp+rownum
  3  from dual
  4  connect by level <= 100;

100 rows created.

SQL> exec dbms_stats.gather_table_stats('','PAR_TS')

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select *
  2  from par_ts
  3  where c > timestamp '2023-09-03 00:00:00 EST'
  4  and   c < timestamp '2023-12-06 00:00:00 EST';

Execution Plan
----------------------------------------------------------
Plan hash value: 650112904

---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |     1 |    30 |  1093   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|        |     1 |    30 |  1093   (1)| 00:00:01 |     4 |     7 |
|*  2 |   TABLE ACCESS FULL      | PAR_TS |     1 |    30 |  1093   (1)| 00:00:01 |     4 |     7 |
---------------------------------------------------------------------------------------------------

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

 2 - filter("PAR_TS"."C_UTC"<TIMESTAMP' 2023-12-06 05:00:00.000000000' AND
            "PAR_TS"."C_UTC">TIMESTAMP' 2023-09-03 05:00:00.000000000')

SQL>
SQL> set autotrace off
SQL>

Hence even though you are querying with time zone data, we can still do a good job of partitioning pruning after that data has been mapped to UTC.

Note: Be aware that this optimization is specific to this particular circumstance. In general, the database cannot automatically remap a non-partition key predicate to the equivalent virtual column partition key.