Hello, Env: Oracle 8.1.6 on Solaris 2.7
Someone has suggested the following idea, and I'm wondering if this is good/bad. Any comments, pro or con, are appreciated. 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. 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) That is true when several tables are in 1 cluster. If only a single table is contained in a cluster, this is of little concern. A simple test was run, executing a series of updates, deletes, inserts against a single-clustered table vs a non-clustered table. The results did not show much difference; in fact, the clustered table was slightly faster. Table had 17 million rows in approx .5GB. Objection 2) Conventional RDBMS theory says: the physical order of rows stored in an RDBMS should not be important. Rebuttal 2) True, if access paths are random. If the majority of access is via a single path, it makes sense to store the data in that order. 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. Objection 4) Very few places seem to use the clustering feature. Rebuttal 4) That doesn't mean this is a bad idea, just unusual. Objection 5) If you want rows stored in order, use an index-organized table. 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. Again, comments regarding the above or other related info is appreciated. Thanks to any responders. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Becker 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).
