What is an index in oracle?

An index helps speed up retrieval data from large table. A column that is frequently referred to in the WHERE clause is a potential candidate for indexing.

Example EMP TABLE   has 100000 rows.

ID	SALARY
1	20000
2	5000
3	1000
………..
100000	1500

Figure 1.1

SELECT * FROM EMP WHERE SALARY=1000;

 Above this query which will return only on row if you create index on the “EMP” table then performance will be  increased  Reason of performance enhancement is that when you create index at that time table is  created by oracle  in which indexed column example here ”salary” will be sorted order and it’s rowid  as shown below Figure 1.2. When you fire query “SELECT * FROM EMP WHERE SALARY=1000” it first check in indexed   table and retrieve rowid from indexed table matched it with  “EMP” table  rowid and return row. Now full scan of “EMP” table no need .This is the Advantage of index.

CREATE INDEX IND_SAL ON EMP (SALARY);

SALARY	ROWID
1000	AABUZkAAGAAEFCbAAA
1500	AABUZkAAGAAEFCbAAB
5000	AABUZkAAGAAEFCbAAC
20000	AABUZkAAGAAEFCbAAD

Figure 1.2

SELECT * FROM EMP WHERE SALARY=1000;

Determining when to create an index:

Create an index if you frequently want to retrieve less than 15% of the rows in a large table.

Small tables do not require indexes.

Limit the Number of Indexes for Each Table.

A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated.

Explain Plan.

 Before execute the query ,please check explain plan . To check explain plan of the query write click on the query and click on explain in pl sql developer. If index is not called automatically then we have to manually call.

BEFORE INDEX  CREATE ON EMP TABLE

Plan

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

SELECT STATEMENT TABLE ACCESS FULL  EMP

AFTER INDEX CREATE ON EMP TABLE      

Plan

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

SELECT STATEMENT

 TABLE ACCESS BY INDEX ROWID EMP

  INDEX RANGE SCAN IND_SAL                     

Type of index

·        B*Tree Indexes

·        Bitmap index

·        Function base index

·        Unique index

·        Composite index

Examples are As Under

ð  Select a.id,a.name,b.address from emp a,dep b where a.id=b.id;

Create index ind_emp on emp (id);

                Create index ind_dep on dep (id);

ð  Select  *  from emp where upper(name)=’ROHIT’;

Create index ind_emp on emp(upper(name));

ð  Select * from emp order by id;

Create index ind_emp on emp (id);

ð  Select * from emp where id=5 and name=’rajesh’;

Create index ind_emp on emp (id,name);

ð  Select a.id,b.address from emp a,dept b where a.id=b.id group by a.id,b.address;

                Create index ind_emp on emp (id);

                Create index ind_dept on dept (id);

ð  select  *  from emp where id>=5;

Here id should be displayed which values greater than 5; create index as below and manually define index for scanning as under query.

Create index ind_emp on emp (id);

Select  /*+  index(emp ind_emp) */   id, name from emp  where id>=5;

ð  SELECT * FROM EMP WHERE SALARY IS NULL;

SELECT * FROM EMP WHERE  (nvl(SALARY,0))=0;

create index

    emp_null_emp_nbr_idx

on

   emp

   (nvl(ename,o));

SELECT * FROM EMP WHERE  (nvl(SALARY,0))=0;

Creating an index with NOLOGGING has the following benefits:

                Space is saved in the redo log files.

                The time it takes to create the index is decreased.

                Performance improves for parallel creation of large indexes.

ð  Create index ind_emp on emp (id) NOLOGGING;

 

Viewing Index Information

The following views display information about indexes:

ð  USER_INDEXES

ð  USER_IND_COLUMNS