Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 31393 invoked from network); 12 Dec 2007 08:16:27 -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 08:16:27 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F045B7D705B; Wed, 12 Dec 2007 09:16:27 -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 27768-07; Wed, 12 Dec 2007 09:16:27 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6A77F7D700A; Wed, 12 Dec 2007 09:16:27 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 12 Dec 2007 08:29:02 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3196E7D809E for ; Wed, 12 Dec 2007 08:29:02 -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 18113-01 for ; Wed, 12 Dec 2007 08:29:02 -0500 (EST) Received: from blu139-omc3-s25.blu139.hotmail.com (blu139-omc3-s25.blu139.hotmail.com [65.55.175.225]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6C6217D8089 for ; Wed, 12 Dec 2007 08:28:59 -0500 (EST) Received: from BLU108-W2 ([65.55.162.189]) by blu139-omc3-s25.blu139.hotmail.com with Microsoft SMTPSVC(6.0.3790.3959); Wed, 12 Dec 2007 05:28:57 -0800 Message-ID: Content-Type: multipart/alternative; boundary="_7f815759-97f3-42b5-afba-c799a54f0c2f_" X-Originating-IP: [66.236.147.253] From: Joe Smith To: , Subject: RE: partitioned tables Date: Wed, 12 Dec 2007 07:28:57 -0600 In-Reply-To: <475F3BC8.3000100@evdbt.com> References: <6d3967610712111625t199e5383s99c20278f022ba6b@mail.gmail.com> <475F3BC8.3000100@evdbt.com> MIME-Version: 1.0 X-OriginalArrivalTime: 12 Dec 2007 13:28:57.0600 (UTC) FILETIME=[F2DDC000:01C83CC2] X-archive-position: 3872 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: joe_dba@hotmail.com Precedence: normal Reply-to: joe_dba@hotmail.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 --_7f815759-97f3-42b5-afba-c799a54f0c2f_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable The logical design team seems to think that the date as a number datatype w= ill speed up queries. Not true? =20 thanks. Date: Tue, 11 Dec 2007 18:39:20 -0700From: tim@evdbt.comTo: oracle-l@freeli= sts.orgSubject: Re: partitioned tables Why use a number to represent a date? There is no advantage whatsoever, an= d numerous disadvantages....and as Gus pointed out, range partitioning syn= tax is "VALUES LESS THAN" not "VALUES LESS THAN OR EQUAL TO"...Gus Spier wr= ote:=20 Of course, it's up to you, but if you set NLS_DATE_FORMAT=3D'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,G= us On Dec 11, 2007 4:51 PM, Joe Smith wrote: My primary key is a composite of 3 columns, but I range parition only on on= e 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 t= ablespace for the index, i.e. PK? CREATE TABLE F_MBR_ACCT_TRANSACTION(MBR_A= CCT_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))p= artion by range (TXN_DATE_ID)(partition jan_2007 values less than (20070131= ) tablespace smart_part_jan_2007, partition feb_2007 values less than (200= 70228) tablespace smart_part_feb_2007, . . . partition jan_2007 values less= than (20071231) tablespace smart_part_dec_2007); =20 Share life as it happens with the new Windows Live. Share now!-- http://www= .freelists.org/webpage/oracle-l=20 _________________________________________________________________ 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/= --_7f815759-97f3-42b5-afba-c799a54f0c2f_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable The logical design team seems to think that the date as a number datatype w= ill 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 r= epresent a date?  There is no advantage whatsoever, and numerous disad= vantages.

...and as Gus pointed out,  range partitioning syntax= is "VALUES LESS THAN" not "VALUES LESS THAN OR EQUAL TO"...


Gus= Spier wrote:=20
Of course, it's up to you, but if you set NLS_DATE_FORMAT=3D'YYYYMM= DD', 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. yyyy= mmdd.
 
 Would I need to set up a partitioned tablespace fo= r the index, i.e. PK?
 
CREATE TABLE F_MBR_ACCT_TRANSACTION
(=
MBR_ACCT_ID          =      INTEGER  NOT NULL ,
TXN_DATE_ID  = ;             I= NTEGER  NOT NULL ,
SPONSOR_TRAN_ID     &nb= sp; INTEGER  NOT NULL ,
TXN_DTTM      = ;            DATE&nb= sp; NULL ,
TXN_AMOUNT        &n= bsp;    NUMBER(14,2)  NULL
CONSTRAINT  F_MBR_A= CCT_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 (2= 0070131) tablespace smart_part_jan_2007,
 partition feb_2007 value= s less than (20070228) tablespace smart_part_feb_2007,
 .
 = .
 .
 partition jan_2007 values less than (20071231) tables= pace 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. <= a href=3D'http://www.xbox.com/en-US/hardware/wheretobuy/' target=3D'_new'>G= et it now! = --_7f815759-97f3-42b5-afba-c799a54f0c2f_-- -- http://www.freelists.org/webpage/oracle-l