Re: Challenge: Partitioning is a wrong idea

Comments embedded.

mikharakiri_nosp..._at_yahoo.com wrote:
> Joel Garry wrote:
> > Even this guy can come up with more things:
> > http://www.dba-oracle.com/oracle_tips_partitioning.htm
>
> Spell out your arguments and I will address them the way I address
> Don's:
>
> * Stable - Partitioning is a very stable technology and has been
used
> in Oracle since Oracle8, back in 1997. Each new release of Oracle
> improves partitioning features.
>
> The code is a liability. There are always bugs in it. There is always
a
> case when during new feature design its interaction with partitioning
> simply haven't been thought out.
>

Prove that. I've yet to see the code as a liability with partitioned tables.

> * Robust - Oracle9i partitioning allows for multi-level keys, a
> combination of the Range and List partitioning technique. The table
is
> first range-partitioned, and then each individual range-partition is
> further sub-partitioned using a list partitioning technique. Unlike
> composite Range-Hash partitioning, the content of each sub-partition
> represents a logical subset of the data, described by its appropriate
> Range and List partition setup.
>
> OK, I can range partition, then I could hash partition on top of it.
> Cool. Prove me that even one level of partitioning gives me a single
> benefit, first.
>

You have, and unfortunately you choose to dismiss the benefits because they don't play into your ill-conceived plan of attack.

> * Faster backups - A DBA can back-up a single partition of a table,
> rather than backing up the entire table, thereby reducing backup
time.
>
> You want to backup a portion of the table? Have it occured to you
that
>
> create table_copy as
> select * from table_at_db_link
> where my_criteria
>
> is perhaps the simplest and the most flexible way to accomplish this?
>

Which also duplicates data whereas partitioning does not. Every time you want to backup that portion of your table you need to recreate your subset table to ensure you have all data meeting the criteria. A partitioned table has none of this duplication, nor does it suffer from short-sighted attempts at recreating the wheel, a task your query above attempts to do, and fails.

> * Less overhead - Because older partitioned tablespaces can be
> marked as read-only, Oracle has less stress on the redo logs, locks
and
> latches, thereby improving overall performance.
>
> What are partitioned tablespaces? Is it one more concept to learn?
>

Clearly you don't understand partitioning, as partitions can be created in separate tablespaces. As such, older partitions, if created in separate tablespaces, which will not undergo inserts/updates can be converted to read-only partitions by simply placing their tablespace into read-only mode. And, a partition or group of partitions can be moved to a different tablespace, thus grouping older partitions exempt from DML into a single tablespace, and that tablespace may be set as read-only, thus reducing redo generation, undo generation and lock/latch contention. Whether created originally in separate tablespaces or moved to a different tablespace at a later time, partions can be made read-only, and that will produce less overhead.

> * Easier management - Maintenance of partitioned tables is improved
> because maintenance can be focused on particular portions of tables.
> For maintenance operations across an entire database object, it is
> possible to perform these operations on a per-partition basis, thus
> dividing the maintenance process into more manageable chunks.
>
> This should be clarifed. What kind of maintenance? The way to focus
> onto a certain portion of the table is
>
> select from table where your_focus_condition
>
> This could be a part of bigger maintenence operation.
>

And where will you move this data should it be deemed necessary at a later time? How can you make such a subset of a 'normal' table read-only to improve overall overhead? Simply put, with your 'mechanism' you cannot. Therefore your idea is woefully lacking in substance relative to partitioned tables.

> * Faster SQL - Oracle is partition-aware, and some SQL may improve is
> speed by several orders of magnitude (over 100x faster).
>
> This is anecdotical evidence without a SQL*Plus log proof in Tom's
> style.
>

Believe the statement, don't believe it. Your choice does not make it any less true.

> - Index range scans - Partitioning physically sequences rows in
> index-order causing a dramatic improvement (over 10x faster) in the
> speed of partition-key scans.
>
> Sorry, no. Index range scans has nothing to do with partitioning.
Their
> speed is attributed to ingenious B*Tree structure.
>

Here you make a valid point. The first in your long list of posts.

> - Full-table scans - Partition pruning only access those data blocks
> required by the query.
>
> Select by index range scan, instead! Admittedly, this is might be
> inferior to partition scan beacause random IO is less performant than
> sequential one. Make the table index-organized, then. (You f**ked up
> the table physical layout with these "partitions" anyway. Index
> organised table provides some coherent structure, at least).
>

Try to update an index-organized table in a production environemnt, especially a large, heavily transacted table, and you'll see the error of your ways. Partitioning suffers not from inserts and updates, especially when the transaction load is great.

> - Table joins - Partition-wise joins take the specific sub-set of
> the query partitions, causing huge speed improvements on nested loop
> and hash joins.
>
> Would it be faster than indexed nested loop?
>

That would depend upon the size of the table or the size of the involved partitions. Nothing is absolute, and likely you'll find cases where the answer to your question is 'Yes' and cases where the answer is 'No'. This, of course, does not satisfy you as you demand absolutes.

> - Updates - Oracle parallel query for partitions improves batch load
> speed by
>
> Parallel query could work with normal tables as well, right?

Certainly it does. However targeting a physically segregated subset of data in a table with parallel query is usually faster than targeting the entire table and relying upon an index range scan to cull your result set.

It appears you are determined to make yourself 'right' in this argument. Knock yourself out, expect absolutes when, in some cases, none exist. Keep pretending you know more about partitioning than you do. You've been given excellent information and more benefits than you deserve. I only wish you'd stop this senseless attack on a feature you apparently don't fully comprehend.

David Fitzjarrell Received on Thu Apr 07 2005 - 22:19:20 CDT