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?
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