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.