Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
What is clustering and non-clustering indexes

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: What is clustering and non-clustering indexes
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    Post subject: Re: what is clustering and non-clustering indexes
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    Post subject: Re: what is clustering and non-clustering indexes
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: 1452
Location: Chicago, IL

PostPosted: Fri Jun 17, 2005 3:07 pm    Post subject: Re: what is clustering and non-clustering indexes
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    Post subject: Re: what is clustering and non-clustering indexes
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    Post subject: Re: what is clustering and non-clustering indexes
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    Post subject: Re: what is clustering and non-clustering indexes
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    Post subject: clustered and non clustered index
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    Post subject: Re: what is clustering and non-clustering indexes
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts DB2 XML indexes (V10) ozburt DB2 2 Tue Jan 07, 2014 3:39 pm
No new posts How to access SYSCAT.INDEXES ravikumar15 DB2 6 Thu Apr 25, 2013 1:08 am
No new posts Performance by DB2 with non-unique in... rakesh1155 DB2 10 Wed Mar 14, 2012 12:58 pm
No new posts Why Alternate indexes are not support... Phrzby Phil Mainframe Interview Questions 18 Mon Nov 28, 2011 12:19 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us