Sunday, September 6, 2015

An Index can change your Life... ehh...Query I mean....

Most of the time I get a question from Co-developer asking “Hey Bridewin, I am stuck with a query… I have written it but output is coming slow?!?... can you look into it?”

I look at them with a smile. I always ask a question, have you written it correctly? If yes is the answer then what could be wrong? Why is the output coming so slow?

What will I do to bring the output quickly? Definitely I won’t be coming and doing any magic. Well I would be carrying out some standard steps.

  1. Check the query to see if it is bringing some unnecessary data.
  2. Check execution plan.
  3. Are the necessary Indexes present in all the required columns of “Where” clauses?

In this article I would be focusing on Indexes because most of the time 2 to 3 years experienced developers are always stuck with Indexes.

Prior to creating indexes make sure you have written your query in ANSI 92 compliant or it would also be fine if you have written Oracle specific queries or SQL server specific queries.

The advantage of writing ANSI 92 compliant query is that any (Oracle/ SQL Server/ Sybase/ MySQL) DBA can help you out with the query if you get stuck with optimization problem.

If you write Oracle specific query or SQL Server specific query then only Oracle DBA/Query Tuner or SQL Server DBA/Query Tuner can help you out.

Now let’s get back to the actual topic Indexes…

So firstly let’s understand why do we need an Index?

Yes let’s ask this question in a different way to our self… why do we need an Index in a book may be text book… Yes, so that we can quickly browse through that particular page instead of scanning through the entire book... Similarly in a table instead of scanning through all records we can simply point to a particular record using Index.

Now the second question would be there are plenty of Indexes available in a database which one do I choose?

Correct if you are up to creating an Index in a table creating a right index is very necessary otherwise it would be of no use! Your query may run slower than now or may show no performance improvement. Now Indexes are database dependent, names and type of indexes vary from database to database.

I will list down the required Indexes for SQL Server and Oracle and its uses one by one…

SQL Server:
  1. Clustered Index- This is useful when you have a unique data in a particular column something like a column having primary key. Normally a column with primary key has clustered index.
  2. Non-Clustered Index- This is useful where a table has a clustered Index and some query is going to be executed frequently. Then Non-clustered index can be created on other column.
  3. Unique Index- This is useful where there is ample of unique data very similar on the lines of having unique keys.
  4. Column Store Index- This is useful where there is high use of read select query and large amount of data is retrieved. Normally used in data warehouses.
  5. Computed Index- This is useful when the data of the column is computed like (Column 1 * Column 2 = Column 3) here column 3 can have computed index.
  6. Spatial Index- This useful in GIS databases where data type of the column is Geometry.
  7. XML Index- This is useful where the column has XML datatype.
  8. Full-Text index – This is useful where we want to do word search on a particular column having large data, containing lot of text etc…


In Oracle indexes are not that complicated compared to SQL server.

  1. B-Tree Index- This is useful where there are lot of data in particular column. If you know that data in the column is going to be unique then use Unique keyword while creating the index this will optimum result.
  2. Function based Index- Oracle does not have any restriction like SQL server it allows to create index on Functions. This is useful where functions are going to be applied on particular column something like upper/lower etc.. This Index is not suitable for Geo spatial (GIS) database.
  3. Bit map Index- This is useful where there is large amount of data and ad hoc queries, but a low level of concurrent DML statements.

After creating the required indexes on daily basis statistics need to be gathered, otherwise these indexes would be of no use and performance of query will gradually decrease.

Now after reading and understanding you will be able to choose the correct index for your table and improve the performance of your query.


No comments: