Re: Oracle8 partitions and joins question.

We need a bit of clarification on this one, but there could be some significant performance overhead.

>Background: I have a 20 gig table that will have 65-99 partitions, I am
>planning on using the primary key of the table to serve as the partition
key

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

> For indexing I am planning on creating local partitioned indexes
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.

>Question: Will queries that involve a join to this table from another
>table, be unduly affected by the number of partitions? Also will the

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.

These are all design questions you have to answer before you can assess the impact.

As an example of what can go wrong:

    Partition 100 ways on date
    Local index on customer ID (excluding date column completely)     Number of rows per typical customer ID is 1-.

    select * from table where customer_id = 'MNP'

In a non-partitioned table, the query would require about 23 logical I/Os (3 to run the index, then 1 per row on the index, and one per row on the table), and probably 11 physicals (1 on the index leaf, 1 per row visited).

In a partitioned table, you have to examine ALL 100 partitions.

    100 * 3 logical I/Os for running 100 indexes     10 logical I/Os on the index leaves with the right rowids     10 logical I/Os on data partitions with the rows

   100 * 1 physical I/Os on the 100 index leaves (pessimistic figure though)

            Be reasonable and assume only 10% actually are physical.     10 physical I/Os on the data partitions with the rows in

Total:

    120 logical I/Os        (up from 23)
    20 physical I/Os        (up from 11)

Partitioning can be very helpful, but you do need to make some careful design decisions about where you are prepared to waste resources, and where you need to go fast.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Prasanth Duvvur wrote in message <3739AFC0.DBBD1E80_at_fyiowa.com>...
>Hi,
>
>Background: I have a 20 gig table that will have 65-99 partitions, I am
>planning on using the primary key of the table to serve as the partition
>key. For indexing I am planning on creating local partitioned indexes
>(for partition elimination).
>
>Question: Will queries that involve a join to this table from another
>table, be unduly affected by the number of partitions? Also will the
>optimizer perform OK with a local partition index as opposed to a global
>partitioned for this situation?
>
>Thanks,
>Prasanth
>
Received on Fri May 14 1999 - 03:15:33 CDT