What column for 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?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top