The following questions are purely for you to measure your level of understanding.  After reading each of the following questions, answer the question in your own words.  Post your answers on the discussion forum, and read other students’ answers.

 

1. What is the difference between a declarative data access language and a navigational data access language?

 

Answer: A navigational data language requires knowledge of the internal tables and index structures.  SQL is a better database access language because the SQL optimizer takes care of the internal navigation.

 

2. What decisions does the SQL optimizer need to make when deciding upon the optimal execution plan?

 

Answer:

 

A)     The order that the tables will be joined together

B)      The choice of indexes

C)      The internal joins methods (sort merge, hash, nested loops).

D)      The method to sort the result set (use an index or invoke a sort)

 

 

3. Why is data independence an important feature of relational databases and SQL?

 

Answer:

 

 

Data independence means that related tables can be joined at any time without having to pre-establish the relationship.  Hence, any two tables will a common column can be joined together at runtime.

 

4. Why is the choice of the SQL optimizer goal an important consideration when tuning an SQL statement?

 

Answer:

 

 

Different optimizer modes have different goals and different internal weights.  The all_rows goal favors full-table scans and minimization of machine resources while the first_rows optimizer goal favors index-scans and return data back to the requestor as soon as possible.

 

5. Why is database design important to SQL performance?  How can databases be designed to make SQL statements run faster?

 

Answer:

 

 

By introducing redundancy into the data model (denormalization) expensive table joins can be avoided.

 

6. Why is searching for large-table full-table scans critical to SQL tuning?

 

Answer:

 

If the optimizer gets confused or cannot find an appropriate index that matches the WHERE clause, the optimizer will read every row in the table.  Hence large-table full-table scans often indicate a missing index or a sub-optimal choice of optimizer goal.

 

 

7. What is the relationship between indexes and SQL performance?

 

Answer:

 

The sole purpose of indexes is to make SQL queries run faster.  If the optimizer detects an indexes that matches part of the WHERE clause of the query, then the optimizer will use the index to avoid having to read every row in the table.