Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 28606 invoked from network); 12 Dec 2007 08:04:48 -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:04:42 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7A4787D82BE; Wed, 12 Dec 2007 09:04:39 -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 25349-08; Wed, 12 Dec 2007 09:04:39 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CDAD27D81BE; Wed, 12 Dec 2007 09:04:38 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 12 Dec 2007 09:02:30 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1E1257D81B4 for ; Wed, 12 Dec 2007 09:02:30 -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 24823-03 for ; Wed, 12 Dec 2007 09:02:29 -0500 (EST) Received: from outbound3-va3-R.bigfish.com (outbound-va3.frontbridge.com [216.32.180.16]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9E6667D81EA for ; Wed, 12 Dec 2007 09:02:29 -0500 (EST) Received: from outbound3-va3.bigfish.com (localhost.localdomain [127.0.0.1]) by outbound3-va3-R.bigfish.com (Postfix) with ESMTP id 935656EA65D; Wed, 12 Dec 2007 14:02:28 +0000 (UTC) Received: from mail31-va3-R.bigfish.com (si1-va3 [10.7.14.5]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by outbound3-va3.bigfish.com (Postfix) with ESMTP id 914C01380064; Wed, 12 Dec 2007 14:02:28 +0000 (UTC) Received: from mail31-va3 (localhost.localdomain [127.0.0.1]) by mail31-va3-R.bigfish.com (Postfix) with ESMTP id 7B0606F823A; Wed, 12 Dec 2007 14:02:28 +0000 (UTC) X-BigFish: VP X-MS-Exchange-Organization-Antispam-Report: OrigIP: 198.22.236.82;Service: EHS Received: by mail31-va3 (MessageSwitch) id 1197468148435898_10763; Wed, 12 Dec 2007 14:02:28 +0000 (UCT) Received: from mailb.nysemail.state.ny.us (mailb.nysemail.state.ny.us [198.22.236.82]) (using TLSv1 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by mail31-va3.bigfish.com (Postfix) with ESMTP id 4D8771A60074; Wed, 12 Dec 2007 14:02:28 +0000 (UTC) Received: from EXCNYSM0A1AJ.nysemail.nyenet ([10.66.81.139]) by mailb.nysemail.state.ny.us with Microsoft SMTPSVC(6.0.3790.2499); Wed, 12 Dec 2007 09:02:27 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C83CC7.A0A26D9E" Subject: RE: partitioned tables Date: Wed, 12 Dec 2007 09:02:26 -0500 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: partitioned tables From: "Mercadante, Thomas F (LABOR)" To: Cc: X-OriginalArrivalTime: 12 Dec 2007 14:02:27.0911 (UTC) FILETIME=[A11ADD70:01C83CC7] X-archive-position: 3876 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Thomas.Mercadante@labor.state.ny.us Precedence: normal Reply-to: Thomas.Mercadante@labor.state.ny.us 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 ------_=_NextPart_001_01C83CC7.A0A26D9E Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Joe, =20 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. =20 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. =20 Just my 2 cents. =20 Tom ________________________________ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Joe Smith Sent: Wednesday, December 12, 2007 8:29 AM To: tim@evdbt.com; oracle-l@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? =20 thanks. ________________________________ Date: Tue, 11 Dec 2007 18:39:20 -0700 From: tim@evdbt.com To: oracle-l@freelists.org Subject: Re: partitioned tables =09 =09 Why use a number to represent a date? There is no advantage whatsoever, and numerous disadvantages. =09 ...and as Gus pointed out, range partitioning syntax is "VALUES LESS THAN" not "VALUES LESS THAN OR EQUAL TO"... =09 =09 Gus Spier wrote:=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.=20 =09 r, =09 Gus =09 =09 On Dec 11, 2007 4:51 PM, Joe Smith wrote: =09 My primary key is a composite of 3 columns, but I range parition only on one column. =20 It that a problem? =20 =09 I know it is a date field, but we are using a number for a date, i.e. yyyymmdd. =20 Would I need to set up a partitioned tablespace for the index, i.e. PK? =20 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 ,=20 TXN_AMOUNT NUMBER(14,2) NULL=20 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,=20 partition feb_2007 values less than (20070228) tablespace smart_part_feb_2007, . . . partition jan_2007 values less than (20071231) tablespace smart_part_dec_2007); =20 =20 =09 =09 ________________________________ Share life as it happens with the new Windows Live. Share now! =20 -- 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. Get it now! =20 ------_=_NextPart_001_01C83CC7.A0A26D9E Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Joe,
 
There is no evidence that a number datatype results in = quicker=20 queries than a date datatype (that I know of).  Date columns are = actually=20 stored as numbers anyway.
 
There is no correct way to do this - this is purely a = professional=20 judgement call.  But a date column allows you to perform date math = - like=20 subtract 10 from the date column to get the value for 10 days prior, or = use the=20 ADD_MONTHS or LAST_DAY function on a date column to return a different = month, or=20 the last day of a month.  If you stored the value as your design = team=20 suggests, then date math would require "to_date" functions to be = used.  Not=20 a big deal, but why force yourself down that road?  It sounds like = your=20 design team does not fully understand an Oracle Date=20 datatype.
 
Just my 2 cents.
 
Tom

From: oracle-l-bounce@freelists.org=20 [mailto:oracle-l-bounce@freelists.org] On Behalf Of Joe=20 Smith
Sent: Wednesday, December 12, 2007 8:29 AM
To: = tim@evdbt.com; oracle-l@freelists.org
Subject: RE: partitioned = tables

The logical design team seems to think that the date as a = number=20 datatype will speed up queries.  Not = true?
 
thanks.


Date: Tue, 11 Dec 2007 18:39:20 -0700
From: tim@evdbt.com
To:=20 oracle-l@freelists.org
Subject: Re: partitioned tables

Why use a number = to=20 represent a date?  There is no advantage whatsoever, and numerous = disadvantages.

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


Gus Spier wrote:=20 Of= =20 course, it's up to you, but if you set NLS_DATE_FORMAT=3D'YYYYMMDD', = you won't=20 need the TXN_DATE_ID.  I might also recommend that your = partition by=20 range (TXN_DATE_ID) use values less than 20070201 instead of = 20070131.=20

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=20 on one column.
 
 It that a=20 problem?
 

 I know it is a date field, but we = are=20 using a number for a date, i.e. yyyymmdd.
 
 Would = I need=20 to set up a partitioned tablespace for the index, i.e.=20 PK?
 
CREATE TABLE=20 = F_MBR_ACCT_TRANSACTION
(
MBR_ACCT_ID     &= nbsp;        =20 INTEGER  NOT NULL=20 = ,
TXN_DATE_ID         &nb= sp;    =20 INTEGER  NOT NULL=20 ,
SPONSOR_TRAN_ID       = INTEGER  NOT=20 NULL=20 = ,
TXN_DTTM          =        =20 DATE  NULL ,=20 =
TXN_AMOUNT          = ;  =20 NUMBER(14,2)  NULL
CONSTRAINT  = F_MBR_ACCT_TRANSACTION_PK=20 PRIMARY KEY (MBR_ACCT_ID,TXN_DATE_ID,SPONSOR_TRAN_ID))
partion = by range=20 (TXN_DATE_ID)
(partition jan_2007 values less than (20070131)=20 tablespace smart_part_jan_2007,
 partition feb_2007 = values less=20 than (20070228) tablespace=20 = smart_part_feb_2007,
 .
 .
 .
 partition = jan_2007 values less than (20071231) tablespace=20 smart_part_dec_2007);
 
 

Share life as it happens with the new Windows Live. Share = now!

--=20 http://www.freelists.org/webpage/oracle-l
The best games are on Xbox 360. Click here for a special offer on an = Xbox 360=20 Console. Get it now! ------_=_NextPart_001_01C83CC7.A0A26D9E-- -- http://www.freelists.org/webpage/oracle-l