The ultimate software tool for improving a database's physical design, and therefore, overall performance, has yet to be delivered. It is a product that would place a database's physical design and environment under a microscope and then produce an expertly altered physical design, crafted especially for the given database's needs.
The data modeling tools on the market can help you build a data model, but they cannot tell you how to build the right data model, and that is a subtle but huge difference.
Let's take the case of when a designer should use a bitmap index. Every data modeling tool will allow you to design a bitmap index for a table, but they will not stop you from putting a bitmap index on a table where one doesn't belong.
Determine Cardinality
To determine if a bitmap index should be used, the designer first needs to know the correct column cardinality. For those not familiar with a bitmap index, they work in pretty much a reverse fashion from a normal
ｏＲＡＣＬＥ ｐＥＲＦＯＲＭＡＮＣＥ ｔＲＯＵＢＬＥＳＨＯＯＴＩＮＧ
B-Tree index. Most indexes require high cardinality (many distinct values) in the table column to work effectively. Bitmap indexes are designed to work with low cardinality data.
For example, if you have a database that tracks patients admitted to a hospital, you may have a column in an admissions table called INSURED that tracks whether the patient was insured or not, basically a YES/NO column. This would be a terrible choice for a regular B-Tree index, but could definitely qualify for a bitmap index.
Such indexes can be intelligently used where the case permits, such as the situation where many low cardinality columns can be ANDed together for Oracle's bitmap merge execution plan.
Data Volume
The second thing a designer needs to know when putting a bitmap index on a table is data volume. Most any index is useless when it comes to enhancing performance on tables with little data because most relational databases will ignore an index on small tables and instead, cache and scan the table faster than if index access was used. On the other hand, if millions of rows were present in our hospital admissions table, then a bitmap index could really prove useful.
Frequency of Update
The third thing a designer needs to know when deciding if a bitmap index will be necessary is whether data modifications occur at frequent levels for the table. Bitmap indexes are notorious for causing performance slowdowns on tables with high DML activity.
Demonstrating the proof of this concept, a DBA once inherited a database that was extremely critical, both in terms of company visibility and bottom line impact. Complaints began to quickly surface in regard to the database's performance, and while many of the normal performance statistics looked good, there seemed to be a bottleneck whenever an OLTP transaction passed through the system.
ｏＲＡＣＬＥ ｐＥＲＦＯＲＭＡＮＣＥ ｔＲＯＵＢＬＥＳＨＯＯＴＩＮＧ
The DBA quickly traced the problem to the hub table in the database - nearly every transaction passed into and out of this one table. The designer who preceded the DBA had chosen to place eight bitmap indexes on this table that was the object of much DML activity in the system. This design decision violated nearly even' rule of thumb with respect to bitmap indexes.
Removing all the bitmap indexes produced an end result like the parting of the Red Sea. Response time throughout the system was immediately restored to more than acceptable measures.
The final and perhaps most important consideration for deciding if a bitmap index is right for the table, is the use of user access patterns.
In other words, will the index be used at all? If no one asks the question,
"How many insured patients were admitted this month?" in a SQL query, the bitmap index placed on the INSURED column in the hospital admissions table is basically useless.
All four points of whether to use a bitmap index on a table column count must be weighed when it comes down to physical design time. The only problem is that a Data Modeler or DBA may not have all the facts needed to make a correct decision before the system goes live. Or, perhaps the designer simply is not privy to the knowledge needed to make the right choice about index placement.
Here is where the dream tool comes into play. First, a data/work load must be imposed on the database to mimic what is to come, with respect to user traffic, user requests, and data volume. If a load-testing tool can be used before a system goes into production to do this - great.
Otherwise, a manual user-driven office environment model must be put in place.
In any event, once such a load exists, the yet-to-be-invented tool interrogates the database and captures data volumes, object statistics, and user request patterns. Using this information, the tool then digests the information and constructs a physical design model that fits the system perfectly.
ｏＲＡＣＬＥ ｐＥＲＦＯＲＭＡＮＣＥ ｔＲＯＵＢＬＥＳＨＯＯＴＩＮＧ
All necessary indexes are present, physical storage placements are correctly in place, and all objects that desperately need denormalizing are reconstructed. The tool would basically tell the designer that this is how your data model should have looked in the beginning.
Until such a product comes along, using a combination of modeling and performance-monitoring tools will be the de facto method for ensuring high performance physical database designs.
Conclusion
So, what was done to fix the slow-running database described at the beginning of this chapter? First, the over-normalization of the database was pointed out to the database modelers and DBAs. Unfortunately, they were not agreeable to working on the design to make things run better. So instead, a small data-mart was created from the transactional database that the end users could work against to build the reports they needed.
A small extract-transform-load (ETL) routine was also written and fronted with an easy to use GUI that the users could run whenever they liked to refresh the data-mart's contents. While their reports had run for over an hour before, the entire ETL process and report process now crossed the finish line in under seven minutes.
And if the ETL routine did not need to be run, the actual report creation could be accomplished in about two minutes. A better design, suited to the end users needs, was just the ticket to make things right.
The main points of this chapter to remember include:
• Never compromise a project by neglecting the database's physical design phase
• Understand that much of a database's performance comes from its physical design foundation
• When troubleshooting system performance, keep an eye on
indicators that signal a problem with the overall database physical
ｏＲＡＣＬＥ ｐＥＲＦＯＲＭＡＮＣＥ ｔＲＯＵＢＬＥＳＨＯＯＴＩＮＧ
design
One critical aspect of a physical design is the creation and placement of storage structures and the storage characteristics of the database objects. The next chapter will look at how to design your storage objects so that they work for you instead of against you.
ｏＲＡＣＬＥ ｐＥＲＦＯＲＭＡＮＣＥ ｔＲＯＵＢＬＥＳＨＯＯＴＩＮＧ