how to decide add index on clumn

950724

Hi all

I am getting confious whlie using index

When should I decide add index ?

Balazs Papp

http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#CNCPT811

In general, consider creating an index on a column in any of the following situations:

    The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.

    A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.

    A unique key constraint will be placed on the table and you want to manually specify the index and all index options.

695836

Hi,
When you find unnecessary full table scans consuming high resources, you decide its time for indexing.

950724

It may be data access by the query?

950724

It not depend on data access by the query?

Balazs Papp

http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#CNCPT811

In general, consider creating an index on a column in any of the following situations:

    The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.

    A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.

    A unique key constraint will be placed on the table and you want to manually specify the index and all index options.

bijunator

1. When you find that column used in majority of your queries in the where clause.
2. When you find that majority of the queries using this column in the where clause do not return the whole total records or may be return less than 90% or 80% of the records present in the table.
In such cases adding an index to that column may increase performance to some extent... :-D

Nikolay Savvinov

Hi,

you an index on a column when you expect lots of queries that select a small percentage of rows from the table based on equality and range conditions against that column.

Similarly for combination of columns -- you create a concatenated index when columns are expected to be frequently used in WHERE clause in this combination, and having just
the index on the leading column is not sufficient. I.e. when an index on (a, b, c) provides sufficient selectivity, but an index on (a) or (a, b) doesn't.

Best regards,
Nikolay