IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

What is clustering and non-clustering indexes


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sangiah

New User


Joined: 10 Jun 2005
Posts: 62

PostPosted: Thu Jun 16, 2005 3:49 pm
Reply with quote

what is clustering and non-clustering indexes
Back to top
View user's profile Send private message
dbaprasad

New User


Joined: 06 Jun 2005
Posts: 3
Location: pune

PostPosted: Fri Jun 17, 2005 2:30 pm
Reply with quote

If you create a clustured index all data rows which are related will come together ,so access is fast compared to non clustered index
Back to top
View user's profile Send private message
sudheer648

New User


Joined: 23 May 2005
Posts: 97
Location: Chennai

PostPosted: Fri Jun 17, 2005 2:50 pm
Reply with quote

Hi dbaprasad,

Could give brief it bit more what is difference btn clustering and non-clustering indexes.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL

PostPosted: Fri Jun 17, 2005 3:07 pm
Reply with quote

An index on attribute P of a relation is a clustering index if tupleswith similar values for P are stored together in the same block.

Other indices are non-clustering (or secondary) indices.

A relation may have at most one clustering index, and anynumber of non-clustering indices.

In other words, Clustering Index known as Primary Index and Non-clustering index is known as secondery Index.

Regards,

Priyesh.
Back to top
View user's profile Send private message
dbaprasad

New User


Joined: 06 Jun 2005
Posts: 3
Location: pune

PostPosted: Fri Jun 17, 2005 3:10 pm
Reply with quote

hi

we know that ,A page is unit of I/O transfermation

i will explain with a example

if we want to retrive data whose dept is 100

if you dont create a clustered index the dept 100 might be in different

pages ,so db2 has to retrive each and every page where dept 100 stores


if you create a clustered index like


create index index1

on emptable(dept)

clustered


so all the rows whose department number are 100 will store in one page

so db2 need not to retrive too many pages
Back to top
View user's profile Send private message
sudheer648

New User


Joined: 23 May 2005
Posts: 97
Location: Chennai

PostPosted: Fri Jun 17, 2005 5:25 pm
Reply with quote

Good Explanation...Thanx

36_8_11.gif
Back to top
View user's profile Send private message
MidasTouch

New User


Joined: 16 May 2005
Posts: 10
Location: The 'SYSTEM'

PostPosted: Fri Jun 17, 2005 11:04 pm
Reply with quote

When would it make more sense to use a clustered index and when would a non clustered index be better??
Back to top
View user's profile Send private message
brganeshbabu

New User


Joined: 23 Jun 2005
Posts: 34
Location: Chennai

PostPosted: Tue Jun 28, 2005 4:47 pm
Reply with quote

hi,
Clustered inded is used to take advantage of I/O delays. Crisply if I say all the rows related to the table will be stored in a single page, so no need to transfer from pages to pages during the retreival of rows. Now you can understand when to use clustered/non clustered index. If you really need the performance go for clustered index.
Hope I answered your question
Back to top
View user's profile Send private message
Cathy123

New User


Joined: 07 Jul 2006
Posts: 4
Location: Chennai

PostPosted: Wed Mar 14, 2007 6:00 pm
Reply with quote

Hi all,

Let me know if im right in my understanding

Consider this table EMP. Say Emp_id is the Primary Key. Region is Unique

Emp_id Dept Region
1 100 INDIA
3 200 USA
4 100 MALASIA
2 100 AFRICA
5 200 DUBAI


1)A clustered index has to be created only on the Primary Key Emp_id

2)A clustered Index cannot be created on Dept or Region.

3)There can be several Unique Indexes for a table, but only One clustered index

4) I dont understand clearly how is the data stored in a clustered Index
It is not stored in ascending or descending order of the Primary Key-
I'm i right? Can u explain to me how data is stored in this index with this example above?

I'm sorry if im asking the same question again..But im confused.Plz help!!
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Wed Mar 14, 2007 9:12 pm
Reply with quote

In other words, I would say:
The data in the table & index will be in the same order. Remember the key values in the index will be in the order. It could be ASC or DESC. Index by definition says its an ordered set of pointers. Using clustering index would be an added advantage when you go for sequential access fo data in the table.

As far as i have seen Clustering indexes will be built on tables in a Partioned table space and Index controlled partioning. You could refer to manual for more info. IBM manuals as a great info on this. Make sure you refer to V8 manuals.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts choice of clustering index DB2 3
No new posts Identify top 30 big tables or indexes... DB2 0
No new posts DB2 XML indexes (V10) DB2 2
No new posts How to access SYSCAT.INDEXES DB2 6
No new posts Performance by DB2 with non-unique in... DB2 10
Search our Forums:

Back to Top