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
Partitioning question (duplicate?)
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).
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).
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).
partitioning in an NAS or SAN environment
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).
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: Table partitioning Oracle 9.2
Hi! No, data blocks below partitions high-high water mark can never be used for another segment. Unformatted blocks above (high)-high water mark can be used for another segment only when you trim the extent(s) using alter table deallocate unused (but this feature is useful only if you have lots of unformatted space in your segment for some reason). So, if your business allow this table to be offline for a while, I'd recommend the following approach after you've archived your old data (some features here require 9i and syntax is written from memory, might have small errors in it): 1) archive your unneeded data 2) create table temp as select * from your_partitioned_tab partition P_1 where condition to filter out old unneeded data; (you can use nologging parallel here if you want) 3) alter table your_partitioned_tab truncate partition P_1 drop storage update global indexes; (can use parallel here as well) - this will truncate your old partition and release any extents above it's minextents) 4) alter table your_partitioned_tab exchange partition P_1 with table TEMP excluding indexes without validation; (just exchanges the TEMP table's segment storing only the required 5% of data with old, now truncated segment). 5) drop table TEMP; (the 5% of required data was moved back to your_partitioned_tab in step 4. 6) rebuild any local indexes on P_1 partition. 7) analyze partition P_1 and it's local indexes, possibly global indexes on your table as well (btw, you can use compute statistics option for gathering basic statistice when rebuilding indexes...) You can use nologging in evey operation mentioned above (with the exception of update global indexes in step 3). Of course in case of nologging operations, your backup strategy has to be aware of them. So, instead of generating lots of redo and undo+redo due huge deletes and index maintenance you just take this small amount of rows you need, insert them into a new segment using direct path and nologging (very little undo and redo), and then just exchange the segments between your old and new tables. Cheers, Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 31, 2003 10:24 AM --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426 Content-Type: text/plain; charset=us-ascii RDBMS Version: 9.2.0.1.0 Operating System and Version: Solaris 8 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Partitioned Table Product Version: 9.2.0.1.0 Table partitioning Hi, I've a query reg. space usage in context of partitioned tables. I've a table with 12 partitions P_1 ... P_12. Until now data got populated in P_1 upto P_6 and future data will come in P_7 etc. If i delete some huge amount of data from P_1 (after archiving it) will that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). Unfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? Data is partitioned by date. So, my query is whether Oracle will put future data (which belongs to partition P_7 etc.) in space earlier used by P_1. Any help from members is appreciated. Thanks, Vikas Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com Bid for Air Tickets on Air Sahara Flights. For Best Deals, log on to http://airsahara.indiatimes.com and Bid Now ! --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426 Content-Type: text/html; charset=us-ascii PSTRONG/STRONGnbsp;/P PSTRONGRDBMS Version/STRONG: 9.2.0.1.0BRBOperating System and Version/B: Solaris 8BRBError Number (if applicable)/B: BRBProduct (i.e. SQL*Loader, Import, etc.)/B: Partitioned TableBRBProduct Version/B: 9.2.0.1.0BRBRTable partitioningBRBRHi, BRBRI've a query reg. space usage in context of partitioned tables. BRBRI've a table with 12 partitions P_1 ... P_12. Until now data BRgot populated in P_1 upto P_6 and future data will come in P_7 etc. BRIf i delete some huge amount of data from P_1 (after archiving it) BRwill that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). BRUnfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? BRData is partitioned by date. So, my query is whether Oracle will put fu! tu! re data (which belongs to partition P_7 etc.) in space earlier used by P_1.BRBRAny help from members is appreciated. BRBRThanks, BRVikas BR/P hrfont face=Arial size=2bGet Your Private, Free E-mail from Indiatimes at /fonta href=http://email.indiatimes.com;font face=Arial size=2http://email.indiatimes.com
Table partitioning Oracle 9.2
RDBMS Version: 9.2.0.1.0 Operating System and Version: Solaris 8 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Partitioned Table Product Version: 9.2.0.1.0 Table partitioning Hi, I've a query reg. space usage in context of partitioned tables. I've a table with 12 partitions P_1 ... P_12. Until now data got populated in P_1 upto P_6 and future data will come in P_7 etc. If i delete some huge amount of data from P_1 (after archiving it) will that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). Unfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? Data is partitioned by date. So, my query is whether Oracle will put future data (which belongs to partition P_7 etc.) in space earlier used by P_1. Any help from members is appreciated. Thanks, Vikas Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com Bid for Air Tickets on Air Sahara Flights. For Best Deals, log on to http://airsahara.indiatimes.com and Bid Now ! --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426 Content-Type: text/html; charset=us-ascii PSTRONG/STRONGnbsp;/P PSTRONGRDBMS Version/STRONG: 9.2.0.1.0BRBOperating System and Version/B: Solaris 8BRBError Number (if applicable)/B: BRBProduct (i.e. SQL*Loader, Import, etc.)/B: Partitioned TableBRBProduct Version/B: 9.2.0.1.0BRBRTable partitioningBRBRHi, BRBRI've a query reg. space usage in context of partitioned tables. BRBRI've a table with 12 partitions P_1 ... P_12. Until now data BRgot populated in P_1 upto P_6 and future data will come in P_7 etc. BRIf i delete some huge amount of data from P_1 (after archiving it) BRwill that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). BRUnfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? BRData is partitioned by date. So, my query is whether Oracle will put fu! tu! re data (which belongs to partition P_7 etc.) in space earlier used by P_1.BRBRAny help from members is appreciated. BRBRThanks, BRVikas BR/P hrfont face=Arial size=2bGet Your Private, Free E-mail from Indiatimes at /fonta href=http://email.indiatimes.com;font face=Arial size=2http://email.indiatimes.com/a/bbrBuy The Best In bBOOKS/b at A href=http://www.bestsellers.indiatimes.com;http://www.bestsellers.indiatimes.com/AbrBid for bAir Tickets/b on Air Sahara Flights. For Best Deals, log on to a href=http://airsahara.indiatimes.com;http://airsahara.indiatimes.com/a and Bid Now !/font --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vikas S 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: Table partitioning Oracle 9.2
more to the point, are the partitions all in the same tablespace? --- John Weatherman [EMAIL PROTECTED] wrote: Vikas, Are you asking if deallocated extent space can be reused by the current partition or wether new dates will suddenly go into a partition that explicitly excludes them? John P Weatherman Oracle Database Administrator Replacements, Ltd. -Original Message- Sent: Friday, October 31, 2003 3:24 AM To: Multiple recipients of list ORACLE-L --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426 Content-Type: text/plain; charset=us-ascii RDBMS Version: 9.2.0.1.0 Operating System and Version: Solaris 8 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Partitioned Table Product Version: 9.2.0.1.0 Table partitioning Hi, I've a query reg. space usage in context of partitioned tables. I've a table with 12 partitions P_1 ... P_12. Until now data got populated in P_1 upto P_6 and future data will come in P_7 etc. If i delete some huge amount of data from P_1 (after archiving it) will that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). Unfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? Data is partitioned by date. So, my query is whether Oracle will put future data (which belongs to partition P_7 etc.) in space earlier used by P_1. Any help from members is appreciated. Thanks, Vikas Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com Bid for Air Tickets on Air Sahara Flights. For Best Deals, log on to http://airsahara.indiatimes.com and Bid Now ! --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426 Content-Type: text/html; charset=us-ascii PSTRONG/STRONGnbsp;/P PSTRONGRDBMS Version/STRONG: 9.2.0.1.0BRBOperating System and Version/B: Solaris 8BRBError Number (if applicable)/B: BRBProduct (i.e. SQL*Loader, Import, etc.)/B: Partitioned TableBRBProduct Version/B: 9.2.0.1.0BRBRTable partitioningBRBRHi, BRBRI've a query reg. space usage in context of partitioned tables. BRBRI've a table with 12 partitions P_1 ... P_12. Until now data BRgot populated in P_1 upto P_6 and future data will come in P_7 etc. BRIf i delete some huge amount of data from P_1 (after archiving it) BRwill that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). BRUnfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? BRData is partitioned by date. So, my query is whether Oracle will put fu! ! tu! re data (which belongs to partition P_7 etc.) in space earlier used by P_1.BRBRAny help from members is appreciated. BRBRThanks, BRVikas BR/P hrfont face=Arial size=2bGet Your Private, Free E-mail from Indiatimes at /fonta href=http://email.indiatimes.com;font face=Arial size=2http://email.indiatimes.com/a/bbrBuy The Best In bBOOKS/b at A href=http://www.bestsellers.indiatimes.com;http://www.bestsellers.indiatimes.com/AbrBid for bAir Tickets/b on Air Sahara Flights. For Best Deals, log on to a href=http://airsahara.indiatimes.com;http://airsahara.indiatimes.com/a and Bid Now !/font --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vikas S 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: John Weatherman 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: Table partitioning Oracle 9.2
Vikas, Are you asking if deallocated extent space can be reused by the current partition or wether new dates will suddenly go into a partition that explicitly excludes them? John P Weatherman Oracle Database Administrator Replacements, Ltd. -Original Message- Sent: Friday, October 31, 2003 3:24 AM To: Multiple recipients of list ORACLE-L --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426 Content-Type: text/plain; charset=us-ascii RDBMS Version: 9.2.0.1.0 Operating System and Version: Solaris 8 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Partitioned Table Product Version: 9.2.0.1.0 Table partitioning Hi, I've a query reg. space usage in context of partitioned tables. I've a table with 12 partitions P_1 ... P_12. Until now data got populated in P_1 upto P_6 and future data will come in P_7 etc. If i delete some huge amount of data from P_1 (after archiving it) will that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). Unfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? Data is partitioned by date. So, my query is whether Oracle will put future data (which belongs to partition P_7 etc.) in space earlier used by P_1. Any help from members is appreciated. Thanks, Vikas Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com Bid for Air Tickets on Air Sahara Flights. For Best Deals, log on to http://airsahara.indiatimes.com and Bid Now ! --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426 Content-Type: text/html; charset=us-ascii PSTRONG/STRONGnbsp;/P PSTRONGRDBMS Version/STRONG: 9.2.0.1.0BRBOperating System and Version/B: Solaris 8BRBError Number (if applicable)/B: BRBProduct (i.e. SQL*Loader, Import, etc.)/B: Partitioned TableBRBProduct Version/B: 9.2.0.1.0BRBRTable partitioningBRBRHi, BRBRI've a query reg. space usage in context of partitioned tables. BRBRI've a table with 12 partitions P_1 ... P_12. Until now data BRgot populated in P_1 upto P_6 and future data will come in P_7 etc. BRIf i delete some huge amount of data from P_1 (after archiving it) BRwill that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). BRUnfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? BRData is partitioned by date. So, my query is whether Oracle will put fu! ! tu! re data (which belongs to partition P_7 etc.) in space earlier used by P_1.BRBRAny help from members is appreciated. BRBRThanks, BRVikas BR/P hrfont face=Arial size=2bGet Your Private, Free E-mail from Indiatimes at /fonta href=http://email.indiatimes.com;font face=Arial size=2http://email.indiatimes.com/a/bbrBuy The Best In bBOOKS/b at A href=http://www.bestsellers.indiatimes.com;http://www.bestsellers.indiatimes.com/AbrBid for bAir Tickets/b on Air Sahara Flights. For Best Deals, log on to a href=http://airsahara.indiatimes.com;http://airsahara.indiatimes.com/a and Bid Now !/font --=_MAILER_ATTACH_BOUNDARY1_2003103151337181540383426-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vikas S 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: John Weatherman 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: Table partitioning Oracle 9.2
Is the table hash-partitioned, range-partitioned, or list-partitioned? -Original Message- Vikas S RDBMS Version: 9.2.0.1.0 Operating System and Version: Solaris 8 Error Number (if applicable): Product (i.e. SQL*Loader, Import, etc.): Partitioned Table Product Version: 9.2.0.1.0 Table partitioning I've a query reg. space usage in context of partitioned tables. I've a table with 12 partitions P_1 ... P_12. Until now data got populated in P_1 upto P_6 and future data will come in P_7 etc. If i delete some huge amount of data from P_1 (after archiving it) will that freed space be used by future inserts (which happens in subsequent partitions like P_7 etc). Unfortunately, we can't delete all data in partition. We have to keep some data which account say 5% of total data. ie, we're deleting 95% of data from a partition. So, will this freed blocks be put to free list and used by future inserts? Data is partitioned by date. So, my query is whether Oracle will put future data (which belongs to partition P_7 etc.) in space earlier used by P_1. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Fwd: RE: RAC for download -- re RAC Pricing and Partitioning
They are indeed, and their prices are (in the order you list them) in addition to the EE price: $20K, $20K, $10K, $10K, $10K. Mogens Hemant K Chitale wrote: oops, I forgot to mention Partitioning pricing. Partitioning is also listed seperately under Enterprise Edition options. This is 25% of the EE price. Thus, EE is US$40K per CPU. RAC is US$60K per CPU [40K + 20K]. Partitioning is US$50K per CPU [40K + 10K] and RAC with Partitioning would be US$70K per CPU ! Data Mining, OLAP, Advanced Security, Spatial and Label Security are also seperately priced options. Hemant Date: Thu, 25 Sep 2003 22:46:40 +0800 To: [EMAIL PROTECTED] From: Hemant K Chitale [EMAIL PROTECTED] Subject: RE: RAC for download -- re RAC Pricing Check oraclestore.com. The default page just shows you the pricing for the DB EE, true. However, when you click on Database under Products in the left panel, you can see Oracle Enterprise Edition Options listed seperately from Oracle Database. RAC is under Enterprise Edition Options while EE is under Database and the RAC price is 50% of the EE price. Thus, an RAC price is 150% of an EE price. Hemant At 11:44 AM 24-09-03 -0800, you wrote: My dear friend, you're wrong. That practice has stopped with 8i. Partitioning option *is* an integral part of 9iEE without an additional check to sign. I got a verbal confirmation from my oracle sales rep and I'll try getting a written (email) one as well. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mogens Nørgaard Sent: Wednesday, September 24, 2003 2:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: RAC for download I've seen the same kind of confusion with respect to the partitioning option, where people have been informed by their sales rep that partitioning option is part of EE. Well, yes, if you pay extra for it. Mogens Hemant K Chitale wrote: If the question is about price [referring to oraclestore], remember that RAC is an option and is generally at a 50% premium on the EE cost. However, Mladen is right in that RAC is on the same CDs as the Enterprise Edition. If your servers are cluster-ready, the OUI automatically includes RAC as an installation option, else, RAC does not apear in the Oracle product list when you run the Installer. Hemant At 06:54 AM 24-09-03 -0800, you wrote: RAC is a part of the EE version, for whichever OS you have. You will still need to purchase the hardware. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of quriyat Sent: Wednesday, September 24, 2003 10:05 AM To: Multiple recipients of list ORACLE-L Subject: RAC for download Hello all Where can i get RAC for download? I don't see one in OTN. Oracle store puts a high tag? Thanks -- -- No banners. No pop-ups. No kidding. Introducing My Way - http://www.myway.com 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. Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com http://hkchital.tripod.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network
RE: Fwd: RE: RAC for download -- re RAC Pricing and Partitioning
One of the reasons I nearly baarfed (sorry) coffee all over my keyboard when I saw the larryism from Oracle world on IBM's capacity on demand ' yes it is capacity on demand but it costs 45k a processor' pots,kettles,black the whole thing really. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mogens Nørgaard Sent: 26 September 2003 12:05 To: Multiple recipients of list ORACLE-L Subject: Re: Fwd: RE: RAC for download -- re RAC Pricing and Partitioning They are indeed, and their prices are (in the order you list them) in addition to the EE price: $20K, $20K, $10K, $10K, $10K. Mogens Hemant K Chitale wrote: oops, I forgot to mention Partitioning pricing. Partitioning is also listed seperately under Enterprise Edition options. This is 25% of the EE price. Thus, EE is US$40K per CPU. RAC is US$60K per CPU [40K + 20K]. Partitioning is US$50K per CPU [40K + 10K] and RAC with Partitioning would be US$70K per CPU ! Data Mining, OLAP, Advanced Security, Spatial and Label Security are also seperately priced options. Hemant Date: Thu, 25 Sep 2003 22:46:40 +0800 To: [EMAIL PROTECTED] From: Hemant K Chitale [EMAIL PROTECTED] Subject: RE: RAC for download -- re RAC Pricing Check oraclestore.com. The default page just shows you the pricing for the DB EE, true. However, when you click on Database under Products in the left panel, you can see Oracle Enterprise Edition Options listed seperately from Oracle Database. RAC is under Enterprise Edition Options while EE is under Database and the RAC price is 50% of the EE price. Thus, an RAC price is 150% of an EE price. Hemant At 11:44 AM 24-09-03 -0800, you wrote: My dear friend, you're wrong. That practice has stopped with 8i. Partitioning option *is* an integral part of 9iEE without an additional check to sign. I got a verbal confirmation from my oracle sales rep and I'll try getting a written (email) one as well. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mogens Nørgaard Sent: Wednesday, September 24, 2003 2:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: RAC for download I've seen the same kind of confusion with respect to the partitioning option, where people have been informed by their sales rep that partitioning option is part of EE. Well, yes, if you pay extra for it. Mogens Hemant K Chitale wrote: If the question is about price [referring to oraclestore], remember that RAC is an option and is generally at a 50% premium on the EE cost. However, Mladen is right in that RAC is on the same CDs as the Enterprise Edition. If your servers are cluster-ready, the OUI automatically includes RAC as an installation option, else, RAC does not apear in the Oracle product list when you run the Installer. Hemant At 06:54 AM 24-09-03 -0800, you wrote: RAC is a part of the EE version, for whichever OS you have. You will still need to purchase the hardware. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of quriyat Sent: Wednesday, September 24, 2003 10:05 AM To: Multiple recipients of list ORACLE-L Subject: RAC for download Hello all Where can i get RAC for download? I don't see one in OTN. Oracle store puts a high tag? Thanks -- -- No banners. No pop-ups. No kidding. Introducing My Way - http://www.myway.com 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. Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com http
Fwd: RE: RAC for download -- re RAC Pricing and Partitioning
oops, I forgot to mention Partitioning pricing. Partitioning is also listed seperately under Enterprise Edition options. This is 25% of the EE price. Thus, EE is US$40K per CPU. RAC is US$60K per CPU [40K + 20K]. Partitioning is US$50K per CPU [40K + 10K] and RAC with Partitioning would be US$70K per CPU ! Data Mining, OLAP, Advanced Security, Spatial and Label Security are also seperately priced options. Hemant Date: Thu, 25 Sep 2003 22:46:40 +0800 To: [EMAIL PROTECTED] From: Hemant K Chitale [EMAIL PROTECTED] Subject: RE: RAC for download -- re RAC Pricing Check oraclestore.com. The default page just shows you the pricing for the DB EE, true. However, when you click on Database under Products in the left panel, you can see Oracle Enterprise Edition Options listed seperately from Oracle Database. RAC is under Enterprise Edition Options while EE is under Database and the RAC price is 50% of the EE price. Thus, an RAC price is 150% of an EE price. Hemant At 11:44 AM 24-09-03 -0800, you wrote: My dear friend, you're wrong. That practice has stopped with 8i. Partitioning option *is* an integral part of 9iEE without an additional check to sign. I got a verbal confirmation from my oracle sales rep and I'll try getting a written (email) one as well. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mogens Nørgaard Sent: Wednesday, September 24, 2003 2:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: RAC for download I've seen the same kind of confusion with respect to the partitioning option, where people have been informed by their sales rep that partitioning option is part of EE. Well, yes, if you pay extra for it. Mogens Hemant K Chitale wrote: If the question is about price [referring to oraclestore], remember that RAC is an option and is generally at a 50% premium on the EE cost. However, Mladen is right in that RAC is on the same CDs as the Enterprise Edition. If your servers are cluster-ready, the OUI automatically includes RAC as an installation option, else, RAC does not apear in the Oracle product list when you run the Installer. Hemant At 06:54 AM 24-09-03 -0800, you wrote: RAC is a part of the EE version, for whichever OS you have. You will still need to purchase the hardware. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of quriyat Sent: Wednesday, September 24, 2003 10:05 AM To: Multiple recipients of list ORACLE-L Subject: RAC for download Hello all Where can i get RAC for download? I don't see one in OTN. Oracle store puts a high tag? Thanks -- -- No banners. No pop-ups. No kidding. Introducing My Way - http://www.myway.com 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. Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com http://hkchital.tripod.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: RE: RAC for download -- re RAC Pricing and Partitioning
Of course, this pricing bears no relation to actual reality. 70% discounts off list, especially on the addons like RAC, are not uncommon. You just have to push a bit. :) Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Hemant K Chitale Sent: Thursday, September 25, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: Fwd: RE: RAC for download -- re RAC Pricing and Partitioning oops, I forgot to mention Partitioning pricing. Partitioning is also listed seperately under Enterprise Edition options. This is 25% of the EE price. Thus, EE is US$40K per CPU. RAC is US$60K per CPU [40K + 20K]. Partitioning is US$50K per CPU [40K + 10K] and RAC with Partitioning would be US$70K per CPU ! Data Mining, OLAP, Advanced Security, Spatial and Label Security are also seperately priced options. Hemant Date: Thu, 25 Sep 2003 22:46:40 +0800 To: [EMAIL PROTECTED] From: Hemant K Chitale [EMAIL PROTECTED] Subject: RE: RAC for download -- re RAC Pricing Check oraclestore.com. The default page just shows you the pricing for the DB EE, true. However, when you click on Database under Products in the left panel, you can see Oracle Enterprise Edition Options listed seperately from Oracle Database. RAC is under Enterprise Edition Options while EE is under Database and the RAC price is 50% of the EE price. Thus, an RAC price is 150% of an EE price. Hemant At 11:44 AM 24-09-03 -0800, you wrote: My dear friend, you're wrong. That practice has stopped with 8i. Partitioning option *is* an integral part of 9iEE without an additional check to sign. I got a verbal confirmation from my oracle sales rep and I'll try getting a written (email) one as well. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mogens Nørgaard Sent: Wednesday, September 24, 2003 2:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: RAC for download I've seen the same kind of confusion with respect to the partitioning option, where people have been informed by their sales rep that partitioning option is part of EE. Well, yes, if you pay extra for it. Mogens Hemant K Chitale wrote: If the question is about price [referring to oraclestore], remember that RAC is an option and is generally at a 50% premium on the EE cost. However, Mladen is right in that RAC is on the same CDs as the Enterprise Edition. If your servers are cluster-ready, the OUI automatically includes RAC as an installation option, else, RAC does not apear in the Oracle product list when you run the Installer. Hemant At 06:54 AM 24-09-03 -0800, you wrote: RAC is a part of the EE version, for whichever OS you have. You will still need to purchase the hardware. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of quriyat Sent: Wednesday, September 24, 2003 10:05 AM To: Multiple recipients of list ORACLE-L Subject: RAC for download Hello all Where can i get RAC for download? I don't see one in OTN. Oracle store puts a high tag? Thanks -- -- No banners. No pop-ups. No kidding. Introducing My Way - http://www.myway.com 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. Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com http://hkchital.tripod.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: [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).
Partitioning
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).
Help Needed regarding partitioning
Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj Blank Bkgrd.gif
RE: Help Needed regarding partitioning
Hi, I'm currently looking into the same. My idea is to create a new table with the partitions I need, insert the data from the old table, drop the old table and rename the new table to the old name. This will invalidate a lot of stuff, which I still have to find out exactly what, so if somebody has a script to find all the dependencies already.? Anybody have a different/better idea, because the insert may take a long time? Jack -Original Message-From: Munish Bajaj [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 11:54 AMTo: Multiple recipients of list ORACLE-LSubject: Help Needed regarding partitioning Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj
RE: Help Needed regarding partitioning
Jack, Munish First, verify that you have licensed partitioning. Additional cost item. Second, learn about EXCHANGE PARTITION. This is a highly useful command that can be used for a multitude of uses. Create a partitioned table and then exchange your table into it. Extremely fast since it just involves dictionary manipulations. It can be used for other purposes like renaming columns, changing the owner of a table, etc. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 6:15 AM To: Multiple recipients of list ORACLE-L Hi, I'm currently looking into the same. My idea is to create a new table with the partitions I need, insert the data from the old table, drop the old table and rename the new table to the old name. This will invalidate a lot of stuff, which I still have to find out exactly what, so if somebody has a script to find all the dependencies already.? Anybody have a different/better idea, because the insert may take a long time? Jack -Original Message- Sent: Wednesday, June 25, 2003 11:54 AM To: Multiple recipients of list ORACLE-L Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj -- 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: Help Needed regarding partitioning
Munish, innocent question, _why_ do you think partitioning will help query performance? Have you checked asktom site? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Munish Bajaj [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 11:54 AMTo: Multiple recipients of list ORACLE-LSubject: Help Needed regarding partitioning Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Help Needed regarding partitioning
Read about exchanging partitions in chapter 17 and especially Converting a Partition View into a Partitioned Table : http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/toc.htm Even though you may not be using a partitioned view, you can use this technique to create the partitioned table. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 06/25/03 05:54AM Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj **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: Help Needed regarding partitioning
That is the greatest RTFM answer that I've ever seen! Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 9:33 AM To: Multiple recipients of list ORACLE-L Read about exchanging partitions in chapter 17 and especially Converting a Partition View into a Partitioned Table : http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/toc.htm Even though you may not be using a partitioned view, you can use this technique to create the partitioned table. Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 06/25/03 05:54AM Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj **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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- 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: Help Needed regarding partitioning
You first create an empty partitioned table with the same structure as the original table and then use "exchange partition" to exchange partitions with the original table. That way, your original table will end up having a single (empy) partition and the new table will have a full partiton. Now, split the partitions in the new table, rename the old one, rebuild the indexes, recreate grants and presto, you're ready to go. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message-From: Munish Bajaj [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 5:54 AMTo: Multiple recipients of list ORACLE-LSubject: Help Needed regarding partitioning Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj
RE: Help Needed regarding partitioning
Thanks for mentioning it Just tried a little test and worked like a charm. alter table part2 exchange partition JUNE2003 with table test_part1 Jacob A. van Zanen -Original Message- Sent: Wednesday, June 25, 2003 4:14 PM To: Multiple recipients of list ORACLE-L Jack, Munish First, verify that you have licensed partitioning. Additional cost item. Second, learn about EXCHANGE PARTITION. This is a highly useful command that can be used for a multitude of uses. Create a partitioned table and then exchange your table into it. Extremely fast since it just involves dictionary manipulations. It can be used for other purposes like renaming columns, changing the owner of a table, etc. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 6:15 AM To: Multiple recipients of list ORACLE-L Hi, I'm currently looking into the same. My idea is to create a new table with the partitions I need, insert the data from the old table, drop the old table and rename the new table to the old name. This will invalidate a lot of stuff, which I still have to find out exactly what, so if somebody has a script to find all the dependencies already.? Anybody have a different/better idea, because the insert may take a long time? Jack -Original Message- Sent: Wednesday, June 25, 2003 11:54 AM To: Multiple recipients of list ORACLE-L Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj -- 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: Jack van Zanen 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[2]:partitioning
Peter, I've not had very good luck with hash partitioning either, but range works damn nicely both from a data insertion/update and query point of view. CBO also runs very consistently. Dick Goulet Reply Separator Author: Peter Barnett [EMAIL PROTECTED] Date: 3/20/2003 6:23 AM As we have discovered, hash partitioning does not always give you partition elimination nor does CBO always work as expected. One of our DBAs has a tar open with Oracle on this issue and we are finding that no one appears to understand the complete ramifications of using hash partitions when a query is run against large data sets. In short, if you only care about getting data into a partition use hash partitioning. If some day you want to get data out you may want to look at another solution. Range partitioning might work depending on your data. Disk striping might work but consistently getting the right data to the right stipe is tricky business. --- [EMAIL PROTECTED] wrote: I'm not sure what your trying to accomplish. You get one insert for one record. Which partition is used depends on how you set them up. If your wanting the partitions to be used in a circular fashion I believe that is accomplished with a hash partition and you get to create the hashing method. Dick Goulet Reply Separator Author: Basavaraja; Ravindra [EMAIL PROTECTED] Date: 3/19/2003 11:00 AM 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: 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). = Pete Barnett Lead Database Administrator The Regence Group [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Barnett 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: 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
partitioning
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).
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: Is range partitioning possible on part of varchar2 column ???
This seems to be a good idea. I will see if this is acceptable to my people.
Earliar I suggested to change to date field, and was not acceptable for them
as there seems plenty of code needs to be changed. I will see if this change
is acceptable for them.
One thing I could understand clearly from the LIST MEMBERS is that it is not
at all possible to range partition without changing the column
type/contents. I have two options now, one with what you suggested.
Thanks for your help and thanks for all those who replied.
-- Babu
-Original Message-
Sent: Thursday, March 13, 2003 8:34 PM
To: Multiple recipients of list ORACLE-L
???
Babu,
On a slightly different approach, is it possible to update the column to the
format MON, from the present MON? If so, then there is hope. You
could create the partitions like this
PARTITIONING BY RANGE (REPORT_CYCLE_CD)
(
PARTITION P1998 VALUES LESS THAN ('1999%'),
PARTITION P1999 VALUES LESS THAN ('2000%'),
PARTITION P2000 VALUES LESS THAN ('2001%'),
.
PARTITION PMAX VALUES LESS THAN (maxvalue)
)
Hope this helps.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 5:19 PM
Babu
I don't think partitions are clearly documented anywhere. Here is some SQL
that works so you can see how to use a date function. It partitions on two
columns, but I wanted you to see something that works.
add partition sum_fy_28
values less than ('FY', to_date('02012003','mmdd'))
tablespace data_fy_28
-Original Message-
Sent: Thursday, March 13, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
??
Dear List,
I have a table of size approx 10gig, and I need to partition based on the
YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
data in the column of format MON . I need to partition the table
based
on the year , that is, substr(report_cycle_cd, 4,4).
Substr function doesn't seem to be permitted in the partitioning syntax
and
so am getting errors. Only TO_DATE function seems to be permitted. Since
it
is not a date column, I would like to know if there is a way to RANGE
partition the table, instead of HASH partitioning.
Appreciate any suggestions.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
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: Arup Nanda
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: Janardhana Babu Donga
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).
Is range partitioning possible on part of varchar2 column ??????
Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: Is range partitioning possible on part of varchar2 column ???
Babu
I don't think partitions are clearly documented anywhere. Here is some SQL
that works so you can see how to use a date function. It partitions on two
columns, but I wanted you to see something that works.
add partition sum_fy_28
values less than ('FY', to_date('02012003','mmdd'))
tablespace data_fy_28
-Original Message-
Sent: Thursday, March 13, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
??
Dear List,
I have a table of size approx 10gig, and I need to partition based on the
YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
data in the column of format MON . I need to partition the table based
on the year , that is, substr(report_cycle_cd, 4,4).
Substr function doesn't seem to be permitted in the partitioning syntax and
so am getting errors. Only TO_DATE function seems to be permitted. Since it
is not a date column, I would like to know if there is a way to RANGE
partition the table, instead of HASH partitioning.
Appreciate any suggestions.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
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: Is range partitioning possible on part of varchar2 column ??????
No, I think you will have to add a column to store '' separately in order to partition on it. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:14 PM Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: 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: Is range partitioning possible on part of varchar2 column ???
Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: Is range partitioning possible on part of varchar2 column ???
Is list partitioning available in 8i? Iam on 8.1.7.4. -- Babu -Original Message- Sent: Thursday, March 13, 2003 1:49 PM To: Multiple recipients of list ORACLE-L ??? Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: Is range partitioning possible on part of varchar2 column ???
No -Original Message- Sent: Thursday, March 13, 2003 5:49 PM To: Multiple recipients of list ORACLE-L ??? Is list partitioning available in 8i? Iam on 8.1.7.4. -- Babu -Original Message- Sent: Thursday, March 13, 2003 1:49 PM To: Multiple recipients of list ORACLE-L ??? Easy to do if it was 'MON' Oracle 9i has list partitioning that may work for you. Regards, Waleed -Original Message- Sent: Thursday, March 13, 2003 4:14 PM To: Multiple recipients of list ORACLE-L ?? Dear List, I have a table of size approx 10gig, and I need to partition based on the YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The data in the column of format MON . I need to partition the table based on the year , that is, substr(report_cycle_cd, 4,4). Substr function doesn't seem to be permitted in the partitioning syntax and so am getting errors. Only TO_DATE function seems to be permitted. Since it is not a date column, I would like to know if there is a way to RANGE partition the table, instead of HASH partitioning. Appreciate any suggestions. Thanks, -- Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janardhana Babu Donga 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: Is range partitioning possible on part of varchar2 column ???
Babu,
On a slightly different approach, is it possible to update the column to the
format MON, from the present MON? If so, then there is hope. You
could create the partitions like this
PARTITIONING BY RANGE (REPORT_CYCLE_CD)
(
PARTITION P1998 VALUES LESS THAN ('1999%'),
PARTITION P1999 VALUES LESS THAN ('2000%'),
PARTITION P2000 VALUES LESS THAN ('2001%'),
.
PARTITION PMAX VALUES LESS THAN (maxvalue)
)
Hope this helps.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, March 13, 2003 5:19 PM
Babu
I don't think partitions are clearly documented anywhere. Here is some SQL
that works so you can see how to use a date function. It partitions on two
columns, but I wanted you to see something that works.
add partition sum_fy_28
values less than ('FY', to_date('02012003','mmdd'))
tablespace data_fy_28
-Original Message-
Sent: Thursday, March 13, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
??
Dear List,
I have a table of size approx 10gig, and I need to partition based on the
YEAR. I have column in the table, REPORT_CYCLE_CD with VARCHAR2(7). The
data in the column of format MON . I need to partition the table
based
on the year , that is, substr(report_cycle_cd, 4,4).
Substr function doesn't seem to be permitted in the partitioning syntax
and
so am getting errors. Only TO_DATE function seems to be permitted. Since
it
is not a date column, I would like to know if there is a way to RANGE
partition the table, instead of HASH partitioning.
Appreciate any suggestions.
Thanks,
-- Babu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Janardhana Babu Donga
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: Arup Nanda
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 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
Partitioning Storage Parameters
Team, Our tables are partitioned by 52 weeks. We would like to know the name of the partitions that are growing/increasing. Are there storage parameters for Partitions in Oracle 7, 8i, 9i ? Where can I find supporting documentation? Or. Where can I find excellent documentation about partition ? Thanks for you help 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).
Partitioning
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).
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
Table Partitioning in a Hybrid-OLTP System
Ive been reading the 9iR2 docs on partitioning, along with Tom Kytes excellent chapter on the subject. It seems that a Global index or unique, local indexes could be used effectively in an OLTP system, but both have their caveats. This is a hybrid system- its part OLTP but is also used quite a bit for reporting. Would anyone out there care to share your good or bad experiences with these indexes on partitioned tables in such a system? Best regards, David B. Wagoner Database Administrator Arsenal Digital Solutions Worldwide, Inc. 8000 Regency Parkway, Suite 100 Cary, NC 27511-8582 Office (919) 466-6723 Mobile (919) 412-8462 Pager [EMAIL PROTECTED] Fax (919) 466-6783 AIM adswDWagoner http://www.arsenaldigital.com/ *** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender by phone or email and delete this e-mail message from your computer. Thank you.
partitioning star schema
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).
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
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).
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).
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).
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).
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).
Limitations of table partitioning.
Hello What are the limitations of partitioning a table in Oracle. Regards, Deepa -- 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: Limitations of table partitioning.
Can you be more precise. --- [EMAIL PROTECTED] a écrit : Hello What are the limitations of partitioning a table in Oracle. Regards, Deepa -- 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). = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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: Limitations of table partitioning.
I recommend reading Jonothan Lewis book titled 'Practical Oracle 8i' There is a chapter on partitioning. I was reading it last night. Very useful. Govind -Original Message- [mailto:PK_Deepa/VGIL;vguard.satyam.net.in] Sent: Wednesday, October 30, 2002 3:43 AM To: Multiple recipients of list ORACLE-L Hello What are the limitations of partitioning a table in Oracle. Regards, Deepa -- 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: Limitations of table partitioning.
merging partitions back will be pain in *** . - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 30, 2002 12:43 AM Hello What are the limitations of partitioning a table in Oracle. Regards, Deepa -- 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: BigP 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).
Index Partitioning
Title: Adhoc queries and limiting the amount of records queried... Hi all System is Siebel Ok I got a couple of tables about 6 GB big, up to 10 million rows. Some of the tables come out of the box with 30+ indexes, now for those not aware Siebel does not support the dropping of any indexes. I do though know what my indexes is that are hit the most and was thinking of partitioning them and or maybe the tables. Firstly If I was to partition only the table, Would I have to make any changes to the currently indexes other than rebuilding them. Second. Is it possible for to only partition a selected index. Here I keep on seeing local and global partitions - indexes. From what I can determine Global is bad news. How do I do local, what consideration are there. Some of the tables/indexes considered is orders, orderliness, shipments, shipments lines, all with well over 5 million records each. The queries is not date specific but more account or contact specific for the order if that's helps. I was considering partitioning on order_id but again it looks best to use hash partitioning since there is not real way of saying the queries will always go this way. Basically trying to reduce the work for Oracle to get to data. All my queries is already using the best possible index. Comment, suggestions thx George George Leonard Oracle Database Administrator Dimension Data (Pty) Ltd (Reg. No. 1987/006597/07) Tel:(+27 11) 575 0573 Fax:(+27 11) 576 0573 E-mail:[EMAIL PROTECTED] Web: http://www.didata.co.za You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
partitioning
Title: Message Hi all, Does anybody know with what version of Oracle partitioning was included at no extra cost? Thanks! - Jerry
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
Partitioning Quandry
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).
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: Another partitioning question
If your requirement is very strictly limited, so that
a) you want to have the flag column ('X') as the first
column of the partition
b) the flag is strictly a single character
c) you are using a character set where 'W' sorts immediately before
'X'
then the best you can do to meet the requirements
you describe has two partitions for the not-X values
and as many as you like for the X values using:
partition Pre_X values less than ('W',maxvalue),
partition PX_100 values less than ('X', 100),
partition PX_200 values less than ('X', 200),
...
partition PX_max values less than ('X', maxvalue),
partition post_X values less than (maxvalue, maxvalue)
There are several drawbacks to using this strategy
though, and I would start by questioning what you
hopes to achieve through partitioning and investigate
whether this notionally correct solution is going to
do what he hopes it would anyway.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
-Original Message-
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: 15 March 2002 17:32
|I think what my boss is asking me to do is not possible, but since I
don't
|have much experience with partitioning I thought I'd ask here (I did
read
|some of manuals but didn't find an answer that suited my conditions).
My
|boss wants a table partitioned by 2 columns - seq_no and type. If the
type =
|'X' then it's just a range partition, but then he wants another
partition
|that contains all data that type!='X' but is inclusive of the entire
range.
|Is this possible?
|Something like (I know this syntax isn't correct )
|create table test_part(
|id number(11) unique,
|owner_id number(11) not null,
|type varchar2(30) not null,
|name varchar2(40))
|partition by range(owner_id,type)
|(partition p1 values less than (2000) and type ='X' tablespace
test,
|partition p2 values less than (5000) and owner_table ='X'
tablespace
|test,
|partition p3 values less than (1) and owner_table ='X'
tablespace
|test,
|partition p4 values less than (5) and owner_table ='X'
tablespace
|test,
|partition p5 values less than (10) and owner_table ='X'
tablespace
|test)
|partition p6 values less that (10) and owner_table !='X'
tablespace
|test;
|--
|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: Jonathan Lewis
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: Another partitioning question part 2
Hello John
Since all values starting with 'V' are lower then 'W' they will be inserted
into partition p1. (tested it).
I did some tests but could not find a way to do what you want.
Maybe a composite partitioning with 4 hash subpartitions
will get you some results.
Yechiel Adar, Mehish Computer Services
[EMAIL PROTECTED]
-Original Message-
From: Shaw John-P55297 [SMTP:[EMAIL PROTECTED]]
Sent: Mon, March 18, 2002 5:41 PM
To: Multiple recipients of list ORACLE-L
Subject: Another partitioning question part 2
I tried the partition by range feature and didn't get the results I
expected. It seemed to ignore the second key when inserting data.
create table test_part(
id number(11) ,
owner_id number(11) not null,
owner varchar2(30) not null,
street varchar2(40))
partition by range(owner,owner_id )
(
partition p5 values less than ('V',99) tablespace test,
partition p1 values less than ('W',2000) tablespace test,
partition p2 values less than ('W',5000) tablespace test,
partition p3 values less than ('W',1) tablespace test,
partition p4 values less than ('W',5) tablespace test,
partition p6 values less than ('W',9) tablespace test,
partition p7 values less than (maxvalue,9) tablespace test
);
What I am trying to accomplish is to get all values less than 'V' into one
partion , all values = 'V' into 5 partitions by numeric range , and all
owner values greater that 'V' into the last partition.
It is sorting correctly one the first value 'owner' but is ignoring the
second range value when inserting records into the table, all owner_id
reanges are just going into the first partition. Anybody tell me what I
did
wrong - or maybe the book and chapter.
8.1.6 on NT
-Original Message-
Sent: Friday, March 15, 2002 2:48 PM
To: Multiple recipients of list ORACLE-L
Kirti - If I can humbly beg to differ. I assumed that it would work the
way
you described. However, I conducted some tests and found to my surprise
that
it seems to base decisions on the right-most column (although I only
tested
two columns). Therefore, to use your examples,
P1 -- values less than ('X', 99) will contain values where column1 =
'X'
and column2 less than 99
P2 -- values less than ('Y', 99) will contain values where column1 =
'Y'
and column2 less than 99.
Since my column1 only has two values, I didn't test what it does with
unlimited in the first column.
I'm not saying that didn't miss something. My ulterior motive for
responding on the list is that I frequently get some fuzzy ideas clarified
by others on the list.
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, March 15, 2002 1:33 PM
To: Multiple recipients of list ORACLE-L
If I understood the original question correctly, with the given conditions
there will be three partitions. Oracle evaluates concatenated key from
left
to right order, so if the type and seq number are the partitioning columns
here then the partitions would be:
P1 -- values less than ('X', 99) This will contain everything where
type
X
P2 -- values less then ('Y', 99) This will contain everything where
type
= X
P3 -- values less then (MAXVALUE, MAXVALUE) This will contain everything
else. I am using 99 to denote the highest value for the seq number.
Any other ideas?
- Kirti
-Original Message-
Sent: Friday, March 15, 2002 11:28 AM
To: Multiple recipients of list ORACLE-L
John - At last a question I can answer! Anyway I think so.
You can partition on a concatenated key. I just did this on our data
warehouse and brought query times from over 2 minutes to under 10 seconds.
Here is what my partition looks like.
create table sumacctfact2
nologging
pctfree 5
partition by range ( periodgrain, periodenddate )
(
partition sum_fy_01 values less than ('FY',
to_date('01011999','mmdd'))
tablespace data_fy_01
storage ( maxextents unlimited ),
partition sum_fy_02 values less than ('FY',
to_date('01012000','mmdd'))
tablespace data_fy_02
storage ( maxextents unlimited ),
partition sum_fy_03 values less than ('FY',
to_date('01012001','mmdd'))
tablespace data_fy_03
storage ( maxextents unlimited ),
partition sum_fy_04 values less than ('FY',
to_date('02012001','mmdd'))
tablespace data_fy_04
storage ( maxextents unlimited ),
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, March 15, 2002 10:38 AM
To: Multiple recipients of list ORACLE-L
I think what my boss is asking me to do is not possible, but since I don't
have much experience with partitioning I thought I'd ask here (I did read
some of manuals but didn't find an answer that suited my conditions). My
boss wants a table partitioned by 2 columns - seq_no and type. If the type
=
'X' then it's just a range
