Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 6401 invoked from network); 12 Dec 2007 08:44:19 -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:44:19 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A6F3A7D8436; Wed, 12 Dec 2007 09:44:19 -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 01279-02; Wed, 12 Dec 2007 09:44:19 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1907D7D83FF; Wed, 12 Dec 2007 09:44:19 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 12 Dec 2007 08:56:53 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6E3BB7D8046 for ; Wed, 12 Dec 2007 08:56:53 -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 23745-06 for ; Wed, 12 Dec 2007 08:56:53 -0500 (EST) Received: from toc-mail.umb.com (toc-mail.umb.com [198.153.253.80]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0E5867D6D2D for ; Wed, 12 Dec 2007 08:56:52 -0500 (EST) X-WSS-ID: 0JSXW1D-02-I7D-01 Received: from x8114a.umb.com (unknown [192.168.145.4]) by toc-mail.umb.com (Tumbleweed MailGate 3.5.0) with ESMTP id 241DB5B0387; Wed, 12 Dec 2007 07:56:00 -0600 (CST) Received: from 172.19.48.183 by x8114a.umb.com with ESMTP (Tumbleweed Email Firewall SMTP Relay (Email Firewall v6.2.0)); Wed, 12 Dec 2007 07:56:43 -0600 X-Server-Uuid: A6E1DA34-39DF-4ABE-A6EE-67E6B24F0DDF X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Subject: RE: partitioned tables Date: Wed, 12 Dec 2007 07:56:43 -0600 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: partitioned tables From: "Kerber, Andrew W." To: joe_dba@hotmail.com, tim@evdbt.com, oracle-l@freelists.org X-WSS-ID: 6B413711298501971-01-01 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C83CC6.D3A36D7D" X-archive-position: 3873 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Andrew.Kerber@umb.com Precedence: normal Reply-to: Andrew.Kerber@umb.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 ------_=_NextPart_001_01C83CC6.D3A36D7D Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable 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. =20 -----Original Message----- =46rom: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Joe Smith Sent: Wednesday, December 12, 2007 7:29 AM To: tim@evdbt.com; oracle-l@freelists.org Subject: RE: partitioned tables =20 The logical design team seems to think that the date as a number datatype will speed up queries. Not true=3F =20 thanks. ________________________________ Date: Tue, 11 Dec 2007 18:39:20 -0700 =46rom: tim@evdbt.com To: oracle-l@freelists.org Subject: Re: partitioned tables Why use a number to represent a date=3F 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:=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 r, Gus 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 one column. =20 It that a problem=3F =20 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=3F =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 ________________________________ Share life as it happens with the new Windows Live. Share now! =20 =20 -- http://www.freelists.org/webpage/oracle-l=20 =20 ________________________________ The best games are on Xbox 360. Click here for a special offer on an Xbox 360 Console. Get it now! =20 ---------------------------------------------------------------------------= --- 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 copie= s= of this message from your computer system. Thank you. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D ------_=_NextPart_001_01C83CC6.D3A36D7D Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: quoted-printable

Not that I have ever seen.  If yo= u= 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@freelists.org [mailto:oracle-l-bounce@freelists.org] = On Behalf Of Joe Smith
Sent: Wednesday, December 12= ,= 2007 7: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=3F
 
thanks.

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

Why use a number to represent a date=3F  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=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,

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=3F
 

 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=3F
 
CREATE TABLE F_MBR_ACCT_TRANSACTION
(
MBR_ACCT_ID          &nbs= p;    INTEGER  NOT NULL ,
TXN_DATE_ID          &nbs= p;    INTEGER  NOT NULL ,
SPONSOR_TRAN_ID       INTEGER  NOT NULL = ,
TXN_DTTM           &= nbsp;      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!

------------------------------------------------------------------------= ------
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 copie= s= of this message from your computer system. Thank you.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D

------_=_NextPart_001_01C83CC6.D3A36D7D-- -- http://www.freelists.org/webpage/oracle-l