Oracle Index and Like clause

Topic: Beginners Level

From time immemorial there has been debate over the usage of like clause and its association (or rather non-association with index). What is the fuzz all about? Let's check here.

Like clause allows you to use wildcard character searches over data stored in oracle database. By this means we can do pattern searching over the existing data.

For example:
You have a daily meeting calendar in which the attendees names are stored in comma seperated values in a VARCHAR2(2000) field. You want to search on what days a particular attendee say for instance Rose has attended the meeting.

table: meeting_schedule
fields:
meeting_date date
meeting_place varchar2(200)
meeting_attendees varchar2(2000)

In such a case of searching, without the usage of wildcard characters such as % will not yeild appropriate results.

The query for such a situation would be:

SELECT meeting_date, meeting_place
FROM meeting_schedule
WHERE meeting_attendees like '%Rose%';

Now the above query will list the meeting_date and meeting_place where Rose has attended. Of course this is a simple usage of LIKE clause.

The Like clause is valid to be used in any DML statement such as SELECT, INSERT, UPDATE and DELETE.

Now what is that links LIKE clause so much with the indexes in Oracle?
Indexes as you may know are like indexes in the book where it points to a page in the book. The same way Oracle indexes point to a data location. Both Indexes and Tables contain two different copies of data, but Indexes contain far less data compared to the tables. Think of Indexes as containing distinct values of a row in a table. So if there are more and more repetitive rows in a row, then accessing Index will prove to be lot faster than accessing the table.

So by this you must have understood where an Index would help. An Index should be used only in such cases, so that it can improve performance. If otherwise used in all places it will degrade the performance.

How can an Index degrade the performance?
By using an index side-by-side of a table, we are agreeing that it has some extra work to do. Like at every insert, update or delete to the table, the index also needs to be updated. This extra work can prove fatal if there are lot of rows in the index.

So as a simple way to know where to use index, if each row of a table is different from that of other row and there are no distinct values do not use indexes. Only use indexes if atleast there are 25% distinct rows available in the table.

Of course there is no such hard core rule as to when to use Index and when not to use. It just depends upon the situation.