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 !
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.
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?
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
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.
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
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?
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.
October 08, 2012 - 3:37 pm UTC
you cannot have a clustered IOT, no.