Индексы, ограничения и язык запросов в PostgreSQL

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ

ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО

ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

НОВОСИБИРСКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

ФАКУЛЬТЕТ АВТОМАТИКИ И ВЫЧИСЛИТЕЛЬНОЙ ТЕХНИКИ

КАФЕДРА ВЫЧИСЛИТЕЛЬНОЙ ТЕХНИКИ

Лабораторная работа №2

по дисциплине «Базы данных»

«Индексы, ограничения и язык запросов в PostgreSQL»

Студенты:             Мельников Г.А.

                               Мордвинов А.А.

Группа:                 АМ-610

Вариант:               2

Преподаватель:   

Новосибирск, 2009

Цель работы

Изучение индексов в PostgreSQL и их влияния на скорость поиска информации в базе данных. Изучение команды EXPLAIN. Получение навыков работы с ограничениями. Получение навыков по работе с командой SELECT.

Задание – общая часть

Ознакомиться с теоретическими сведениями о возможностях создания индексов и ограничений в PostgreSQL. С помощью функции add_n(int) добавить в какую-либо из таблиц, созданных в лабораторной работе №1, 1000 записей (таблица не должно содержать полей типа date, но должно быть хотя бы одно поле типа char). Выполнить команду EXPLAIN ANALYSE select * from таблица where поле(условие)значение. Здесь таблица – имя таблицы, в которую добавлялись записи, поле – имя поля, по которому осуществляется выборка, условие – поочередно каждое из условий =, <, >, between, значение – значение поля, являющееся критерием выборки. Проанализировать полученную после выполнения операции информацию. Создать индекс типа BTREE для поля, по которому ранее осуществлялась выборка. Выполнить команду EXPLAIN ANALYSE с теми же условиями, что и без индекса, проанализировать полученную после выполнения операции информацию, сравнить результаты выполнения операции по каждому из условий выборки. Удалить индекс. Создать индекс типа HASH для того же поля. Выполнить команду EXPLAIN ANALYSE с теми же условиями, что и ранее, проанализировать полученную после выполнения операции информацию, сравнить результаты выполнения операции по каждому из условий выборки. Удалить индекс. Выполнить команду EXPLAIN ANALYSE select * from таблица where upper(поле)(условие)значение, условия те же, что и ранее, значение вводится большими буквами, поле должно быть типа char. По полю типа char той же таблицы создать функциональный индекс с функцией upper(поле). Выполнить команду EXPLAIN ANALYSE с теми же условиями, что и ранее, проанализировать полученную после выполнения операции информацию, сравнить результаты выполнения операции по каждому из условий выборки с информацией, полученной при выборке по этому полю без индекса. Наложить на выбранные поля идентификаторов в таблицах ограничение уникальности. Наложить ограничения согласно своему варианту. Проверить работоспособность ограничений путем добавления в таблицы данных, удовлетворяющих и не удовлетворяющих условиям ограничений. Ознакомиться с теоретическими сведениями о способах выборки данных из базы командой SELECT. Выполнить запросы согласно своему варианту. Если в базе нет данных, удовлетворяющих условиям запроса, изменить условия, либо добавить подходящие данные. Просмотреть и проанализировать полученную в результате выполнения операций информацию. Сохранить базу в файл для использования в следующих работах.

Задание – индивидуальная часть

Ограничить поля «цена» и «стоимость» так, чтобы они не содержали значений ниже 10 р. и выше 1500р. Найти все товары, прибыль от продажи которых более 10%. Вывести полную информацию о проданных товарах заданного наименования. Найти все товары заданного поставщика, количество которых на складе более 25 кг.

Ход работы

·  С помощью функции add_n(int) добавить в какую-либо из таблиц, созданных в лабораторной работе №1, 1000 записей

shop=# create function add_n(int) returns char

shop-#  as 'declare t int; begin

shop'#  select max(id) into t from products;

shop'#  for k in (t+1)..($1+t+1) loop

shop'#  insert into products(id, name, kolvo, priceprod, idmaster) values(k, round(random()*10^12) || '' '' || round(random()*10^5), round(random()*10^3), round(random()*10^2), round(random()*4));

shop'#  end loop; return ''Done!'';

shop'#  end;' language 'plpgsql';

CREATE FUNCTION

shop=# select add_n(1000);

 add_n

-------

 Done!

(1 row)

·  Выборка без использования индекса

shop=# EXPLAIN ANALYSE select * from products where id=500;

                                             QUERY PLAN

-----------------------------------------------------------------------------

--------------------

 Seq Scan on products  (cost=0.00..21.56 rows=1 width=37) (actual time=0.080..0.149 rows=1 loops=1)

   Filter: (id = 500)

 Total runtime: 0.171 ms

(3 rows)

shop=# EXPLAIN ANALYSE select * from products where id<500;

                                               QUERY PLAN

-----------------------------------------------------------------------------

------------------------

 Seq Scan on products  (cost=0.00..21.56 rows=500 width=37) (actual time=0.009..0.305 rows=499 loops=1)

   Filter: (id < 500)

 Total runtime: 0.444 ms

(3 rows)

shop=# EXPLAIN ANALYSE select * from products where id>500;

                                               QUERY PLAN

-----------------------------------------------------------------------------

------------------------

 Seq Scan on products  (cost=0.00..21.56 rows=505 width=37) (actual time=0.080..0.266 rows=505 loops=1)

   Filter: (id > 500)

 Total runtime: 0.404 ms

(3 rows)

shop=# EXPLAIN ANALYSE select * from products where id between 573 and 800;