Phil,
Thanks for the info. Sounds similar to an IOT in Oracle (index organized
table). In Oracle a clustered table is something else. One type is where
you frequently get a parent and the child records (eg an employee's
demographic information and their pay history). In Oracle you can cluster
this information together so a block has the parent record, the child
records (without repeating the key that joins them). You can still refer to
them as seperate tables, but if you are going to frequently get the parent
and all the related child records then it can be advantagouse to use a
cluster. The other type of Oracle cluster is a hash cluster where you have
a constant set of rows (preferably) and choose a column that you are going
to look up by frequently. Then Oracle constructs a hash and is very
efficient at going right to the block. (not good if the row count is always
increasing, good for static look up tables with equality conditions.)
Jim
"Phil Singer" <psinger1_at_chartermi.invalid> wrote in message
news:3DA24A4E.F5B362A_at_chartermi.invalid...
> Jim Kennedy wrote:
> >
> > I think we might be assuming different definitions for cluster. What do
you
> > mean by cluster? (That isn't a flame item, just a fact. I suspect that
DB2
> > might have a different meaning for cluster than Oracle - not better or
> > worse, just different.)
> > Jim
> > "Serge Rielau" <srielau_at_ca.ibm.com> wrote in message
> > news:3D9C98A7.70206_at_ca.ibm.com...
> > > I prefer not to be the one answering that question since I can see the
> > > scale tip towards flame the moment I do so.. Maybe someone less likely
> > > to sound biased can pick up the reigns here...
> >
>
> Having read thru all of this (and having once worked with
> DB2 in an earlier life), I thought I might have something
> to contribute.
>
> DB2 is designed around IBM hardware. On an IBM mainframe,
> there is special hardware support to do a sequential read
> 'real fast'. Because the database is so tuned to the hardware,
> one has to jump thru hoops to avoid making sequential reads
> be anything other than fast. Consequently, DB2 goes out
> of its way to make sequential reads (in Oracle read: Full
> Table Scan) do as much as possible.
>
> Consequently, it has the construction of a 'cluster index'.
> Forget everything you know from Oracle, it is unrelated.
> If an index is the cluster index for a table (which can
> have only one) it means that the data is physically ordered
> on the disk in the order implied by the index. This means,
> that if you do a full table scan, the rows come out in
> the same order as they would if you retrieved each row
> by the cluster index.
>
> This means that queries which are ordered by this index
> can be done quite easily (the index may not even have to
> be read). Index range scans also go fast.
>
> Now couple this with the fact that DB2 programs typically
> use what Oracle now calls optimizer plan stability (you
> cannot change the execution plan of a compiled program)
> and you see that it is essential to keep the table
> physically ordered. And, if you have a lot of inserts
> and updates, the only way to do this is to unload and
> reload the table. When I was a DB2 DBA most of the
> nightly production cycle was based on doing exactly
> that.
>
> I believe that SQL Server has something similar, although
> I know I am likely to be in error.
>
> This is really the only need to reorganize a DB2 table.
> It was designed to be as re-org free as possible, in
> contrast to IMS and other early DBMS which sometimes
> had to be re-org'd twice a day. Of course, some of
> these IMS guys were booted up to manager, where they
> were put in charge of Oracle systems....
>
> In defense of those who claim that Oracle databases
> need to be re-org'd: they may well have found that
> in practice it works. When it does, it probably is
> because the export/import process also cleaned up
> row chaining and rebuilt unbalanced indexes, items
> which could have been prevented in the first place
> with a little thought in the table definition
> (or at least could have been fixed with a lot less work).
>
>
>
>
> --
> Phil Singer | psinger1ATchartermiDOTnet
> Oracle DBA
> Replace the obvious to reply
Received on Tue Oct 08 2002 - 00:35:51 CDT
