|
Re: What are the best practices with indexes? [message #485885 is a reply to message #485882] |
Fri, 10 December 2010 15:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
DataMouse wrote on Fri, 10 December 2010 21:17
After reading the section, it seems that there are only positive impacts of using an index, so why are they not automatically created?
Because they take disk space. Because oracle has to modify them whenever you do dml on related data thus slowing it down. Because they aren't always useful.
DataMouse wrote on Fri, 10 December 2010 21:17
Is there any reason to NOT use an index?
If you need to retrieve a large proportion of the data from a table using an index becomes less effiecient then just reading the table directly and oracle will just ignore the index in these cases.
Think of them in the same way as the index in a text book or encyclopedia - if you want to look up a couple of things you'll use the index to find them, if you want to read a whole chapter or more you won't bother with it.
DataMouse wrote on Fri, 10 December 2010 21:17
What is the general best practice with indexes?
Index selective columns that are used in the where clauses of a lot of queries. Don't bother with anything else unless you find a slow query that you determine would be improved with an index.
|
|
|
|
|