Oracle Scratchpad

Indexing Strategies

Back to main Tutorials Page

Indexing Strategies – Agenda

One of the most important, yet overlooked, aspects of efficiency is the need for a good indexing strategy.

Indexes improve the speed of queries, but increase the cost of DML. Given a thorough understanding of indexes you have the option for deciding the optimal set of indexes to suit the needs of your application. Everything you might want to know about indexes will be in this tutorial.

Session 1
1.5 hours

The Structure of Indexes

What is a B-tree index, what is a Bitmap index and why are the two so different? How does Oracle build indexes? Are there differences in reverse indexes, function-based indexes, cluster indexes, global partitioned indexes, indexed organized tables, and secondary indexes? With the aid of a few block dumps and carefully monitored test results we examine the way that different types of indexes are built and behave.

Break – coffee and informal discussion: 30 minutes
Session 2

1.5 hours

The Uses of Indexes

How many different ways can Oracle take advantage of an index. We have primary keys and unique keys - do we need unique indexes to enforce them. We may have problems with foreign keys if we don’t have related indexes. We need optimal access paths – but will indexing help and what will it cost? When will Oracle use an index to accelerate a query? Can we assist performance by understanding the mechanisms.

Break – Lunch and informal discussion: 1 hour
Session 3

1.5 hours

Popular misconceptions

There are several surprising ideas floating around the Internet about how Oracle indexes work. Sometimes these ideas are irrelevant and have no use or impact. Sometimes they have a serious impact on your effectiveness. They may cause you to do work that need not, or should not, be done. They may introduce down-time that you do not need. They may make you avoid a feature that is really the perfect feature for your application. This session examines some of the more popular and potentially damaging misconceptions about indexes.

Break – coffee and informal discussion: 30 minutes
Session 4

1.5 hours

Maximum benefit, minimum cost

We all know that indexes are supposed to make things go faster – but sometimes we forget that they have maintenance costs – of two types. How do you identify the optimal set of indexes for your application? How do you identify redundant indexes? How do you check whether or not an index has become inefficient and needs to be rebuilt – and how do you rebuild it most cost-effectively? This session discusses strategies for ensuring that you don’t waste effort supporting indexes that shouldn’t exist, and don’t waste valuable batch time rebuilding indexes that don’t need it.

 

Back to main Tutorials Page

Leave a Comment »

No comments yet.

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,113 other followers