I'm not a database expert here and I just learned this tip today.
If you have a large table and one or more columns are often used in the where clause, you might want to consider setting up index for those columns.
But one thing to note, in oracle (maybe for other database also), if you're doing a calculation on the column in the where clause, your index isn't used by the optimizer.
e.g. where column * 12 > 1000
My friend today was trying to optimize a query, by introducing an index, the result was awesome, instead of doing a full table scan (the size around 60 something mb), the optimizer use the index thereby reducing the cost and the time consumed.
One other option that was set is to allow the database engine to execute parallel queries on the table.
But if we setup too many indices, will that make inserting record to the table much slower?
Subscribe to:
Post Comments (Atom)
2 comments:
It depends in what you do more on your system, if you're querying much more than inserting, add all the indexes you need, if you have a system, where insetr is more common (and important) than querys denormalize and reduce index count.
If you are using always the same calculation in your where clause i belive you can use a function based index
Another input, thanks a lot!
I would certainly take that into consideration.
Thanks!!
Post a Comment