Re: partitioning questions

becker.bill_at_marshfieldclinic.org 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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).