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:
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.
The explain plan for sales_jan2014 if we choose a range (1st Jan 2014 -14th Jan 2014) from the table :
Continue by creating the other “partitions”
Create tha sales_feb2014 table in the same way as sales_jan2014:
And the sales_mar2014 table:
And so on untill all 12 month “partitions” have been created.
And now to the magic – we create the VIEW containing all “partitions”
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:
The indexes from all “partitions” are scanned, and the select returns the expected two rows:
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:
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
PSTOPcolumns 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
PSTOP, optionally with an additional attribute.
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:
And then we create the VIEW to hold the “partitions”
Let us check what we have in our database:
What does the CBO think about some queries against the v_saleslist2014 -VIEW?
SELECT … where state_code IN (‘CA’,’XA’)
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
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” ?
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.
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.
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?
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.
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!
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.