View previous topic :: View next topic
|
Author |
Message |
sangiah
New User
Joined: 10 Jun 2005 Posts: 62
|
|
|
|
what is clustering and non-clustering indexes |
|
Back to top |
|
|
dbaprasad
New User
Joined: 06 Jun 2005 Posts: 3 Location: pune
|
|
|
|
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 |
|
|
sudheer648
New User
Joined: 23 May 2005 Posts: 97 Location: Chennai
|
|
|
|
Hi dbaprasad,
Could give brief it bit more what is difference btn clustering and non-clustering indexes. |
|
Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
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 |
|
|
dbaprasad
New User
Joined: 06 Jun 2005 Posts: 3 Location: pune
|
|
|
|
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 |
|
|
sudheer648
New User
Joined: 23 May 2005 Posts: 97 Location: Chennai
|
|
|
|
Good Explanation...Thanx
|
|
Back to top |
|
|
MidasTouch
New User
Joined: 16 May 2005 Posts: 10 Location: The 'SYSTEM'
|
|
|
|
When would it make more sense to use a clustered index and when would a non clustered index be better?? |
|
Back to top |
|
|
brganeshbabu
New User
Joined: 23 Jun 2005 Posts: 34 Location: Chennai
|
|
|
|
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 |
|
|
Cathy123
New User
Joined: 07 Jul 2006 Posts: 4 Location: Chennai
|
|
|
|
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 |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
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 |
|
|
|