Re: Index vs. partitioning for performance

<aehven_at_gmail.com> wrote in message
news:1145568226.033724.276500_at_e56g2000cwe.googlegroups.com...
> Hi,
>
> Suppose:
>
> - you have a table that has only a few colums and one of them, an
> integer column, is used as an index;
> - rows in this table are only ever selected using the index field;
> - no rows are ever deleted from the table; only inserts and updates
> are allowed;
> - this table can grow to one billion rows.
>
> Can query (or insert) performance for this table be improved by
> partitioning the table and/or its index? If partitioning were done,
> the only sensible partitioning scheme for this data would be
> hash-partitioning on the indexed column.
>
> I'm guessing the answer is no. I'm also guessing that rebuilding the
> index is never an issue in this scenario. Am I right or am I right?
>
> Thanks,
>
> Adam
>

Does this table have a primary key ?

Is this integer column that key, or at least a component of that key.

Are the queries only of the form:

    col = {constant}
or do you also do

    col between {const1} and {const2}
or

    col > {const1}
or do the queries include joins to this
table on the integer column ?

Is the column sequence based, randomly
generated, or the primary key of another table with referential integrity in place.

If you do anything other than

    col = {constant}
then HASH partitioning is likely to introduce an unacceptable performance overhead,
but range partitioning may help, but you then have an issue dealing with MAXVALUE
problems, and adding new partitions, and if the queries are always for "the most recent" values, you may get a contention problem on just one partition.

If you really only every do:

    col = {constant}
and the column is (the leading column of) the primary key, then a partitioned IOT could be ideal - hash partitioning became available for IOTs in 10g.

Generic Traps:

    One partition becoming the "hot" partition

    Queries not doing partition elimination, therefore     doing N times the index work and causing N times     the contention on index root blocks where N is the     number of partitions

Generic benefits

    If partition elimination is perfect, and query spread     is perfect, the potential for contention is distributed     evenly across N partitions

--
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html