RE: partitioned tables
- From: "Kerber, Andrew W." <Andrew.Kerber@xxxxxxx>
- To: tim@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Dec 2007 07:23:50 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman
Sent: Tuesday, December 11, 2007 7:39 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx> 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) NULL
CONSTRAINT F_MBR_ACCT_TRANSACTION_PK PRIMARY KEY
(MBR_ACCT_ID,TXN_DATE_ID,SPONSOR_TRAN_ID))
partion by range (TXN_DATE_ID)
(partition jan_2007 values less than (20070131) tablespace
smart_part_jan_2007,
partition feb_2007 values less than (20070228) tablespace
smart_part_feb_2007,
.
.
.
partition jan_2007 values less than (20071231) tablespace
smart_part_dec_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>
-- //www.freelists.org/webpage/oracle-l
------------------------------------------------------------------------------
NOTICE: This electronic mail message and any attached files are confidential.
The information is exclusively for the use of the individual or entity intended
as the recipient. If you are not the intended recipient, any use, copying,
printing, reviewing, retention, disclosure, distribution or forwarding of the
message or any attached file is not authorized and is strictly prohibited. If
you have received this electronic mail message in error, please advise the
sender by reply electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your computer
system. Thank you.
==============================================================================
Other related posts:
