Query Optimization: Heuristics and Cost-Based Approach

Description

This lecture delves into the principles of optimality in query optimization, emphasizing the importance of interesting orders and selectivity estimates. It discusses the challenges of estimating cardinalities and the impact of errors on plan selection. The instructor shares insights on the System R optimizer, highlighting its reliance on heuristics and cost models. Additional optimization opportunities, such as static vs. dynamic re-optimization and optimization granularity, are explored. The lecture also covers optimization for multiple queries, disk-based scan sharing, and the benefits of sharing common subexpressions. Overall, the query optimizer's crucial role in database management system performance and scalability, particularly for OLAP queries, is underscored.

Official source

About this result

This page is automatically generated and may contain information that is not correct, complete, up-to-date, or relevant to your search query. The same applies to every other page on this website. Please make sure to verify the information with EPFL's official sources.

Interprocedural optimization

Interprocedural optimization (IPO) is a collection of compiler techniques used in computer programming to improve performance in programs containing many frequently used functions of small or medium length. IPO differs from other compiler optimizations by analyzing the entire program as opposed to a single function or block of code. IPO seeks to reduce or eliminate duplicate calculations and inefficient use of memory and to simplify iterative sequences such as loops.

Object code optimizer

An object code optimizer, sometimes also known as a post pass optimizer or, for small sections of code, peephole optimizer, forms part of a software compiler. It takes the output from the source language compile step - the object code or - and tries to replace identifiable sections of the code with replacement code that is more algorithmically efficient (usually improved speed). The earliest "COBOL Optimizer" was developed by Capex Corporation in the mid 1970s for COBOL.

Program optimization

In computer science, program optimization, code optimization, or software optimization, is the process of modifying a software system to make some aspect of it work more efficiently or use fewer resources. In general, a computer program may be optimized so that it executes more rapidly, or to make it capable of operating with less memory storage or other resources, or draw less power. Although the word "optimization" shares the same root as "optimal", it is rare for the process of optimization to produce a truly optimal system.

Relational database

A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relational database systems are equipped with the option of using SQL (Structured Query Language) for querying and updating the database. The term "relational database" was first defined by E. F. Codd at IBM in 1970. Codd introduced the term in his research paper "A Relational Model of Data for Large Shared Data Banks".

Common subexpression elimination

In compiler theory, common subexpression elimination (CSE) is a compiler optimization that searches for instances of identical expressions (i.e., they all evaluate to the same value), and analyzes whether it is worthwhile replacing them with a single variable holding the computed value. In the following code: a = b * c + g; d = b * c * e; it may be worth transforming the code to: tmp = b * c; a = tmp + g; d = tmp * e; if the cost of storing and retrieving tmp is less than the cost of calculating b * c an extra time.