Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Tet Cheng.

Asked: August 20, 2000 - 9:37 pm UTC

Last updated: October 08, 2012 - 3:37 pm UTC

Version: 7.2/7.3 and 8

Viewed 50K+ times! This question is

You Asked

Hi Tom,

What is cluster index and how to you use them?

Tet Cheng

and Tom said...

First, you must understand what a cluster is in Oracle.

A cluster is simply a method for storing more then 1 table on the same block. Normally -- a block contains data for exactly 1 table. In a cluster -- you have data from many tables sharing the same block.

For example -- if you join the data from EMP and DEPT by DEPTNO frequently -- you might consider clustering this data by DEPTNO. In that fashion all of the rows from EMP for deptno=10 and the row in DEPT for deptno=10 will reside on the same exact block (one IO to get all of the data instead of some IO's to EMP and DEPT).

In order to organize data in such an object, we create a cluster KEY -- deptno in our above example. We must index this cluster key. This index on deptno in the cluster is called a CLUSTER INDEX and is used to locate the blocks that contain data about deptno=10 (for example).

See
</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76965/c08schem.htm#12872 <code>
for more details.

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

what are oracle's clustered and nonclustered indexes

Steve, June 16, 2006 - 6:50 pm UTC

In Sybase, "There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows."

What are the equivalance in ORACLE?

Thanks a lot !


Tom Kyte
June 16, 2006 - 7:28 pm UTC

Before I answer I want to know

why


the #1 and #2 and .... #1000 reason for using them in Sybase is "concurrency"

The last reason (well, actually the NULL reason) for using something similar in Oracle would be concurrency - we don't have the same issues.

So - what is the goal.


To me, it is better to understand what Oracle has to offer (b*tree clusters, hash clusters, single table hash clusters, sorted hash clusters, index organized tables, heap tables, external tables, partitioned tables, ..........)

I go over all of those in my book Expert Oracle Database Architecture, as does the for free concepts guide.

but don't ever try to do the "in sybase you do this, in oracle you do that", there are just way way way too many caveats and you'll end up with a really poorly performing application


(the answer is technically "IOT" - the index organized table, but the only RESPONSIBLE answer is "why" - because you DO NOT - repeat DO NOT want to use an IOT everywhere you used a clusterd index in sybase. I've seen that. It is comical at first - and then really really sad)

Why Inserts in Clustered tables are slow

Maria, September 20, 2006 - 5:08 pm UTC

Hi Tom, I have read like "Clusters can reduce the performance of INSERT statements compared with storing a table separately with its own index". Could you please explain why the INSERT statements on a clustered tables would be slower.

Thanks in advance.

-Maria.


Tom Kyte
September 21, 2006 - 2:02 am UTC

because data has a place to go - and we have to compute where that place is.

For example, to insert into a normal heap table we in general:

a) go to freelists which are found rapidly by looking in a fixed location (first block in table points to them)
b) find block on freelist with space and do the insert

to insert into a b*tree cluster we in general:

a) have to range scan the b*tree cluster index looking for the pointer to the block this row goes on. (this is already more "expensive" than step a) above.
b) if we find it, then goto the block and try to put the data there (and maybe have to go to the freelists and do the equivalent of a) and b) above)
c) if we don't find it, we have to do a) and b) above to find a place to put the block and then update the b*tree cluster index to tell everyone else where we put this data so they can put their similar data on this same block.


clusters (b*tree, hash, sorted hash), index organized tables, partitioning - these are all techniques that can be used to make DATA RETRIEVAL more efficient - at the cost of possibly slower data insertion rates.

But remember, you insert a row once....
Hopefully you query it over and over and over.

concept of non-clustered indexes

Shyam, August 24, 2007 - 12:58 am UTC

While searching on google, I found out the following differences between primary and unique key

Primary Key
------------
1. It does not allow NULL value to be inserted in the column.
2. Oracle creates a clustered index by default for the column.

Unique Key
-----------
1. It allows one NULL value to be inserted in the column.
2. Oracle creates a non-clustered index by default in the column.

Could you please explain the concept of non-clustered index in oracle?

Tom Kyte
August 24, 2007 - 2:52 pm UTC

define to us first what you mean by a clustered index :)

no, actually, check out the concepts guide (a MUST READ)

and if you want it in the way I say things - see the book - I describe how Oracle works...

Clustering with Partitioning

dan, January 26, 2011 - 4:53 am UTC

Hi
Without considering the merits of the approach. Can a Cluster Table be Partitioned and exploit partitioning features and functions?


Or put another has cluster table functionality been superseded by partitioning?
Best Regards
Dan

Tom Kyte
February 01, 2011 - 2:39 pm UTC

clustered (b*tree or hash) segments cannot be partitioned.

.. Or put another has cluster table functionality been superseded by partitioning?
...

absolutely NOT.

with a cluster you can store data from more than one table on a single block (pre-joined). You cannot do that with partitioning.

with a cluster you can make sure that every row that has a common key (like deptno, or empno in a history table) is stored right next to other rows with the same key. Unless you create a partition PER KEY value (unreasonable in general, wasteful really) you won't be doing that with partitions.

with a cluster you can have immediate access to your row data by key without using an index (hash cluster). Not so with partitioning.


clusters are not a subset of partitioning, they are entirely different.

A reader, March 08, 2011 - 2:17 am UTC

test

how to delete

venkata, July 19, 2011 - 5:17 am UTC

Tom,

I was thinking can you add a button to delete the comments posted by a user, which will help to repost a query again.
Tom Kyte
July 19, 2011 - 8:01 am UTC

given that I do not make anyone log in, and given that I do not want to make it so that anyone has to log in - this will not likely happen. sorry.

Just as an update ...

David Aldridge, July 28, 2011 - 3:11 am UTC

Iggy Fernandez points out that support of partitioned clusters may be on its way.

http://iggyfernandez.wordpress.com/2011/05/10/major-new-undocumented-partitioning-feature-in-oracle-database-11g-release-2/
Tom Kyte
July 28, 2011 - 7:21 pm UTC

yeah - i saw that :)

cluster

M.Pradeeba, January 23, 2012 - 1:42 am UTC

1.how to create cluster index and non clusert index?
and definition and one example?
2.normal view and materialized view?and definition and one example?
3.if i drop procedure and drop function,and drop package
the table is drop or not drop?
4. if i drop the table? the procedure,function,package
is drop or not?


Tom Kyte
January 23, 2012 - 6:09 am UTC

please read this document from cover to cover - and if you want to hear it all in my voice, read 'Expert Oracle Database Architecture'.

You will never be sorry you've read this - if you read it and retain just 10% of it, you'll probably know twice as much or more than your coworkers:

http://www.oracle.com/pls/db112/to_toc?pathname=server.112/e25789/toc.htm

tables and procedures are separate and distinct from each other, dropping one will not drop the other.

IOT for cluster tables

Maha, October 01, 2012 - 9:59 am UTC

To get the benefit of both the IOT and multi-table clustering is it possible to achieve them together? I am having couple of tables that do have have a common key which I am thinking of using as the cluster key. Butm the application accesses the data from the 2 tables via a non-unique column that I am thinking of indexing for IOT benefit.
Tom Kyte
October 08, 2012 - 3:37 pm UTC

you cannot have a clustered IOT, no.