Re: Partitioning question (duplicate?)
that's what I get for not testing but just reading the manual :)
remind me not to answer questions when I don't have a database
handy.
sounds like Dan's going to have to add a column.
--- Kirtikumar Deshpande [EMAIL PROTECTED] wrote:
Rahcel, Dan:
I played with such things a long time ago
Here's the text for ORA-14120 error that I used to get:
14120, 0, incompletely specified partition bound for a DATE
column
// *Cause: An attempt was made to use a date expression whose format
// does not fully (i.e. day, month, and year (including
century))
// specify a date as a partition bound for a DATE column.
// The format may have been specified explicitly (using
// TO_DATE() function) or implicitly (NLS_DATE_FORMAT).
// *Action: Ensure that date format used in a partition bound for a
// DATE column supports complete specification of a date
// (i.e. day, month, and year (including century)).
// If NLS_DATE_FORMAT does not support complete
// (i.e. including the century) specification of the year,
// use TO_DATE() (e.g. TO_DATE('01-01-1999', 'MM-DD-')
// to fully express the desired date.
And here is what I just tested to make sure it has (DATE in range
partitions) not changed in
9.2.0.4 (AIX 4.3.3):
kirti @dbmt : SQL l
1 CREATE TABLE Orders
2(order_id NUMBER,
3 order_dt DATE,
4 cust_id NUMBER)
5 PARTITION BY RANGE(order_dt)
6 (PARTITION JanOrd VALUES LESS THAN
7 (TO_DATE('02','MM')),
8PARTITION FebOrd VALUES LESS THAN
9 (TO_DATE('03','MM')),
10PARTITION MarOrd VALUES LESS THAN
11* (TO_DATE('04','MM')))
kirti @dbmp : SQL /
(TO_DATE('02','MM')),
*
ERROR at line 7:
ORA-14120: incompletely specified partition bound for a DATE column
kirti @dbmp : SQL
If anyone has any tricks to get around this issue, I would love to
hear.
Cheers!
- Kirti
--- Rachel Carmichael [EMAIL PROTECTED] wrote:
First time I've seen this post. And from the fine Data Warehousing
manual:
here's an example of range partitioning. Note the to_date in the
values clause. I don't see why you couldn't use
to_date(date_column,'MONTH')
Rachel
CREATE TABLE sales
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
PARTITION BY RANGE(s_saledate)
(PARTITION sal99q1 VALUES LESS THAN
(TO_DATE('01-APR-1999','DD-MON-')),
PARTITION sal99q2 VALUES LESS THAN
(TO_DATE('01-JUL-1999','DD-MON-')),
PARTITION sal99q3 VALUES LESS THAN
(TO_DATE('01-OCT-1999', 'DD-MON-')),
PARTITION sal99q4 VALUES LESS THAN
(TO_DATE('01-JAN-2000', 'DD-MON-')),
PARTITION sal00q1 VALUES LESS THAN
(TO_DATE('01-APR-2000', 'DD-MON-')),
PARTITION sal00q2 VALUES LESS THAN
(TO_DATE('01-JUL-2000', 'DD-MON-')),
PARTITION sal00q3 VALUES LESS THAN
(TO_DATE('01-OCT-2000', 'DD-MON-')),
PARTITION sal00q4 VALUES LESS THAN
(TO_DATE('01-JAN-2001', 'DD-MON-')));
--- Daniel Fink [EMAIL PROTECTED] wrote:
Pardon if this is a duplicate, but the original has not shown up
on the list after 3 hours...
Is it possible in 9.2 to partition on a function?
I have a table with a date column and I would like to partition
by month, regardless of the year. For example, data from January
2003 or January 2004 would go into the same partition. Any
sneaky ideas on how to accomplish this without changing the data
structures.
Daniel Fink
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Daniel Fink
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
http://www.fatcity.com
San Diego, California-- Mailing list and web hosting
services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and
in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may
also send the HELP command for other information (like
subscribing).
__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting
services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include
Re: Partitioning question (duplicate?)
First time I've seen this post. And from the fine Data Warehousing
manual:
here's an example of range partitioning. Note the to_date in the
values clause. I don't see why you couldn't use
to_date(date_column,'MONTH')
Rachel
CREATE TABLE sales
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
PARTITION BY RANGE(s_saledate)
(PARTITION sal99q1 VALUES LESS THAN
(TO_DATE('01-APR-1999','DD-MON-')),
PARTITION sal99q2 VALUES LESS THAN
(TO_DATE('01-JUL-1999','DD-MON-')),
PARTITION sal99q3 VALUES LESS THAN
(TO_DATE('01-OCT-1999', 'DD-MON-')),
PARTITION sal99q4 VALUES LESS THAN
(TO_DATE('01-JAN-2000', 'DD-MON-')),
PARTITION sal00q1 VALUES LESS THAN
(TO_DATE('01-APR-2000', 'DD-MON-')),
PARTITION sal00q2 VALUES LESS THAN
(TO_DATE('01-JUL-2000', 'DD-MON-')),
PARTITION sal00q3 VALUES LESS THAN
(TO_DATE('01-OCT-2000', 'DD-MON-')),
PARTITION sal00q4 VALUES LESS THAN
(TO_DATE('01-JAN-2001', 'DD-MON-')));
--- Daniel Fink [EMAIL PROTECTED] wrote:
Pardon if this is a duplicate, but the original has not shown up
on the list after 3 hours...
Is it possible in 9.2 to partition on a function?
I have a table with a date column and I would like to partition
by month, regardless of the year. For example, data from January
2003 or January 2004 would go into the same partition. Any
sneaky ideas on how to accomplish this without changing the data
structures.
Daniel Fink
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Daniel Fink
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
__
Do you Yahoo!?
Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Partitioning question (duplicate?)
Dan,
Good question, but unless I'm misinterpreting the results, the answer is
no...
SQL show release
release 902000100
SQL create table test
2 (a date, b number, c number)
3 partition by list (to_char(a, 'MON'))
4 (partition pJAN values ('JAN')),
5 (partition pFEB values ('FEB'))
6 (partition pMAR values ('MAR'))
7 (partition pAPR values ('APR'))
8 (partition pMAY values ('MAY'));
partition by list (to_char(a, 'MON'))
*
ERROR at line 3:
ORA-00907: missing right parenthesis
..seems to clearly be interpreting the phrase to_char as a column name...
Hope this helps...
-Tim
on 1/14/04 3:24 PM, Daniel Fink at [EMAIL PROTECTED] wrote:
Pardon if this is a duplicate, but the original has not shown up
on the list after 3 hours...
Is it possible in 9.2 to partition on a function?
I have a table with a date column and I would like to partition
by month, regardless of the year. For example, data from January
2003 or January 2004 would go into the same partition. Any
sneaky ideas on how to accomplish this without changing the data
structures.
Daniel Fink
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Partitioning question (duplicate?)
The only way I see is using a system-maintained ( through a before-insert and if necessary before-update trigger ) field that is set to to_char(date_column,'mm') and then range partition on that. At 03:24 PM 1/14/2004, you wrote: Pardon if this is a duplicate, but the original has not shown up on the list after 3 hours... Is it possible in 9.2 to partition on a function? I have a table with a date column and I would like to partition by month, regardless of the year. For example, data from January 2003 or January 2004 would go into the same partition. Any sneaky ideas on how to accomplish this without changing the data structures. Daniel Fink -- Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning option licensing
Title: Message as I understand it Oracle no longer uses (officially) processor speed to determine license costs, though it appears that *actually* it uses revenue targets to determine license costs Niall -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David WagonerSent: 03 December 2003 14:25To: Multiple recipients of list ORACLE-LSubject: RE: partitioning option licensing As of 9iR2, partitioning is still licensed separately as a $10K (retail) extra charge per processor. So, the total retail comes to a painful $50K per processor for 9i + Partitioning. Also, remember that support costs X% of the licensing per year, depending on your support level. You'll have to confirm the exact numbers with your sales rep. You can estimate about 22%, as I recall. We just increased our licensing a few months ago. Get the fastest processors you can. Anyone know how 10g will be licensed? Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions Web: http://www.arsenaldigital.com "the most trusted source for STORAGE MANAGEMENT SERVICES" The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system. Thank you. -Original Message- From: Patricia Zhu [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 03, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Subject: partitioning option licensing Hi, We're looking into migrating from SQL server to Oracle. Does anyone know if Partitioning option is still licensed separately? Thanks. pat _ Our best dial-up offer is back. Get MSN Dial-up Internet Service for 6 months @ $9.95/month now! http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Patricia Zhu INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning option licensing
Title: RE: partitioning option licensing As of 9iR2, partitioning is still licensed separately as a $10K (retail) extra charge per processor. So, the total retail comes to a painful $50K per processor for 9i + Partitioning. Also, remember that support costs X% of the licensing per year, depending on your support level. You'll have to confirm the exact numbers with your sales rep. You can estimate about 22%, as I recall. We just increased our licensing a few months ago. Get the fastest processors you can. Anyone know how 10g will be licensed? Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions Web: http://www.arsenaldigital.com the most trusted source for STORAGE MANAGEMENT SERVICES The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system. Thank you. -Original Message- From: Patricia Zhu [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 03, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Subject: partitioning option licensing Hi, We're looking into migrating from SQL server to Oracle. Does anyone know if Partitioning option is still licensed separately? Thanks. pat _ Our best dial-up offer is back. Get MSN Dial-up Internet Service for 6 months @ $9.95/month now! http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Patricia Zhu INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning option licensing
-Original Message- Hi, We're looking into migrating from SQL server to Oracle. Does anyone know if Partitioning option is still licensed separately? Thanks. pat Having just met with an Oracle rep yesterday. Yes, it is still licensed separately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Simpson, Ken INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: partitioning option licensing
never pay retail with oracle licensing. who pays the full $10k? If your buying other stuff you should be able to knock off alot. Never pay the full amount. From: David Wagoner [EMAIL PROTECTED] Date: 2003/12/03 Wed AM 09:24:38 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: partitioning option licensing As of 9iR2, partitioning is still licensed separately as a $10K (retail) extra charge per processor. So, the total retail comes to a painful $50K per processor for 9i + Partitioning. Also, remember that support costs X% of the licensing per year, depending on your support level. You'll have to confirm the exact numbers with your sales rep. You can estimate about 22%, as I recall. We just increased our licensing a few months ago. Get the fastest processors you can. Anyone know how 10g will be licensed? Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions Web: http://www.arsenaldigital.com the most trusted source for STORAGE MANAGEMENT SERVICES The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system. Thank you. -Original Message- Sent: Wednesday, December 03, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Hi, We're looking into migrating from SQL server to Oracle. Does anyone know if Partitioning option is still licensed separately? Thanks. pat _ Our best dial-up offer is back. Get MSN Dial-up Internet Service for 6 months @ $9.95/month now! http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Patricia Zhu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Title: RE: partitioning option licensing As of 9iR2, partitioning is still licensed separately as a $10K (retail) extra charge per processor. So, the total retail comes to a painful $50K per processor for 9i + Partitioning. Also, remember that support costs X% of the licensing per year, depending on your support level. You'll have to confirm the exact numbers with your sales rep. You can estimate about 22%, as I recall. We just increased our licensing a few months ago. Get the fastest processors you can. Anyone know how 10g will be licensed? Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions Web: http://www.arsenaldigital.com the most trusted source for STORAGE MANAGEMENT SERVICES The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system. Thank you. -Original Message- From: Patricia Zhu [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 03, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Subject: partitioning option licensing Hi, We're looking into migrating from SQL server to Oracle. Does anyone know if Partitioning option is still licensed separately? Thanks. pat _ Our best dial-up offer is back. Get MSN Dial-up Internet Service for 6 months @ $9.95/month now! http://join.msn.com/?page=dept/dialup -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Patricia Zhu INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want
RE: partitioning option licensing
-Original Message- Sent: Thursday, 4 December 2003 01:25 To: Multiple recipients of list ORACLE-L As of 9iR2, partitioning is still licensed separately as a $10K (retail) extra charge per processor. So, the total retail comes to a painful $50K per processor for 9i + Partitioning. Also, remember that support costs X% of the licensing per year, depending on your support level. You'll have to confirm the exact numbers with your sales rep. You can estimate about 22%, as I recall. We just increased our licensing a few months ago. Get the fastest processors you can. Anyone know how 10g will be licensed? --- Yep, absolutely no change in licensing for 10g - this from the 10g roadshow that just went through town. So you'll be able to visualise and provision yourself all the way to bankruptcy :-) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: partitioning in an NAS or SAN environment
To paraphrase Pythia, an early leader on the DSS market, my advice would be Partition not create monolythic. Partitioning is done for performance reasons, that much is clear. To really answer your question in any other way then speaking quixotically on laurel (SQL for short), I'd need to know the structure of your NAS device. If, for instance, your NAS device is one huge, monolythic RAID-5 device, then you don't benefit from spreading things over several mount points. In other words, mount points are here only for the ease of administration. You can still benefit from partitioning, because god partitioning will help you with partition elimination. In other words, optimizer will automatically discard partitions that are not needed from your access path and you'll have to do lot less reading. If, on the other hand, your NAS partitions have different network paths and you'd use eth0 for /data1 and eth1 for /data2, then the situation is not so simple and you'd have to take into account the usage of both network paths, their capacity and reliability and work with your SA and network analyst. On 11/06/2003 01:54:26 PM, [EMAIL PROTECTED] wrote: most of the oracle docs state that when you partition a table you will get the most performance benefits by splitting the datafiles for each partition onto seperate storage devices. Im on an NAS and all I see are logical mount points. What are your recommendations for this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: partitioning in an NAS or SAN environment
At the site of one of my customers, in a not too big (100GB) database environment, using NAS over NFS on RS/6000 with AIX gave us far from enough throughput. It turned out that much more mountpoints (20 i.s.o. 2) were necessary to get a more-or-less satisfactory throughput. At 10:54 6-11-03 -0800, you wrote: most of the oracle docs state that when you partition a table you will get the most performance benefits by splitting the datafiles for each partition onto seperate storage devices. Im on an NAS and all I see are logical mount points. What are your recommendations for this? DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel. +31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile+31 (0) 653 911 950 e-mail [EMAIL PROTECTED]
RE: Partitioning - followup
Thanks all who replied. The purpose of this excercise is mainly #1 mentioned in Dennis W.'s e-mail. Because of the size of this table, purging has been a challenge and we want to keep only 2 years data in the table and periodically drop partitions to save space. Dennis -Original Message- Sent: Tuesday, August 12, 2003 4:19 PM To: Multiple recipients of list ORACLE-L Dennis What are you trying to achieve by partitioning? Generally I've seen two common goals, 1) break a large table into more manageable pieces, 2) performance tuning, so a query only has to scan a small partition. Sometimes the two can be achieved simultaneously, sometimes they are at odds. If you had a year column, and partitioned on that column, you might have manageability, but if none of your queries included that column, Oracle would probably do a full table scan on all partitions (maybe in parallel if you have the partitions on separate devices). On the other hand, I've partitioned a table by week, which produces 52 partitions for each year. Not good for manageability, but it made the queries blazingly fast. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 12, 2003 11:04 AM To: Multiple recipients of list ORACLE-L I have worked with partitioning before but have yet encountered the following challenge - The table we are trying to partition is a large table with hundreds of millions of rows, which is ok. But it does not have a month column, although it has dates. I would like to partition by month because this table contains years of data and partitioning by days will result in thousands of partitions. Of course we can add a month column but I think that will require extensive downtime which we can't afford and I suspect it will cause row-chaining as well. So anybody care to share with me any other options/suggestions? TIA Dennis Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitioning
Dennis What are you trying to achieve by partitioning? Generally I've seen two common goals, 1) break a large table into more manageable pieces, 2) performance tuning, so a query only has to scan a small partition. Sometimes the two can be achieved simultaneously, sometimes they are at odds. If you had a year column, and partitioned on that column, you might have manageability, but if none of your queries included that column, Oracle would probably do a full table scan on all partitions (maybe in parallel if you have the partitions on separate devices). On the other hand, I've partitioned a table by week, which produces 52 partitions for each year. Not good for manageability, but it made the queries blazingly fast. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 12, 2003 11:04 AM To: Multiple recipients of list ORACLE-L I have worked with partitioning before but have yet encountered the following challenge - The table we are trying to partition is a large table with hundreds of millions of rows, which is ok. But it does not have a month column, although it has dates. I would like to partition by month because this table contains years of data and partitioning by days will result in thousands of partitions. Of course we can add a month column but I think that will require extensive downtime which we can't afford and I suspect it will cause row-chaining as well. So anybody care to share with me any other options/suggestions? TIA Dennis Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Partitioning
If it has a date column, partition by that column into whatever makes sense (weeks,
months, etc.)
CREATE TABLE YOURTABLE
(
YOURDATE DATE NOT NULL,
YOURCOLUMN NUMBER
)
PARTITION BY RANGE (YOURDATE)
(
PARTITION YOURTABLE_JUN03 VALUES LESS THAN (TO_DATE(' 2003-07-01', '-MM-DD')
TABLESPACE DATA_06,
PARTITION YOURTABLE_JUL03 VALUES LESS THAN (TO_DATE(' 2003-08-01', '-MM-DD)
TABLESPACE DATA_07,
PARTITION DETAILS_AUG03 VALUES LESS THAN (TO_DATE(' 2003-09-01', '-MM-DD')
TABLESPACE DATA_08
);
Jay
[EMAIL PROTECTED] 08/12/03 12:04PM
I have worked with partitioning before but have yet encountered the following
challenge -
The table we are trying to partition is a large table with hundreds of millions of
rows, which is ok. But it does not have a month column, although it has dates. I would
like to partition by month because this table contains years of data and partitioning
by days will result in thousands of partitions. Of course we can add a month column
but I think that will require extensive downtime which we can't afford and I suspect
it will cause row-chaining as well. So anybody care to share with me any other
options/suggestions?
TIA
Dennis
**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the
individual or entity to which they are addressed and may contain information that is
privileged, proprietary and confidential. If you are not the intended recipient, you
may not use, copy or disclose to anyone the message or any information contained in
the message. If you have received this communication in error, please notify the
sender and delete this e-mail message. The contents do not represent the opinion of
DE except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jay Hostetter
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Partitioning
A little addition to my post: Of course partitioning downtime will not be that bad if you got spare space to build partitioned table first, transfer data, then do switchover, then transfer data changed meanwhile (using triggers, snapshots or even logminer..). Note that it is possible to add column online using dbms_redefinition package, but this has it's complications as well. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 10:14 PM Hi! You can use to_date function in partition by clause on date column. But, you'll have downtime for this table anyway, if you want to split existing table to partitions. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 7:04 PM I have worked with partitioning before but have yet encountered the following challenge - The table we are trying to partition is a large table with hundreds of millions of rows, which is ok. But it does not have a month column, although it has dates. I would like to partition by month because this table contains years of data and partitioning by days will result in thousands of partitions. Of course we can add a month column but I think that will require extensive downtime which we can't afford and I suspect it will cause row-chaining as well. So anybody care to share with me any other options/suggestions? TIA Dennis Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Partitioning
Are you saying you want to have twelve partitions, one for each month of the year (regardless of year)? --- Meng, Dennis [EMAIL PROTECTED] wrote: I have worked with partitioning before but have yet encountered the following challenge - The table we are trying to partition is a large table with hundreds of millions of rows, which is ok. But it does not have a month column, although it has dates. I would like to partition by month because this table contains years of data and partitioning by days will result in thousands of partitions. Of course we can add a month column but I think that will require extensive downtime which we can't afford and I suspect it will cause row-chaining as well. So anybody care to share with me any other options/suggestions? TIA Dennis Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitioning - followup
Dennis, Remember that you need to have a catch all partition to keep the data that does not fall into the date ranges you specify for the partitions. Someone will always enter a future date into a record if they are allowed. When It comes time to drop the old partitions and add a new year there are specific steps that should be followed in creating the new partition with the proper sizing needed. Ron [EMAIL PROTECTED] 08/12/03 06:24PM Thanks all who replied. The purpose of this excercise is mainly #1 mentioned in Dennis W.'s e-mail. Because of the size of this table, purging has been a challenge and we want to keep only 2 years data in the table and periodically drop partitions to save space. Dennis -Original Message- Sent: Tuesday, August 12, 2003 4:19 PM To: Multiple recipients of list ORACLE-L Dennis What are you trying to achieve by partitioning? Generally I've seen two common goals, 1) break a large table into more manageable pieces, 2) performance tuning, so a query only has to scan a small partition. Sometimes the two can be achieved simultaneously, sometimes they are at odds. If you had a year column, and partitioned on that column, you might have manageability, but if none of your queries included that column, Oracle would probably do a full table scan on all partitions (maybe in parallel if you have the partitions on separate devices). On the other hand, I've partitioned a table by week, which produces 52 partitions for each year. Not good for manageability, but it made the queries blazingly fast. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, August 12, 2003 11:04 AM To: Multiple recipients of list ORACLE-L I have worked with partitioning before but have yet encountered the following challenge - The table we are trying to partition is a large table with hundreds of millions of rows, which is ok. But it does not have a month column, although it has dates. I would like to partition by month because this table contains years of data and partitioning by days will result in thousands of partitions. Of course we can add a month column but I think that will require extensive downtime which we can't afford and I suspect it will cause row-chaining as well. So anybody care to share with me any other options/suggestions? TIA Dennis Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City
Re: Partitioning
Hi! You can use to_date function in partition by clause on date column. But, you'll have downtime for this table anyway, if you want to split existing table to partitions. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 7:04 PM I have worked with partitioning before but have yet encountered the following challenge - The table we are trying to partition is a large table with hundreds of millions of rows, which is ok. But it does not have a month column, although it has dates. I would like to partition by month because this table contains years of data and partitioning by days will result in thousands of partitions. Of course we can add a month column but I think that will require extensive downtime which we can't afford and I suspect it will cause row-chaining as well. So anybody care to share with me any other options/suggestions? TIA Dennis Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Meng, Dennis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitioning
Partitioning by range will do just fine!
partition by date_col (
partition jan2000 values less than to_date('02-01-2000', 'mm-dd-')
,partition feb2000 values less than to_date('03-01-2000', 'mm-dd-')
);
Kevin
-Original Message-
Sent: Tuesday, August 12, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L
I have worked with partitioning before but have yet encountered the
following challenge -
The table we are trying to partition is a large table with hundreds of
millions of rows, which is ok. But it does not have a month column, although
it has dates. I would like to partition by month because this table contains
years of data and partitioning by days will result in thousands of
partitions. Of course we can add a month column but I think that will
require extensive downtime which we can't afford and I suspect it will cause
row-chaining as well. So anybody care to share with me any other
options/suggestions?
TIA
Dennis
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Meng, Dennis
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kevin Toepke
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: partitioning
Title: RE: partitioning Hi Jacques, How do I exactly implement this.In the before insert trigger what after I generate the value for the new partition column.How does the records go into that partition. Have you tried this.How is the performance for an insert into a table of 10 records everyday.Executing the trigger for every insert for high volume of data may be costly on the performance..? Can we achieve this or anything closer using HASH partitioning as suggested by others. thanks -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]Sent: Wednesday, March 19, 2003 11:52 AMTo: '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: RE: partitioning You could accomplish this with a before insert trigger and a partitioning column that contains the value 0 through 7. e.g. create trigger before insert for each row begin select mod (sequence.nextval, 8) into :new.partition_column from dual ; end ; / Something similar would be achieve by hash partitioning, which is easier to implement. -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]] I am wondering if there is any way to achieve horizontal partitioning in Oracle. Assuming that I have about 8 partitions for a table.When there is INSERT onto this table I want one record to be inserted into each partition i.e 1st record goes into partition 1 2nd record goes into partition 2 3rd record goes into partition 3 . . 8th record goes into partition 8 9th record goes into partition 1. I guess this feature is available in Informix handled by The informix engine.I am not sure if Oracle has something similiar to this OR is it possible to design a logic and embede it ,but what would be the performance effect? Any thoughts or similiar ideas
RE: partitioning
Title: RE: partitioning -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]] How do I exactly implement this.In the before insert trigger what after I generate the value for the new partition column.How does the records go into that partition. Have you tried this.How is the performance for an insert into a table of 10 records everyday.Executing the trigger for every insert for high volume of data may be costly on the performance..? Can we achieve this or anything closer using HASH partitioning as suggested by others. To answer your questions: How do you implement? Create a column that is populated by mod (sequence_number, num_desired_partitions) and then do a range partition on that column. (see first example below) But if you have access to the hash partition feature, then you can use hash partitions (see second example below) that will have pretty much the same distribution of row count against partitions. How is the performance? Don't know, never really tried the range partition method I suggested. Can you do it with hash partitions? Yes, see below. My opinion is: if you can do it with an existing Oracle feature, why try and write more complicated code to do it yourself? If you want to separate the table into multiple partitions for load balancing, then the hash partition should be the right solution. Some of our performance experts might have some educated reasons for choosing the first method over the other, but I doubt it. -- using sequence and range partition drop table sales ; drop sequence sales_seq ; drop sequence sales_partition_seq ; create table sales (sales_id number, sales_partition_key number (1), sales_date date, sales_amt number (6,2), item_count number (5), constraint sales_pk primary key (sales_id) ) partition by range (sales_partition_key) (partition sales_p0 values less than (1), partition sales_p1 values less than (2), partition sales_p2 values less than (3), partition sales_p3 values less than (4), partition sales_p4 values less than (5), partition sales_p5 values less than (6), partition sales_p6 values less than (7), partition sales_p7 values less than (8) ) ; create sequence sales_seq ; create sequence sales_partition_seq ; create trigger sales_b4i before insert on sales for each row begin select sales_seq.nextval, mod (sales_partition_seq.nextval, 8) into :new.sales_id, :new.sales_partition_key from dual ; end ; / insert into sales (sales_date, sales_amt, item_count) select a.last_ddl_time, mod (a.object_id, 100) / 100, mod (b.object_id, 10) from dba_objects a, dba_objects b where rownum 10 ; commit ; select 'p0', count (*) from sales partition (sales_p0) union select 'p1', count (*) from sales partition (sales_p1) union select 'p2', count (*) from sales partition (sales_p2) union select 'p3', count (*) from sales partition (sales_p3) union select 'p4', count (*) from sales partition (sales_p4) union select 'p5', count (*) from sales partition (sales_p5) union select 'p6', count (*) from sales partition (sales_p7) union select 'p7', count (*) from sales partition (sales_p7) ; SQL select 'p0', count (*) from sales partition (sales_p0) 2 union 3 select 'p1', count (*) from sales partition (sales_p1) 4 union 5 select 'p2', count (*) from sales partition (sales_p2) 6 union 7 select 'p3', count (*) from sales partition (sales_p3) 8 union 9 select 'p4', count (*) from sales partition (sales_p4) 10 union 11 select 'p5', count (*) from sales partition (sales_p5) 12 union 13 select 'p6', count (*) from sales partition (sales_p7) 14 union 15 select 'p7', count (*) from sales partition (sales_p7) ; 'P COUNT(*) -- - p0 12499 p1 12500 p2 12500 p3 12500 p4 12500 p5 12500 p6 12500 p7 12500 8 ligne(s) sélectionnée(s). -- using hash partition drop table sales ; drop sequence sales_seq ; drop sequence sales_partition_seq ; create table sales (sales_id number, sales_date date, sales_amt number (6,2), item_count number (5), constraint sales_pk primary key (sales_id) ) partition by hash (sales_id) (partition sales_p0, partition sales_p1, partition sales_p2, partition sales_p3, partition sales_p4, partition sales_p5, partition sales_p6, partition sales_p7 ) ; create sequence sales_seq ; create trigger sales_b4i before insert on sales for each row begin select sales_seq.nextval into :new.sales_id from dual ; end ; / insert into sales (sales_date, sales_amt, item_count) select a.last_ddl_time, mod (a.object_id, 100) / 100, mod (b.object_id, 10) from dba_objects a, dba_objects b where rownum 10 ; commit ; select 'p0', count (*) from sales partition (sales_p0) union select 'p1', count (*) from sales partition (sales_p1) union select 'p2', count (*) from sales partition (sales_p2) union select 'p3', count (*) from sales partition (sales_p3) union select 'p4', count (*) from sales partition (sales_p4) union select 'p5', count (*) from sales partition (sales_p5
RE: partitioning
read about hash partitioning -Original Message- Sent: Wednesday, March 19, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Hi, I am wondering if there is any way to achieve horizontal partitioning in Oracle. Assuming that I have about 8 partitions for a table.When there is INSERT onto this table I want one record to be inserted into each partition i.e 1st record goes into partition 1 2nd record goes into partition 2 3rd record goes into partition 3 . . 8th record goes into partition 8 9th record goes into partition 1. I guess this feature is available in Informix handled by The informix engine.I am not sure if Oracle has something similiar to this OR is it possible to design a logic and embede it ,but what would be the performance effect? Any thoughts or similiar ideas Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning
Title: RE: partitioning You could accomplish this with a before insert trigger and a partitioning column that contains the value 0 through 7. e.g. create trigger before insert for each row begin select mod (sequence.nextval, 8) into :new.partition_column from dual ; end ; / Something similar would be achieve by hash partitioning, which is easier to implement. -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]] I am wondering if there is any way to achieve horizontal partitioning in Oracle. Assuming that I have about 8 partitions for a table.When there is INSERT onto this table I want one record to be inserted into each partition i.e 1st record goes into partition 1 2nd record goes into partition 2 3rd record goes into partition 3 . . 8th record goes into partition 8 9th record goes into partition 1. I guess this feature is available in Informix handled by The informix engine.I am not sure if Oracle has something similiar to this OR is it possible to design a logic and embede it ,but what would be the performance effect? Any thoughts or similiar ideas
RE: partitioning
Ravindra Disk striping with RAID will accomplish what you are seeking. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 19, 2003 1:00 PM To: Multiple recipients of list ORACLE-L Hi, I am wondering if there is any way to achieve horizontal partitioning in Oracle. Assuming that I have about 8 partitions for a table.When there is INSERT onto this table I want one record to be inserted into each partition i.e 1st record goes into partition 1 2nd record goes into partition 2 3rd record goes into partition 3 . . 8th record goes into partition 8 9th record goes into partition 1. I guess this feature is available in Informix handled by The informix engine.I am not sure if Oracle has something similiar to this OR is it possible to design a logic and embede it ,but what would be the performance effect? Any thoughts or similiar ideas Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning
Title: RE: partitioning thanks -Original Message-From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]Sent: Wednesday, March 19, 2003 11:52 AMTo: '[EMAIL PROTECTED]'Cc: '[EMAIL PROTECTED]'Subject: RE: partitioning You could accomplish this with a before insert trigger and a partitioning column that contains the value 0 through 7. e.g. create trigger before insert for each row begin select mod (sequence.nextval, 8) into :new.partition_column from dual ; end ; / Something similar would be achieve by hash partitioning, which is easier to implement. -Original Message- From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]] I am wondering if there is any way to achieve horizontal partitioning in Oracle. Assuming that I have about 8 partitions for a table.When there is INSERT onto this table I want one record to be inserted into each partition i.e 1st record goes into partition 1 2nd record goes into partition 2 3rd record goes into partition 3 . . 8th record goes into partition 8 9th record goes into partition 1. I guess this feature is available in Informix handled by The informix engine.I am not sure if Oracle has something similiar to this OR is it possible to design a logic and embede it ,but what would be the performance effect? Any thoughts or similiar ideas
Re: Partitioning
Title: RE: Partitioning It's true for 9.2, too. It doesn't make sense to have different storage parameters for hash partitions. - Original Message - From: Jacques Kilchoer To: Multiple recipients of list ORACLE-L Sent: Wednesday, February 26, 2003 6:39 PM Subject: RE: Partitioning I'll add that for HASH partitions or subpartitions you can only specify TABLESPACE, all other storage parameters are taken from table / partition defaults. At least in 8.1.7. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] YES, here's an example: create table ate_headers(module_id varchar2(13), session_number varchar2(16), test_group number(4), test_date date, first_record char(1) default 'F', last_record char(1) default 'F', tester_id number(4), slot number(6), test_kind number(4), work_order varchar2(15), session_duration number, program_id number(4), spec_name varchar2(20), spec_revision varchar2(2), vector_name varchar2(12), vector_revision varchar2(2), bin_number number(6), constraint test_header_fk foreign key (module_id) references module_master(module_id) on delete cascade) partition by range (test_group) ( partition h1q398 values less than (2) tablespace ate1 storage(initial 150M next 150M maxextents 99), partition h2q398 values less than (3) tablespace ate2 storage(initial 150M next 150M maxextents 99), partition h3q398 values less than (4) tablespace ate3 storage(initial 150M next 150M maxextents 99), partition h4q398 values less than (5) tablespace ate4 storage(initial 150M next 150M maxextents 99), /* partition h5q398 values less than (6) tablespace ate5 storage(initial 150M next 150M maxextents 99), */ partition h6q398 values less than (100) tablespace ate6 storage(initial 150M next 150M maxextents 99)); Dick Goulet Reply Separator Author: "Conrad Meertins" [EMAIL PROTECTED] Date: 2/26/2003 1:44 PMIf you have a table partitioned, can you specify the storage size of each partition in that tables
Re: Partitioning
what do you mean by storage size? if you mean the initial, next and so on - yes you can. if you are talking about how big it should be - i dont think you can do it... Babu Conrad Meertins [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] e-data.com cc: Sent by: Subject: Partitioning [EMAIL PROTECTED] 02/26/03 04:44 PM Please respond to ORACLE-L If you have a table partitioned, can you specify the storage size of each partition in that tables I looked at dba_tab_partitions and dba_segments views. Although the show me storage information, I am unable to create a table where I can specify the storage size for each partition. Am I doing something wrong Or you cannot specify a storage size for partitions. Please help.. Thanks Conrad... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Conrad Meertins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ This e-mail transmission and any attachments to it are intended solely for the use of the individual or entity to whom it is addressed and may contain confidential and privileged information. If you are not the intended recipient, your use, forwarding, printing, storing, disseminating, distribution, or copying of this communication is prohibited. If you received this communication in error, please notify the sender immediately by replying to this message and delete it from your computer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitioning
Yes you can. Check the storage option of the partition clause.
Something like:
create table (sales_yr varchar2(4),)
partition by range (sales_yr)
(partition p1 values less than ('1996')
tablespace blah_p1
storage (initial 100M next 100M pctincrease 0),
partition p2 values less than ('2000')
tablespace blah_p2
storage (initial 200M next 200M pctincrease 0),
.
)
/
BTW.. does you company sell 'DBA-IN-A_BOX' ???
HTH,
- Kirti
-Original Message-
Sent: Wednesday, February 26, 2003 3:44 PM
To: Multiple recipients of list ORACLE-L
If you have a table partitioned, can you specify the storage size of each
partition in that tables
I looked at dba_tab_partitions and dba_segments views.
Although the show me storage information, I am unable to create a table
where I can specify the storage size for each partition.
Am I doing something wrong Or you cannot specify a storage size for
partitions.
Please help..
Thanks
Conrad...
-
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Partitioning
Thank you very much Conrad... -Original Message- Sent: Wednesday, February 26, 2003 5:31 PM To: Conrad Meertins; Multiple recipients of list ORACLE-L YES, here's an example: create table ate_headers(module_id varchar2(13), session_number varchar2(16), test_group number(4), test_date date, first_record char(1) default 'F', last_record char(1) default 'F', tester_id number(4), slot number(6), test_kind number(4), work_order varchar2(15), session_duration number, program_id number(4), spec_name varchar2(20), spec_revision varchar2(2), vector_name varchar2(12), vector_revision varchar2(2), bin_number number(6), constraint test_header_fk foreign key (module_id) references module_master(module_id) on delete cascade) partition by range (test_group) ( partition h1q398 values less than (2) tablespace ate1 storage(initial 150M next 150M maxextents 99), partition h2q398 values less than (3) tablespace ate2 storage(initial 150M next 150M maxextents 99), partition h3q398 values less than (4) tablespace ate3 storage(initial 150M next 150M maxextents 99), partition h4q398 values less than (5) tablespace ate4 storage(initial 150M next 150M maxextents 99), /* partition h5q398 values less than (6) tablespace ate5 storage(initial 150M next 150M maxextents 99), */ partition h6q398 values less than (100) tablespace ate6 storage(initial 150M next 150M maxextents 99)); Dick Goulet Reply Separator Author: Conrad Meertins [EMAIL PROTECTED] Date: 2/26/2003 1:44 PM If you have a table partitioned, can you specify the storage size of each partition in that tables I looked at dba_tab_partitions and dba_segments views. Although the show me storage information, I am unable to create a table where I can specify the storage size for each partition. Am I doing something wrong Or you cannot specify a storage size for partitions. Please help.. Thanks Conrad... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Conrad Meertins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Conrad Meertins INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitioning
Conrad
I've always stored each partition in a separate tablespace. Make each
tablespace LMT with uniform extents. But if you want, you can use the
syntax:
partition by range ( parm1, periodenddate )
(
partition sum_fy_01 values less than ('FY', to_date('01011999','mmdd'))
tablespace data_fy_01
storage ( ),
partition sum_fy_02 values less than ('FY', to_date('01012000','mmdd'))
tablespace data_fy_02
storage ( ),
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, February 26, 2003 3:44 PM
To: Multiple recipients of list ORACLE-L
If you have a table partitioned, can you specify the storage size of each
partition in that tables
I looked at dba_tab_partitions and dba_segments views.
Although the show me storage information, I am unable to create a table
where I can specify the storage size for each partition.
Am I doing something wrong Or you cannot specify a storage size for
partitions.
Please help..
Thanks
Conrad...
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Conrad Meertins
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Partitioning
Title: RE: Partitioning I'll add that for HASH partitions or subpartitions you can only specify TABLESPACE, all other storage parameters are taken from table / partition defaults. At least in 8.1.7. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] YES, here's an example: create table ate_headers(module_id varchar2(13), session_number varchar2(16), test_group number(4), test_date date, first_record char(1) default 'F', last_record char(1) default 'F', tester_id number(4), slot number(6), test_kind number(4), work_order varchar2(15), session_duration number, program_id number(4), spec_name varchar2(20), spec_revision varchar2(2), vector_name varchar2(12), vector_revision varchar2(2), bin_number number(6), constraint test_header_fk foreign key (module_id) references module_master(module_id) on delete cascade) partition by range (test_group) ( partition h1q398 values less than (2) tablespace ate1 storage(initial 150M next 150M maxextents 99), partition h2q398 values less than (3) tablespace ate2 storage(initial 150M next 150M maxextents 99), partition h3q398 values less than (4) tablespace ate3 storage(initial 150M next 150M maxextents 99), partition h4q398 values less than (5) tablespace ate4 storage(initial 150M next 150M maxextents 99), /* partition h5q398 values less than (6) tablespace ate5 storage(initial 150M next 150M maxextents 99), */ partition h6q398 values less than (100) tablespace ate6 storage(initial 150M next 150M maxextents 99)); Dick Goulet Reply Separator Author: Conrad Meertins [EMAIL PROTECTED] Date: 2/26/2003 1:44 PM If you have a table partitioned, can you specify the storage size of each partition in that tables
RE: partitioning star schema
Typical canned answer is try to partition by a numeric. We partition by a date when we can. I'm torn between the order of one and two, but that is more personal preference. I generally try to partition by the fields that make joins run best and the ones that make maintainence better if I can. I would definately try to make the primary partition the one most used in a where clause... make the access fast as you can. 9.2 has some way better partitioning options than 8i has... we have been just using those because we are in the process of getting up to speed on our new 9i upgrade. April -Original Message- To: Multiple recipients of list ORACLE-L Sent: 1/10/2003 8:33 AM Hello, We are still struggling with partitioning of star schema fact tables. As of yet, we haven't been able to test/compare any of the following scenarios (because we're not yet legal with the partitioning option), so I am posting in the hope that someone with more partitioning experience will comment. This will eventually be implemented on 9.2 on Solaris. By star schema fact tables, I am referring to tables that consist mostly of surrogate key id fields (used for joining to dimension tables), and numeric fields containing a quantity measure. The id fields are never directly referenced in WHERE clauses of queries as *filter* conditions, but are frequently referenced in join conditions. The filter conditions usually reference fields in one of the dimension tables joined to be the fact table. We have developed some (untested) practical guidelines for partitioning. They are listed from best to worst. These are intended to optimize querying (not the incremental loading), and they apply to tables rather than indexes. (We are creating a similar list for indexes) Here they are: 1) partition by a field most frequently referenced in the WHERE clause as a filter condition; subpartition by a field less frequently referenced as a filter condition. This enables a double partition-pruning. 2) partition by a field most frequently referenced in the WHERE clause as a filter condition; subpartition by a field frequently referenced as a join condition, where the joined-to table is partitioned exactly the same way. This enables partition-pruning and partition-wise joins. 3) partition by a field in the table that is often referenced in WHERE clauses as a filter condition; this enables partition pruning. 4) partition by a frequently-used join field where the joined-to table is partitioned exactly the same; this enables partition-wise joins. 5) partition by a frequently-used join field. 6) partition by something is usually better than not partitioning at all. In many cases, we have to go all the way to #5 before this applies. As I said, the fact table id fields are never referenced in WHERE clauses, the dimension tables are rarely large enough to be partitioned, and the WHERE filter conditions usually apply to a dimension table, so we wind up partitioning by an id field frequently used in a join clause. This id field is often a date_id field, which is used to join to a dates dimension table, because a date range is frequently used as a filter condition in queries. Questions: 1) Do you agree with the ranking above? 2) Is there any substantial benefit to partitioning a fact table by an id field, when the id field is used to join to a non-partitioned dimension table which is referenced in a filter condition? Thanks to all who made it this far. More thanks to any responders. Most thanks to those with helpful comments. All-thanked-out, Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication, including attachments, is strictly confidential and for the intended use of the addressee only; it may also contain proprietary, price sensitive, or legally privileged information. Notice is hereby given that any disclosure, distribution, dissemination, use, or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this communication in error, please notify the sender immediately by reply e-mail, delete this communication, and destroy all copies. Corporate Systems, Inc. has taken reasonable precautions to ensure that any attachment to this e-mail has been swept for viruses. We specifically disclaim
RE: partitioning star schema
Bill - Since nobody has replied yet, I'll toss in a couple of ideas. What is your motivation for partitioning? Performance? That is what I get from your posting. I think you have good ideas. I'll provide one more that got us a good performance boost. Some queries were often comparing this month with year ago month. Table scans were killing us. I ended up creating monthly partitions so the query just has to scan two small partitions. So don't just consider your partitioning keys, but also the granularity of your partitions. Manageability? Build materialized views wherever possible and let the users query them. Use yearly partitions so it is easier to manage the data. I think your ideas are good, just trying to get you to consider other ways to achieve your goals. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 8:34 AM To: Multiple recipients of list ORACLE-L Hello, We are still struggling with partitioning of star schema fact tables. As of yet, we haven't been able to test/compare any of the following scenarios (because we're not yet legal with the partitioning option), so I am posting in the hope that someone with more partitioning experience will comment. This will eventually be implemented on 9.2 on Solaris. By star schema fact tables, I am referring to tables that consist mostly of surrogate key id fields (used for joining to dimension tables), and numeric fields containing a quantity measure. The id fields are never directly referenced in WHERE clauses of queries as *filter* conditions, but are frequently referenced in join conditions. The filter conditions usually reference fields in one of the dimension tables joined to be the fact table. We have developed some (untested) practical guidelines for partitioning. They are listed from best to worst. These are intended to optimize querying (not the incremental loading), and they apply to tables rather than indexes. (We are creating a similar list for indexes) Here they are: 1) partition by a field most frequently referenced in the WHERE clause as a filter condition; subpartition by a field less frequently referenced as a filter condition. This enables a double partition-pruning. 2) partition by a field most frequently referenced in the WHERE clause as a filter condition; subpartition by a field frequently referenced as a join condition, where the joined-to table is partitioned exactly the same way. This enables partition-pruning and partition-wise joins. 3) partition by a field in the table that is often referenced in WHERE clauses as a filter condition; this enables partition pruning. 4) partition by a frequently-used join field where the joined-to table is partitioned exactly the same; this enables partition-wise joins. 5) partition by a frequently-used join field. 6) partition by something is usually better than not partitioning at all. In many cases, we have to go all the way to #5 before this applies. As I said, the fact table id fields are never referenced in WHERE clauses, the dimension tables are rarely large enough to be partitioned, and the WHERE filter conditions usually apply to a dimension table, so we wind up partitioning by an id field frequently used in a join clause. This id field is often a date_id field, which is used to join to a dates dimension table, because a date range is frequently used as a filter condition in queries. Questions: 1) Do you agree with the ranking above? 2) Is there any substantial benefit to partitioning a fact table by an id field, when the id field is used to join to a non-partitioned dimension table which is referenced in a filter condition? Thanks to all who made it this far. More thanks to any responders. Most thanks to those with helpful comments. All-thanked-out, Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
Re: partitioning star schema
Bill, I'm impressed! Most of us follow something similar to the rankings you mentioned - but a very few actually spell it out and put it for the data architects. Now that you have done most of the work, I have a few comments. First, you don't have to worry about legal issues for testing these out. Oracle licensing enables you to try out all (yes, all, including RAC option) in development as long as you promise not to deploy in production. It is perfect for your situation - try out all 9.2 partitioning schemes in development. Second, you are under impression that partitioning keys should be numeric; they don't have to be. Dates are most frequently used and 9.2 has another superior option called list partitioning where you specify discrete values, very useful in situations like, say, partitioning based on business units. Say you have 30 business units called 'TOYS', 'DOLLS', 'GAMES', etc.. and each one sends data infrequently. Your scheme will be to partition based on the business units and then sub partition based on the date. Third, the one thing you have left out in the consideration for partitioning keys is the needs to store and archive. What is your archival strategy? If you archive off every quarter, then the date should be part of the key. Similarly in the previous point I mentioned the arrival of data in different intervals from different sources. So, in order to minimize the downtime you will need to partition based on source. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 10, 2003 9:33 AM Hello, We are still struggling with partitioning of star schema fact tables. As of yet, we haven't been able to test/compare any of the following scenarios (because we're not yet legal with the partitioning option), so I am posting in the hope that someone with more partitioning experience will comment. This will eventually be implemented on 9.2 on Solaris. By star schema fact tables, I am referring to tables that consist mostly of surrogate key id fields (used for joining to dimension tables), and numeric fields containing a quantity measure. The id fields are never directly referenced in WHERE clauses of queries as *filter* conditions, but are frequently referenced in join conditions. The filter conditions usually reference fields in one of the dimension tables joined to be the fact table. We have developed some (untested) practical guidelines for partitioning. They are listed from best to worst. These are intended to optimize querying (not the incremental loading), and they apply to tables rather than indexes. (We are creating a similar list for indexes) Here they are: 1) partition by a field most frequently referenced in the WHERE clause as a filter condition; subpartition by a field less frequently referenced as a filter condition. This enables a double partition-pruning. 2) partition by a field most frequently referenced in the WHERE clause as a filter condition; subpartition by a field frequently referenced as a join condition, where the joined-to table is partitioned exactly the same way. This enables partition-pruning and partition-wise joins. 3) partition by a field in the table that is often referenced in WHERE clauses as a filter condition; this enables partition pruning. 4) partition by a frequently-used join field where the joined-to table is partitioned exactly the same; this enables partition-wise joins. 5) partition by a frequently-used join field. 6) partition by something is usually better than not partitioning at all. In many cases, we have to go all the way to #5 before this applies. As I said, the fact table id fields are never referenced in WHERE clauses, the dimension tables are rarely large enough to be partitioned, and the WHERE filter conditions usually apply to a dimension table, so we wind up partitioning by an id field frequently used in a join clause. This id field is often a date_id field, which is used to join to a dates dimension table, because a date range is frequently used as a filter condition in queries. Questions: 1) Do you agree with the ranking above? 2) Is there any substantial benefit to partitioning a fact table by an id field, when the id field is used to join to a non-partitioned dimension table which is referenced in a filter condition? Thanks to all who made it this far. More thanks to any responders. Most thanks to those with helpful comments. All-thanked-out, Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED]
Re: partitioning questions
Actually, even without the date field, queries will still benefit from the partition-wise join on the charge_id column. You would see something like this (partition hash all) in the plan: SELECT STATEMENT CHOOSE (Cost=178026) PARTITION HASH ALL 1:4:1 HASH JOIN PARTITION RANGE ALL 1:13:3 TABLE ACCESS FULL TAB_5 * 1:52:3 PARTITION RANGE ALL 1:13:5 TABLE ACCESS FULL TAB_6 * 1:52:5 [EMAIL PROTECTED] 11/23/02 03:19a.m. 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? No for the same reason as above. It would be also interesting to check whether you should rather have a LOCAL or GLOBAL index on charge_id in this case. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Binley Lim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: partitioning questions
[EMAIL PROTECTED] wrote: Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? I do think so. 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? I doubt it, because the main benefit of partitioning is clipping - trying to limit searches to a few partitions. In other words, you should partition on a criterion you have input (I mean something which appears as WHERE PARTITION_KEY = constant or (better) bind variable in your queries). If charge_id is just use for joins, it means that in a way it is derived from something else (condition on dates) and therefore using it as a partition key would be useless. 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? No for the same reason as above. It would be also interesting to check whether you should rather have a LOCAL or GLOBAL index on charge_id in this case. 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? With PQO probably less expensive, but I have not tested it specifically. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning questions
That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: partitioning questions Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: partitioning questions
Mladen, are you sure, partitioning is included with oracle 9? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 11:08 AM That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: partitioning questions Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning questions
Partitioning is still a separately licensed product. RF Robert G. Freeman - Oracle OCP Oracle Database Architect CSX Midtier Database Administration Author of several Oracle books you can find on Amazon.com! Londo Mollari: Ah, arrogance and stupidity all in the same package. How efficient of you. -Original Message- Sent: Friday, November 22, 2002 12:19 PM To: Multiple recipients of list ORACLE-L Mladen, are you sure, partitioning is included with oracle 9? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 22, 2002 11:08 AM That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: partitioning questions Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning questions
MyViews below... Regards, Viral Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? -- NO, they have to be equi-partitioned and you have to specify atleast the leading keys in the join for both tables. 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? -- SEEMS like a good choice since you always limit your query on charge_id, however data distribution in that column also plays a role. 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do jointhe tables by charge_id still benefit? - Dont think that would help. However, you could have a global index on charge_id on both tables. If you insist topartition the data as mentioned in #3, then for the benefit of your queries you may want the exclusive globalindex on charge_id. (As there are pros, there are cons for this too) Again depending on the type of the data contents/value of the columns, you could have 2 bitmap indexes (one on the date and another on charge_id, but this is not always advisable) 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? -- I think it is same as non-partioned tables. From: "Gogala, Mladen" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: partitioning questions Date: Fri, 22 Nov 2002 08:08:55 -0800 That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: partitioning questions Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). MSN 8 helps ELIMINATE E-MAIL VIRUSES. Get 2 months FREE*. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viral Desai INET: [EMAIL PROTECTED] Fat City Network
RE: partitioning questions
As of when? It's still listed as a costly option on the Oracle Store web page. The perpetual license is $10,000.00 per CPU for the U.S. market. Oracle 9i comes with lots of options many of which cost extra. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, November 22, 2002 8:09 AM To: Multiple recipients of list ORACLE-L That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, November 22, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Subject: partitioning questions Hello, We are planning to move to Oracle 9.2 on as-yet-undecided platform (probably red hat linux on ibm hardware). We finally pursuaded management to purchase the partitioning license, and I have some questions on partitioning: Scenario: Range-Partition tableA on a service_date field by year; Range-Partition tableB on a posted_date field by year; These tables are frequently joined using a separate field called charge_id, a surrogate key. Queries against these tables usually include some sort of date filter, join on the charge_id field, and are done in parallel. 1) Would this configuration promote the use of partition-wise joins between tableA and tableB by the optimizer? 2) Would it be better to partition the tables (either range or hash) by the join field, charge_id? 3) If we range-partition by date, subpartition by hash (charge_id), would queries that do not reference the date field, but do join the tables by charge_id still benefit? 4) Is it more expensive, less expensive, or about equal to do a full table scan on a partitioned table vs the same table non-partitioned? As always, thanks to any responders. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Partitioning
Is there a single column/value that you could do LIST partitioning on instead of range or hash? Rick Rishi.Jain@VerizonWi reless.com To: Multiple recipients of list ORACLE-L Sent by:[EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject: Partitioning 11/19/2002 02:38 PM Please respond to ORACLE-L We have a table with around 80 million rows. The table has been partitioned by hash as there is no clear way of partitioning depending on range etc.. The data is very unevenly distributed in these partitions. Some of them even have 3 times the number of rows as compared to the other partitions. This application is being ported from Informix to Oracle (9i R2). In informix the dba's had partitioned the table based on a function . He was taking the mod of the number ( dividing by 10 ) . The values were then placed in either of the 10 partitions ranging from 0 - 9. This really gave us very good distribution of data .Can we achieve something similar in 9i with list partitioning. TIA Rishi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitioning
Rishi, The algorithm for has partitioning in Oracle requires you to choose your partition count as a power of 2 (i.e. 2, 4, 8, 16, 32 partitions, etc) any other number will be unbalanced as you've seen. Cheers, Pete -- Peter Moore Systems DBA, Mid-Range Centre of Expertise, Global Service Delivery, SchlumbergerSema, Reading Phone: 0118 963 6827 Email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 19 November 2002 19:39 To: Multiple recipients of list ORACLE-L Subject: Partitioning We have a table with around 80 million rows. The table has been partitioned by hash as there is no clear way of partitioning depending on range etc.. The data is very unevenly distributed in these partitions. Some of them even have 3 times the number of rows as compared to the other partitions. This application is being ported from Informix to Oracle (9i R2). In informix the dba's had partitioned the table based on a function . He was taking the mod of the number ( dividing by 10 ) . The values were then placed in either of the 10 partitions ranging from 0 - 9. This really gave us very good distribution of data .Can we achieve something similar in 9i with list partitioning. TIA Rishi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. _ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MOORE, Peter Rbh INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Partitioning
[EMAIL PROTECTED] wrote: We have a table with around 80 million rows. The table has been partitioned by hash as there is no clear way of partitioning depending on range etc.. The data is very unevenly distributed in these partitions. Some of them even have 3 times the number of rows as compared to the other partitions. This application is being ported from Informix to Oracle (9i R2). In informix the dba's had partitioned the table based on a function . He was taking the mod of the number ( dividing by 10 ) . The values were then placed in either of the 10 partitions ranging from 0 - 9. This really gave us very good distribution of data .Can we achieve something similar in 9i with list partitioning. TIA Rishi -- I have recently met a similar problem. The risk with hash partitioning and a skewed distribution is that a low cardinality key hashes into the same value as a high cardinality one. Bad for the rows with the low cardinality key. What you should do is to decide first how many partitions you want, and what keys you want together. Then find a way, whether it is range or list partitioning, to have the corresponding rows stored where they should. Hash partitioning is fine, but when the distribution is more or less uniform. HTH, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Partitioning
You can do the same thing in Oracle as you did in Informix. Create range partitions for 0-9 and use your mod 10 on the key. I believe you will have to add a column in the table to hold the mod number and make that the partitioning column. HTH, John [EMAIL PROTECTED] 11/19/02 11:38AM We have a table with around 80 million rows. The table has been partitioned by hash as there is no clear way of partitioning depending on range etc.. The data is very unevenly distributed in these partitions. Some of them even have 3 times the number of rows as compared to the other partitions. This application is being ported from Informix to Oracle (9i R2). In informix the dba's had partitioned the table based on a function . He was taking the mod of the number ( dividing by 10 ) . The values were then placed in either of the 10 partitions ranging from 0 - 9. This really gave us very good distribution of data .Can we achieve something similar in 9i with list partitioning. TIA Rishi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Carlson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Partitioning
Rishi, In hash partitioning you have to select the number of partitions equal to the power of 2. i.e. 2,4,8,16 so on. Your problem of skewed partition size is well documented in hash partitioning if you don't choose correct number of partitions. Please refer to JLewis Book. He deals with this very well and from practical stand point. http://www.amazon.com/exec/obidos/ASIN/0201715848/jlcomp/102-6448893-9036931 Check his site too http://www.jlcomp.demon.co.uk/ HTH Cheers RS P.S.: Listers ...I am back..:) ( I knowI know...you all say who noticed ya gone man..!! ) --- [EMAIL PROTECTED] wrote: We have a table with around 80 million rows. The table has been partitioned by hash as there is no clear way of partitioning depending on range etc.. The data is very unevenly distributed in these partitions. Some of them even have 3 times the number of rows as compared to the other partitions. This application is being ported from Informix to Oracle (9i R2). In informix the dba's had partitioned the table based on a function . He was taking the mod of the number ( dividing by 10 ) . The values were then placed in either of the 10 partitions ranging from 0 - 9. This really gave us very good distribution of data .Can we achieve something similar in 9i with list partitioning. TIA Rishi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sakthi , Raj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: partitioning
Title: Message None It is an option (Means you pay). Yechiel AdarMehish - Original Message - From: Cunningham, Gerald To: Multiple recipients of list ORACLE-L Sent: Wednesday, May 22, 2002 6:38 PM Subject: partitioning Hi all, Does anybody know with what version of Oracle partitioning was included at no extra cost? Thanks! - Jerry
RE: partitioning
Absolutely true! Even worse, it is an option on top of Enterprise Edition, so you can't license Standard Edition and buy the partitioning option. -Original Message- Sent: Wednesday, May 22, 2002 12:06 PM To: Multiple recipients of list ORACLE-L None It is an option (Means you pay). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Wednesday, May 22, 2002 6:38 PM Hi all, Does anybody know with what version of Oracle partitioning was included at no extra cost? Thanks! - Jerry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning
Damn! -Original Message- Sent: Wednesday, May 22, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Absolutely true! Even worse, it is an option on top of Enterprise Edition, so you can't license Standard Edition and buy the partitioning option. -Original Message- Sent: Wednesday, May 22, 2002 12:06 PM To: Multiple recipients of list ORACLE-L None It is an option (Means you pay). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Wednesday, May 22, 2002 6:38 PM Hi all, Does anybody know with what version of Oracle partitioning was included at no extra cost? Thanks! - Jerry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cunningham, Gerald INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning
Partition views, after all, were not that bad! -Original Message- Sent: Wednesday, May 22, 2002 5:21 PM To: Multiple recipients of list ORACLE-L Damn! -Original Message- Sent: Wednesday, May 22, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Absolutely true! Even worse, it is an option on top of Enterprise Edition, so you can't license Standard Edition and buy the partitioning option. -Original Message- Sent: Wednesday, May 22, 2002 12:06 PM To: Multiple recipients of list ORACLE-L None It is an option (Means you pay). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Wednesday, May 22, 2002 6:38 PM Hi all, Does anybody know with what version of Oracle partitioning was included at no extra cost? Thanks! - Jerry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cunningham, Gerald INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: partitioning
instead of triggers Waleed -Original Message- Sent: Wednesday, May 22, 2002 7:12 PM To: Multiple recipients of list ORACLE-L Quick memory test - were you able to create partition views such that you could insert rows into view? In other words, could you create multiple tables joined in a view, then be able to insert into the view? If anybody can recall, I would appreciate it. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 22, 2002 5:50 PM To: Multiple recipients of list ORACLE-L Partition views, after all, were not that bad! -Original Message- Sent: Wednesday, May 22, 2002 5:21 PM To: Multiple recipients of list ORACLE-L Damn! -Original Message- Sent: Wednesday, May 22, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Absolutely true! Even worse, it is an option on top of Enterprise Edition, so you can't license Standard Edition and buy the partitioning option. -Original Message- Sent: Wednesday, May 22, 2002 12:06 PM To: Multiple recipients of list ORACLE-L None It is an option (Means you pay). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Wednesday, May 22, 2002 6:38 PM Hi all, Does anybody know with what version of Oracle partitioning was included at no extra cost? Thanks! - Jerry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cunningham, Gerald INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
RE: partitioning
Dennis, Sure, you need to use INSTEAD OF triggers on views. I don't think Oracle8 supports INSTED OF triggers, but Oracle8i does. I was working on a project some time ago where we had used partitioned views and instead of triggers to implement functionality that's somewhat similar to partitioning option! Sure, you don't have all the nice features at your disposal for administration and maintenance that partitioning provides, but when you look at the price difference between the two choices, and all you need is simple and basic partitioning, then partitioning view might work for you, and save you some big bucks :) HTH and GL! Jay -Original Message- Sent: Wednesday, May 22, 2002 7:12 PM To: Multiple recipients of list ORACLE-L Quick memory test - were you able to create partition views such that you could insert rows into view? In other words, could you create multiple tables joined in a view, then be able to insert into the view? If anybody can recall, I would appreciate it. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, May 22, 2002 5:50 PM To: Multiple recipients of list ORACLE-L Partition views, after all, were not that bad! -Original Message- Sent: Wednesday, May 22, 2002 5:21 PM To: Multiple recipients of list ORACLE-L Damn! -Original Message- Sent: Wednesday, May 22, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Absolutely true! Even worse, it is an option on top of Enterprise Edition, so you can't license Standard Edition and buy the partitioning option. -Original Message- Sent: Wednesday, May 22, 2002 12:06 PM To: Multiple recipients of list ORACLE-L None It is an option (Means you pay). Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Wednesday, May 22, 2002 6:38 PM Hi all, Does anybody know with what version of Oracle partitioning was included at no extra cost? Thanks! - Jerry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cunningham, Gerald INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from
RE: Partitioning Quandry
Kevin - This sounds similar to a partitioning issue that I was able to resolve. My suggestion is to consider partitioning on a concatenated key, INVOICE_STATE, UPDATE_DATE. You'll have to play with it, the partitions don't work the way you think they do. As I recall, if you say less than 'AL', '01-DEC-02' it will actually partition on values that equal 'AL', but less than '01-DEC-02'. I haven't done dates myself, so I probably have the syntax wrong. The part about sub-partitioning some partitions, should work as well, since your syntax is less than. If this isn't making sense, email me directly. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, April 30, 2002 10:04 AM To: Multiple recipients of list ORACLE-L here's one for the partitioning gurus out there I have an INVOICE table that I want to partition for performance and purging. The way I want to partition it is to do range partitioning on the INVOICE_STATE column, then sub-partition some of the partitions by UPDATE_DATE. The logic behind this is: 1) An invoice may be in sent, but unpaid (A) state for several months. 2) We never want to purge off unpaid invoices 3) After an invoice has been in paid (P) state for 6 months, we want to purge the invoice My basic idea was to have partition-movement enabled and to use a partitioning scheme like the following: TABLE invoice ( invoice_id, invoice_state, update_date, ... ) partition by range (invoice_state) ( partition inv_act values less than 'B' ,partition inv_hist values less than 'R' subpartition by range (update_date) ); Alas, you can only subpartition by HASH (or LIST in 9iR2) The only solution I can come up with is a 2 table solution -- keeping the unpaid invoices in one table and the paid invoices in another table that is range partitioned on UPDATE_DATE. The difficulties with this solution are coding the row movements (bi-directional) and having to code a partition-view. Any suggestions would be helpful. Kevin Toepke [EMAIL PROTECTED] The information in this electronic mail message is Trilegiant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Trilegiant Corporation is not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitioning Quandry
Thanks to Dennis, I found that partitioning on a concatenated key solves my
dilema.
For those who are interested, here is my new partitioning clause:
PARTITION BY RANGE (invoice_state, update_date) (
PARTITION inv_active VALUES LESS THAN
('B', TO_DATE('01-jan-', 'DD-MON-'))
TABLESPACE fins_sml_tbl
,PARTITION inv_paid_00 VALUES LESS THAN
('P', TO_DATE('01-jan-2001', 'DD-MON-'))
TABLESPACE fins_sml_tbl
,PARTITION inv_paid_01 VALUES LESS THAN
('P', TO_DATE('01-jan-2002', 'DD-MON-'))
TABLESPACE fins_sml_tbl
,PARTITION inv_paid_02 VALUES LESS THAN
('P', TO_DATE('01-jan-2003', 'DD-MON-'))
TABLESPACE fins_sml_tbl
The following table shows where records go:
invoice_state update_date partition
'A' does not matter inv_active
'P' '01-jan-2001' inv_paid_00
'P' '01-jan-2002' inv_paid_01
'P' '01-jan-2003' inv_paid_02
The kicker is that when doing concatenated key range partitioning, the first
column in the key has to be EQUAL to the value for the 2nd column to be
considered!
Caver
-Original Message-
Sent: Tuesday, April 30, 2002 12:53 PM
To: Multiple recipients of list ORACLE-L
Kevin - This sounds similar to a partitioning issue that I was able to
resolve. My suggestion is to consider partitioning on a concatenated key,
INVOICE_STATE, UPDATE_DATE. You'll have to play with it, the partitions
don't work the way you think they do. As I recall, if you say less than
'AL', '01-DEC-02' it will actually partition on values that equal 'AL', but
less than '01-DEC-02'. I haven't done dates myself, so I probably have the
syntax wrong. The part about sub-partitioning some partitions, should work
as well, since your syntax is less than. If this isn't making sense, email
me directly.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Tuesday, April 30, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L
here's one for the partitioning gurus out there
I have an INVOICE table that I want to partition for performance and
purging. The way I want to partition it is to do range partitioning on the
INVOICE_STATE column, then sub-partition some of the partitions by
UPDATE_DATE.
The logic behind this is:
1) An invoice may be in sent, but unpaid (A) state for several
months.
2) We never want to purge off unpaid invoices
3) After an invoice has been in paid (P) state for 6 months, we want
to purge the invoice
My basic idea was to have partition-movement enabled and to use a
partitioning scheme like the following:
TABLE invoice (
invoice_id, invoice_state, update_date, ...
) partition by range (invoice_state) (
partition inv_act values less than 'B'
,partition inv_hist values less than 'R'
subpartition by range (update_date)
);
Alas, you can only subpartition by HASH (or LIST in 9iR2) The only solution
I can come up with is a 2 table solution -- keeping the unpaid invoices in
one table and the paid invoices in another table that is range partitioned
on UPDATE_DATE. The difficulties with this solution are coding the row
movements (bi-directional) and having to code a partition-view.
Any suggestions would be helpful.
Kevin Toepke
[EMAIL PROTECTED]
The information in this electronic mail message is Trilegiant Confidential
and may be legally privileged. It is intended solely for the addressee(s).
Access to this Internet electronic mail message by anyone else is
unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or action taken or omitted to be taken in reliance on
it is prohibited and may be unlawful.
The sender believes that this E-mail and any attachments were free of any
virus, worm, Trojan horse, and/or malicious code when sent. This message and
its attachments could have been infected during transmission. By reading the
message and opening any attachments, the recipient accepts full
responsibility for taking protective and remedial action about viruses and
other defects. Trilegiant Corporation is not liable for any loss or damage
arising in any way from this message or its attachments.
--
RE: Partitioning question
You are right. Partitioning can still make your job as a DBA easier. You can purge data (assuming the purge key is the same as partition key) by dropping partition, etc. -Original Message- Sent: Thursday, March 14, 2002 12:33 PM To: Multiple recipients of list ORACLE-L I am new to the world of partitioning. 816 on W2K. In a white paper on metalink How to Implement Partitioning in Oracle Versions 8 and 8i it states that The RULE-based optimizer does not take the partitioning of tables and indexes into account. My question is - if your application is still rule based is there any value to partitioning? Some of the tables are over 30 million rows and 5G. If my understanding is correct I wouldn't see any performance improvement but would make maintenance simpler. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ji, Richard INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitioning question
You're right about the performance - they (the ones before I got here) tried to run the stats and use choose and the performance was terrible. Now it is not an option to use CBO. One of my tasks is to set up an environment and take it to CBO, but this is not the immediate priority. -Original Message- Sent: Thursday, March 14, 2002 12:25 PM To: Multiple recipients of list ORACLE-L John, First question, how do you know that your application is rule based? Most application scan be switched to cost optimization with no changes, although I will admit performance sometimes goes south. I prefer to set the database to 'choose' mode which allows the best of both worlds. Dick Goulet Reply Separator Author: Shaw John-P55297 [EMAIL PROTECTED] Date: 3/14/2002 9:33 AM I am new to the world of partitioning. 816 on W2K. In a white paper on metalink How to Implement Partitioning in Oracle Versions 8 and 8i it states that The RULE-based optimizer does not take the partitioning of tables and indexes into account. My question is - if your application is still rule based is there any value to partitioning? Some of the tables are over 30 million rows and 5G. If my understanding is correct I wouldn't see any performance improvement but would make maintenance simpler. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: partitioning option with parallel server
Sathish Tatikonda wrote: hi all, I have read this document on partitioning which says that partitioning cannot be implemented in clusters. [snip] This functionality is available for both indexes and tables, but cannot be implemented in clusters. does this mean that we cannot implement partitioning with Parallel server. It would be of great help in case you can clarify this point. thanks, Sathish. Clusters as in 'CREATE CLUSTER'. This has nothing to do with OPS. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitioning Questions
Harvinder... What is the access patterns of the queries that will be using these tables... Knowing how the data will be accessed is am important factor in determining how to set this stuff up... i.e. If your data is historical in nature and the queries typically access data for via time periods, then a range partition by date is an excellent choice... It all depends on the distribution of your data and how it will be accessed... Tim -Original Message- Sent: Wednesday, December 05, 2001 2:35 PM To: Multiple recipients of list ORACLE-L Hi, The system is Oracle 9i on Sun 2.8 1) Which partitioning is better to use HASH or RANGE. Do there is some overhead for oracle to calculate the hash number (hash partitioning) to find the particular partition. We need to partition 2 tables of sizes 175G and 162G 2 We have about 10 72G hard drives and 22 9G Hard Drives How many partitions to use (does this number depends upon number of hard disks). 3) What should be the ideal size of datafiles. Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Partitioning Tables
Eric, In my prev. assignment I had to deal with partitions quite a bit. Thankfully, on the tables I had to deal with, the parent and child records were on partitions corresponding to the same month. Your's is a tricky situation. I have an idea but I am not sure it is feasible for you. But here it is anyway. Good luck my friend Assumptions : I am assuming that there is a foreign key based on sesion_id from C -- A, and also from B -- A. Also assuming that data is partitioned by month. So if we need at least 90 days worth of days, say we have partitions for Jan, Feb, Mar, and April (3 full months + the current month. At the end of the current month we will actually have 120 days worth of data). On the last day of April, we will drop the partition for Jan and create one for May. Also assuming that if the child is less than 90 days old, but the parent is older than 90, the complete set (parent and child) need to be retained (and not deleted). Also assuming that you have a little window of down time when you will be doing this. Using the above example, Partition maintenance would involve. step 1. Drop the Jan partition from B step 2. Drop Jan partition from C Now the tricky part. J step 4. Any session_ids in the Jan partition of A that exists in the Feb, MAR or Apr partitions of B OR C, CANNOT be deleted 'cose they have children that are not old enough ( ?). Identify these records. (NOTE : Its been a while since Ive worked on this, and I dont remember if we can update the partition key (i.e, date).. something tells me that it is not possible, but I could be wrong , if it is possible, as an easier option we can update the date so that these records get moved to the Feb partition (on A), drop the Jan partition, and then reset them back to their old dates, and skip the remaining steps other than 8. ) Step 5. Temporarily Disable the foreign constraints from B -- A and from C -- A. Step 6. Exchange the oldest partition from A. ALTER TABLE A EXCHANGE PARTITION jan WITH TABLE exchange_table WITHOUT VALIDATION; Step 7. Drop the oldest partition. Step 8. Create a partition for May. Step 9. Copy records identified in step 4 from the exchange table back into A (this will now be in the Feb partition) Step 10. Re-enable the foreign keys from B-- A and from C-- A. You are disabling the constraints for a small window and that too during down time (I assume). I dont like having to disable constraints too, but this may be faster than delete cascades, involving Non indexed B and C. Sunny From: Erik Williams [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Partitioning Tables Date: Thu, 15 Nov 2001 11:00:54 -0800 I have a set of three tables that I am having trouble deleting from. The issues are caused by the size of the tables and the foreign keys. There are no indexes on B and C due to insertion speed requirements. The last time I emailed the group about this, partitioning was a popular response. I am now concidering and testing partitioning. I need to keep 90 days of data online, so partiioning by date seemed like the logical solution. So, I created a test environment and range partitioned by the date attributes. This worked great for tables B and C. I was able to export the oldest partitions and then drop them quickly. The problem was with the A table. I could not drop partitions becuase of the FK constraints. I would have to disable the constraints and this is not a great solution. I then thought about hash partitioning by session_id. Then when I deleted from A with ON DELETE CASCADE, scans (no indexes on B and C) would be limited to the size of the partitions. This also seems to be suboptimal, as the deletes are taking a very long time in my test environment. Does anyone have any experience on this sort of design that can provide some guidance? I am at a loss and hope that someone has done this sort of thing sucessfully and can point me in the right direction. Erik Table A - Session session_id (primary key) start_dtm (date) Table B - Session Event session_event (primary key) session_id (FK to session table) event_datetime (date) Table C - Session Quote session_quote (primary key) session_id (FK to session table) quote_datetime (date) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other
RE: Partitioning
Charlie, Thanks for the link - it did seem to suggest that since RAC is shown as an option but partition is ticked as included. However, the following (huge) link (to http://store.oracle.com and then click on the Enterprise Edition link) https://oraclestore.oracle.com/OA_HTML/ibeCCtpSctDspRte.jsp?section=11468db s03_crm1ap_ses=C2E211806309D901CF9035F0A570CD460FB11F71BBBD78BBDF6D2BC207F8A 49BBD34F109DE37002F561669846DC65CC239199C5096812D93623D3F99A186C4EB6CE4ED884 663B5567F2FAC898BEEA99B3909AD26B9D16E626B467AE0DF682ADF2703DA9C37DA5E50BD659 2A356E477DE2679A8E11EB100A1A929D502F65DD4B0943251BEAD2205B7A1D26AA6199DF99C7 A6D84FC2158911B9F5820E0F537E45D2042D38EB90291694A01A8E86609D1CF4C4066FDEEEF9 2F2B76E24BBDD9E81C671147B4D82326265D3AA5328AC046DA8388374FC21B7321260CADED17 D28D68596DE31863DE0A56BE6F54C64842CF4E18B7CC552EE447B19444BADEBFA50DCC2370B7 231FB1619EA59FAD899D31CB6EE42649C03099DFA2F53E8A7F634100A says: Oracle Database Enterprise Edition can be extended with the following options: Oracle Real Application Clusters Oracle Partitioning Oracle OLAP Oracle Data Mining Oracle Spatial Oracle Advanced Security Oracle Label Security Oracle Diagnostics Pack Oracle Tuning Pack Oracle Change Management Pack Oracle Management Pack for Oracle Applications Oracle Management Pack for SAP R3 Then using the quick search on product=database, partitioning shows the prices for the partitioning option: $10,000 per processor, or $200 per named user. So it seems that unfortunately partitioning is still an extra cost option :-( Regards, Bruce Reardon -Original Message- Sent: Thursday, 4 October 2001 3:00 IIRC, last week or so the question was raised about whether or not partitioning is an extra cost option. My reading of the URL below is that it appears that partitioning is included in 9i Standard. Plus, now I'm not sure exactly what the differences are between Standard Enterprise WRT to 9i. http://www.oracle.com/ip/deploy/database/oracle9i/index.html?sp_pkgsum.html HTH YMMV! -- Charlie MenglerMaintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 United WE Stand! Justice WILL Be Served! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
