Data Management Systems Query Processing Introduction Execution - PowerPoint PPT Presentation

Data Management Systems • Query Processing Introduction • Execution models • Optimization I – heuristics & rewriting • Optimization II – cost models • Optimization III - Operators Gustavo Alonso Institute of Computing Platforms Department of Computer Science ETH Zürich Query processing introduction 1

Architecture of a database Relations, views Application Queries, Transactions (SQL) Logical data (tables, schemas) Logical view (logical data) Record Interface Logical records (tuples) Access Paths Record Access Physical records Physical data in memory Page access Page structure Pages in memory File Access Storage allocation Blocks, files, segments Storage Query processing introduction 2

Anatomy of query processing (I) CLIENT QUERY RESULTS INTERFACE P/T DB ENGINE https://docs.oracle.com/en/database/oracle/oracle- database/19/cncpt/process-architecture.html#GUID-B9B8BB8D-FB3D- 46BC-AFBD-346A69BAB3EC Query processing introduction 3

Anatomy of query processing II QUERY Validation, access control Check caches QUERY PARSER INTERMEDIATE REPRESENTATION Interpretation DB SCHEMA REWRITING Compilation OPERATOR TREE (PLAN) QUERY CODE OPTIMIZATION STATISTICS EXECUTION GENERATION QUERY CODE/ PLAN PLAN Query processing introduction 4

IBM DB2 Query processing introduction 5 https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5 .0/com.ibm.db2.luw.admin.perf.doc/doc/c0005292.html

Commercial systems • All aspects of query optimization are a big part of the difference between commercial database engines and open source (or rather new) engines • Huge investments in query optimization • Long term efforts to tune performance at all levels • Many approaches tailored to particular situations • Query performance matters because: • Any gain when processing a tuple is multiplied by the number of tuples being processed • Any gain in a query is multiplied by the number of queries being processed • Both numbers (tuple, queries) can be very, very large Query processing introduction 6

Query processing • Studying query processing, we get our first complete view of a database engine and all the different steps and components involved in answering a query • We will look at a somewhat simplified view, each system expands different components in different ways • Keep in mind that the engine runs many queries at the same time so resources must be shared • We will see many optimizations at very different levels, sometimes this can be confusing Query processing introduction 7