Indexes in DB2 - Part II


In this post we will talk more about the types of index and in next post how indexes works.

Theoretically speaking there can be indexes categorized in to various types but we will talk more about the types which matters most to the programmers.

Unique Index  

This is the type of index which ensures that the value in a column or set of column of table is Unique.

So Unique Index is the way using which DB2 ensures that no identical key values are stored in table.

So when the Unique index creation is must??

When you create a table, say EMP_DETAILS which has all employee details, and one of the column of this table is EMP_NO (Employee Number). You , for obvious reasons, want this column to have only unique values so you define this column as PRIMARY KEY.

The definition of this PRIMARY KEY is not complete until you define the UNIQUE INDEX on it.So you must create unique index on this PRIMARY KEY which is the only way DB2 ensures that your PRIMARY KEY will not contain any duplicate values.

Creating Unique index is very simple just add the word UNIQUE to normal CREATE INDEX definition.

CREATE UNIQUE INDEX EMPINDEX
   ON EMP_DETAILS (EMP_NO);

So once this UNIQUE INDEX is created DB2 prevents inserting any value in this table which has existing EMP_NO.

What if program tries to insert the existing value of EMP_NO in this table??

The DB2 throws error and SQLCODE is -803 which is UNIQUE INDEX violation.

Clustering Index

When you define the clustering index on the table, then this index will determine how the rows are physically stored/ordered from table in table space.

Why as a programmer i should be bothered about how the rows are physically stored in table space , you might ask? 

Well ,CLUSTERING  INDEX  provides significant performance advantage in some operations.

Examples :- When there are grouping/ordering operations are performed in programs, it is very beneficial to have clustering index on table.

This is how you create CLUSTERING INDEX on table,just add the word CLUSTER at end.

CREATE INDEX EMPINDEX
  ON EMP_DETAILS
    (EMP_NO ASC) 
     CLUSTER;  

15 comments:

Unknown said...

can you please post how indexes work. it will be helpful please.
all your topics are clearly understandable

Unknown said...

Thanks for the information. Also, visit Eduinformer for more resources.

Clipping Path Service said...

Beautiful site for these post and i am seeing the most of contents have useful for my bussinces.Thanks to such a useful information.Any informationClipping Path ServiceThanks for You Sharing.

Jennifer Tuffen said...

Such A Great Article. Thanks
http://techobar.mystrikingly.com/

Alexa james said...

Thanks, This One Is Really Amazing. Also, I created One Video On Sofa Reviews And Guides.
So, if you are planning to buy sofa, then make sure that you check it out.https://sofareviewsguides.blogspot.com/

Alexa james said...

Such A Nice Article. I loved it.
Also, I created one website in which i publish some articles related to Sofas. So, Make sure that you will check it out. Click Hereto checkout my website here.

Alexa james said...

Great Share. Thanks for this. I loved it.
Also, I created one website on Sofa Reviews. So if you want to purchase some sofa, or looking for new sofas, then here is the link to my website Click Here for more information related to Sofas

Alexa james said...

This article is awesome. meanwhile I have also written one article on Monitors and technology. So if you want to check it out then you can click here for more information related to Monitors

Jeffery Weight said...

This article is awesome. meanwhile I have also written one article on Monitors and technology. So if you want to check it out then you can Click here for more information related to Monitors

HOFSA said...

This article is awesome. meanwhile I have also written one article on Monitors and technology. So if you want to check it out then you can click here for more information related to Monitors

Rio Williams said...

https://anchor.fm/sofabuyingguides
https://edex.adobe.com/member/Yw9edzEsJ
https://flgclassifieds.cce.cornell.edu/author/sofabuyingguides/
https://themeforest.net/user/sofabuyingguides

BINOD said...

Hey, Thanks for this detailed article. It really helps me a lot. Searching for good pc monitor is really a touch task. I found one good articles over internet. You can check them out.

Benq GW2480 Review
Best frameless monitors
best csgo monitors
best gaming monitors

ed medication said...

Inspiring story there. What occurred after? Good luck!

EssayExperts said...

essaygator is the top
assignment help experts
company thier experts is very qualified so if you think where i find best experts so essaygator is the best

pg slot said...

pg slot th เป็นเกมออนไลน์ ที่เกมบันเทิงใจยอดเยี่ยม เล่นแล้วได้จริง เครดิตฟรี 50 เล่นแล้วร่ำรวยทำให้คุณมั่งคั่งขึ้นได้ PG SLOT แค่เพียงคุณร่วมบันเทิงใจกับ สล็อต ต่างๆที่มีให้เล่น ทดลอง เล่น ฟรี ได้แล้ววันนี้

Post a Comment