I have a 5 Million row table that I'm trying to recreate as a partitioned table sy roughly into partitons pt1,pt2.............now my question is after creating these partitons should i change my application who orginally say acessed the tables as
select * from t1
now should i go around and change the code at every place to
select * from pt1
select * from pt2
and so on ?what could be the repercussions of partioning a table
i am running oracle8.1.6 R2 on redhat linux 6.2
The great aspect of Oracle Partitioning is that it knows which partition to go to based on the partitioning key set up during table creation. Therefore, this eliminates the need as a application developer to understand the physical storage aspects of the data. Your developer will only need to perform queries as normal select * from x where y=a;
There are two types of Oracle Partitioning range and hash. I would need to know more about your data before I could recommend which way method of partitioning to use. We use hash partioning very effectively on an OLTP application.
What type of data and how will it be accessed in your 5 million row table.
In order to access a partition directly, you would use:
select * from t1 partition (pt1);
If you have chosen a good partition key, oracle will know which partitions you need to look at. I would look at the explain plan output of the queries you intend to run in order to determine if they will use the partition scheme you have setup.
BTW, I wouldn't partition a table just because it had 5M rows. A good index would be just as effective.
your syntax for accesing partitiones is wrong, but in general you do not need to change application.
Partitioning tables is one thing, another one is indexes: local indexes are partitioned as table is and therefor more maintenable. But if your query can not prune partitions then I believe it can take longer time to get response on local index(as oracle has to look into every index partition lineary) than on global index.
my application is a normal oltp and lil bit of batch can anybody tell me a reason why i should not partiton a table taht is 5 million rows ? what type of aprtitons to use hash range or composite
I would recommend looking strongly into partioning any time your table exceeds 2GB that goes for individual partitions. Lets say you start out with 5 million rows, and 2 partitions, you conclude that each partition is taking up 1.5GB of space based on you user_segments view. Lets say you add another 2 million rows to your table, it grows to 2.1GB. Now you conclude it's time to add another 2 partitions.
Note: Only create partitions in even numbers, this little know fact is buried deep within the Oracle docs and know it from experience myself.
In general use a hash index partition key in a OLTP database because you are always going to be going after a small set of specific records. You would want to use range partitioning in a warehouse environment where you are performing large sweeps on the data and know the ranges you are looking for.
It doesn't sound like you need to use composite keys yet, as your table is not large enough to benefit, but if you think you need to access your data based on ranges and there is a alot of it you can benefit from a combination of range,hash.
thanks for all of you for sharing those thoughts of your's
Click Here to Expand Forum to Full Width