Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 21632 invoked from network); 12 Dec 2007 17:56:45 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-69-64-49-119.inaddr.intergenia.de with SMTP; 12 Dec 2007 17:56:45 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 913B17D82B1; Wed, 12 Dec 2007 18:56:46 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 00951-05; Wed, 12 Dec 2007 18:56:46 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0CBC17D7C31; Wed, 12 Dec 2007 18:56:46 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 12 Dec 2007 18:09:49 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B0FBE7D8762 for ; Wed, 12 Dec 2007 18:09:49 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 24076-06 for ; Wed, 12 Dec 2007 18:09:49 -0500 (EST) Received: from mx04.mailboxcop.com (mx04.mailboxcop.com [206.125.223.74]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6E1857D868B for ; Wed, 12 Dec 2007 18:09:49 -0500 (EST) Received: from secure9.apollohosting.com (secure9.apollohosting.com [206.125.217.251]) by mx04.mailboxcop.com (8.13.8/8.13.8) with ESMTP id lBCN9max006795 for ; Wed, 12 Dec 2007 17:09:48 -0600 Received: (qmail 13582 invoked from network); 12 Dec 2007 17:09:48 -0600 Received: from 65-115-37-3.dia.static.qwest.net (HELO ?10.2.0.15?) (tim@65.115.37.3) by hologistics.co.uk with ESMTPA; 12 Dec 2007 23:09:47 -0000 Message-ID: <476069AD.4010405@evdbt.com> Date: Wed, 12 Dec 2007 16:07:25 -0700 From: Tim Gorman Organization: Evergreen Database Technologies, Inc. User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 CC: oracle-l@freelists.org Subject: Re: partitioned tables References: <6d3967610712111625t199e5383s99c20278f022ba6b@mail.gmail.com> <475F3BC8.3000100@evdbt.com> In-Reply-To: Content-Type: text/html; charset=ISO-8859-1 X-Spam-Score: undef - evdbt.com is whitelisted. X-CanItPRO-Stream: outgoing (inherits from default) X-Canit-Stats-ID: Bayes signature not available X-Scanned-By: CanIt (www . roaringpenguin . com) on 192.168.15.6 X-archive-position: 3898 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: tim@evdbt.com Precedence: normal Reply-to: tim@evdbt.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain 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@evdbt.com
To: oracle-l@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.

r,

Gus

On Dec 11, 2007 4:51 PM, Joe Smith <joe_dba@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.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.freelists.org/webpage/oracle-l