IOT vs Heap tables in Oracle

Having worked with 开发者_运维技巧SQLServer for a long time, I'm used to think that clustered table (table with clustered index) is usually a better choice compared to heap table. Now I'm also working with Oracle and I don't really understand why their tables are heap by default. From my experience I can say that there is a limited number of cases where tables should be heap (again, I mostly dealt with SQLServer).

Does Oracle have a good reason for "forcing" (by forcing I mean that CREATE TABLE without specifying organization index creates heap in contrast to SQLServer which by default creates clustered table) users to use heap tables?

[Update]

Clarification about SQLServer - I might have misused "default" for describing SQL Server behaviour; I'm aware that it creates a clustered index if primary key specified in CREATE TABLE. My point here is that I don't have to specify PK is clustered.

[/Update]

Also, there are many good articles about clustered tables vs heaps in SQLServer, and I wonder to which extent it can be applied to Oracle.

Any information is greatly appreciated.

Thanks.

One of Oracle's historical advantages has been its row-level locking mechanism where the locks are stored in the data row (rather than in memory or a separate structure). That means there has never been a need to 'minimise' the number of locks in a transaction, and no concept of escalating row locks to page (or table locks).

Its concurrency and recovery mechanism also allows it to write uncommitted data to disk rather than keeping them in memory.

These mitigate against the same problems that can also be addressed by clustering/btree structured tables.

In short, heap tables in Oracle don't have the same problems as heap tables in other databases so you don't get the same benefits from co-locating related data in the same physical blocks.

I work alot with Oracle and in my experience IOT (= BTree-like) can have a cost which neutralizes or overweights the possible benefit...

"clustered" and IOT/Btree are different beasts in Oracle terminology - I don't know SQL Server very well but read that "clustered" means something different from what it means in Oracle...

This is more of a case-by-case decision... although I like to stick with the default and optimize as needed...

Some links with information regarding Oracle/Index:

• http://psoug.org/reference/indexes.html
• http://psoug.org/reference/iot.html
• http://psoug.org/reference/clusters.html
• When should I use Oracle's Index Organized Table? Or, when shouldn't I?
• http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:707440500346653259
• http://www.datadisk.co.uk/html_docs/oracle/tables.htm