Oracle Vs MS-SQL: INDEX

In continuation with the topic 'Oracle Vs MS-SQL : Views', I would like to say about indexes before taking you to the materialized views, because materilazied views use the Index. Index is not a new concept or strictly database concept. It is similar as book index which helps to find out a paricular topic instead of searching all the pages to find out the topic. Index in database is used to improve the performance of the query. Index concept is there in both oralce as well as  MS-SQL.  Indexes are most useful on larger tables, on columns that are likely to appear in 'where ' clauses. We can create index on multiple columns and it is better to create less number of index with multiple column instead of creating index on each columns. If we create more index on the table then the DML operation will take longer time to execute.

There are two types of Index, first one is Btree and the other is Bitmap, but MS-SQL does not support Bitmap seraching.

Btree
A B-tree consist of a root node that contains a single page of data, zero or more intermediate levels containing additional pages and leaf level.

MS-SQL have two types of Btree index one is Clustered and the other is Non-Clustered Index.

Columns you defined for a clustered index are called clustering key. Only one cluster index is possible per table because it sorts the data physically. Actually a clustered index causes the rows in a table as well as the data pages in the doubly linked list that stores all the table data to be ordered according to the clustering key.

In contrast with clustered index, a non clustered index does not force a sort order on the table. We can create multiple non clustered index per table. Unlike a clustered index, in a non clustered index the leaf level of the index contains a pointer to the data instead of actual data.  If the table has clustered index, the pointer points to the clustering key and it does not have pointer points to a relative identifier (RID), which is a reference to the physical location of the data within a data page.

Fig 1

Oracle supports below mentioned Btree indexes

• Unique
• Functional
• Cluster

 Unique index can create only on those fields which are declared as unique. Any query that performs a function on a column generally does not use that index of the coloum. So in that case we can create index using the function to make use the index in the query which uses function.

E.G

CREATE INDEX indx_name on table(UPPER(colmun_name))

Clustering is the method of storing tables that are intimately related and often joined together into the same area on disk.

Bitmap

For bitmap index  field, there will be entry in the index level for each bitmap field value. For e.g consider bitmap index on rank field and there are 3 ranks inserted into the table level. Number of records for rank 1 is 3 and for rank 2 is 2 and finally for rank 3 is 2. In this case Index will look like fig 2

Fig 2

Bitmap indexes are used more on those fields which are less likely to be unique and more often it will be for flag values. It should not be used for tables involved in online transaction processing applications due to the internal mechanisms that oracle uses to maintain them. Restrict their usage to tables involved in batch transactions.

Syntax

CREATE BITMAP INDEX bit_name ON table_name(field_name)

SQL Server 2008 introduces new Index which is known as filtered Index.Filtered indexes basically allow you to create a filter on an index – think WHERE clause. The index B-Tree will only contain the rows of data that meet the filtering criteria. This allows you to reduce the amount of data contained in an index which means that you are also reducing the data affected in an index by data modifications.

Syntax

CREATE NONCLUSTERED INDEX indx_Name ON Table(ClnDate) INCLUDE (clnSession)

WHERE colDate > DateFilter