Select Page

Effective Database Indexing Can Optimize Operations

John Kaufling | | October 30, 2013

Indexing is important for a database administrator since it ultimately contributes to database performance and productivity, as well as user satisfaction.  An effective database indexing strategy will ensure that the index stores data in the database logically and can potentially improve a user’s search performance; however, the wrong index may render the process useless.

Indexes can also be used for optimizing other operations. Their structures can be either clustered or non-clustered indexes. Indexes can also be created across partitions. These choices can contribute to the database’s performance.

“Indexing drives query performance. Understanding how indexes work and implementing sound database indexing techniques enables you to improve database response time and end-user experience,” says Jeff Garbus, author and consultant on database architecture. “Proper indexing boosts performance and ultimately, your bottom line.”

Indexing is frequently a task for developers. As Markus Winand, author of books on SQL performance issues such as SQL Performance Explained and Use the Index, Luke, explains:

“Database indexing is, in fact, a development task. That is because the most important information for proper indexing is not the storage system configuration or the hardware setup. The most important information for indexing is how the application queries the data. This knowledge — about the access path — is not very accessible to database administrators (DBAs) or external consultants. Quite some time is needed to gather this information through reverse engineering of the application: development, on the other hand, has that information anyway.”

Still, because indexing has a direct affect on performance, it is incumbent upon the administrator to review existing indexing and make some fine-tuning through periodic changes, such as removing a redundant index. Kimberly L. Tripp writes in The Accidental DBA series:

“The unfortunate thing about indexing is that there’s both a science and an art to it. The science of it is that EVERY single query can be tuned and almost any SINGLE index can have a positive effect on certain/specific scenarios. What I mean by this is that I can show an index that’s ABSOLUTELY fantastic in one scenario but yet it can be horrible for others. In other words, there’s no shortcut or one-size-fits-all answer to indexing.”

As with any changes to the database, comprehensive testing is needed to make certain they don’t cause any problems in the performance for end users. What are your thoughts? Let us know, we’d love to hear from you.

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine over 100 logins on the source server, you need to migrate them to the destination server. Wouldn’t it be awesome if we could automate the process?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.