Home » SQL & PL/SQL » SQL & PL/SQL » Sub-partition within the sub-partition
Sub-partition within the sub-partition [message #183880] Mon, 24 July 2006 04:13 Go to next message
bala_id
Messages: 27
Registered: July 2005
Junior Member
Hi All,
Is it possible to create subpartiton within the
subpartition. My main partition would be Range partition
within the range partiton, there is one subpartition(List partition), within this list partiton I want to create one list partition. Please help me on this.

Re: Sub-partition within the sub-partition [message #183881 is a reply to message #183880] Mon, 24 July 2006 04:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm sure I've seen a message like this somewhere before.....
Re: Sub-partition within the sub-partition [message #184052 is a reply to message #183881] Tue, 25 July 2006 02:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't think so.

The annoying thing with LIST partition is that you can only partition on one column. Otherwise you could just include both LIST partition columns in the sub-partition clause.

Sub-partitioning is pretty extreme. Oracle performs pretty well with up to 5M rows per partition. So with daily RANGE partitions, I would normally only consider sub-partitioning when I had significantly more than that - say 10M/day.

So then if you need to sub-sub-partition..... How much data do you have?

If you're really desperate, you can create a denormalized column that is the concatenation of your two list-partition columns and then sub-partition on that. I think you'd have to be really, really, desperate though.

Ask yourself why you want to sub-sub-partition. Is it really going to give you that much better performance?

Ross Leishman.
Re: Sub-partition within the sub-partition [message #184288 is a reply to message #184052] Wed, 26 July 2006 01:06 Go to previous messageGo to next message
bala_id
Messages: 27
Registered: July 2005
Junior Member
Hi,

Thanks for your information.

I have few questions based on your answer.

(i) Is there any limitation in creating partiton.
Can I have as many as partition?
Because for each date, I would have 24 combinations
of data. So I have a column in the table with date and
the other column, I have to create 365*24 = 8760 partitions.
Would oracle accommodate that many partitons?

(ii) If it allows to have that many partitons,
Is there any maintain or performance problem would
occur?

Please clarify my above doubts.

Thanks in advance.

With Regards,
Bala
Re: Sub-partition within the sub-partition [message #184316 is a reply to message #184288] Wed, 26 July 2006 02:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do you have 24 combinations because you want hourly partitions? Or is it just a coincidence that you have 24 different combinations of other values?

There is certainly no problem with daily partitions. I don't see enormous problems with the sub-partitions either, although you might want to benchmark non-unique LOCAL indexes. These indexes have a segment per partition/sub-partition. If you perform a query that uses the non-unique local index, but does not include the partition/sub-partition column, then Oracle must scan every index segment. If you have (say) 10000 partitions, one small range scan may have to perform quite a bit of I/O.

The same problem can occur with a partitioned unique index if the partition column is not the leading column.

If you are going to have that many partitions, you might be better off making your non-unique indexes GLOBAL. This creates its own problems though.

How many rows per day do you have? Do you really need sub-partitions?

Ross Leishman
Re: Sub-partition within the sub-partition [message #184344 is a reply to message #184316] Wed, 26 July 2006 04:33 Go to previous messageGo to next message
bala_id
Messages: 27
Registered: July 2005
Junior Member
Hi,

Thanks again for your reply.

Normally I would get 25 - 30 millions of data into this table.
I don't have index on this table. Why I want partition is
I have to update this table if the row already exists otherwise I have to insert. Instead of updating, I am deleting whatever I am getting for insert then I am inserting.
It is just coincident of 24, it is not hour.
I definetely need month partiton for purging. Within that
If I have partiton for date with another column called DCP(value from 1 to 24), I can directly truncate that partiton and insert records.

Let me know if you want any other details.




Re: Sub-partition within the sub-partition [message #184522 is a reply to message #184344] Wed, 26 July 2006 21:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You should not partition and sub-partition on two date components (month partition and date sub-partition). Just range-partition on date.

Is that 25-30 million rows per day, per month, or total. If it is per-day, I can see why you would want to sub-partition. Otherwise, partition daily or monthly - aiming for 1-5million rows per partition.

To do a bulk UPSERT (update existing rows or insert):
- Get the MAX() and MIN() dates of the rows you are loading.
- For each partition covering that date range:
- Use CTAS to build a copy of the partition in another table (same tablespace), that includes all rows to be loaded into the partition plus all rows from the original partition to be kept.
- Add indexes and constraints to the new table
- Use Partition Exchange to swap the original partition with its new replacement.

I use this method, and find that it is efficient even when the number of rows to upsert forms a small percentage (b/w 1% and 5%) of the total rows in the partition.

I think sub-partitioning should really only be considered when you are dealing with truly enormous data volumes, and that doesn't seem to be the case here.

Ross Leishman
Re: Sub-partition within the sub-partition [message #184971 is a reply to message #184522] Sat, 29 July 2006 04:06 Go to previous messageGo to next message
bala_id
Messages: 27
Registered: July 2005
Junior Member
Hi Leishman,

Thanks for your reply.

Your advise is fine. But will exchange partition works for update. For e.g I have table

tabA(a number,b number)
partition by range(a)
(partition part1 values less than(1),
partition part2 values less than(2),
partition part3 values less than(10));

I have data on part3 as
2,2
3,2
4,2
5,2
6,2

Now I am moving these values to temp table.
And I am inserting new values into temp table as
2,3
3,3
4,3
7,3

If I do exchange partition , all the values in temp table
is appended into tabA. What I want is if the values is
already there, it has to update otherwise it has to insert.
Following are the expected values in tabA for partition part3.
2,3
3,3
4,3
5,2
6,2
7,3
But what I am getting following values using exchange partition.
2 2
3 2
4 2
5 2
6 2
2 3
3 3
4 3
7 3

How to handle this problem using exchange partition.

Thanks in advance.



Re: Sub-partition within the sub-partition [message #185034 is a reply to message #184971] Sun, 30 July 2006 07:26 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So, you only move those rows into the temp table that DONT exist in your new data.

- Load the temp table
- Merge
  INSERT /*+append*/ 
  INTO temp_table 
  SELECT * 
  FROM part_table PARTITION(part3)
  WHERE key NOT IN (SELECT key FROM temp_table)

- Exchange partition.

Ross Leishman
Previous Topic: updating a view
Next Topic: BULK Collect, CTAS And PL/SQL with COMMIT AFTER 10,000 Records !!
Goto Forum:
  


Current Time: Tue Dec 06 08:08:19 CST 2016

Total time taken to generate the page: 0.09814 seconds