Partition – possible in Oracle SE?

(c) Ann Sjökvist

Partition in Standard Edition ?


A couple of weeks ago I had a great talk with my friend Joel Goodman about Oracle Standard Edition, and he pointed out the fact, that we can achieve some kind of partition environment in a Standard Edition with little work by using some constraints, indexes and union all clause to make the magic. I was told it would actually also be pretty fast. So I thought I just needed to test it out on my sandbox.

Remember though, that this sandbox is running on a laptop, so if you think the idea is worth a try, please test it out on a test environment before actually implementing it on a production system. No need to underestimate the power of testing, right?

For me the partition feature has been “partition= Enterprise Edition only”, so I have not given it any deeper thoughts until now.
So a big thanks to Joel for sharing and guiding me into this direction of thinking!I do believe it can solve some performance issues that might occur in a Standard Edition Database system.

Time to test the idea…

First some Oracle Enterprise Edition Partition Philosophy
First I think it would be a good idea to get acquainted with the philosophy of the partition feature in the Enterprise Edition Database.

I took Tom Kyte’s advice from his post about On Oracle Database 12c, Part 3 and read Arup Nanda’s article on the subject:

“If you are unfamiliar with partitioning and the terms associated with it, I suggest that you first read “Partition Decisions,” by Arup Nanda

I checked the Oracle 12c White Paper on partition:

Oracle Partitioning, first introduced in Oracle 8.0 in 1997, is one of the most important and successfulfunctionalities of the Oracle database that enables large tables and indexes to be subdivided in smaller
pieces, improving the performance, manageability, and availability for tens of thousands ofapplications.

And so the license about partition feature:

11g and 12c license

Why would we like to have the partition feature?

  • To subdivide large environments into smaller pieces and in believe of making the DBA’s life easier.

How is it done in an Enterprise Edition?

  • Reference: Arup Nanda post above will show how it is done, but there seems to be three different methods:  “partition by range, partition by list, and partition by hash”. Plus either a local or a global index.

What do we get out of a partitioned environment?

  • We get improved performance, management, and availability.

Can we achieve this in a Standard Edition Database?

After reading the article by Arup Nanda I do believe a company should first evaluate its business needs against this article, and follow the guidelines mentioned by Mr Nanda.
Once it is done, the company are better off making the decision on whether they need the Enterprise Edition partition feature or is it ok with a less fancy solution that can be built in a Standard Edition database.

Is it possible to create a “partition by range in Standard Edition?
Let us make a test case.

I created some monthly tables with some constraints and indexes. Important note: They all need to have the same columns.

For the moment I just create tables for jan-mars, but the idea is to create one table for each month (jan-dec). For now I was just curious on getting a hunch about what the CBO would do.

create_table_jan

create_table_jan_2create_table_jan_3create_table_jan_4

create_table_jan_full

The explain plan for sales_jan2014  if we choose a range (1st Jan 2014 -14th Jan 2014) from the table :

create_table_jan_5

Continue by creating the other “partitions”

Create tha sales_feb2014 table in the same way as sales_jan2014:

create_table_febcreate_table_feb_1create_table_feb_2

And the sales_mar2014 table:

create_table_marcreate_table_mar_2create_table_mar_1

And so on untill all 12 month “partitions” have been created.

And now to the magic –  we create the VIEW containing all “partitions”

view_sales

Just add all the remaining tables (april-december) to the VIEW creation to make it complete.

Let’s check the explain plan on a range query on the VIEW, when we would like to have rows between 15.2.2014 – 16.2.2014:

view_sales_2view_sales_3

The indexes from all “partitions” are scanned, and the select returns the expected two rows:

view_sales_4

So this will as I understand it, fullfil the task of a “partition by range”. A more accurate question might be; at what amount of data will this solution start to be unusable?

The only way of getting an answer to that question is to implement this solution into a test environment, that is a snapshot of the actual production system, and start testing.

#7.8.2014 TestStart due to comment by Ilmar

Thanks Ilmar Kerm for sharing your thought about “partition by range” and the bind variable usage.

I performed a test with the bind variables, and the explain plan looks like this:

sales_feb_bindsales_feb_bind_1

I read about the partition pruning that happens in a real partition environment (=Enterprise Edition).

In the Standard Edition  “partition” we make use of the constraints. After talking to Joel, I understood that they are actually helping the CBO to make a good plan, and can also be used in a EE environment to speed things up a little bit. Implemented triggers will not do the trick.

Is it possible to explain the two different predicate information; one with literals and the other with binds, by saying that the CBO is just handling a partition prune issue?  Of cause we do not have any values in PSTART and PSTOP columns of the explain plan, since Oracle is not aware of our partitioning.

Refering to this DOC :

If at parse time Oracle can identify which contiguous set of partitions are accessed, then the PSTART and PSTOP columns in the execution plan show the begin and the end values of the partitions being accessed. Any other cases of partition pruning, including dynamic pruning, show the KEY value in PSTART and PSTOP, optionally with an additional attribute.

#7.8.2014 TestEnd

tweet_3tweet_4

Thanks Ilmar for your comments. I really appreciate it and thanks for taking the time!


Is it possible to create a “partition by list” in Standard Edition?

Let us make a second test case.

I created three tables containing some pre-defined values using the constraint and one index. Important note: All tables must have the same columns.  The tables are like in Mr Nanda’s example:

sales_ca

sales_ct

sales_def

And then we create the VIEW to hold the “partitions”

view

Let us check what we have in our database:

sales_tab

sales_ind

sales_constraint

sales_views

What does the CBO think about some queries against the  v_saleslist2014 -VIEW?

SELECT…where state_code=’CA’

sql_ca_1sql_ca_2

SELECT … where state_code IN (‘CA’,’XA’)

sql_caxa_1sql_caxa_2

SELECT … everythingsql_full_caxa_1

SELECT … where state_code NOT IN (‘CA’,’XA’)sql_notin_caxa_1sql_notin_caxa_2

It seems like CBO is choosing an index when we can expect it to, and performing  a FTS when that probably would be the fastest way to get the results.

But whether this solution is good enough in a huge Standard Edition Environment or not, it is impossible to say without more thourough tests.

Theoretically as I understand this, we can create a “partition by list” feature. I guess that in Enterprise Edition the “partition by list”  will perform something under the hood, that this SE solution cannot provide, but what it is I cannot tell since I have no clue at the moment.


Is it possible to create a “partition by hash” in Standard Edition?

As I understood the Enterprise Edition feature in Mr Nanda’s article, this we cannot achieve in SE. Anybody having other thoughts, on how this might be possible to to create in a Standard Edition Environment?

It would be just awesome to get a push into right direction, if somebody out there could provide some examples on how one might be able to create a “partition by hash” in a Standard Edition environment. Thanks in advance!

#comment8.8.2014 by Timo RaitalaaksoStart

comment8aug2014_timo

Thanks Timo for your contribution. I really appreciate it and thanks for taking the time! Sure need to test it out.

My reply to this comment, can be found down below if curious. 🙂

#comment8.8.2014 by Timo RaitalaaksoEnd

So how about the “Ease of Administration” ?
Table
The time we create our tables we can create them on different tablespaces as I did in the example (jan2014, feb2014, mar2014 and so on). This would give us the possibility to make the tablespace Read-Only, and at least our RMAN would speed up.

Index
In Enterprise Edition we are able to rebuild a partition of an index. This we cannot do in a Standard Edition Database. We need to rebuild the entire index. I guess we can always try to split data into more tables (on own tablespaces), and create some more indexes, but is it a good and solid solution for your system, will only get clarified by testing the solution.

As you notice. The SE partition solution talked about here, will not be as elegant as the one in an Enterprise Edition Database.

Data Purge
As Mr Nanda points out in his article, when we delete rows some undo and redo will be generated, which impact database performance.

In Enterprise Edition we can just drop the partition (=alter table trans drop partition y05q1;), and it will have very little impact on the database, since it will just update the dictionary, so it knows that the partition is not available anymore.
This benefit is not of cause available in this Standard Edition “partition” solution. We must delete the rows in a normal way, and by doing so we might cause some degradation on the performance of the system.
Can we perhaps eliminate this by building a partition solution from the business needs point of view? I mean by looking a little deeper into how the company actually uses the data and what is the software trying to accomplish?

The only way to answer all those questions is by testing our ideas, right?

Data archiving.
It seems that Enterprise Edition partition feature has a neat way of archiving data by using the “exchange partition”. Once again Oracle will just update the dictionary to point to another place; no physical move executed, and therefore not causing any big impact on the system.
As you expected. This cannot be done in a Standard Edition Database. Could maybe the Flashback Data Archives feature  provide a solution? We  can use the basic FDA in a standard edition database, without history table optimization. You can find more information about FDA from an article written by Bjoern Rost.

Conclusions

In a way it seems possible to create a partitioned environment in Standard Edition. It will not be as elegant as in Enterprise Edition, but if it is good enough for the business, then why not just test it out, and see if it can be an option and provide you with a portion of the EE partition features promise:

We get improved performance, management, and availability.

And remember. Always test before implementing into a production system!

What do you think about the idea?

Have a nice day!
Ann
-Turku 7.8.2014-


#tweet7Aug2014Start

tweet_1

tweet_2

Thanks J. Vlatko for sharing this link and giving your comments. I really appreciate it and thanks for taking the time!

The document he is refering to is “Oracle7 Tuning, release 7.3.3”. And yes some old documents will sometimes help us SE DBA’s to find a solution.  So why not go ahead and read it? It might be worth your time.

#tweet7Aug2014End


8 thoughts on “Partition – possible in Oracle SE?”

  1. We are using similar on smaller environments in production.

    In practice I have found problematic the following:
    – code sometimes needs two branches (EE vs SE)
    – different plans (EE &&) means possible different problems
    – execution plan are much bigger – harder to follow
    – complex SQL may be tricky to tune
    – ROWID – we write dynamic code for this
    – administration (ex. add new partition) requires custom code
    – function based indexes are not optimized perfectly
    (http://stackoverflow.com/questions/34725337/union-all-and-function-based-index)

  2. Nice post Ann. One can also use partition views ‘over’ partitioned tables so that the optimiser can choose different plans for each partition.

    Ill test and send you demos next week.

  3. To hash partition you need a hash function. Wonder why anyone would like to do such a mess, but here is a thought. Instance_id sounds like a nice hash function.

    create table ta(p int primary key, c int, h as (mod(p,2)-1) check (h=0));
    create table tb(p int primary key, c int, h as (mod(p,2)) check (h=0));
    insert into ta(p,c) values (1,1);

    create or replace view t as select p,c from ta union all select p,c from tb;

    create or replace trigger t_i instead of insert on t for each row
    begin
    if mod(:new.c,2)-1=0
    then insert into ta(p,c) values (:new.c,:new.p);
    else insert into tb(p,c) values (:new.c,:new.p);
    end if;
    end t_i;
    /

    insert into t(p,c) values (2,2);

    select * from t order by c;

    1. Hi Timo,
      Thanks for your contribution. I really appreciate it and thanks for taking the time!

      When I started this blog about Oracle Standard Edition I had a vision trying to build some kind of toolbox to help others finding solutions to daily work. There are so much good stuff on the internet, but almost always from an Enterprise Edition point of view, so I thought by writing articles from EE perspective, and trying to turn those “goodies” into a “Standard Edition Way of doing”, this blog hopefully will serve a purpose. The purpose is to help each other’s enjoying the opportunities that Standard Edition can provide, and give a hand if possible.

      So to your good question “Wonder why anyone would like to do such a mess?”, maybe Arup Nanda’s thoughts will give some guidance to why?
      Sometimes it may not be possible to define the ranges of—or a set of known values for—each partition. A typical example is a table, CUST, containing customers, with the CUST_ID column as the primary key. The CUST_ID value is an incrementally increasing but rather meaningless number, so a range partition based on these values may also be rather meaningless.

  4. Hash partition: I’ve created tables like #1, #2, … And decided on the INSTEAD OF trigger on what partition to place the row. In case of RAC instance_id is a really good number. If not RAC, maybe a random number or something like that?

    Also about range partitioning – the checks constraints are really good at eliminating partitions from scanning, but only when literals are used in SELECT statement, bind variables do not give the same partition elimination anymore.

Leave a Reply

Your email address will not be published. Required fields are marked *