Oracle7 Partitioning

I'd like some help with the following:

A parent child set of tables (1:N) grows at a large rate (~3M rows / month on the parent, ~6M rows/month on the child). These are transaction tables and their details.

We are not currently in a position to move to Oracle8. What we should do is partition both tables via a partitioned view.

PK of parent is trx_id, FK to child table. Obviously, most queries will take advantage of child.trx_id = parent.trx_id for joining puposes. The problem is that we would like to partition the child records by transaction date (are most common parameter for reports and form queries).

The parent table, when setup to partition by trx date as well, confuses the optimizer. Full table scans of all parent partitions are done. I presume this is the fault of our not partitioning the parent and child by PK/FK.

The question is: can the parent partitioning be accomplished by trx_id and the child by trx_date? Will this help the optimizer?

Additionally, I suspect we have been faulty in our partition setup, since most reading I have done on Oracle7 partitioning has been done by a date field and this is presumably not the PK of the table. If this is the case, are there a "TOP 10" list of O7 partitioning requirements or tips?

Thank you,

Marc Isikoff

(908) 523-5827

Please respond to both email (drinks_at_webspan.net) and newsgroup. Received on Tue May 05 1998 - 12:29:11 CDT