Forum Stats

  • 3,751,479 Users
  • 2,250,366 Discussions
  • 7,867,435 Comments

Discussions

how to decide add index on clumn

950724
950724 Member Posts: 78
edited Aug 28, 2012 6:50AM in General Database Discussions
Hi all

I am getting confious whlie using index

When should I decide add index ?
Tagged:

Best Answer

  • Balazs Papp
    Balazs Papp Member Posts: 748 Gold Badge
    Accepted Answer
    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.

Answers

  • 695836
    695836 Member Posts: 785
    Hi,
    When you find unnecessary full table scans consuming high resources, you decide its time for indexing.
  • 950724
    950724 Member Posts: 78
    It may be data access by the query?
  • 950724
    950724 Member Posts: 78
    It not depend on data access by the query?
  • Balazs Papp
    Balazs Papp Member Posts: 748 Gold Badge
    Accepted Answer
    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
    bijunator Member Posts: 40
    edited Aug 28, 2012 6:36AM
    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
    Nikolay Savvinov Member Posts: 1,860 Silver Trophy
    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
This discussion has been closed.