Index:

*The index is a DB object that makes data to fast retrieval, for ex. let us consider a book, in that we can see index page .it is use to find a particular topic can locate fastly as the page the index in database use to locate the data fastly.

*Basically the index created on the column and that column is called index key.

*Ex: if we are going to create index for emp.salary or job (so index created on column) here the column is called index key

Types of index:

*In oracle database indexes are categories into two types these categories are based on the structure internally created

1)BTree Indexes - if the index is B tree index internally one tree structure is created

2)BITMAP indexes - if the index is Bitmap index internally one structure is created that contains bits

B Tree Index:

Create Index syntax:
Create index index name on table(column);


SQL> create index hr_employees_sal on hr.employees(salary);

Index created.

In above hr_employees_sal index name

(Note: hr.employees = schema name.table name
salary=column)

*Based on the column search we  can create the index

(Note: In above we never mentioned what type of index it is? basically, if we never mentioned the type while creating the index it .by default Oracle will create B tree index)

*The purpose of creating index is  to retrieve the data very fast manner.   ex:if we have the user HR want to get the information of the person who is getting salary 3000 in normally they write a query

SQL> select * from hr.employees where salary=3000;

*After hitting the query sometimes it ll take more time to search and retrieve the data, so the performance of a database will be affected.

*To solve this issue we are creating the index to improve the performance of database

*In my hr.employess table salary column have

Salary

5000

2000

2500

3000

1500

4000

3300

 

 

Note: Here the star contains rowid (value =no.of stars ex.1000=1000 stars) ex:1000 values = address of the salary  contains 1000

*Let us we assume we need the details one who getting salary 3000  after entering this query it ll goes to oracle server

 

Oracle server can access the data by two methods I)table scan II)Index scan

I)Table scan method:

*If we need the details of employee list one who getting salary=3000

*let us assume if the index is not available then it ll search each row whether the condition is true or False (it ll search row by row)

This search is called "Linear search".

(Note: Suppose the table contains millions of row the data retrieval is slow and it ll affect the database performance)

II)Index Scan Method:

*If suppose we salary column have the index it 'll go to index node and compare the condition (here the condition is less than value it goes to left side , if the value is greater than value it goes to right side of the index node)

 

 

*In our example, we need employee details one who getting salary=3000

*it 'll go the first index the value is equal to 3000 so it ll goes the right pointer now the value is 4000 our condition value is 3000 so it goes on the left  pointer to the data node

*Now the data pointer values match the condition more over it has two records of in this table so it fetches the data and retrieve it

 

why the index is faster than table scan?

*In our case, the data retrieval is 3 search index scan but the table scan have each comparison all the rows

*Here  we would know index makes data retrieval faster so the number of comparisons are reduced then performance will be improved

How Oracle can search the data?

*if we want to know how Oracle can search the data through index or table scan ,for this doubt, we can use execution plan


SQL>Set Autotrace on

SQL> select * from hr.employees where salary=3000;


EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL

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

----

PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_ID D

EPARTMENT_ID

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

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

        187 Anthony              Cabrio                    ACABRIO

650.509.4876         07-FEB-07 SH_CLERK         3000                       121

          50


        197 Kevin                Feeney                    KFEENEY

650.507.9822         23-MAY-06 SH_CLERK         3000                       124

          50




Execution Plan

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

Plan hash value: 3772135169


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

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

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%

CPU)| Time     |

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

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

|   0 | SELECT STATEMENT            |                  |     2 |   138 |     2

 (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     2 |   138 |     2

 (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | HR_EMPLOYEES_SAL |     2 |       |     1

 (0)| 00:00:01 |

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

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


Predicate Information (identified by operation id):

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


   2 - access("SALARY"=3000)

(Note in above explain the Oracle can retrieval data using index scan method the id is "HR_EMPLOYEES_SAL")

*if we do salary>=300 and salary <=3000 condition it ll use same index scan method.

 

*if we search the employee details based on the firstname oracle will use the index of this table or how it 'll serach the data let us we check

SQL> select * from hr.employees where first_name='Hermann';


EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL

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

----

PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_I

EPARTMENT_ID

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

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

        204 Hermann              Baer                      HBAER

515.123.8888         07-JUN-02 PR_REP          10000                       10

          70




Execution Plan

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

Plan hash value: 3033625502


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

-----------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CP

 Time     |

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

-----------

|   0 | SELECT STATEMENT            |             |     1 |    69 |     2   (

 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |    69 |     2   (

 00:00:01 |

|*  2 |   INDEX SKIP SCAN           | EMP_NAME_IX |     1 |       |     1   (

 00:00:01 |

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

-----------


Predicate Information (identified by operation id):

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


   2 - access("FIRST_NAME"='Hermann')

       filter("FIRST_NAME"='Hermann')





Note: oracle will search and retrieval the data using full table scan access method

Source: Google