Decision when to create Index on table column in database?

Question

I am not db guy. But I need to create tables and do CRUD operations on them. I get confused should I create the index on all columns by default or not? Here is my understanding which I consider while creating index.

Index basically contains the memory location range ( starting memory location where first value is stored to end memory location where last value is stored). So when we insert any value in table index for column needs to be updated as it has got one more value but update of column value wont have any impact on index value. Right? So bottom line is when my column is used in join between two tables we should consider creating index on column used in join but all other columns can be skipped because if we create index on them it will involve extra cost of updating index value when new value is inserted in column.Right?

Consider this scenario where table mytable contains two three columns i.e col1,col2,col3. Now we fire this query

select col1,col2 from mytable

Now there are two cases here. In first case we create the index on col1 and col2. In second case we don't create any index.** As per my understanding case 1 will be faster than case2 because in case 1 we oracle can quickly find column memory location. So here I have not used any join columns but still index is helping here. So should I consider creating index here or not?**

What if in the same scenario above if we fire

select * from mytable

instead of

select col1,col2 from mytable

Will index help here?

Solution 1

but update of column value wont have any impact on index value. Right?

No. Updating an indexed column will have an impact. The Oracle 11g performance manual states that:

UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes and data in tables. They also create additional undo and redo.

---

So bottom line is when my column is used in join between two tables we should consider creating index on column used in join but all other columns can be skipped because if we create index on them it will involve extra cost of updating index value when new value is inserted in column. Right?

Not just Inserts but any other Data Manipulation Language statement.

Consider this scenario . . . Will index help here?

With regards to this last paragraph, why not build some test cases with representative data volumes so that you prove or disprove your assumptions about which columns you should index?

Solution 2

Don't create Indexes in every column! It will slow things down on insert/delete/update operations.

As a simple reminder, you can create an index in columns that are common in WHERE, ORDER BY and GROUP BY clauses. You may consider adding an index in colums that are used to relate other tables (through a JOIN, for example)

Example:

SELECT col1,col2,col3 FROM my_table WHERE col2=1

Here, creating an index on col2 would help this query a lot.

Also, consider index selectivity. Simply put, create index on values that has a "big domain", i.e. Ids, names, etc. Don't create them on Male/Female columns.

Solution 3

In the specific scenario you give, there is no WHERE clause, so a table scan is going to be used or the index scan will be used, but you're only dropping one column, so the performance might not be that different. In the second scenario, the index shouldn't be used, since it isn't covering and there is no WHERE clause. If there were a WHERE clause, the index could allow the filtering to reduce the number of rows which need to be looked up to get the missing column.

Oracle has a number of different tables, including heap or index organized tables.

If an index is covering, it is more likely to be used, especially when selective. But note that an index organized table is not better than a covering index on a heap when there are constraints in the WHERE clause and far fewer columns in the covering index than in the base table.

Creating indexes with more columns than are actually used only helps if they are more likely to make the index covering, but adding all the columns would be similar to an index organized table. Note that Oracle does not have the equivalent of SQL Server's INCLUDE (COLUMN) which can be used to make indexes more covering (it's effectively making an additional clustered index of only a subset of the columns - useful if you want an index to be unique but also add some data which you don't want to be considered in the uniqueness but helps to make it covering for more queries)

You need to look at your plans and then determine if indexes will help things. And then look at the plans afterwards to see if they made a difference.

Comments

[Twisted Code]

can you clarify what "covering" means? I am a student and relatively new to SQL/DB design

[Cade Roux]

@TwistedCode if an index is covering, it means that all the columns needed are in the index, so a lookup or other method is not needed to bring in other columns from the base table. Similarly with something like an indexed view. Or even indexes on two different tables. Together they might be used and the base tables never accessed (on the select).