Pre-creating Interval Partitions

One of the major problems with interval-based partitioning is the statistics. Partitions appear dynamically as-needed based upon data being inserted or udpated, and any partition magically appearing isn’t going to have any statistics.

This is generally a stability issue as you will, at best, be using dynamic statistics for your optimizations. So how do we deal with it? My preferred method is to pre-create the partitions and copy statistics from a good partition into the pre-created partition. But how do we get the partitions to appear? Here’s 2 options:

  1. Insert data into the row with the partition key for the partition we wish to create, and rollback. This can be tricky, especially with tables containing many NOT NULL columns, and is subject to failure based upon table evolution.
  2. Lock the relevant partition in shared mode using the commandLOCK TABLE .x. PARTITION FOR <partition-key> IN SHARE MODE;

    This will place a shared lock on the non-existant partition, which Oracle will create so it can lock it. A much less messy solution, and not one I had thought of until shown the light by Dan Jankowski.

So does option 2 work? Here’s a quick example:

10:51:55 NEIL @ ORCL01 > CREATE TABLE interval_table (id NUMBER, created_date DATE)
10:51:55   2             PARTITION BY RANGE (created_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
10:51:55   3           ( PARTITION part_01 values LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY')))
10:51:55   4  /
Table created.

10:51:55 NEIL @ ORCL01 > select table_name, partition_name,high_value from user_tab_partitions order by 1,2;
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
INTERVAL_TABLE                 PART_01                        TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


use a shared lock to generate a new partition

10:51:55 NEIL @ ORCL01 > lock table interval_table partition for (to_date('01-JAN-2016','DD-MON-YYYY')) in share mode;
Table(s) Locked.

10:51:55 NEIL @ ORCL01 > select table_name, partition_name,high_value from user_tab_partitions order by 1,2;
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
INTERVAL_TABLE                 PART_01                        TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TABLE                 SYS_P647                       TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

10:51:55 NEIL @ ORCL01 > lock table interval_table partition for (to_date('01-FEB-2016','DD-MON-YYYY')) in share mode;
Table(s) Locked.
10:51:55 NEIL @ ORCL01 > lock table interval_table partition for (to_date('01-MAR-2016','DD-MON-YYYY')) in share mode;
Table(s) Locked.
10:51:55 NEIL @ ORCL01 > lock table interval_table partition for (to_date('01-APR-2016','DD-MON-YYYY')) in share mode;
Table(s) Locked.
10:51:55 NEIL @ ORCL01 > select table_name, partition_name,high_value from user_tab_partitions order by 1,2;
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
INTERVAL_TABLE                 PART_01                        TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TABLE                 SYS_P647                       TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TABLE                 SYS_P648                       TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TABLE                 SYS_P649                       TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TABLE                 SYS_P650                       TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

10:51:55 NEIL @ ORCL01 > -- and release the locks... the rollback doesn't rollback the creation of the new partitions.
10:51:55 NEIL @ ORCL01 > rollback;
Rollback complete.

So now what? To get the stats right I follow the following rule set:

Firstly, lock the table stats when you create the table and have a dedicated, focused stats job. This stop the general stats job from getting in the way of this this important partitioned table.

  • Create a new partition
  • Copy stats using DBMS_STATS.COPY_TABLE_STATS from your fullest partition (with FORCE=TRUE to override the locked stats option). Always try to pretend you have more data than you really have if faking it – it’s safer as an optimized plan with larger expected data sets processing a smaller data set tends to work much better than the stats assuming a small data set and being surprised by lots of data. Consider using SCALE_FACTOR if you have a growing dataset. Don’t reply upon Optimizer magic, such as Adaptive Execution Plans to dig out of a hole.
  • Once a partition becomes “old” (like a no-longer used date-range partition), gather actual stats to get all your stats for that partition accurate. That may even become your new baseline stats copy partition. You will possibly never need to gather stats again for that partition.
  • Be careful if you are copying stats when you have an object-level change. For example, if you put a new index on, you need to re-copy stats post change to any pre-created partitions. Empty pre-created partitions will have accurate (i.e. empty) stats and that’s really not what you want.
  • Repeat as often as you pre-create a new partition

SYSTIMESTAMP INTERVAL Processing

…or how to calculate dates in Oracle.

This is just a quick post to try to encourage the use of the INTERVAL function when adjusting (sys)timestamps (or dates). I thought this would be better expressed through a quick script with comments to show how using the traditional Oracle method of calculating fractions of a day can cause problems and make you have to think more than necessary.

There are 2 main issues with using the traditional Oracle method of calculating date/time changes. Firstly, it’s strange. You have to calculate fractions of a day. 1 second is 1/86400, 1 minute is 1/1440 [maybe 1/(24*60) expresses it better]. Secondly, with timestamps it causes an implicit conversion to a date type, with all of the unintended consequences that implicit conversion carries.

Lets run the script and see what happens:

Script Output

> -- Lets just set the date format explicitly before we start. > alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' session SET altered.
> -- Lets start simple and add a day. > select systimestamp, systimestamp + 1 from dual
SYSTIMESTAMP  SYSTIMESTAMP+1             ------------- ------------------------- 09-DEC-11 18.39.53.026000000 +00:00 10-DEC-2011 18:39:53  
> -- Hang on, doing this the traditional Oracle way has implicitly cast > -- the timestamp into a sysdate format. We need to convert it back. > -- Lets try that again, but add 3 hours instead > select systimestamp, to_timestamp( > systimestamp + (3/24), >      'DD-MON-YYYY HH24:MI:SS' ) from dual
SYSTIMESTAMP  TO_TIMESTAMP(SYSTIMESTAMP+(3/24),'DD-MON-YYYYHH24:MI:SS') ------------- ------------------------- 09-DEC-11 18.39.53.026000000 +00:00 09-DEC-11 21.39.53.000000000
> -- So we have an implicit conversion, and we've lost the timestamp precision. > -- Now lets add 3 hours, 5 minutes 10.5 seconds. >select systimestamp, to_timestamp( > systimestamp + (3/24) + (5/(24*60)) + (10.5/24/60/60), > 'DD-MON-YYYY HH24:MI:SS') from dual
SYSTIMESTAMP  TO_TIMESTAMP(SYSTIMESTAMP+(3/24)+(5/(24*60))+(10.5/24/60/60),'DD-MON-YYYYHH24:MI:SS') ------------- ------------------------- 09-DEC-11 18.39.53.026000000 +00:00 09-DEC-11 21.45.04.000000000
> -- And we completely lost the .5 in the 10.5 as we've converted to a sysdate. > -- It was rounded up. You might also have noticed that I have used some > -- different date processing to calculate fractions of a day. > -- There are many different way to calculate time in Oracle: > -- 10 minutes can be 10/1440, 10/60/24, 10/(60*24), 0.00694, 600/86400 etc, > -- It's all tricky to understand and standards vary from company to company, > -- if the company actually has a standard. > -- There is a better way, using the INTERVAL function. It goes like this: > -- Lets start simple and add a day.
> select systimestamp, systimestamp + INTERVAL '1' day from dual
SYSTIMESTAMP  SYSTIMESTAMP+INTERVAL'1'DAY ------------- --------------------------- 09-DEC-11 18.39.53.042000000 +00:00 10-DEC-11 18.39.53.042000000 +00:00
> -- So far so good. And we haven't lost the data type either! > -- No implicit conversion to break our code in the future. > -- Now lets try to add 3 hours
> select systimestamp, systimestamp + INTERVAL '3' hour from dual
SYSTIMESTAMP  SYSTIMESTAMP+INTERVAL'3'HOUR ------------- ---------------------------- 09-DEC-11 18.39.53.042000000 +00:00 09-DEC-11 21.39.53.042000000 +00:00
> -- Note the indicator is always singular. > -- Now lets add 3 hours, 5 minutes 10.5 seconds
> select systimestamp, > systimestamp + INTERVAL '3' hour > + INTERVAL '5' minute >                    + INTERVAL '10.5' second >       from dual
SYSTIMESTAMP  SYSTIMESTAMP+INTERVAL'3'HOUR+INTERVAL'5'MINUTE+INTERVAL'10.5'SECOND ------------- ------------------------------------------------------------------- 09-DEC-11 18.39.53.042000000 +00:00 09-DEC-11 21.45.03.542000000 +00:00                                 
> -- Spot on - and the precision is correct too > -- My mam/mum/mom* could read it and work out what was going on. > -- But she is an OCA** (* delete as applicable) (**not really) > -- There you go. Get your company to use this nomenclature and you're home free. > -- No more strange time-base calculations or implicit conversion errors. > -- It also works with DATE types too.

Original Script set echo on
-- Lets just set the date format explicitly before we start. alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; -- Lets start simple and add a day. select systimestamp, systimestamp + 1 from dual; -- Hang on, doing this the traditional Oracle way has implicitly cast -- the timestamp into a sysdate format. We need to convert it back. -- Lets try that again, but add 3 hours instead select systimestamp, to_timestamp(        systimestamp + (3/24),                                    'DD-MON-YYYY HH24:MI:SS' ) from dual; -- So we have an implicit conversion, and we've lost the timestamp precision. -- Now lets add 3 hours, 5 minutes 10.5 seconds. select systimestamp, to_timestamp(        systimestamp + (3/24) + (5/(24*60)) + (10.5/24/60/60),                                    'DD-MON-YYYY HH24:MI:SS') from dual; -- And we completely lost the .5 in the 10.5 as we've converted to a sysdate. -- It was rounded up. You might also have noticed that I have used some -- different date processing to calculate fractions of a day. -- There are many different way to calculate time in Oracle: -- 10 minutes can be 10/1440, 10/60/24, 10/(60*24), 0.00694, 600/86400 etc, -- It's all tricky to understand and standards vary from company to company, -- if the company actually has a standard. -- There is a better way, using the INTERVAL function. It goes like this: -- Lets start simple and add a day. select systimestamp, systimestamp + INTERVAL '1' day from dual; -- So far so good. And we haven't lost the data type either! -- No implicit conversion to break our code in the future. -- Now let's try to add 3 hours select systimestamp, systimestamp + INTERVAL '3' hour from dual; -- Note the indicator is always singular. -- Now lets add 3 hours, 5 minutes 10.5 seconds select systimestamp, systimestamp + INTERVAL '3' hour                                   + INTERVAL '5' minute                                   + INTERVAL '10.5' second                      from dual; -- Spot on - and the precision is correct too -- My mam/mum/mom* could read it and work out what was going on. -- But she is an OCA** (* delete as applicable) (**not really) -- There you go. Get your company to use this nomenclature and you're home free. -- No more strange time-base calculations or implicit conversion errors. -- It also works with DATE types too.
%d bloggers like this: