What are good candidates for creating indexes on beside pk and unique columns?

Question

I'm always asking myself should I create an index or not in my Oracle tables, what criteria in a table makes the index worth creating? And is it also a criteria in the query? And are there (with respect to Oracle) different kinds of indexes you can create based on some factor(s)?

Answer 1

It's all about what queries are going to run against the table, how fast they're going to be, how fast you need them to be and how often they're going to be run. If you need to be able to find records quickly based on it, index it.

Answer 2

Your queries.

You must look or better yet profile your client app code to see what fields are being used most often and /or have performance issues. If your app is not yet built, think about how the data is going to be used. For instance; If it is a table that will be used heavily for reports, and you have a datetime field, you most likely need an index (compound or not) on that field.