Query Processing Parser Parse tree Semantic Checker Query

Query Processing Parser Parse tree Semantic Checker Query block graph Type checker Query block graph Query Rewrite Query block graph Optimizer Query block plan graph Compile-time Select avg(R. a), R. b From R, S Where R. c = S. a. m() and R. c > 5 Group by R. b;

Query Processing 45, “t 1” 76, “t 2” 10, “t 3” Execution plan Execution Engine Run-time Select avg(R. a), R. b From R, View Where R. c = View. a. m() and R. c > 5 Group by R. b;

Parse Tree Rel. View. Class: from selection projection group by having order by

Query Block Graph Before Type Checking GBY SPJ Access to R Proj: Avg(R. a), R. b Agg: Avg(R. a) Gby: R. b Proj: R. a, R. b Sel: R. c = View. a. m() and R. c > 5 SPJ Access to S Proj: S. a

Query Block Graph After type checking GBY SPJ Access to R Proj: Avg(0(1)), 0(2) Agg: Avg(0(1)) Gby: 0(2) Proj: 0(1), 0(2) Sel: 0(3) = 1(1). m() and 0(3) > 5 SPJ Access to S Proj: 0(1)

Query Block Graph After query rewrite GBY SPJ Access to R Proj: Avg(0(1)), 0(2) Agg: Avg(0(1)) Gby: 0(2) Proj: 0(1), 0(2) Sel: 0(3) = 1(1). m() and 0(3) > 5 Access to S

Execution Plan Executor Proj: Avg(0(1)), 0(2) Agg: Avg(0(1)) Gby: 0(2) Gby. Plan. Op SMJoin. Plan. Op Access to R (indexed) Stored Relation R Proj: 0(1), 0(2) Sel: 0(3) = 1(1). m() and 0(3) > 5 Access to S (sequential) Stored Relation S

Query Processing modules • Types - values records – schema • Expressions Independent of relational query processing • • Relation Storage manager Catalog Manager Indexes Parser Optimizer Execution Engine

Types and Records Date (Type) Specific Date Type M/D/Cye (Meta-Info) Date Values. Array: […, Date Value, …] ADT array Record offset Record. Schema: […, Att i, …]

Abstract Data Types (fields) • • • Identifier: index in ADT array Type Name Meth. Array: array of scalar methods Aggr. Method: array of aggregate methods Maintains. Catalog: is there metainformation to be stored in the catalog? • Store. In. Field: is the value stored in place in a record?

Abstract Data Types (some methods) • • • Max. Object. Size() Type. Copy() Equals() Read. Text(), Write. Text() Get. Meta. Info() Create. Stats. Info() Func. Type. Check() Func. Optimize() Func. Reorganize() Get. Meth. By. Index() • Cast. Check() • Create. Env()

ADT Values • Data & Behavior – Using programming language objects • Values are instances of type classes • Need for serialization mechanism to translate from in-memory to on-disk data representation – Using a specific mechanism (Predator approach) • Predator ADT Values are not instances of ADT classes • Data representation is similar in-memory and on-disk • Type information is more than behavior and storage management: – Optimization – Catalog Management

ADT Values header value padding • Header: – 4 bytes of flag: is null, little indian/big indian, … • Value • Padding: – For aligment purpose (value length must be a multiple of 8 bytes)

Methods • To register a function with an ADT – Xxx. Func. Method. Info • Fields: Arg. Info, Arg. Types • Methods: Constructor, Matches – Xxx. Aggr. Method. Info • To represent a function in the parse tree: – Xxx. Func. Parse. Info • To represent a function in the execution plan: – Xxx. Func. Plan. Info • Evaluate(Xxx. Value. Env *Env, Xxx. Func. Method. Info*This. Meth. Info, Xxx. ADTValue *Return. Value)

Record and Record Schema • Record – Get. Field(int position, Record. Schema* Schema, char*& Field) • Record Schema – Get. Attribute • Name • Type • Meta-information – Get. Offset • In record structure

Expressions

Unknown Value (fields) • Name of the attribute • Index – Source: child block – Attribute Index: position in child block • Correlation Height

Unknown Value (some methods) • Resolve Variables / Update Unknowns – Manipulate index for source and attribute • Get Dependencies / Redirect Dependencies – Initialize dependency bitmap structure • Match – Checks whether function expression matches a given expression -- matching information is returned • Evaluate – Extracts the ADT Value from position Attribute. Index in Source. Index child record

Expression & Plan • Function Expression – – Func. Parse. Info Owner, Arg Update Unknowns, Get. Dependencies, Match Optimize • Function Plan – – Func. Plan. Info Owner. Plan, Arg. Plan eval. Owner, eval. Args Evaluate

Relational Query Processing • Relational ADT – No ADT methods defined • Data Engine – Relation – Storage Manager – Catalog Manager • Query Processing Engine – Parse – Optimize – Execute

Relation • Relation Type – Stored: Shore File Relation • match. Index (Expression, Matched. Info) • choose. Access. Path • Add to / delete from index – Derived • Access – Indexed access – Sequential access: • Init cursor, next item, close cursor • Delete record, insert record, update record

Relation • Relation subclassed as Shore. File. Relation, Derived. Relation, … • Rel. Impl. Info – Index. List – Stored / derived • Rel. Stats. Info – Cardinality, average tuple size • Rel. Catalog. Info – Relation name – Record schema

Storage Manager • Storage Structures – – Create, mount, delete device Create, delete file Insert, update, delete object Iterator, get, pin object • Transaction support – Begin, commit, abort • Indexes – Btree, Rtree – Clustered / Unclustered • Sorting – Sort File • Problem: how to pass expression used for sorting • Threads – Thread model • Preemptive scheduling • Non preemptive scheduling – Synchronization primitives

Catalog Manager • Catalog relations – _STABLES(tablename, arity) – _SATTRS(tablename, attindex, atttype, key? , attmetainfo) – _SINDXS(tablename, indexexpression) – _SSTATS – _SATSTATS • One catalog per storage device – Get. Catalog. Rel: Bootstrapping problem

Indexes • Index Class (superclass of Shore. BTree and Shore. RTree) – Type Id – Index range: get range(given an Expression), merge ranges – Create / delete index – Insert, delete, get entry – Match (specific to Index class) • Index. Impl. Info associated to a relation – Index Type – Index expression – Match

SQL Parser • Flex (tokenizer)/ Bison (grammar) • Interaction with: – Expressions – Types – Data Engine • Insert, Update, Delete Record into Relation • Create, Delete Index • Create, Delete Relation – Query Engine • Store View • Exec Query • Generates Parse Tree

Semantic Checker • Creates Query Graph – – – SPJ block One block per relation in the From clause Views are developed Aggregate block is added If needed a SPJ block is added at the root • Verifies conditions on SQL input – Targets with similar names, aggregates in where clause, grouping without aggregates, same expression in aggregate and to group on, …

Type Checker • Traverses the Query Block graph – Bottom-up then top down • Relies on – Query graph structure – Methods defined for Unknown Variables • Resolve Variables

Query Rewrite Rules • Rule Engine – Vector list of rules – Execute rules on one downward and one upward pass • Rules – Manipulation across query blocks • Pushing projections, selections • Merging query blocks • Eliminating distinct clauses – Each rule is a class that implements the following method • Apply. Rule(Rel. Query. Node *In, Xxx. Bool &Success, Rel. Query. Node *Out)

Optimizer • Predicates – Array of predicate plans, predicate dependencies and predicate selectivities – Init, Selection, Join, Residual bitmaps • Query Block Plan – Redirect dependencies

Optimizers • Simple – Naive • Join order fixed by order in the from clause. Generates a single N-way SPJ node. – Greedy • Join order based on cardinality of intermediate relations. Generates a leftdeep pipeline of two-way joins. • Cost based – Simplified KBZ • Tries each relation as outer most relation and compares cost. Generates a left-deep pipeline of twoway joins. – Dynamic Programming • System R like enumeration of join space and pruning. Generates a left-deep pipeline of two-way joins.

SPJ Naive Optimization phases • Step 1: – Generate plan for children blocks • Step 2: – Create the predicate bitmap for the selections and joins • Step 3: – Construct a remapping of unknown variables depending on schema of children • Step 4: – Modify all expressions based on remapping • Step 5: – Generate plan operator for SPJ

Relational Operators • Iterator interface • Shared data structure (handles) for passing arguments – State information: e. g. , end-of-stream – Operator specific information: cursor position (nested loop) • Single records flowing across operators • Access Method is chosen dynamically for each accesses

Execution • Executor: wrapper on top of execution plan – creates a derived relation – Initializes derived relation (recursively initializes execution plan) – Iterates over records – Process resulting records • Write to client – Close iterator – Clean-up

Server Architecture Console Client Server Thread Init Thread Request Thread Monitor Thread Request Thread: Client interaction Relies on Protocol - text - binary

Summary • Predator achieves extensibility by isolating these modules which are independent from the rest of the system – Types and Expressions are used throughout the system and are prone to changes • Predator reuses the clean internal data structures defined in Starbust