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
My Views 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 join the 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 to partition the data as mentioned in #3, then for the benefit of your queries you may want the exclusive global index 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 >--
RE: partitioning questions
As of when? It's still listed as a "costly" option on the "Oracle Store" web page. The perpetual license is $10,000.00 per CPU for the U.S. market. Oracle 9i comes with lots of "options" many of which cost extra. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, November 22, 2002 8:09 AM To: Multiple recipients of list ORACLE-L That was not a good buy. Partitioning comes with Oracle 9, partitioning option is no longer sold separately. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]] > Sent: Friday, November 22, 2002 8:44 AM > To: Multiple recipients of list ORACLE-L > Subject: partitioning questions > > > > Hello, > > We are planning to move to Oracle 9.2 on as-yet-undecided platform > (probably red hat linux on ibm hardware). > > We finally pursuaded management to purchase the partitioning license, > and I have some questions on partitioning: > > Scenario: > Range-Partition tableA on a service_date field by year; > Range-Partition tableB on a posted_date field by year; These tables > are frequently joined using a separate field called charge_id, a > surrogate key. > > Queries against these tables usually include some sort of date filter, > join on the charge_id field, and are done in parallel. > > 1) Would this configuration promote the use of partition-wise >joins between tableA and tableB by the optimizer? > 2) Would it be better to partition the tables (either range or hash) >by the join field, charge_id? > 3) If we range-partition by date, subpartition by hash (charge_id), >would queries that do not reference the date field, but do join >the tables by charge_id still benefit? > 4) Is it more expensive, less expensive, or about equal to do a >full table scan on a partitioned table vs the same table > non-partitioned? > > As always, thanks to any responders. [EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L (or the > name of mailing list you want to be removed from). You may also send > the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: partitioning questions
Actually, even without the date field, queries will still benefit from the partition-wise join on the charge_id column. You would see something like this (partition hash all) in the plan: SELECT STATEMENT CHOOSE (Cost=178026) PARTITION HASH ALL 1:4:1 HASH JOIN PARTITION RANGE ALL 1:13:3 TABLE ACCESS FULL TAB_5 * 1:52:3 PARTITION RANGE ALL 1:13:5 TABLE ACCESS FULL TAB_6 * 1:52:5 >>> [EMAIL PROTECTED] 11/23/02 03:19a.m. >>> > 3) If we range-partition by date, subpartition by hash (charge_id), >would queries that do not reference the date field, but do join >the tables by charge_id still benefit? No for the same reason as above. It would be also interesting to check whether you should rather have a LOCAL or GLOBAL index on charge_id in this case. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Binley Lim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Partitioning Questions
Harvinder... What is the access patterns of the queries that will be using these tables... Knowing how the data will be accessed is am important factor in determining how to set this stuff up... i.e. If your data is historical in nature and the queries typically access data for via time periods, then a range partition by date is an excellent choice... It all depends on the distribution of your data and how it will be accessed... Tim -Original Message- Sent: Wednesday, December 05, 2001 2:35 PM To: Multiple recipients of list ORACLE-L Hi, The system is Oracle 9i on Sun 2.8 1) Which partitioning is better to use HASH or RANGE. Do there is some overhead for oracle to calculate the hash number (hash partitioning) to find the particular partition. We need to partition 2 tables of sizes 175G and 162G 2 We have about 10 72G hard drives and 22 9G Hard Drives How many partitions to use (does this number depends upon number of hard disks). 3) What should be the ideal size of datafiles. Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
