> -----Original Message----- > From: Rothouse, Michael [mailto:[EMAIL PROTECTED] > Sent: Monday, 27 October 2003 00:04 > To: Multiple recipients of list ORACLE-L > Subject: RE: Oracle pricing ain't going down > > > I realized in my hasty response that I failed to complete my > thought/question. My response should have been: > > IOT and clustered indexes are not comparable to each other. Are they? > > As you mention, by definition they do appear similar. I originally > thought more about how they are used as it appears (from my > experiences > anyway) that clustered indexes are utilized more frequently in SQL > Server/Sybase than IOTs in Oracle. I too am curious as to > when it is an > advantage or disadvantage to utilize an IOT. I read > somewhere that IOTs > are best suited for lookup tables. Tables with a large number of > columns are not a very good IOT candidate. I'm just not experienced > enough in understanding why that is.
Given I threw up the little list, I'll throw in my two cents. My understanding of the difference between Oracle IOTs and SQL Server tables with clustered indexes is as follows. Oracle IOT: (Quoting Oracle SQL Syntax guide) "Oracle maintains the table rows (both primary key column values and nonkey column values) in an index built on the primary key." Which to me means the IOT structure contains complete rows in all blocks of the index structure - root block, branch blocks, leaf blocks. (I'm willing to be corrected here ... in fact, I'd like someone to :-) ). SS table w. clustered index: A SQL Server clustered index builds a standard b-tree structure for the root and branch pages, but leaf pages are the actual data pages themselves (a page in SS2000 is eight 8kB extents), rather than pointers to the data pages (as per a standard index). As such, only the leaf pages contain complete data rows. A minor difference, but a difference none the less. Any criticisms welcome (it's a Monday, and the coffee has run out. Believe me, nothing you say can affect me now :-) ). Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).
