RE: partitioned tables

From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Wed, 12 Dec 2007 09:02:26 -0500
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF018E2F90@EXCNYSM0A1AJ.nysemail.nyenet>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joe Smith Sent: Wednesday, December 12, 2007 8:29 AM To: tim_at_evdbt.com; oracle-l_at_freelists.org 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_at_evdbt.com
	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.

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)  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>

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. Get it now!
<http://www.xbox.com/en-US/hardware/wheretobuy/>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 12 2007 - 08:02:26 CST