RE: partitioned tables
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <joe_dba@xxxxxxxxxxx>
- Date: Wed, 12 Dec 2007 09:02:26 -0500
Joe,
There is no evidence that a number datatype results in quicker queries
than a date datatype (that I know of). Date columns are actually stored
as numbers anyway.
There is no correct way to do this - this is purely a professional
judgement call. But a date column allows you to perform date math -
like subtract 10 from the date column to get the value for 10 days
prior, or use the ADD_MONTHS or LAST_DAY function on a date column to
return a different month, or the last day of a month. If you stored the
value as your design team suggests, then date math would require
"to_date" functions to be used. Not a big deal, but why force yourself
down that road? It sounds like your design team does not fully
understand an Oracle Date datatype.
Just my 2 cents.
Tom
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Joe Smith
Sent: Wednesday, December 12, 2007 8:29 AM
To: tim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: partitioned tables
The logical design team seems to think that the date as a number
datatype will speed up queries. Not true?
thanks.
________________________________
Date: Tue, 11 Dec 2007 18:39:20 -0700
From: tim@xxxxxxxxx
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
________________________________
The best games are on Xbox 360. Click here for a special offer on an
Xbox 360 Console. Get it now!
<http://www.xbox.com/en-US/hardware/wheretobuy/>
Other related posts:
