Re: debunking partitioning

In case you are interested
Jonathan Lewis has a good comparison/comments on partitioned tables and partitioned views in his book Practial Oracle8i ....

Anurag

"Mikito Harakiri" <mikharakiri_at_yahoo.com> wrote in message news:bdf69bdf.0212031431.7d82d168_at_posting.google.com...
> Greetings, masters of storage management and other oracle
> implementation quirks!
>
> Stumbled over multiple partitioning bugs, could I ask if partitioning
> a really good idea, or just an extra level of complexity? Were
> partition designers familiar with the concept of a view? Obviously,
>
> CREATE view sales_range as
> select * from sales_jan2000
> union all
> select * from sales_feb2000
> union all
> select * from sales_mar2000
> ...
>
> I wish partitioned tables were designed as updateable views like
> sales_range above! In reality query against a disjoint union is
> completely different from the query against partitioned table: there
> is no similarity in execution plans whatsoever.
>
> Then, one have to learn what partition prunning is. And then, there
> are partition skews and bugs associated with that too.
>
> Lets go through "the advantages" enumerated in the Concepts Guide:
>
> <1>Partitioning enables data management operations such data loads,
> index creation and rebuilding, and backup/recovery at the partition
> level, rather than on the entire table. This results in significantly
> reduced times for these operations.</1>
>
> My question is if huge tables management can't be done by standard
> means.
>
> <2>Partitioning improves query performance. In many cases, the results
> of a query can be achieved by accessing a subset of partitions, rather
> than the entire table. For some queries, this technique (called
> partition pruning) can provide order-of-magnitude gains in
> performance.</2>
>
> If I feel that range scan is fast, I create an index (providing the
> same "order-of-magnitude" gains). No need in spoecial facility like
> prunning.
>
> <3>Partitioning can significantly reduce the impact of scheduled
> downtime for maintenance operations.</3>
>
> I don't see how bullet #3 is different from #1.
>
> <4>Partition independence for partition maintenance operations lets
> you perform concurrent maintenance operations on different partitions
> of the same table or index.</4>
>
> But parallel hints were designed for that purpose too!
>
> <4>You can also run concurrent SELECT and DML operations against
> partitions that are unaffected by maintenance operations.</4>
>
> Concurrency protocols are supposed to to isolate parts of the table
> affected by transaction. Those protocols could digest as much
> concurrency as data consistency allows. Is the bullet implying that
> those protocols are not effective? They certainly are more general
> than naive transaction isolation through partitioning.
>
> <5> Partitioning increases the availability of mission-critical
> databases if critical tables and indexes are divided into partitions
> to reduce the maintenance windows, recovery times, and impact of
> failures. </5>
>
> This is reiteration of #4 and #1.
>
> <5> Partitioning can be implemented without requiring any
> modifications to your applications. For example, you could convert a
> nonpartitioned table to a partitioned table without needing to modify
> any of the SELECT statements or DML statements which access that
> table. You do not need to rewrite your application code to take
> advantage of partitioning. </5>
>
> Just as an aside, since partition names are allowed in the queries,
> stupid users started to use them. Therefore, in practice applications
> are dependent upon partitions!
Received on Wed Dec 04 2002 - 07:18:36 CST