Minnu's Blog on Informatica & Data warehouse concepts

Archives

Saturday, May 12, 2007

Indexes

free html hit counters
http://www.hit-counter-download.com/

An index is like a set of pointers to specific rows in a table. These pointers are ordered in terms of the column(s) defined by the index, which makes SQL's scans much more efficient - they just look up the pointers to the rows with the relevant data (based on a WHERE or other clause), and jump right to the row(s).

If a table is created with no indexes, the data rows are not stored in any particular order. This structure is called a heap


Lets assume the following sample table stored in heap format

a b c
-------
1 2 3
2 2 2
1 2 2
3 1 2
2 1 2


If we create an index based on all 3 coloumns a, b & c

a b c
-----
1 2 2
1 2 3
2 1 2
2 2 2
3 1 2

So now, imagine running a query SELECT a,b,c FROM table WHERE a=1. This query will be very efficient, because the pointers have grouped these records all together.

Now, imagine running a similar query, but this time SELECT a,b,c FROM table WHERE b=1. This query will not be very efficient, since SQL Server's only index option is this index which does NOT consider b to be a top priority (and it just so happens that these records are NOT grouped together). It's the jumping around on the pointers that makes SQL Server work harder to get all the rows that match the WHERE clause - in some cases it may be more efficient for SQL Server to just do a table scan, rather than care about your index.

Let's create multiple indexes now, one on each column. The pointers for each, in order, will look something like this:


a b c
-----
1 2 2
1 2 3
2 1 2
2 2 2
3 1 2


a b c
-----
2 1 2
3 1 2
1 2 2
1 2 3
2 2 2


a b c
-----
1 2 2
2 1 2
2 2 2
3 1 2
1 2 3

Now, running the two queries mentioned above, each will be very efficient. In the first query, SQL Server will choose the first index, and get all the rows where a is grouped together - minimizing read / scan time. In the second query, SQL Server is smart enough to ignore the first index, and use the second index instead.

Indexes not only speed up the retrieval of rows for selects, they also usually increase the speed of updates and deletes. This is because SQL Server must first find a row before it can update or delete the row. The increased efficiency of using the index to locate the row usually offsets the extra overhead needed to update the indexes, unless the table has a lot of indexes.

What index you use and what will work best depends on your schema, the nature of your queries, where your performance counts, the load on your system, hardware, levels of transactions, acceptable query times, type of application, etc.


There are two ways to define indexes. The CREATE INDEX statement creates and names an index. The CREATE TABLE statement supports the following constraints that create indexes:
  • PRIMARY KEY creates a unique index to enforce the primary key.
  • UNIQUE creates a unique index.
  • CLUSTERED creates a clustered index.
  • NONCLUSTERED creates a nonclustered index.

To create an index in Oracle, use the syntax:

create [unique] index on ();

In general, could contain more than one attribute. Such an index allows efficient retrieval of tuples with given values for . The optional keyword UNIQUE, if specified, declares to be duplicate-free, which in effect makes a key of .


To get rid of an index, use:

drop index ;;


Oracle automatically creates an index for each UNIQUE or PRIMARY KEY declaration. For example, if you create a table foo as follows:

create table foo (a int primary key,
b varchar(20) unique);

Oracle will automatically create one index on foo.a and another on foo.b. Note that you cannot drop indexes for UNIQUE and PRIMARY KEY attributes. These indexes are dropped automatically when you drop the table or the key constraints

To find out what indexes you have, use

select index_name from user_indexes;

USER_INDEXES is another system table just like USER_TABLES. This can become especially helpful if you forget the names of your indexes and therefore cannot drop them. You might also see weird names of the indexes created by Oracle for UNIQUE and PRIMARY KEY attributes, but you will not be able to drop these indexes.

--------------------

Tuning Indexes http://docs.rinet.ru/O8/ch18/ch18.htm


http://www.oracle.com/technology/pub/articles/sharma_indexes.html

http://www.devx.com/dbzone/Article/26995

The ordered hint requests that the tables listed in the FROM clause of a SQL statement be joined in the order specified, with the first table in the FROM clause specifying the driving table. The driving table should be the table that returns the smallest number of rows. Using the ordered hint will bypass the very expensive and time-consuming parsing operation and speed the execution of Oracle SQL.

1 comments:

Anonymous said...

Cool Post