debunking partitioning

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 Tue Dec 03 2002 - 16:31:19 CST