RE: partitioned tables

From: Kerber, Andrew W. <Andrew.Kerber_at_umb.com>
Date: Wed, 12 Dec 2007 07:56:43 -0600
Message-ID: <D40740337A3B524FA81DB598D2D7EBB30C391BB4@x6009a.umb.corp.umb.com>

Not that I have ever seen. If you really need only the date and not time portion of the date, just do a trunc on the column.. From what I have seen, partitioning works really well on date columns.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joe Smith Sent: Wednesday, December 12, 2007 7: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/>

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.

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

Received on Wed Dec 12 2007 - 07:56:43 CST