• PODSTAWY BAZ DANYCH

Podstawy baz danych 08

Baza

DVD Rental Service

source

restore

UNION

SELECT film_id, title
FROM film
WHERE title ILIKE 'Ac%'
UNION --ALL
SELECT film_id, title
FROM film
WHERE title ILIKE 'A%'
ORDER BY title;

EXCEPT

SELECT film_id, title
FROM film
WHERE title ILIKE 'A%'
EXCEPT
SELECT film_id, title
FROM film
WHERE title ILIKE 'Ac%'
ORDER BY title;

INTERSECT

SELECT film_id, title
FROM film
WHERE title ILIKE 'A%'
INTERSECT
SELECT film_id, title
FROM film
WHERE title ILIKE 'Ac%'
ORDER BY title;

Index

CREATE INDEX index_name ON table_name (field_name);
DROP INDEX index_name;
  • B-tree (default)
  • Hash (uuid)
  • GiST (2d geometric data)
  • SP-GiST (space partitioning)
  • GIN (arrays)
CREATE INDEX index_name_2 ON table_name (field_name_a, field_name_b);
index (a, b, c)

WHERE a = 5 AND b >= 42 AND c < 77

a = 5
b = 42 with a = 5
entries c >= 77 would be skipped

CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
CREATE UNIQUE INDEX name ON table (column [, ...]);
CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)));
CREATE INDEX orders_unbilled_index ON orders (order_nr) WHERE billed is not true;

Ćwiczenia

  1. sprawdzić i przenalizować indeksy istniejące w bazie
  2. napisać zapytania:
    • UNION
    • INTERSECT
    • EXCEPT
  3. na tabeli logs dodac indexy:
    • pole string, jaki typ indeksu?
    • pole timestamp, jaki typ indeksu?
    • pole uuid (jako PK), jaki typ indeksu?