Oracle Database INTERVAL Partitioning and Virtual Columns

  • 0

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:

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.

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!

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

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:

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:

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

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.

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

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.

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

 


  • 0

Oracle Database Interval Partition Set Up Script

Get Social!

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

 


  • 0

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:

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:

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.

When partitioned monthly, the LESS THAN value for inserting DATE ‘9999-12-31’ would have to be DATE ‘1000-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.


  • 0

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.

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.

Finally install the required version of Java.

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.

 


  • 0

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.

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

 


  • 0

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.

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

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.

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.

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:

Install Java 8

For Oracle JDK8 use:

 

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.

Or

 


Visit our advertisers

Search

Visit our advertisers