Disk / Data read increase after putting on an index

I have a small query that runs pretty fast. And somehow I thought adding an index to an unindexed collumn would make it faster but turned out it didn't. In fact, it does increase my disk reads and execution time. What I'd like to ask is can someone explain me a detailed info about how the index works and why it could decrease performance rather than increase it.

Thanks in advance!

PS : My RDBMS : Oracle

Answers


Entirely possible on a small table. If the table is truly small it could be that the table can be read entirely into memory with a single read, and a full table scan can be performed entirely in memory. Adding an index here would require reading at least a single index page, followed by reading the data page, for a doubling of the I/O's. This is an unusual case but not unheard of.

However, this is just guesswork on my part. To truly find out what's going on grab the execution plan for your query with the index on, drop the index, and grab the execution plan without the index. Compare the plans, and decide if you want to re-add the index.

Share and enjoy.


Need Your Help

Sending 64 bit numbers to and from php/MySQL server with JSON?

php mysql json 64bit

I am working on an app that will have to send 64bit numbers back and forth with a php api via JSON which will store and retrieve them from a MySQL database. How do you format them in JSON so as not...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.