Practice.md

1. Оптимально ли составлен запрос? Какие индексы нужно построить для эффективного выполнения запроса и почему?

SELECT rental_id FROM rental
WHERE rental_date BETWEEN '2005-07-01' AND '2005-08-01'
  AND customer_id = 2
ORDER BY rental_date, inventory_id;

2. Оптимально ли составлен запрос? Какие индексы нужно построить для эффективного выполнения запроса и почему?

SELECT rental_id FROM rental
WHERE EXTRACT(SECOND FROM '2005-07-01'::timestamp - rental_date) > 0
  AND customer_id = 2
ORDER BY rental_date;

3. Оптимально ли составлен запрос? Какие индексы нужно построить для эффективного выполнения запроса и почему?

SELECT rental_id FROM rental
WHERE rental_date BETWEEN '2005-07-01' AND '2005-08-01'
  AND customer_id IN (2, 42, 73);

4. Оптимально ли составлен запрос? Какие индексы нужно построить для эффективного выполнения запроса и почему?

SELECT city.city_id, city.city, country.country
FROM city
JOIN country ON city.country_id = country.country_id
WHERE LOWER(LEFT(city.city, 2)) = 'mo';

5. Оптимально ли составлен запрос? Какие индексы нужно построить для эффективного выполнения запроса и почему?

SELECT COUNT(*), country.country
FROM city
JOIN country ON city.country_id = country.country_id
GROUP BY country.country;

6. Оптимально ли составлен запрос? Какие индексы нужно построить для эффективного выполнения запроса и почему?

SELECT film.title, SUM(payment.amount)
FROM customer
JOIN rental ON (rental.customer_id = customer.customer_id)
JOIN inventory ON (rental.inventory_id = inventory.inventory_id)
JOIN film ON (inventory.film_id = film.film_id)
JOIN payment ON (payment.rental_id = rental.rental_id)
WHERE EXTRACT(YEAR FROM rental.rental_date) = 2005
  AND customer.store_id = 2
GROUP BY film.title;

7. Оптимально ли составлен запрос? Какие индексы нужно построить для эффективного выполнения запроса и почему?

SELECT country.country, COUNT(*)
FROM store
JOIN address ON (store.address_id = address.address_id)
JOIN city ON (city.city_id = address.city_id)
JOIN country ON (country.country_id = city.country_id)
WHERE LOWER(LEFT(country.country, 1)) = 'a'
GROUP BY country.country;

8. Оптимально ли составлен запрос? Какие индексы нужно построить для эффективного выполнения запроса и почему?

SELECT film_id, title, description
FROM film
WHERE (
  SELECT COUNT(*)
  FROM actor
  JOIN film_actor ON (film_actor.actor_id = actor.actor_id)
  WHERE film_actor.film_id = film.film_id
    AND LOWER(actor.last_name) = 'monroe'
) > 0
  AND film.rating = 'G'
  AND film.release_year BETWEEN 2005 AND 2008;

Theory.md

Приветствуются подробные ответы с ссылками.

1. Что такое ACID? Ссылка на презентацию. Если вкратце, то сама абиривиатура расшивровывается как:

• Atomicity — Атомарность Основная статья: Атомарность Атомарность гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной. Поскольку на практике невозможно одновременно и атомарно выполнить всю последовательность операций внутри транзакции, вводится понятие «отката» (rollback): если транзакцию не удаётся полностью завершить, результаты всех её до сих пор произведённых действий будут отменены и система вернётся во «внешне исходное» состояние — со стороны будет казаться, что транзакции и не было. (Естественно, счётчики, индексы и другие внутренние структуры могут измениться, но, если СУБД запрограммирована без ошибок, это не повлияет на внешнее её поведение.)

• Consistency — Согласованность Основная статья: Согласованность данных Транзакция, достигающая своего нормального завершения (EOT — end of transaction, завершение транзакции) и, тем самым, фиксирующая свои результаты, сохраняет согласованность базы данных. Другими словами, каждая успешная транзакция по определению фиксирует только допустимые результаты. Это условие является необходимым для поддержки четвёртого свойства.

Согласованность является более широким понятием. Например, в банковской системе может существовать требование равенства суммы, списываемой с одного счёта, сумме, зачисляемой на другой. Это бизнес-правило и оно не может быть гарантировано только проверками целостности, его должны соблюсти программисты при написании кода транзакций. Если какая-либо транзакция произведёт списание, но не произведёт зачисление, то система останется в некорректном состоянии и свойство согласованности будет нарушено.

Наконец, ещё одно замечание касается того, что в ходе выполнения транзакции согласованность не требуется. В нашем примере, списание и зачисление будут, скорее всего, двумя разными подоперациями и между их выполнением внутри транзакции будет видно несогласованное состояние системы. Однако не нужно забывать, что при выполнении требования изоляции, никаким другим транзакциям эта несогласованность не будет видна. А атомарность гарантирует, что транзакция либо будет полностью завершена, либо ни одна из операций транзакции не будет выполнена. Тем самым эта промежуточная несогласованность является скрытой.

• Isolation — Изолированность См. также: Уровни изолированности транзакций Во время выполнения транзакции параллельные транзакции не должны оказывать влияние на её результат. Изолированность — требование дорогое, поэтому в реальных БД существуют режимы, не полностью изолирующие транзакцию (уровни изолированности Repeatable Read и ниже).

• Durability — Устойчивость Независимо от проблем на нижних уровнях (к примеру, обесточивание системы или сбои в оборудовании) изменения, сделанные успешно завершённой транзакцией, должны остаться сохранёнными после возвращения системы в работу. Другими словами, если пользователь получил подтверждение от системы, что транзакция выполнена, он может быть уверен, что сделанные им изменения не будут отменены из-за какого-либо сбоя.

2. Уровни изолированности транзакций.

3. Что такое “Потерянное обновление (Lost Update)”?

4. Что такое “Грязное чтение (Dirty Read)”?

5. Что такое “Неповторяющееся чтение (Non-Repeatable Read)”?

6. Что такое “Чтение фантомов (Phantom Reads)”?

7. Что такое “Аномалии сериализации”?

8. Что такое MVCC? Для чего нужно и как работает?

9. Зачем нужны хранимые процедуры и какие особенности их использования?

10. Зачем нужны триггера и какие особенности их использования?

11. Что такое журнал транзакций? Для чего он нужен и как работает?

12. Зачем нужны индексы?

13. Что такое частичные индексы?

14. Что такое кластерные индексы?

15. Что такое покрывающие индексы?

16. В чем разница между B-Tree/Hash/Bitmap индексами?

17. В чем разница между Merge join/Nested loop join/Hash join?

18. План выполнения запросов. EXPLAIN.

19. Нормализация и денормализация данных.

20. Что такое секционирование и для чего оно нужно?

21. Что такое COLLATION?

22. Чем отличаются корелирующие и не коррелирующие подзапросы?

23. Что лучше, JOIN или подзапрос? Почему?

24. Почему использование индекса может замедлить выполнение запроса?

25. Что такое селективность индекса?

26. Какие есть варианты протоколирования запросов?

27. Что такие VACUUM?