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: [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).
RE: single clustered tables
I'm going with the Kimball-ian definition of a DW showing changes over time (temporal). You don't want to go back and update historical data - unless you're Enron - as this will skew results and trend analysis. Scott Shafer San Antonio, TX 210-581-6217 > -Original Message- > From: Tim Gorman [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, April 25, 2002 4:20 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: single clustered tables > > Not true. Many folks think a data warehouse is "read only". There is a > huge difference between being "designed to optimize reading" and being > "read-only"... > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, April 25, 2002 2:40 PM > > > > 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. > > > > If its being updated, its not a true data warehouse. > > > > Scott Shafer > > San Antonio, TX > > 210-581-6217 > > > > > > > -Original Message- > > > From: Bill Becker [SMTP:[EMAIL PROTECTED]] > > > Sent: Thursday, April 25, 2002 3:22 PM > > > To: Multiple recipients of list ORACLE-L > > > Subject: single clustered tables > > > > > > 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. > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Tim Gorman > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Re: single clustered tables
Not true. Many folks think a data warehouse is "read only". There is a huge difference between being "designed to optimize reading" and being "read-only"... - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, April 25, 2002 2:40 PM > 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. > > If its being updated, its not a true data warehouse. > > Scott Shafer > San Antonio, TX > 210-581-6217 > > > > -Original Message- > > From: Bill Becker [SMTP:[EMAIL PROTECTED]] > > Sent: Thursday, April 25, 2002 3:22 PM > > To: Multiple recipients of list ORACLE-L > > Subject: single clustered tables > > > > 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. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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).
Re: single clustered tables
Objection #6) Clusters do not impose a physical sort order upon the data; they only impose the physical "clustering" of rows with the same data values in it's cluster-key columns to reside in the same database blocks. Will the same cluster-key values be found in the same blocks? Yes. Are they sorted? No. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, April 25, 2002 2:21 PM > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman 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).
RE: single clustered tables
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. If its being updated, its not a true data warehouse. Scott Shafer San Antonio, TX 210-581-6217 > -Original Message- > From: Bill Becker [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, April 25, 2002 3:22 PM > To: Multiple recipients of list ORACLE-L > Subject: single clustered tables > > 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. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
