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 -0700From: tim@xxxxxxxxxxx: 
oracle-l@xxxxxxxxxxxxxxxxxxxx: 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.freelists.org/webpage/oracle-l 
_________________________________________________________________
The best games are on Xbox 360.  Click here for a special offer on an Xbox 360 
Console.
http://www.xbox.com/en-US/hardware/wheretobuy/

Other related posts: