Q

What column for partition key?

I thought of having a partition on "TxnDt," but then I have to maintain data of 50 years (1975-2025). So is it feasible to have a partition on TxnDt or is there any other column that can be considered as the partition key?

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 a partition on "TxnDt," but then I have to maintain data of 50 years (1975-2025). So is

it feasible to have a partition on TxnDt or is there any other column that can be considered as the 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 needed basis)
Currency (All the available currencies) Amount (Format: Number)

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

The column(s) that you use as the partition key should be the column that is most commonly used in your queries. One of the primary reasons for partitioning tables with lots and lots of rows is to take advantage of "partition pruning" to dramatically reduce the number of rows considered in your query. For instance, if you partition table1 on the TXNDT column, then this column should show up in the WHERE clause of your queries similar to the following:
SELECT * FROM tbl1 WHERE txndt BETWEEN '01/01/2006' AND '01/31/2006';

If all of the rows in the date range above are in one partition, Oracle can completely ignore the other partitions as the values for this column will not appear in the other paritions. Oracle prunes the non-participating partitions automatically.

I'm confused how your date values can have a specific format. Oracle dates do not have a specific format, so your question for TBL2 does not make much sense to me. Oracle stores dates as a floating point number which defines the number of days that have elapsed since some point in the very distant past. So Oracle does not store just the month (MON) and the year (YYYY) in the DATE datatype. If you are using a string datatype, like VARCHAR2 or CHAR, to store your date values, then I would highly recommend reconsidering this design as you will have to write your own date arithmetic routines.

This was first published in November 2006

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close