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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
