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;  

Indexes in DB2 - Part I

In order to get the bottom of any topic, I always prefer getting the answer of  three important questions.

What is it ?
Why we need it?
What if we don't have it?

So, today we are going to understand the topic indexes in DB2 using these questions and try to learn as much as possible.

What is it??

Index is very powerful concept , which uses the pointers to actual data to more efficiently access the specific data item.

DB2 Indexes are the DB2 objects which can be created on columns of the table to speed up the processing of SQL queries and sometimes can also help in uniquely identifying each row in a table.

To give one very practical example of what indexes are , think of indexing in books which you often see either at the start or end of the book catalog.




Why we need it?

As we mentioned above, it helps in speeding up the query processing by allowing DB2 optimizer to choose most optimized path to access the data you are looking for.

It also helps (Depending on type of index) to uniquely identify each single row in your DB2 table.

Lets understand this using our example of books.

Consider any book of 1000 pages and you don't want to read a whole book but a specific information/chapter in that book. what you will do?

Will you start looking/reading from page 1 ,each and every page/line ,until you find the information you are looking for ?  If yes, what is the problem here?

Obviously, It will be very time consuming to get a small piece of information in big book. So here indexing of the books helps us , you can just look for the specific keyword in book index and that will tell you what all the pages in book have that keyword and then you just read those specific pages to find out the exact information you are looking for.

What it did, reduces a lot of time to get to the data and speed up our process of looking information.

DB2 indexes are same, book is DB2 table, pages in book are DB2 pages, lines are rows in a table and specific data you are looking is the value of columns of the DB2 tables.

What if we don't have it?

In above example of books, imaging you don't have indexes. You get the drill, right?

If there is a DB2 table with large amount of data in it and there are no indexes defined on any of the columns, and you fire any simple SELECT SQL query on it.

SELECT EMP_NAME FROM EMP_TABLE
WHERE EMP_NO = '123456'

It will start reading the each and every DB2 page sequentially until it finds the data matching to predicates in your query and it will consume lot of CPU and Elapsed time. With time comes lot of other consequence like if the query is not defined with UR(Uncommitted read) , the locks will be held on tables/pages/rows and other process/program trying to access the same table/data at around same time will have to wait and more likely the waiting process fails with TIMEOUT.

So proper indexing is very important and there are only pros of having indexes.

But in some rare cases there are times when indexing  a table is not the way to go.

Consider a table with very less data.. may be 100 or even 1000 rows. Here query can get you the data faster when there are no indexes.

This is because having index is two way read process, first the indexes have to be read and then the pointers/pages  mentioned by index have to be read and with table having very less rows it is rather faster to read the data sequentially than having indexes.

So consider having indexed when there are tables with large number of rows.

Now, in next post we will cover the answer of next set of questions like how it works??.How indexes work and what are the types of indexes etc.