[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).
