You have to read the DB2 faq!

The following articles are a summary of DB2 FAQ answers. The following articles will provide you with solutions. If you are curious about the DB2 FAQ, the following articles will unveil its mysteries. In Example 4 of my previous column, "The Mystery of DB2 Sorts", see references ).

I have discussed how to use indexes in the following SQL statements:

 
 

  
  
Select workdept, lastname, jobcode from employee_master   
  
  
Where workdept in ('A01', 'B22', 'B46') and lastname >= :hvlastname   
  
  
Order by lastname  
 
 

The article states:

When the third index [on jobcode, workdept, lastname] is used, DB2 cannot match any predicate, but it can be filtered instead of matched.) lastname and workdept apply predicate to the index data. For each index row that meets the condition, DB2 can obtain the three selected columns from the index itself to avoid reading the table. Because the first column of the index is lastname, the data should be sorted by lastname. SORT is not required here.

Next, you asked, the first column of the third index is not lastname. Why can I return data in the order of lastname without data sorting? This is the secret.

At least 200 readers have reflected this problem to me. For me, this is both good news and bad news. The good news is that many readers read my column very carefully. The bad news is that I felt very embarrassed when I received the last email.

This is the case. I used a completely different fictional "third index" and a new idea at the beginning, but then I changed my mind and made changes to the index. Then I repeatedly entered an existing paragraph and accidentally forgot to delete the sentence marked in italic in the last two sentences ).

So what are my new ideas? It means that when DB2 selects an access path only for an index, DB2 will ignore the CLUSTERRATIO of the index. For pure index access, DB2 does not care about the relationship between the index sequence and the table sequence. DB2 will never use indexes in the form of list prefetch. Why? There is no need to execute the rid sort command to make the read operations on the table more orderly, because the table will not be read.

In this example, DB2 performs a complete index space scan, reads each individual index row using ordered prefetch, applies the two predicates to each row, and, all three columns are retrieved from the index data. Data is not sorted by lastname. Therefore, DB2 must execute SORT to satisfy the order by clause.

Now, I finally make up for my mistakes. The above content is a collection of answers to common DB2 questions. I hope you will find some gains.

The above content is a collection of descriptions of the DB2 FAQ, hoping to help you in this regard.