Data Management Systems Query Processing Introduction Execution - - PowerPoint PPT Presentation

▶

Aug 07, 2023 244 likes •327 views

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

SLIDE 1

Data Management Systems

Query Processing
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

1 Query processing introduction

Introduction

SLIDE 2

Architecture of a database

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

2 Query processing introduction

Storage

SLIDE 3

Anatomy of query processing (I)

Query processing introduction 3

CLIENT DB ENGINE P/T INTERFACE QUERY RESULTS

https://docs.oracle.com/en/database/oracle/oracle- database/19/cncpt/process-architecture.html#GUID-B9B8BB8D-FB3D- 46BC-AFBD-346A69BAB3EC

SLIDE 4

Anatomy of query processing II

Query processing introduction 4

QUERY PARSER QUERY INTERMEDIATE REPRESENTATION DB SCHEMA REWRITING OPERATOR TREE (PLAN) STATISTICS OPTIMIZATION CODE GENERATION QUERY EXECUTION QUERY PLAN CODE/ PLAN Validation, access control Check caches Interpretation Compilation

SLIDE 5

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

SLIDE 6

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

SLIDE 7

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