Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 325 invoked from network); 11 Dec 2007 20:29:23 -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; 11 Dec 2007 20:29:23 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 80EDC7D8135; Tue, 11 Dec 2007 21:29:23 -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 06199-05; Tue, 11 Dec 2007 21:29:23 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E25DD7D6432; Tue, 11 Dec 2007 21:29:22 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 11 Dec 2007 20:41:57 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 741D17D7860 for ; Tue, 11 Dec 2007 20:41:57 -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 30175-04 for ; Tue, 11 Dec 2007 20:41:57 -0500 (EST) Received: from s-utl01-dcpop.stsn.net (s-utl01-dcpop.stsn.net [72.255.0.201]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 31D7C7D782B for ; Tue, 11 Dec 2007 20:41:56 -0500 (EST) Received: from s-utl01-dcpop.stsn.net ([127.0.0.1]) by s-utl01-dcpop.stsn.net (SMSSMTP 4.1.2.20) with SMTP id M2007121120415321382 for ; Tue, 11 Dec 2007 20:41:53 -0500 X-Spam-Status: No, hits=0.0 required=9.9 tests=ALL_TRUSTED: -2.867,AWL: -0.432,BAYES_00: -1.665, HTML_50_60: 0.539,HTML_MESSAGE: 0.001,MIME_HTML_ONLY: 1.156, UPPERCASE_25_50: 0.039 X-Spam-Level: Received: from [10.60.103.184] ([10.60.103.184]) by s-utl01-dcpop.stsn.net for oracle-l@freelists.org; Tue, 11 Dec 2007 20:41:51 -0500 Message-ID: <475F3BC8.3000100@evdbt.com> Date: Tue, 11 Dec 2007 18:39:20 -0700 From: Tim Gorman Organization: Evergreen Database Technologies, Inc. User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: oracle-l@freelists.org Subject: Re: partitioned tables References: <6d3967610712111625t199e5383s99c20278f022ba6b@mail.gmail.com> In-Reply-To: <6d3967610712111625t199e5383s99c20278f022ba6b@mail.gmail.com> Content-Type: text/html; charset=ISO-8859-1 X-archive-position: 3858 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 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