Re: DB2 HADB

"Mark Townsend" <markbtownsend_at_comcast.net> wrote in message news:4387F2A0.7070004_at_comcast.net...
> Your initial statement was
> "The main reason is that DB2 has table clustering, which Oracle does not
> have unless you use an index ordered table"
>
> You later modified your statement to
> "Oracle does not have table clustering in the same manner as DB2 unless
> you create an index ordered table"
>
> You now agree (I think) that table clustering and IOT's are in fact
> different technologies and you now say on table clustering that "Oracle
> does not do clustering of tables like DB2 does it, and I think there are
> some advantages to the way DB2 does clustering".
>
> So the inaccuracy was..
> 1) Index organized tables and table clustering are completely different
> concepts, designed to solve different problems, and should not really be
> compared to each other.
> 2) Oracle does indeed have (a form of) table clustering, that does not
> rely on index organized tables at all.

Mark, I think you a are a bit paranoid. I had no intention of trying to making inaccurate statements, and I don't think I did. In light of your numerous wildly inaccurate statements about DB2 HADR (although probably not intentional) I find your remarks to be a bit harsh.

Clearly, the main point I was making is that the reason why reorgs are more important in DB2 than Oracle is because of the way DB2 table clustering works. It works different than Oracle, which is why Oracle does not usually need as many reorgs (or at least Oracle DBA's don't do many reorgs). Oracle IOT's don't need a reorg to make room for additional inserts because they dynamically reorder the rows even if a page split is needed (just like DB2 does with indexes).

I disagree with you about the difference between DB2 clustering and IOT in Oracle. I think that generally they attempt to do the same thing.

For example in an employee table, if the most important query was to search employee phone numbers based on last name (and optionally first name), then clustering (or ordering) the table rows by (last name, first name) would likely reduce the number of blocks that need to be accessed, and reduce the number of blocks that need to be put in the buffer cache, since the needed rows they would be close together in the table.

Alternately, if a query which listed employees for a given department was determined to be the most important query, then ordering the employee rows by department would yield the best performance (by reducing the number of blocks needed to get the data).

Both DB2 table clustering and IOT's order the rows in a fashion that promotes better performance for the above 2 examples, than if the rows were randomly inserted into the table. So in that sense, I do think they serve the same general purpose. Perhaps IOT's perform another function that I am not aware of, and perhaps you can enlighten me on that.

Both Oracle and DB2 have other types of clustering which I did not discuss. For example DB2 has MDC (Multi-dimensional clustering) which tends to work best in decision support environment. Oracle hash clustering, and perhaps other concepts which you would call clustering that I am not familiar with. Unfortunately the word "clustering" is a loaded word, and my mean different things to different databases.

As I stated, I answered Noon's question about why DB2 DBA's are more concerned about reorgs than most Oracle DBA's and I replied it is because of the way DB2 clustering works. In DB2, if a table is clustered and if there are lots of inserts into the table, a reorg is desirable to restore the percent free on each block (page in DB2) so that additional rows can be inserted on the correct page to maintain the approximate (but not exact) clustering sequence in DB2. Received on Fri Nov 25 2005 - 23:59:41 CST