OraFAQ Forum: Performance Tuning » Which Partition to Apply? What should be the partition key?

Home » RDBMS Server » Performance Tuning » Which Partition to Apply? What should be the partition key?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Which Partition to Apply? What should be the partition key? [message #203194]

Tue, 14 November 2006 04:13

blazingrock4u
Messages: 30
Registered: March 2006
Location: India

Member

Hi All,

TBL1
LoadDt (Format: dd/mm/yyyy)
ID (Sequence num)
TxnDt (Format: dd/mm/yyyy)
Currency (All the available currencies)
Total (Format: Number)
ItemCode (For every Item there will be different code and there can be millions of Items)

Here I thought of having partition on "TxnDt", but the I have to maintain data of 50 years (1975-2025).
So is it feasible to have partition on TxnDt or is there any other column that can be considered as
partition key?
===============
TBL2
LoadDT (Format: dd/mm/yyyy)
PId (Format: "SAP****" where * will be a sequence number)
FinYr (Format: "MON-YYYY")
Role (There can be many roles and one can add as many as required - on need basis)
Currency (All the available currencies)
Amount (Format: Number)

Here I thought of having partition on "FinYr", but the format (MON-YYYY) doesn't allows me to do so.
Is there any possbility of having partition on "FinYr"?

So far I've thought about Range Partition only.
Is there any other partition that can be considered for these two tables?

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #203355 is a reply to message #203194]

Tue, 14 November 2006 19:53

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Why are you partitioning in the first place? What problem are you trying to solve by partitioning?

- Segment manageability
- Archiving
- Store old data in read-only tablespaces
- Load data by truncating and replacing partitons
- Faster queries with partition-wise joins
- Faster queries with partition pruning

Obviously it would be good to achieve all of them, but you may not be able to.

Prioritize them, and we'll see what we can do.

Ross Leishman

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #203359 is a reply to message #203355]

Tue, 14 November 2006 21:02

blazingrock4u
Messages: 30
Registered: March 2006
Location: India

Member

One of the main reason for having the partition is: Volume of data.

I am told that, millions of records will be populated into this table (and it'll be for past 20 years and future 20 years).

This table will be used to update other tables based on some conditions/joins.

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #203409 is a reply to message #203359]

Wed, 15 November 2006 01:15

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Non-partitioned tables can hold just as much data as partitioned tables. If you are partitioning just because its big, then why partition it at all?

Think it over again. What do you want to achieve? It's likely to be one of the things I listed above.

Ross Leishman

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #203635 is a reply to message #203194]

Wed, 15 November 2006 22:13

blazingrock4u
Messages: 30
Registered: March 2006
Location: India

Member

Thanks for the immediate response Ross.

Considering the requirement is:
"Faster queries with partition pruning"
what are steps to be taken.

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #203662 is a reply to message #203635]

Thu, 16 November 2006 00:14

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

To use Partition Pruning, partition on whichever column you will use in your WHERE clause to filter the query will be your partition column.

WHERE col BETWEEN 'val1' AND 'val2'     -- USE RANGE PARTITION ON col

WHERE col IN ('val1','val2',...)        -- USE RANGE OR LIST PARTTION ON col

WHERE col IN (SELECT val FROM tab ...)  -- USE LIST PARTITION ON col

Ross Leishman

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #203989 is a reply to message #203662]

Fri, 17 November 2006 04:13

blazingrock4u
Messages: 30
Registered: March 2006
Location: India

Member

Okay.

I've thought of having Range Partition on LoadDt (because this is going to be my main WHERE predicate)

TBL1
LoadDt (Format: dd/mm/yyyy)
ID (Sequence num)
TxnDt (Format: dd/mm/yyyy)
Currency (All the available currencies)
Total (Format: Number)
ItemCode (For every Item there will be different code and there can be millions of Items)
===============
TBL2
LoadDT (Format: dd/mm/yyyy)
PId (Format: "SAP****" where * will be a sequence number)
FinYr (Format: "MON-YYYY")
Role (There can be many roles and one can add as many as required - on need basis)
Currency (All the available currencies)
Amount (Format: Number)

Here, I've thought of having List Partition on FinYr.

Please advise whether my asuumptions are correct and the indexes that can be created on it.

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #204026 is a reply to message #203989]

Fri, 17 November 2006 06:29

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Where does FinYr come into the picture? Doe query on FinYr? Or on LoadDt?

>>Please advise whether my asuumptions are correct ...

What assumptions? List them.

>> ...and the indexes that can be created on it

What indexes? Who mentioned indexes. Be specific.

Ross Leishman

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #204331 is a reply to message #204026]

Mon, 20 November 2006 03:29

blazingrock4u
Messages: 30
Registered: March 2006
Location: India

Member

FinYr is one of the fields from TABLE2. And they will be pre-defined in a separate metadata table.

LOADDT will be used as the filter in most of the SELECTs on TABLE2. The reason for not using LOADDT as range partition is as below:
Say we load the data on 14-NOV-2006 for the first time, then the whole data (which could be in billions) will be stored in one partition.
Note: Historic data should be preserved.

So, i thought we better to have List partn on FinYr and Index on LoadDt. Plz advise.

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #204459 is a reply to message #204331]

Mon, 20 November 2006 20:09

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

So, if you LIST partition FinYr, will it give you any benefits that you want? If not, don't do it.

If partitioning on LoadDt will allow you to Partition Prune, and that is the primary benefit you are interested in, then do it. The fact that the partitions will be imbalanced is unimportant.

However, if most queries will be over a LoadDt range that includes a skewed partition containing 90% of the data, then it will not confer any advantage. You would have to go back to my original question and ask what you hope to improve through partitioning. If all you hope to achieve is Partition Pruning, then there is no point partitioning because Partition Pruning won't improve performance.

Ross Leishman

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #204550 is a reply to message #204459]

Tue, 21 November 2006 04:05

blazingrock4u
Messages: 30
Registered: March 2006
Location: India

Member

Benefit for going with FinYrCode is balancing of the data. And also because, over a period of time, the Old FinYrCodes (say Mar-2005, Apr-2005) will not be used any longer.

I will be running my jobs on the TABLE2, based on the LOADDT. Once I am done with one partition of LoadDt, then I will not be looking back at this partition again (unless the second run is going to store the data in the same partition, which is unlikely going to happen, becuase each load will be done on monthly basis)

Correct me if I am wrong.
I assume that the partition key "need not be neccessarily" based on the WHERE clause filteration key.

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #204678 is a reply to message #204550]

Tue, 21 November 2006 12:09

blazingrock4u
Messages: 30
Registered: March 2006
Location: India

Member

Does a hash partition on (LoadDt, FinYr) help the cause, by anyways?

Report message to a moderator

Re: Which Partition to Apply? What should be the partition key? [message #204721 is a reply to message #204678]

Tue, 21 November 2006 20:00

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

No, a composite partition key will just mean that even fewer queries will perform a prune.

>>>Benefit for going with FinYrCode is balancing of the data

Balancing data is not a benefit, except aesthetically. The database doesn't care.

>>>I will be running my jobs on the TABLE2, based on the LOADDT.

If that means you will be running SQL SELECT, UPDATE, or DELETE statements with range conditions on LoadDt, AND you want these statements to partition prune, then LoadDt is a good candidate for the partition key.

>>>over a period of time, the Old FinYrCodes (say Mar-2005, Apr-2005) will not be used any longer.

If this means you want to store older FinYrs in read-only tablespaces, or you want to easily archive data based on FinYr, then it is a good candidate.

You can't have both. Decide what is more important and partition on that.

Ross Leishman

Report message to a moderator

Previous Topic:	DB tuning
Next Topic:	Difference Between Global Index and Local Index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 02:40:47 CST 2024