Oracle Database INTERVAL Partitioning and Virtual Columns

Oracle Database INTERVAL Partitioning and Virtual Columns

Get Social!

oracle-logoI bumped into this issue recently which prevents you INSERTING a date of 31-12-9999 into an INTERVAL partitioned table due to the fact that the LESS THAN partition value would be larger than an Oracle Date.

After speaking with Jeff Moss he mentioned that we might be able to get round the problem by using a virtual column on the table and partition on that. I didn’t find a satisfactory solution using this method, but understanding some of the optimisers quirks and abilities when using partitions and virtual columns was rather interesting.

Starting with the following table structure:

CREATE TABLE interval_test
(
  data_from_date   DATE NOT NULL
, data_to_date     DATE
, business_key     NUMBER
)
PARTITION BY RANGE (data_to_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_interval_test_0 VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT
/

The purpose here is to create a tuple timestamped table for storing historical and current data. The data_from_date represents the date the row is known to be valid from and the data_to_date is when the row is known to changed or been removed entirely from the source system. The current row, the one that is still available in the source system, would not have an ‘end date’ and therefore would either be NULL or a made up ‘high’ date, such as DATE 9999-12-31.

And this is where the problems start.

Winding back a little, and forgetting the high date scenario for a moment, let’s take a look at how queries use the partitions. Generally, queries would use a specific date that’s between the data_from_date and data_to_date to get a view of the data for a point in time.

EXPLAIN PLAN FOR 
SELECT * 
FROM interval_test 
WHERE DATE '2001-01-01' BETWEEN data_from_date AND data_to_date
/
-----------------------------------------------------------------------------
| Id  | Operation                | Name          | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|               | 00:00:01 |    14 |1048575|
|*  2 |   TABLE ACCESS FULL      | INTERVAL_TEST | 00:00:01 |    14 |1048575|
-----------------------------------------------------------------------------

As you can see, the partitions are being pruned where possible, starting at partition 14. Side note: because this table is INTERVAL partitioned and there is no maximum value set for our data_to_date Oracle may have to look at all the partitions after partition 14. Potentially, this could be all the way up to 1048575 which is the maximum partition number that could exist. It’s a shame the optimiser isn’t a little more intelligent here – I know there aren’t 1048575 partitions and so should the optimiser!

SELECT COUNT(*) FROM dba_tab_partitions WHERE table_name = 'INTERVAL_TEST';

  COUNT(*)
----------
        34

Anyway, back to the matter at hand. Inserting a high date into this table results in an error (described further here).

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

A potential solution to the problem was to add a virtual column to the table that translates the high date into something that would go into a monthly partition. There are a bunch of options, to name a few:

  • TRUNC the date to ‘MM’ and minus 1
  • Minus 31 away from the date
  • TRUNC the date to ‘IQ’ – this would give quarterly partitions and not monthly partitions, but let’s not rule it out.
  • TO_CHAR the date to a YYYYMM, then CAST that as a NUMBER and use numbered partitioning.

I’m not going to explore all of these options and the set up script for you to test further scenarios is here. Note the test SQL will be:

SELECT * FROM interval_test WHERE DATE '2001-01-01' BETWEEN data_from_date AND data_to_date

Truncate the month and minus 1

This was my first test as this would give me the previous months value to partition on which would nicely fit in without causing the ORA-01841 error. The virtual column expression is as follows:

, partition_column AS (TRUNC(data_to_date, 'MM') - 1)

The result wasn’t a success with a query plan of:

------------------------------------------------------------------------
| Id  | Operation           | Name          | Time     | Pstart| Pstop |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|               | 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | INTERVAL_TEST | 00:00:01 |     1 |1048575|
------------------------------------------------------------------------

Minus 31

Next I simplified the expression by simply minusing 31 days from the date being inserted. This removes the TRUNC ‘complication’ to produce a less complex expression.

, partition_column AS (data_to_date -31)

Again, the result was a failure with a query plan of:

------------------------------------------------------------------------
| Id  | Operation           | Name          | Time     | Pstart| Pstop |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|               | 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | INTERVAL_TEST | 00:00:01 |     1 |1048575|
------------------------------------------------------------------------

Does it ever work?

Yes! The following expressions work, but as you’ll notice have mixed results in solving our initial problem. It seems as soon as any math operator is specified that the optimiser cannot work out that partition pruning can occur. Using TRUNC however, which to me seems a more complex function, works perfectly. Interestingly when it does work, the filter section of the query plan shows an additional predicate that your query doesn’t specify but that is used to partition prune.

filter("DATA_FROM_DATE"<=TO_DATE(' 2001-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "DATA_TO_DATE">=TO_DATE(' 2001-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"INTERVAL_TEST"."PARTITION_COLUMN">=TRUNC(TO_DATE(' 2001-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'),'IQ'))

Here are some of the virtual columns that do partition prune however, might not solve the original issue.

, partition_column AS (TRUNC(data_to_date, 'IQ'))

-----------------------------------------------------------------------------
| Id  | Operation                | Name          | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|               | 00:00:01 |   KEY |1048575|
|*  2 |   TABLE ACCESS FULL      | INTERVAL_TEST | 00:00:01 |   KEY |1048575|
-----------------------------------------------------------------------------
, partition_column AS (TRUNC(data_to_date)) 

-----------------------------------------------------------------------------
| Id  | Operation                | Name          | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|               | 00:00:01 |   KEY |1048575|
|*  2 |   TABLE ACCESS FULL      | INTERVAL_TEST | 00:00:01 |   KEY |1048575|
-----------------------------------------------------------------------------

 


Oracle Database Interval Partition Set Up Script

Get Social!

This script is used to set up the test table for the following blog posts:

DROP TABLE interval_test PURGE
/
CREATE TABLE interval_test
(
  data_from_date   DATE NOT NULL
, data_to_date     DATE
, business_key     NUMBER
, partition_column AS (TRUNC(data_to_date, 'MM') - 1) -- change this
)
PARTITION BY RANGE (partition_column) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_interval_test_0 VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT
/

INSERT INTO interval_test (data_from_date, data_to_date, business_key)
SELECT   DATE '2000-01-01' + (ROWNUM)     data_from_date
,        DATE '2000-01-01' + (ROWNUM + 1) data_to_date
,        ROWNUM                           table_key
FROM     dual
CONNECT BY LEVEL <= 365
/

COMMIT
/
 

BEGIN
	Sys.DBMS_Stats.Gather_Table_Stats(
		    Ownname          => null
		,   Tabname          => 'interval_test'
		,   Estimate_Percent => 100
		,   Degree           => Sys.DBMS_Stats.Auto_Degree
		,   Granularity      => 'AUTO'
		,   Cascade          => Sys.DBMS_Stats.Auto_Cascade
		,   Force            => true
		);
END;
/
 

EXPLAIN PLAN FOR SELECT * FROM interval_test WHERE DATE '2001-01-01' BETWEEN data_from_date AND data_to_date
/
SELECT plan_table_output FROM TABLE(dbms_xplan.display(format=>'ALL -PROJECTION'))
/

Oracle Database INTERVAL Partitioning Date Error With 31-12-9999

Get Social!

oracle-logoYou may have seen the below error when using dates at the extremes with an Oracle Database. This post focuses on the scenario where you’re inserting a high date, such as 31-12-9999 into an INTERVAL partitioned table. High dates such as this are often used in warehouse environments where from and to dates are specified to denote a period of time the data was valid for – a high date specifying the current data row.

Take the following table and partition definition:

CREATE TABLE interval_date_test
(
  my_date   DATE NOT NULL
)
PARTITION BY RANGE (my_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_interval_date_test0 VALUES LESS THAN (TO_DATE('2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT
/

Each row inserted will go into a partition for that year-month combination. This can be useful for high volume data that’s generally queried for one-or-so months at a time.

If, however, you try to insert the following row, you’ll get an error:

INSERT INTO interval_date_test (my_date) VALUES(DATE '9999-12-31');
INSERT INTO interval_date_test (my_date) VALUES(DATE '9999-12-31')
            *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

This is due to the way the Oracle Database defines a partition. An INTERVAL RANGE partition in an Oracle Database is defined as accepting dates LESS THAN a specific value. The below example would store any dates up to the clock ticking over into May 2016.

VALUES LESS THAN (TO_DATE('2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

When partitioned monthly, the LESS THAN value for inserting DATE ‘9999-12-31’ would have to be DATE ‘10000-01-01’ and that is not a valid date value. Oracle Database dates must be between the year -4713 and +9999 inclusive as explained by the error.

The solution? Either use a non-INTERVAL partition strategy, reduce the date being INSERTed or experiment with a Virtual Column and partition on that.


Manually downloading Java For Use With Webupd8’s apt-get Package

Category : How-to

Get Social!

java-logoOracle Java is one of the biggest problems in life. That’s just a fact. Half of it is the fact that you have to download it directly from Oracle each time, quarter of it is the almost daily updates (compounded by the first problem), and the remainder is dealing with the fact that the first problem is there by design.

Thankfully the boys and girls over at Webupd8 have created and maintain an apt-get deployed version of Oracle Java that handles initial setup and future updates. The apt package doesn’t actually contain the Oracle Java binaries, because that’s against Oracle’s TOC’s, but it contains a script that downloads it for you – all behind the scenes.

Sometimes Corporate networks block access to Oracle’s download servers and therefore block access to you installing Oracle Java by this method. Except…

You can manually download the JDK from Oracle and copy it to the required machine in the apt cache directory. When you then run the apt-get command the installer will realise that the tar.gz containing the Java binaries is available and won’t need to download it. You’ll still need the internet to download the apt package, but you won’t need to access oracle.com.

First off, make the relevant cache directory for use with your version of java. This example assumes Java 8.

mkdir /var/cache/oracle-jdk8-installer

Copy the latest version of Java into the above cache directory. This assumes that the download is in your current directory and is Java version 8 update 91.

cp jdk-8u91-linux-x64.tar.gz /var/cache/oracle-jdk8-installer/jdk-8u91-linux-x64.tar.gz

Finally install the required version of Java.

apt-get install oracle-java8-set-default

You’ll see the message Installing from local file as part of the installation indicating the installer hasn’t needed to download the Java binaries from Oracle.

Reading package lists... Done
Building dependency tree
Reading state information... Done
oracle-java8-set-default is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 3 not upgraded.
2 not fully installed or removed.
After this operation, 0 B of additional disk space will be used.
Do you want to continue? [Y/n] y
Setting up oracle-java8-installer (8u92+8u91arm-2~really8u91~webupd8~0) ...
Installing from local file /var/cache/oracle-jdk8-installer/jdk-8u91-linux-x64.tar.gz

 


DataStax Cassandra 3.2 Bash Install Script

Get Social!

The below script installs the DataStax distribution of Cassandra 3.2.x and the latest Oracle Java 8 on Debian. Copy and paste the script into a file called install_cassandra.sh and execute it as root.

Change the version 3.2 on line 12 to match the version you’d like to install.

#!/bin/bash
set -e

apt-get update
apt-get install -y wget curl

echo "Installing repos"
echo "deb http://ppa.launchpad.net/webupd8team/java/ubuntu xenial main" | tee /etc/apt/sources.list.d/webupd8team-java.list
echo "deb-src http://ppa.launchpad.net/webupd8team/java/ubuntu xenial main" | tee -a /etc/apt/sources.list.d/webupd8team-java.list
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys EEA14886

echo "deb http://debian.datastax.com/datastax-ddc 3.2 main" | tee -a /etc/apt/sources.list.d/cassandra.sources.list
curl -L https://debian.datastax.com/debian/repo_key | apt-key add -


echo "Installing binaries"
apt-get update
echo oracle-java7-installer shared/accepted-oracle-license-v1-1 select true | /usr/bin/debconf-set-selections
apt-get install -y oracle-java8-installer datastax-ddc

echo "Complete"

Then connect to the local Cassandra instance run the cqlsh tool.

cqlsh

Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.2.1 | CQL spec 3.4.0 | Native protocol v4]
Use HELP for help.
cqlsh>

 


Install Oracle Java In Debian/ Ubuntu using apt-get

Category : How-to

Get Social!

java-logo

Oracle Java, due to Oracle’s license conditions, it quite a pain to install. The problem is that anyone who isn’t Oracle isn’t allowed to distribute the JDK binaries – they have to be downloaded strictly from Oracle. Luckily the guys over at webupd8team have been creative in this area and created and apt-get package that downloads the Java binaries from Oracle, presents the license agreement that you have to accept and and installs Java on the local machine.

Add Java Repository Using apt-add-repository on Ubuntu

We’ll use the apt-add-repository tool in this section to add the webupd8team’s ppa repository to our local apt package manager. This method should be used on Ubuntu – see the next section for Debian.

Before running the command, make sure you’ve got apt-add-repository installed as it’s often missing in the more minimal installations.

apt-get install software-properties-common

Older versions of Ubuntu (13.10 and older) would need a slightly different package:

apt-get install python-software-properties

Once you’ve got the package installed, go ahead and add the ppa Java repository and update your local apt cache with the new available packages.

apt-add-repository ppa:webupd8team/java
apt-get update

Add Java Repository Manually on Debian

If you’re using Debian or would like to manually add and maintain the list of repositories used by your apt installation then use the below commands.

echo "deb http://ppa.launchpad.net/webupd8team/java/ubuntu xenial main" | tee /etc/apt/sources.list.d/webupd8team-java.list
echo "deb-src http://ppa.launchpad.net/webupd8team/java/ubuntu xenial main" | tee -a /etc/apt/sources.list.d/webupd8team-java.list
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys EEA14886
apt-get update

This will create a new file containing the repository location in /etc/apt/sources.list.d/webupd8team-java.list

 

Installing Oracle Java

When using this method, the first step of the install process is to download the Oracle Java binaries from Oracle.com. You’ll then need to accept the license agreement and then the installation will begin.

You can automatically accept the license agreement, which is useful when scripting the install where it’s impossible to interact with the process. The first command in the following sections instructs the install to automatically accept the license agreement. If you’d like to read and manually accept the agreement then do not run the first line.

Choose your Java version:

Install Java 7

For Oracle JDK7 use:

echo oracle-java7-installer shared/accepted-oracle-license-v1-1 select true | /usr/bin/debconf-set-selections
apt-get install oracle-java7-installer

Install Java 8

For Oracle JDK8 use:

echo oracle-java8-installer shared/accepted-oracle-license-v1-1 select true | /usr/bin/debconf-set-selections
apt-get install oracle-java8-installer

 

Java Variables

Finally, you can set the Java variables automatically using another package provided by the webupd8team. Upgrading, say from Java 7 to Java 8 will automatically remove the predecessors package.

apt-get install oracle-java7-set-default

Or

apt-get install oracle-java8-set-default

Scripted Install

See the following for copy and paste scripts for installing:


Visit our advertisers

Search

Quick Poll

Do you use ZFS on Linux?

Visit our advertisers