Memory Hierarchy: Cost and Access Time

What reads data in blocks?

Most storage media is fast in randomly accessing a block, but slow at reading consecutive blocks.

What is the typical block size for NTFS filesystems?

What is main concern when fitting database records into blocks?

What does a secondary index provide in a database system?

Sequential searching of rows is required when using an index.

Which of the following is a common type of index?

What is the role of Primary Index?

What is the purpose of query optimization in a database management system (DBMS)?

Query optimization always guarantees the absolute best execution plan.

Which of the following is a key consideration when optimizing queries?

What is main goal of 'Optimizing' Queries?

A general Cost-Based Algorithm does which of these steps?

A Heuristic-Based algorithm is generally more complex than Cost-Based

Which is NOT a con of Cost-Based aproach?

What should you do, If you can replace a cartesian product with a join?

What does 'cheapest' mean in cost-based optimization

Can we Limit the search space while not guaranteeing the cheapest solution?

What do we need for Cost-Based?

For Cost-Based aproach, what should you enumerate?

Always consider the time needed for evaluating the query together vs. the time needed for ______ it

Once the Query is Parsed then what happends?

Cost Model is not part of General Cost-Based Algorithm

What is used to represent unoptimized relational

Annotated Operator Tree is [blank] towards the execution plan

What should the Query Optimization do?

Query optimization determines the 'worst' order of the relational algebra operators

What is the predominant factor for Cost Metrics

Disk accesses are measured by Number of block reads * [blank]

What should you remember about In-Memory-Databases...?

Goodhart's law is a law that says : When a target becomes a measure, it ceases to be a good measure.

Campbell's Law about social ______, says The more any quantitative social indicator is used for social decision-making, the more subject it will be to corruption pressures

Relational algebra allows for which?

In what plans the Querying behaviour is heterogeneous?

Hybrid plans combine pre-optimization in a quick with fine-tuning during runtime

What transformations are based on?

Transformation rules transform an ______ tree step by step

What effect results of a query are affected by?

Pushing selections is always a good heuristic

What is used to break Cartesian products

What is a typical block size for NTFS and most 'traditional' file systems?

What are the two ways to organize blocks to fit database records?

Indexes always require sequential searching of rows to find stored data.

Which of the following is a type of index mentioned?

What is the primary purpose of a query processor in a DBMS?

File organization and indexing are irrelevant to query processing efficiency.

A query optimizer aims to find the way of executing a query.

Match the memory type with its typical capacity:

What is the goal of Query Optimization?

The 'optimal' way of executing a query is always the fastest in absolute terms.

What is a key technique for 'Optimizing' Queries?

What is one way you can optimize a query?

Heuristic based query optimization guarantees the absolute best result.

Which of the following is a con related to heuristic-based query optimization?

What is crucial to have when using Cost-Based Query Optimization?

Match the query optimization phase with its description:

In the context of file organization, what do file systems group together?

A primary index refers to additional data structures, beyond the raw data, that facilitate efficient data retrieval.

In database systems, a secondary index helps to efficiently find specific in stored data.

Which of the following is a core question to consider when fitting database records into blocks?

What is meant by the term 'algebra' in the context of 'Algebraic Query Rewriting'.

Algebraic query rewriting always leads to drastically different results.

What does it mean to Cascade a selection?

What does it mean to be 'Commutative' in the context of selections applied to Relational Algebra?

What does it mean to be 'Commutative' in the context of Joins?

Match the following concepts

Algebraic Optimization has no importance to a relational algebra.

What is the end goal when it comes to Algebraic Optimization.

What is a Static Plan?

What is the purpose of a Hybrid plan?

The canonical tree has different semantics to the final tree, during query optimization.

After running a query plan through the Query Optimizer, what should you make ABSOLUTELY SURE OF?

What might be more costly when a Cost Model attempts to break things up?

For algebraic transofmrations, all transformations are based on a set of what?

When is commuting projections with 𝜎 ONLY possible?

The best results are always possible when using Simple Heuristics.

When using Simple Heuristics for query plans, should be applied as early as possible.

Which of the following is NOT typically found in a memory hierarchy?

A query processor is a component external to a DBMS, responsible for translating SQL queries into a machine-readable format.

What is the primary goal of query optimization?

In the context of file organization within databases, the primary question to address is whether to organize blocks by ______ or by columns.

Match the following memory types with their relative access times:

What is a key characteristic of storage media regarding data access speed?

In database systems, all file systems blocks are grouped into smaller blocks to optimize storage.

In the context of database indexes, what type of searching is avoided through the use of an index?

The primary index on old encyclopedias is analogous to 'Sorting and ______ of Data'.

Match each memory type with its approximate capacity:

What is the primary function of a (secondary) index in a database system?

In query optimization, algebraic rewriting always involves changing the semantics of the original query to improve performance.

Briefly describe the goal of query optimization.

Query optimization can be seen as finding a better operator ______.

Match the step to the stage of Query Processing:

In the context of query optimization, what does a 'cost-based' approach involve?

In query optimization, a heuristic-based algorithm guarantees the selection of the absolute best query plan.

What is the purpose of defining 'good cost metrics' in cost-based query optimization?

The two classes of query optimization algorithm are Cost-Based Algorithm and ______-Based Algorithm.

Match the following steps with the general optimization algorithm the belong to:

Which approach is typically the most accurate for Query Optimization?

In the context of query optimization, it's always necessary to find the absolute best query execution plan to ensure optimal performance.

What is the downside for an operator tree that has too many joins?

The canonical and final operator tree should have a similar ______.

Match the description to each concept:

With indexes available, the process of SQL Query is how many times faster?

A static plan must be found at run time for its query.

Cite 2 set operations with Commutativity.

A is a heuristic to improve a canonical operator tree step by step, but still doesn't change the results.

Match the definition to the action:

What is true in regards to selections within heuristic pushing heuristics?

Applying projections early doesn't reduce block sizes in blocks containing intermediate results.

What operator should native joins be more efficient than?

The step to combine selections and is used to find joins.

Match the definition with the step of Simple Algebraic Heuristic Algorithm:

For Pipelining, which is not necessary?

Pipelining always works well for every operation.

If one knows how to evaluate a query better than the optimizer, then what is a potential tool to use?

The final state of query processes is ______.

Match the concept to what is mentioned in algebraic rewriting:

What is the primary role of a query processor in a Database Management System (DBMS)?

All storage media have the same speed when randomly accessing data blocks.

What is the core question to consider when fitting database records into blocks during file organization?

A __________ index is an additional lookup data structure to more efficiently find specific rows.

Match the storage media with their respective access times:

Which of the following tasks is not part of the query optimization process?

When optimizing queries, it is generally advantageous to waste more time on optimizing than the time you are saving.

What does it mean to get the 'same query result but faster' and what is it in relation to?

A General __________-Based Algorithm involves enumerating alternative query plans, estimating their costs, and choosing the cheapest plan.

Match each description with the correct term.

When is it appropriate to to use Dynamic plans as opposed to static plans?

With Algebraic Query Rewriting transformations, the results of a query can be affected.

What is at the 'heart' of the operations of the Query Processor?

Applying __________ early minimizes the size of records in intermediate results.

Match heuristic optimization techniques with their descriptions: