Re: single clustered tables

General comments in line.

Object (0) though is that clustered tables cannot be partitioned. This could be a severe limitation on future growth, and add administrative woes as the database increases in size.

Rebuttal (0) - the database is too small, and the licence fee too high to cater for partitioned table.

Bear in mind that you cannot do direct path loads to clustered tables - (another common practice with d/w systems - but if you can't partition, this may be irrelevant anyway).

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----

|Env: Oracle 8.1.6 on Solaris 2.7
|

|The idea is, store datawarehouse data in clustered tables,
|1 table per cluster. The rationale is that this imposes a physical
|sort order upon the data; if access is usually via the cluster key,
|access will be optimized.
|

    I assume that you mean indexed cluster, rather than hash cluster,     as you have to predefine the full size of a hash cluster, whereas an

    index cluster can grow by (a) chaining and (b) adding new cluster     key values.

|Objection 1) Most Oracle docs recommend: don't store data in clusters
|if it's going to be updated frequently. Updating clustered tables is
bad.
|

    Rebuttal 1 - addendum - I think this point is made about the     cluster key value, rather than the rest of the columns. (Presumably

    you won't be updating the cluster key - as this leads to chaining).

|
|Objection 2) Conventional RDBMS theory says: the physical order of
rows
|stored in an RDBMS should not be important.
|

    Rebuttal 2 - addendum - The theory is about correctness of result, not

    speed of getting it.

|
|Objection 3) Clustered tables require more space management, and may
be
|wasteful if avg record size and block size are not reasonably
matched.
|
|Rebuttal 3) True. But the benefit of faster access outweighs the
slight
|disadvantage of better planning when the table is created and loaded.
|In the test mentioned above, the space consumed by the clustered
table
|and index was comparable to the non-clustered table and index.
|

    But if you're going to think about things properly, and plan how     to do the job well, that's cheating. You're supposed to wade     right in, mess it up, and then tell us that clustering is useless.

|Objection 4) Very few places seem to use the clustering feature.
|
|Rebuttal 4) That doesn't mean this is a bad idea, just unusual.
|

   Rebuttal 4 - addendum - Everywhere uses clustering (as Tom     Kyte points out) - have you ever looked at the data dictionary     tables (see sql.bsq) ?

|Objection 5) If you want rows stored in order, use an index-organized
table.
|

    Index only tables (with proper planning - see 3) should not     in general have a significant update problem.

    They also give you the advantage that they can be partitioned,     which means small data segments - which can make rebuilds     rebuilds very quick (if that turns out to be necessary) and     direct loads for new partitions.

    There is also the benefit that data will actually be stored in     exact order - potentially eliminating SORTs from ORDER BY     and GROUP BY queries: whereas the clustered option only     gives you 'close location' for an I/O gain, rather than actual     sorted order.

|Rebuttal 5) That does have significant updating problems, and is not
|practical unless you can drop and rebuild the entire table everytime
it
|receives updates. Single-clustered tables do not appear to have these
|updating problems.
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).