Cost-based Query Optimization

In course

Description

This lecture covers the fundamentals of cost-based query optimization in database systems, including heuristics, cost estimation, selectivity estimation, and join cardinality estimation. It explains how to generate cost estimates for executing query plans, optimize join orders, and estimate the number of intermediary results. The instructor discusses various techniques such as histograms, equi-width, and equi-depth histograms for accurate selectivity estimation. The importance of join cardinality estimates for optimizing join operations is also highlighted, emphasizing the need to reorder joins efficiently to minimize query execution costs.

Instructor

Official source

https://mediaspace.epfl.ch/media/0_6vkebbih

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.

Related lectures (32)

Related concepts (33)

Query optimization

Query optimization is a feature of many relational database management systems and other databases such as NoSQL and graph databases. The query optimizer attempts to determine the most efficient way to execute a given query by considering the possible query plans. Generally, the query optimizer cannot be accessed directly by users: once queries are submitted to the database server, and parsed by the parser, they are then passed to the query optimizer where optimization occurs.

Object database

An object database or object-oriented database is a database management system in which information is represented in the form of objects as used in object-oriented programming. Object databases are different from relational databases which are table-oriented. A third type, object–relational databases, is a hybrid of both approaches. Object databases have been considered since the early 1980s. Object-oriented database management systems (OODBMSs) also called ODBMS (Object Database Management System) combine database capabilities with object-oriented programming language capabilities.

Optimizing compiler

In computing, an optimizing compiler is a compiler that tries to minimize or maximize some attributes of an executable computer program. Common requirements are to minimize a program's execution time, memory footprint, storage size, and power consumption (the last three being popular for portable computers). Compiler optimization is generally implemented using a sequence of optimizing transformations, algorithms which take a program and transform it to produce a semantically equivalent output program that uses fewer resources or executes faster.

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.

In-memory database

An in-memory database (IMDB, or main memory database system (MMDB) or memory resident database) is a database management system that primarily relies on main memory for computer data storage. It is contrasted with database management systems that employ a disk storage mechanism. In-memory databases are faster than disk-optimized databases because disk access is slower than memory access and the internal optimization algorithms are simpler and execute fewer CPU instructions.