3

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.

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • SQL Server does NOT create a table with a Clustered Index be default. A table will be physically stored as a HEAP unless a Clustered Index is defined in the create table statement. (Note: By default a Primary Key will be implemented as a Clustered Index on said table, unless explicit specified otherwise.) – John Sansom Aug 20 '11 at 15:42
  • I don't get how it contradicts to what I said in update section... `CREATE TABLE table1(id int not null identity(1,1) PRIMARY KEY)` creates clustered index on `id` unless I specify that it's non-clustered. Is it not a default behaviour? – a1ex07 Aug 20 '11 at 15:51

2 Answers2

6

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.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
2

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:

Community
  • 1
  • 1
Yahia
  • 69,653
  • 9
  • 115
  • 144
  • Thanks for links, I'll check them. I know that clustered means different in SQLServer and Oracle, I'm just used to sql server terminology; sorry about that. – a1ex07 Aug 20 '11 at 15:29