News, views, and items of interest on IBM's Db2 database management system and mainframes.
Just a quick blog post to wish everybody a very happy holiday season. Whether you celebrate Christmas, Hanukkah, Kwanza, or some other holiday season, all the best to you from me!
And a very Happy New Year to one and all. The past couple of years have been difficult and stressful for many, so here's a wish for a better 2022 for everybody.
I plan to take some time off over the last week of 2021 through New Year's Day, but I'll see you all again on the other side of the holiday season.
One of the constant struggles with Db2 development, and indeed developing for any relational DBMS, is designing and creating appropriate indexes. Perhaps the most important thing you can do to assure optimal application performance when accessing data in a relational/SQL database is to create correct indexes for your tables based on the queries your applications use. Of course, this is easier said than done.
But we
can start with some basics. For example, consider this SQL statement:
|
SELECT LASTNAME, SALARY |
What
index (or indexes) would it make sense to build for this simple query? First, think about all
the possible indexes that you could create. Your first short-list probably
looks something like this:
This is a good start, and Index3 is likely to be the best of the lot. It enables the optimizer to use the index to immediately look up the row or rows that satisfy the two simple predicates in the WHERE clause. Of course, if you already have a lot of indexes on the EMP table, you might want to examine the impact of creating yet another index on the table.
With
the caveat that appropriate index creation can be complicated, let’s look at
the Top 10 things you can do to build the right indexes on your tables:
1. Index by workload, not by object
Many
people make the mistake of just guessing at some indexes to create when they
are creating other database objects (like tables and tablespaces). But without an
idea of how the tables are going to be accessed these guesses are
usually wrong – at least some of them.
Indexes
should be built to optimize the access of your SQL queries. To properly create
an optimal set of indexes requires a list of the SQL to be used, an estimate of
the frequency that each SQL statement will be executed, and the importance of
each query. Only then can the delicate balancing act of creating the right
indexes to optimize the right queries most of the time be made.
If you
are doing it any other way, you are doing it wrong.
2. Build indexes based on predicates
3. Index most-heavily used queries
Numbers
2 and 3 can be thought of as corollaries to Number 1… that is, these are the
aspects of application workload that need to be examined to produce appropriate
and effective indexes.
Look at the predicates of the queries in the workload and work to create a set of indexes that match up to most (if not all) of them. When it is not practical to build all of the indexes you have identified, then it makes sense to look at the queries that will be used most often and optimize them first.
4. Index important queries
The more important the query, the more you might want to tune by index creation. If you are coding a query that the CIO will run every day, you want to make sure it delivers optimal performance. So building indexes for that particular query is important. On the other hand, a query for a clerk might not necessarily be weighted as high, so that query might have to make do with the indexes that already exist.
Of course, the decision should depend on the application’s importance
to the business - not just on the user’s importance. For example, if that clerk runs a query hundreds of times a day and the CIO runs his query only once, then perhaps the clerk's query is more important.
5. Index to avoid sorting (GROUP BY, ORDER BY)
In
addition to building indexes to optimize data access, indexes can be used to
avoid sorting. The GROUP BY and ORDER BY clauses tend to invoke sorts, which
can cause performance slowdowns. By indexing on the columns specified in these
clauses, the optimizer can use an index to avoid a sort, and thereby
potentially improve performance.
6. Create indexes for uniqueness (PK, U)
Some
indexes are required in order to make the database schema valid. Most Database
systems require that unique indexes be created when unique and primary key
constraints exist.
7. Create indexes for foreign keys
Even if
not required, creating indexes for each foreign key can optimize the
performance when accessing and enforcing referential constraints (RI –
referential integrity).
8. Consider adding columns for index-only access
Sometimes
it can be advantageous to include additional columns in an index to increase
the chances of index-only access. With index-only access, all of the data needed
to satisfy the query can be found in the index alone — without having to read
data from the tablespace.
For
example, suppose that there is an index on the DEPTNO column of the DEPT table.
The following query may use this index:
|
SELECT DEPTNAME
|
Of course, this is not always a good idea. You have to take into account whether other queries use the index and how it might negatively impact their performance.
It is also worth mentioning index include columns, which allows you to define additional (included) columns that are not part of the actual key but are available in the index. So we can create an index like this:
CREATE INDEX IXNAME
ON DEPT ( DEPTNO )
INCLUDE ( DEPTNAME )An example of a bad standard would be to place an artificial limit on indexing. There should be no arbitrary limit on the number of indexes that you can create for any database table. Indexes are undoubtedly one of the most important factors in creating efficient queries. Relational optimizers rely on indexes to build fast access paths to data. Without indexes data must be scanned – and that can be a long, inefficient means by which to retrieve your data. When a rule such as this exists, it usually is stated in the standards manual using verbiage something like “Each table can have at most five indexes created for it” — or — “Do not create more than three indexes for any single table in the database.” These are bad standards.
If
you already have three indexes, or five indexes, or even 57 indexes, and
another index will improve performance why would you arbitrarily want to avoid
creating that index?
Anyway, a good indexing standard, if you choose to have one, should read something like this: “Create indexes as necessary to support your database queries. Limitations on creating new indexes should only be entertained when they begin significantly to impede the efficiency of data modification.”
Which brings us to…
10. Be aware of I/U/D implications
Db2 must automatically maintain every index you create. This means every
INSERT and every DELETE to an indexed table will insert and delete not just
from the table, but also from its indexes.
Additionally, when you UPDATE the value of a column that has been defined in an index, the DBMS must also update the index. So, indexes speed the process of retrieval but slow down modification.
So the general rule of thumb should be "Index until it hurts... and then back off the least effective index to make it no longer hurt." Sure, that is easier said than done, but it is a valid philosophy to follow.
Summary
Following
these Top 10 index design techniques can go a long way toward improving not
just your index usage, but also the performance of your database applications.
Historically, one of the most fertile grounds for disagreement between database professionals was the appropriate usage of views. Some analysts promote the liberal creation and usage of views, whereas others preach a more conservative approach.
When properly implemented and managed, views can be fantastic tools that help to ease data access and simplify development. Although views are simple to create and implement, few organizations take a systematic and logical approach to view creation. And therein lies the problem. A strategic and reasonable policy guiding the creation and maintenance of views is required to avoid a muddled and confused mish-mash of view usage. Basically, views are very useful when implemented wisely, but can be an administrative burden if implemented without planning.
Let's back up a minute and define what a view is. First, remember that all SQL access to relational tables results in another table. This is called relational closure and it is a requirement of the relational model. A view can be considered to be a logical table. That is, a view is a "logical" representation of data that is "physically" stored in other tables (and perhaps other views as well). Views are defined using SQL and are represented internally to the DBMS by SELECT statements, not by stored data (unless it is a materialized view, which is another thing altogether and beyond the scope of this particular post). Almost any SQL that can be issued natively can be coded into a view.
View Implementation Rules
After you understand the basics of views, you should develop guidelines for view creation in order to limit administrative burden. The following rules can be used to ensure that views are created in a responsible and useful manner at your shop. These rules were developed over a number of years as a result of reviewing implementations and working with views in many different environments. There are three basic view implementation rules:
1. The View Usage Rule
2. The Proliferation Avoidance Rule
3. The View Synchronization Rule
The View Usage Rule: The first rule is simple: your view creation strategy should be goal-oriented. Views should be created only when they satisfy a specific application or business requirement. That requirement should be documented somewhere, preferably in a dictionary or repository. Although this rule may seem to be obvious, views are implemented at some shops without much thought as to how they will be used. This can cause the number of views that must be supported and maintained to continually expand until so many views exist that it is impossible to categorize their uses. And the time needed to maintain and administer the system increases as the number of views increases.
There are five basic uses for which views excel. These are:
1) to provide row and column level security,
2) to ensure efficient access paths,
3) to mask complexity from the user,
4) to ensure proper data derivation, and
5) to rename tables and/or columns.
The second rule is the Proliferation Avoidance Rule. This rule is simple and to the point: do not needlessly create views (or, indeed, any database objects) that are not absolutely required. Whenever a database object is created additional entries are placed in the system catalog. Creating needless views causes "catalog clutter"…that is, entries in the catalog for objects which are not needed and/or are not used.
The proliferation avoidance rule is based on common sense. Why create something that is not needed? It just takes up space that could be used for something that is needed.
The third, and final view implementation rule is the View Synchronization Rule. The basic intention of this rule is to ensure that views are kept in sync with the base tables upon which they are based.
Whenever a change is made to a base table, all views that are dependent upon that base table should be analyzed to determine if the change impacts them. All views should remain logically pure. The view was created for a specific reason (if we followed the View Usage Rule above). The view should therefore remain useful for that specific reason. This can only be accomplished by ensuring that all subsequent changes that are pertinent to a specified usage are made to all views that satisfy that usage.
For example, say a view was created to satisfy an access usage, such as a join between the employee table and the department table. If a column is added to the employee table specifying the employee’s location, it should also be added to the EMP_DEPT view if it is pertinent to that view’s specific use. Of course, the column can be added to the table immediately and to the view at the earliest convenience of the development team.
The synchronization rule requires that strict change impact analysis procedures be in place. Every change to a base table should trigger the usage of these procedures. Simple SQL queries can be created to assist in the change impact analysis. These queries should pinpoint any SQL in QMF queries, application packages, dynamic packages, and so on. Policies for informing users of the views to be impacted also need to be established before view changes can be implemented.
View synchronization is needed to support the view usage rule. By keeping views in sync with table changes the original purpose of the view is maintained.
Synopsis
Views are practical and helpful when implemented in a systematic and thoughtful manner. Hopefully, this post has provided you with some food for thought pertaining to how views are implemented at your shop.
