How to decide to create index on column?
Suppose a table has 30 columns.
Sometimes my query is running fast ,sometimes may not?
why?any reasons?
However, the presence of an index on a column does not guarantee that it will be used. Among the factors that can prevent an index from being used, are:
· The indexed column is used in mathematical operations:
where salary = 100 + 50
CREATE UNIQUE INDEX EMPLOYEES_PK
ON EMPLOYEES (deptno, empno)
Both columns together are still unique so queries with the full primary key can still use an INDEX UNIQUE SCAN but the sequence of index entries is entirely different. The DEPTNO has become the primary sort criterion. That means that all entries for a subsidiary are in the index consecutively so the database can use the B-tree to find their location.
Even though the two-index solution delivers very good select performance as well, the single-index solution is preferable. It not only saves storage space, but also the maintenance overhead for the second index.
The fewer indexes a table has, the better the insert, delete and update performance.
x
