Index overrides and DB2 lookup performance

When the index contains all the columns in the search, we usually say that the index contains the search, and whenever this happens, the DB2 (DB2 certified DB2 training) optimizer typically chooses to access only the indexes required for the lookup, known as pure Index access or index overrides. But "usually" does not mean "always". For example, let's consider the following chart structure:

Createtablecontact (

Zipcodeintnotnull,

Phone_numberchar (Notnull),

Some_other_stuffvarchar (100));

Createindexcontact_zip_phoneoncontact (Zipcode,phone_number);

Createindexcontact_phoneoncontact (Phone_number);

Letusconsiderthisquery:

Selectzipcode,phone_numberfromcontactwherephone_numberlike ' 312987654% ' andzipcode= ' 60606 '

It is obvious that the index Contact_zip_phone does not overwrite the lookup, but the DB2 optimizer does not use it. Instead of accessing the chart through another index called "Contact_phone," it surprises us, doesn't it? The decision is actually meaningful, and the DB2 optimizer is very strong in finding the best access plan. Let us understand why the decision of the DB2 optimizer is really good. On the one hand, the qualifying zipcode= ' 60606 ' has more than 15,000 rows, on the other hand, the number of rows conforming to Phone_numberlike ' 312,987,654% ' does not exceed 10 rows. This means that the conditions of phone_number are more selective. One notable experience is that "put the most selective column in front of the index definition." Let's take a closer look at the actual execution cost to see if the theory (optimizer) is right:

Read two index pages, one is the root index page, the other is the leaf level page

Read 10 data pages because 10 matches the number of rows scattered in the chart

Now let's take a look at the "What if" analysis, let's first remove the Contact_phone index and perform the same search,

The DB2 Optimizer is now scanning the contact_zip_phone portion of the index, starting with the value ' 60 ' or later, scanning to ' 61 ', which is significantly more expensive to execute.

Scan more than 100 leaf-level pages

As we can see, the optimizer wisely chooses not to use the overwrite index.

Now let's go back to the experience we just mentioned: "Put the most selective column in front of the index definition", as we discussed, in most cases is right. But there are several exceptions, let's imagine an example, in order to begin, our husband has this most selective index in the definition.

Selectzipcode,phone_numberfromcontactwherezipcode= ' 60606 '

If you let the two indexes be selected, the DB2 optimizer will most likely choose the index at (zipcode,phone_number). During execution, only the index part is checked to support lookups. If this index is canceled, the DB2 database engine will ensure that the search is done by checking the entire index (PHONE_NUMBER,ZIPCODE), which is certainly slower. If this search is often performed, the index that takes the (Zipcode,phone_number) is right.

As we can see, the experience says, "putting the most selective column in front of the index definition" is just a suggestion. Yes, that's good advice, and in most cases it's right. But when the most selective column is placed behind the definition of the index, it will happen, and in exceptional cases, think carefully to make your own decisions.

Index overrides and DB2 lookup performance