Collection Contents Index Using keys to improve query performance Search strategies for queries from more than one table pdf/chap24.pdf

User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 24. Monitoring and Improving Performance       

Using indexes to improve query performance


Sometimes you need to search for something which is not in a primary or foreign key. In this case, a key cannot be used to improve performance. Creating indexes speeds up searches on particular columns. For example, suppose you wanted to look up all the employees with a last name beginning with M.

A query for this is as follows:

SELECT *
FROM employee
WHERE emp_lname LIKE 'M%'

If you execute this command, the plan description in the Interactive SQL Statistics window shows that the table is searched sequentially.

Creating an index 

If a search by employee last names is common, you may wish to create an index on the emp_lname column in order to speed up the queries. You can do this with a CREATE INDEX statement.

CREATE INDEX lname
ON employee ( emp_lname )

The column name emp_lname indicates the column that is indexed. An index can contain one, two, or more columns. However, if you create a multiple-column index, and then do a search with a condition using only the second column in the index, the index cannot be used to speed up the search.

An index is similar to a telephone book, which first sorts people by their last name, and then all the people with the same last name by their first name. A telephone book is useful if you know the last name, even more useful if you know both the first name and last name, but worthless if you only know the first name and not the last name.

Once you have created the index, rerunning the query produces the following plan description in the Statistics window:

PLAN> employee (lname)

How indexes are used 

Indexes are used automatically. Once an index is created, it is automatically kept up to date and used to improve performance whenever it can.

You could create an index for every column of every table in the database. But that would make data modifications slow, since all indexes affected by the change have to be updated. Further, each index requires space in the database. For these reasons, you should only create indexes that are used frequently.

Since you will not be using this index again, you should delete it by entering the following statement:

DROP INDEX lname

Top of page  How indexes work

This section provides a technical description of how the server uses indexes when searching databases.

Index page structure 

The Adaptive Server Anywhere query processor uses modified B+ trees. Each index page is a node in the tree and each node has many index entries. Leaf page index entries have a reference to a row of the indexed table. Indexes are kept balanced (uniform depth) and pages are kept close to full.

An index lookup 

An index lookup starts with the root page. The index entries on a nonleaf page determine which child page has the correct range of values. The index lookup moves down to the appropriate child page. This continues until a leaf page is reached. An index with N levels will require N reads for index pages and 1 read for the data page that contains the actual row. Index pages tend to be cached due to the frequency of use.

Recommended page sizes 

About the first 10 bytes of data for each index entry are stored in the index pages. This allows for a fan-out of roughly 200 using 4K pages, meaning that 200 rows can be indexed on one page, and 40,000 rows can be indexed with a two-level index. Each new level of an index allows for a table 200 times larger. Page size can significantly affect fan-out, in turn affecting the depth of index required for a table. 4K pages are recommended for large databases.

The leaf nodes of the index are linked together. Once a row has been looked up, the rows of the table can be scanned in index order. Scanning all rows with a given value requires only one index lookup, followed by scanning the leaf nodes of the index until the value changes. This occurs when you have a WHERE clause that filters out rows with a certain value or a range of values. It also occurs when joining rows in a one-to-many relationship.

Top of page  

Collection Contents Index Using keys to improve query performance Search strategies for queries from more than one table pdf/chap24.pdf