Flashcard on Sorting and Grouping in Databases

Front

What is a major problem with sort operations in databases?

Back

They need to buffer results temporarily.

Front

How can using an index help avoid an explicit sort operation for an ORDER BY clause?

Back

Indexes store data in a pre-sorted fashion matching the index definition.

Front

What is the "third power of indexing" mentioned in the chapter?

Back

Enabling pipelined execution of the ORDER BY.

Front

For a query with ORDER BY col_a ASC, col_b DESC, what is generally required from an index definition to allow pipelined execution without sorting?

Back

An index specifically defined with matching ASC/DESC modifiers, like (col_a ASC, col_b DESC)

Front

Under what circumstance might a FULL TABLE SCAN followed by an explicit sort be faster than using an INDEX SCAN to satisfy an ORDER BY clause? Options: When the table is very small., When the ORDER BY clause involves multiple columns., When the INDEX SCAN covers a large portion of the table and requires many subsequent table accesses., When the index is not clustered.

Back

When the INDEX SCAN covers a large portion of the table and requires many subsequent table accesses.

Front

Which databases support NULLS FIRST/LAST in index definitions?

Back

Oracle and PostgreSQL.

Front

What is the difference between hash and sort/group algorithms?

Back

Sort/group algorithm can use an index to avoid sorting.

Front

Which of the following is a common algorithm used by SQL databases to implement the GROUP BY operation: Nested Loops, Hash Join, Hash algorithm, Index Range Scan?

Back

Hash algorithm

Front

When a GROUP BY operation uses an index to avoid a sort, it enables a more efficient execution. What is this type of GROUP BY execution often called?

Back

Pipelined Group By

Front

When comparing a standard (non-pipelined) GROUP BY with a pipelined GROUP BY, which of the following is generally considered more important for achieving efficient partial results and better scalability? Achieving the absolute fastest overall execution time, The ability to deliver the first result quickly without processing the entire input., Minimising the use of temporary disk space., Using the least amount of CPU resources

Back

The ability to deliver the first result quickly without processing the entire input.