Select

Abstract

In this chapter, we describe how an application can extract data that was once stored in the database (execution of the SELECT statement).

Self Test Questions

 

• 1. Table Size

  What is the table size if it has 8 billion tuples and each tuple has a total size of 200 byte?

  • (a) $\approx$ 12.8 TB

  • (b) $\approx$ 12.8 GB

  • (c) $\approx$ 2 TB

  • (d) $\approx$ 1.6 TB

• 2. Optimizing SELECT

  How could the performance of SELECT statements be improved?

  • (a) Reduce the number of indices

  • (b) By using the FAST SELECT keyword

  • (c) Order multiple sequential select statements from low selectivity to high selectivity

  • (d) Optimizers try to keep intermediate result sets large for maximum flexibility during query processing.

• 3. Selection Execution Order

  Given is a query that selects the names of all German women born after January 1, 1990 from the world_population table (contains data about all people in the world). In which order should the query optimizer execute the selections? Assume a sequential query execution plan.

  • (a) country first, birthday second, gender last

  • (b) country first, gender second, birthday last

  • (c) gender first, country second, birthday last

  • (d) birthday first, gender second, country last.

• 4. Selectivity Calculation

  Given is the query to select the names from German men born after January 1, 1990 and before December 31, 2010 from the world population table (8 billion people). Calculate the selectivity.

  Selectivity = number of tuples selected / number of tuples in the table

  Assumptions:

  • there are about 80 million Germans in the table

  • males and females are equally distributed in each country

  • there is an equal distribution between all generations from 1910 until 2010

  • (a) 0.001

  • (b) 0.005

  • (c) 0.1

  • (d) 1

• 5. Execution Plans

  For any one SELECT statement...

  • (a) there always exist exactly two execution plans, which mirror each other

  • (b) exactly one execution plan exists

  • (c) several execution plans with the same result set, but differing performance may exist

  • (d) several executions plans may exist that deliver differing result sets.