Shadow tables improve analytic query performance in OLTP environments

Because shadow tables are column-organized tables, complex queries can run faster against a shadow table than against its source table, which is row-organized.

In predominantly OLTP environments, several indexes are often created just to speed up these analytic queries. A shadow table requires no other index than primary key index, which is used by the replication solution. Using shadow tables, you can drop these indexes to improve the performance of updates to the source table. Eliminating these indexes offsets any resource requirements for shadow tables maintenance.

The following examples show a separate OLTP and OLAP environments and how to improve analytic query performance to transform the OLTP environment by using shadow tables.

Separate OLTP and OLAP environments

In this architecture, the front-end OLTP database is characterized by a high number of small transactions that are relatively quick to run. By contrast, the back-end OLAP database has relatively small number of complex and long-running transactions as shown in the following Figure 1.

Figure 1. Separate OLTP and OLAP environments.

In Figure 1, the OLTP applications issue short running transactions that access a single sales record in the OLTP database. These applications tend to access few records, often as small as one. The challenge in this environment is to quickly process a massive number of those small transactions. Indexes are typically created to improve the performance.

The OLAP applications issue long running complex transactions, such as a monthly sales trend report. These applications tend to access many records in the OLAP database, often as large as the entire table. However, depending on the queries, the application might need to access only a subset of columns. Again, indexes are typically created, but they might be different from indexes in OLTP. An ETL operation transfers data daily from the OLTP database to the OLAP database. In general, the time interval for ETL operations varies from minutes or hours to days.

Several factors lead to the separation of these two environments. One of these factors is the resource capacity limitation of processing power and memory. However, even when sufficient resources exist, processing power and memory are separated to ensure the best OLTP performance. For example, if both OLTP and OLAP access the same table, locking on data by OLAP queries might slow down OLTP operations. Extra indexes, created to serve OLAP queries, might slow down OLTP operations.

OLTP environment with shadow tables

Figure 2 shows the TRADE table and the TRADE_SHADOW table that reside in the same database. While the TRADE table is the source table and is row-organized, the TRADE_SHADOW table is the shadow table copy and is column-organized. The TRADE_SHADOW table can have all or a subset of columns in the TRADE table.

Figure 2. OLTP environment with shadow tables.

OLAP applications require some settings at the connection level to enable query routing to shadow tables. By default, all applications access the source table. These connection settings enable the DB2® optimizer to perform latency-based routing. For more information about how to automate the connection settings with the connect procedure, see Customizing the connect procedure for shadow tables.

An application accesses shadow tables only when the latency between the source table and the shadow table is within your defined limit. If the latency is greater than your defined limit, the optimizer determines that the shadow table is no longer valid and automatically routes the queries back to the source table. You can define the latency to different values for each connection.

The result is a truly flexible OLTP environment where shadow tables provide the incredible speed of BLU Acceleration for analytical queries and the source tables remain row-organized to best suit OLTP operations.