OraFAQ Forum: SQL & PL/SQL » Difference between Index and Clustered Index

Home » SQL & PL/SQL » SQL & PL/SQL » Difference between Index and Clustered Index (Oracle 8i and above)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference between Index and Clustered Index [message #469123]

Tue, 03 August 2010 07:14

kish_ulike
Messages: 30
Registered: June 2008
Location: chennai

Member

Hi Every body,

Any on give explanation for difference between Index and Clustered Index?

It will be great if i get explanation how memory allocation and
Execution takes place.?

Thanks
Kishore

Report message to a moderator

Re: Difference between Index and Clustered Index [message #469146 is a reply to message #469123]

Tue, 03 August 2010 09:26

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

Read Difference between clustered and non-clustered indexes

Report message to a moderator

Re: Difference between Index and Clustered Index [message #469148 is a reply to message #469146]

Tue, 03 August 2010 09:32

rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.

Senior Member

And to asnwer to your 2/ question is Oracle SQL execution steps

Report message to a moderator

Re: Difference between Index and Clustered Index [message #469151 is a reply to message #469123]

Tue, 03 August 2010 09:53

Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome

Senior Member

The question is not clear because Oracle doesn't offer a direct way to create clustered index.

Clustered indexes are indexes used to force the row order in the table segment. These indexes are used by other RDBMS like MS SQL Server and they result good for range selections mainly and to keep records with the same keys in the same physical data blocks. A side effect of such indexes is that they need a relevant extra job to keep the row order in the table and to update the secondary indexes during table DMLs.

In Oracle you may have a similar behavior when you create an Index Organized Table or a B-Tree Cluster.

With an Index Organized Table you define a table on the Primary Key Index B-Tree structure with the possibility to move part of its column to a different heap segment (IOT-Overflow). To choose the clustering order on that table it's commonly used to define the columns you want to index as the first part of the primary key.

With a B-Tree cluster it's required to create a cluster index and that index can be considered as a clustered index for the tables in the cluster. The B-Tree cluster is just used to keep the records sharing the same values on cluster keys in less blocks as possible and it doesn't keep the row order.

Bye Alessandro

Report message to a moderator

Re: Difference between Index and Clustered Index [message #469214 is a reply to message #469151]

Tue, 03 August 2010 22:25

kish_ulike
Messages: 30
Registered: June 2008
Location: chennai

Member

Thanks Alot ...Alessandro

Report message to a moderator

Previous Topic:	ORA-01422 multiple records in SP
Next Topic:	Index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 06 08:10:12 CDT 2025