Exercise 9.1

Consider the following questions.

1. Compare and contrast the concepts of keys and indexes.

2. Indicate what indexes, if any, you’d suggest to improve the performance of the following query.

   SELECT a.firstName || ' ' || a.lastName
   FROM Actor a, ActorOrdinal ao
   WHERE a.id = ao.id
     AND ao.ordinal = (SELECT TRUNC(dbms_random.value(0,1908))
                       FROM Dual);

3. Can you give a case in which using indexes wouldn’t help?

Exercise 9.2

How would you improve the performance of the following query in an environment in which the data doesn’t change very often but there are many queries?

SELECT a.firstName || ' ' || a.lastName, m.name
FROM Actor a, Role r, Movie m, MovieOrdinal mo
WHERE a.id = r.actorId
  AND r.movieId = m.id
  AND m.id = mo.id
  AND mo.ordinal = (SELECT TRUNC(dbms_random.value(1,40))
                    FROM Dual);

Exercise 9.3

Which, if either, of the following sets of (equivalent) queries is more efficient?

Exercise 9.3.1

Get the minimum rank for movies with some casting.

SELECT MIN(rank)
FROM Movie m, Role r
WHERE m.id = r.movieId;

SELECT MIN(rank)
FROM Movie m
WHERE EXISTS (SELECT 1
              FROM Role r
              WHERE m.id = r.movieID);

Exercise 9.3.2

Get the actors who haven’t acted in anything.

SELECT a.id
FROM Actor a
WHERE NOT EXISTS (SELECT r.actorId
                  FROM Role r
                  WHERE a.id = r.actorId)

SELECT a.id
FROM Actor a LEFT OUTER JOIN Role r ON a.id = r.actorId
WHERE r.actorId IS NULL;

SELECT a.id FROM Actor a
MINUS
SELECT r.actorId FROM Role r;