Use constraints of DB2 performance optimization to improve query optimization

Here we will mainly introduce you to DB2 Performance Tuning: Design and configure the Use constraints of your database solution to improve query optimization, we all know that many database design and configuration options can affect query performance. For more suggestions on Database Design, see the "Planning your Physical Database Design" Best Practices article.

DB2 Performance Tuning: Design and configure constraints for your database to improve query optimization

Consider the uniqueness of definitions, check and refer to consistency constraints. These constraints provide semantic information that allows the DB2 optimizer to override queries to evaluate connections and reduce aggregation and fetch first n rows through connections, remove unnecessary DISTINCT options and some other optimizations. When an application can ensure its own relationship, Information constraints can also be used to check and reference consistency constraints. The same optimization is also possible.

When updating or inserting) rows, mandatory constraints from the Database Manager may cause high system overhead, especially when updating many rows with consistency constraints. If an application has verified the information before updating a row, the use of information constraints is more effective than normal constraints.

For example, consider two tables DAILY_SALES and CUSTOMER. Each row in the CUSTOMER table has a unique CUSTOMER key value CUST_KEY ). DAILY_SALES contains a CUST_KEY column and each row references the CUSTOMER key in a CUSTOMER table. You can create a reference consistency constraint to prevent a 1: N relationship between CUSTOMER and DAILY_SALES.

If the application needs to force this relationship, you can create an information constraint. The following query avoids the connection between CUSTOMER and DAILY_SALES, because no column is obtained from CUSTOMER, and each row from DAILY_SALES can find matching rows in CUSTOMER, therefore, the query optimizer automatically deletes the connection.

 
 

  
  
SELECT AMT_SOLD, SALE PRICE, PROD_DESC   
  
  
FROM DAILY_SALES, PRODUCT, CUSTOMER   
  
  
WHERE   
  
  
DAILY_SALES.PROD_KEY = PRODUCT.PRODKEY AND   
  
  
DAILY_SALES.CUST_KEY = CUSTOMER.CUST_KEY  
 
 

The application must execute Information constraints, otherwise the query may return incorrect results. In the preceding example, if the row exists in DAILY_SALES and the corresponding CUSTOMER key cannot be found in the CUSTOMER table, the rows returned by the preceding query may be incorrect.

The above content is to optimize the performance of DB2: Design and configure the Use constraints of your database to improve query optimization. I hope you will gain some benefits.