Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: partitioned tables
Its not the best way to do it anymore, but its not all that uncommon,
especially in legacy software. It had its uses for sorting. This
method of dealing with dates is definitely something a dba should be
familiar with, because there are plenty of apps that still use it.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Tuesday, December 11, 2007 7:39 PM
To: oracle-l_at_freelists.org
Subject: Re: partitioned tables
Why use a number to represent a date? There is no advantage whatsoever, and numerous disadvantages.
...and as Gus pointed out, range partitioning syntax is "VALUES LESS THAN" not "VALUES LESS THAN OR EQUAL TO"...
Gus Spier wrote:
Of course, it's up to you, but if you set NLS_DATE_FORMAT='YYYYMMDD', you won't need the TXN_DATE_ID. I might also recommend that your partition by range (TXN_DATE_ID) use values less than 20070201 instead of 20070131.
r,
Gus
On Dec 11, 2007 4:51 PM, Joe Smith <joe_dba_at_hotmail.com> wrote:
My primary key is a composite of 3 columns, but I range parition only on one column.
It that a problem?
I know it is a date field, but we are using a number for a date, i.e. yyyymmdd.
Would I need to set up a partitioned tablespace for the index, i.e. PK?
CREATE TABLE F_MBR_ACCT_TRANSACTION
(
MBR_ACCT_ID INTEGER NOT NULL , TXN_DATE_ID INTEGER NOT NULL , SPONSOR_TRAN_ID INTEGER NOT NULL , TXN_DTTM DATE NULL , TXN_AMOUNT NUMBER(14,2) NULLCONSTRAINT F_MBR_ACCT_TRANSACTION_PK PRIMARY KEY
smart_part_jan_2007, partition feb_2007 values less than (20070228) tablespace smart_part_feb_2007, .
Share life as it happens with the new Windows Live. Share now! <http://www.windowslive.com/share.html?ocid=TXT_TAGHM_Wave2_sharelife_12 2007>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 12 2007 - 07:23:50 CST