OraFAQ Forum: SQL & PL/SQL » How to view if a table indexs are clustered or non clustered?

Home » SQL & PL/SQL » SQL & PL/SQL » How to view if a table indexs are clustered or non clustered? (11g, 11.2.0.3, Windows 7)

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

How to view if a table indexs are clustered or non clustered? [message #614077]

Thu, 15 May 2014 18:38

JP10
Messages: 29
Registered: May 2014
Location:

Junior Member

I've tried using all_indexes, but nothing stands out if the indexs are clustered or not? I want to know if indexs on table are clustered or not. Appreciate the help.

Report message to a moderator

Re: How to view if a table indexs are clustered or non clustered? [message #614078 is a reply to message #614077]

Thu, 15 May 2014 18:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

SQL> select cluster_type, count(*) from dba_clusters group by cluster_type;

CLUST   COUNT(*)
----- ----------
INDEX         10

Report message to a moderator

Re: How to view if a table indexs are clustered or non clustered? [message #614087 is a reply to message #614077]

Fri, 16 May 2014 01:08

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Clustered index does not exist in Oracle.
A table is in a cluster or not.
An index is a cluster index or not.
But there is no clustered index.
The (about) same thing in Oracle is an IOT (Index-Organized Table).

Report message to a moderator

Re: How to view if a table indexs are clustered or non clustered? [message #614094 is a reply to message #614077]

Fri, 16 May 2014 02:43

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I echo with Michel. It is wonderfully explained by Tom here. The term "Clustered Index" is applicable in some other DB technologies, but not in Oracle. In Oracle the same is called an Indexed Organized Table.

Report message to a moderator

Re: How to view if a table indexs are clustered or non clustered? [message #614111 is a reply to message #614094]

Fri, 16 May 2014 11:20

JP10
Messages: 29
Registered: May 2014
Location:

Junior Member

Thanks guys appreciate the informative content. Last question so is it true that then a primary key creates a clustered index by default?

Report message to a moderator

Re: How to view if a table indexs are clustered or non clustered? [message #614112 is a reply to message #614111]

Fri, 16 May 2014 11:29

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is NO clustered index.

Report message to a moderator

Re: How to view if a table indexs are clustered or non clustered? [message #614113 is a reply to message #614112]

Fri, 16 May 2014 11:33

JP10
Messages: 29
Registered: May 2014
Location:

Junior Member

Haha ok gotcha. Reading the article somebody said in created a clustered index. I got it now thanks guys.

Report message to a moderator

Re: How to view if a table indexs are clustered or non clustered? [message #614114 is a reply to message #614112]

Fri, 16 May 2014 11:41

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel Cadot wrote on Fri, 16 May 2014 12:29

There is NO clustered index.

Well, technically it does - IOT.

SY.

Report message to a moderator

Re: How to view if a table indexs are clustered or non clustered? [message #614115 is a reply to message #614114]

Fri, 16 May 2014 11:47

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

http://www.orafaq.com/forum/mv/msg/192759/614087/#msg_614087

The answer is contextual. Smile

Report message to a moderator

Previous Topic:	multiple select with and
Next Topic:	plsql statment with this output

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 04:55:22 CDT 2024