How Indexes improve performance?

[dilippanda]

Hi,

Can you please explain me how indexes improve performance?
And where to use which index?

A link will also be useful for me.

Thanks,
Dilip

[debasisdas]

An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.


The syntax for creating a index is:

CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];

UNIQUE indicates that the combination of values in the indexed columns must be unique.

COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

We could also choose to collect statistics upon creation of the index as follows:

CREATE INDEX emp_namesal_ind ON emp (ename, sal) COMPUTE STATISTICS;

[debasisdas]

You can also follow this link for further details.

[dilippanda]

Thank you very much.

You can also follow this link for further details.