Re: Oracle8 partitions and joins question.

>I interpret this as being: the first (one or two) columns
>of the primary key are being used as the partition key.

Yes actually the primary key is only one column so, partition key = primary key.

>But will they be prefixed (start with the partition key) or non-prefixed,
>or a mixture of both. Pre-fixed can be undesirably large but
>may give you partition elimination in circumstances that would
>otherwise require you to check every partition.

Yes they will be prefixed.

>Will the queries include literal values for the columns that define
>the partitions, will they use bind variables, will the partition key
>columns only appear as join columns in the WHERE clause.
>Will you use queries that join on the secondary indexes and do
>not use the partition key columns at all ? Will your use of the
>partition key columns in join queries be equality, bounded
>range scan (BETWEEN) or unbounded range scan (<= ,>=).
>Are typical queries supposed to collect a few rows, or lots of
>rows.

most queries will look like:
from table
table.partition_key = :value (only returning one record)

there will be a report running once a night that will join with another table:
from table, other_table
table.partition_key=other_table.field (returning several records)

What do you think, about using local prefixed indexes for this situation? I tried a sample query (the join query) , and it appeared that the optimizer did a partition concatenation (explain plan output), with local prefixed indexes.

Thanks. Received on Fri May 14 1999 - 12:00:53 CDT