Ask the Expert

What column for partition key?

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?

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: