Re: partitioned tables

  • From: Tim Gorman <tim@xxxxxxxxx>
  • Date: Wed, 12 Dec 2007 16:07:25 -0700

Not true.

But why speculate at all?  A question like this is so easy to test out.  No need to ask or wonder when facts are so easy to gather...



Joe Smith wrote:
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!

-- //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!
-- //www.freelists.org/webpage/oracle-l

Other related posts: