Управление данными представления по своей сути мало чем отличается от таблиц, однако это во многом может облегчить вашу жизнь, сделав выполнение различных задач проще и удобнее.
Статья вводная. В ней хочу поделиться своим опытом использования представлений и постараюсь разжевать некоторые нюансы для новичков. Всех интересующихся приглашаю под кат.
Что такое View
Представление (View) это объект базы данных, представляющий собой именованный запрос, определенный с помощью оператора SELECT. Иногда представления называют виртуальными таблицами, и отчасти это так view доступно для пользователя как таблица, но само оно физически не содержит данных это просто именованный запрос, сохраненный в базе. Когда мы запрашиваем данные у представления, Oracle использует этот хранимый запрос для извлечения данных из базовых таблиц. Основное назначение View распределение прав на чтение и удобство использования, но у него есть и другие плюсы.
Преимущества представлений
- Безопасность
С помощью представлений можно ограничить доступ пользователей к хранимой информации. - Простота запросов
Представления позволяют извлекать данные из нескольких таблиц и представлять их как одну таблицу, превращая тем самым сложный запрос ко многим таблицам в простой однотабличный запрос к View. - Структурная простота
Для каждого пользователя можно создать собственную структуру базы данных, определив ее как множество доступных пользователю виртуальных таблиц. - Целостность данных
Если доступ к данным или их ввод осуществляется с помощью представления, Oracle может автоматически проверять выполнение определенных условий целостности.
Недостатки представлений
- Производительность
Если представление определяется многотабличным запросом, то простой запрос к нему становится сложным объединением, на выполнение которого может потребоваться много времени. Сложность запроса инкапсулируется во View, и пользователи не всегда представляют, какой объем работы может вызвать простой, на первый взгляд, запрос. - Ограничения на обновление
Обновление доступно только для простых представлений. Более сложные представления обновить не получится, так как они доступны только для выборки.
Эти недостатки не отменяют всех преимуществ View, они лишь означают, что не стоит применять многоуровневые представления бездумно. Каждый конкретный случай требует внимательного рассмотрения и продуманного подхода.
Создание представления
Перейдем к базовым примерам. Для большей наглядности, ниже схематично представлена структура создания представления:
Пример:
CREATE VIEW employees_vAS SELECT first_name, last_nameFROM employees;
Для создания представления используется структура CREATE VIEW, после которой мы указываем имя представления.
Важно: нельзя создавать представление с именем уже существующей таблицы, имена таблиц и представлений в рамках одной базы данных должны быть уникальными.
После оператора AS идет SELECT запрос (это может быть запрос к одной или сразу к нескольким таблицам, он может содержать группировки или другие сложные вычислительные выражения).
При необходимости, после имени представления можно указать названия столбцов (они указываются в круглых скобках через запятую). Список названий столбцов должен содержать столько элементов, сколько столбцов содержится в запросе, который определяет представление. Задаются только имена столбцов: тип данных, длину и другие характеристики мы берем из определения столбца в исходной таблице. Если список имен столбцов в инструкции CREATE VIEW отсутствует, то каждый из них по умолчанию получает имя соответствующего столбца запроса. Конструкция WITH CHECK OPTION определяет уровень проверки, когда данные вставляются или обновляются через представление.
Для удаления представления используется команда DROP VIEW и далее имя представления, которое мы хотим удалить. Принцип такой же, как и с таблицами базы данных:
DROP VIEW view_name;
Для просмотра содержимого представления используется оператор SELECT, аналогично как и в случае запроса к простой таблице:
SELECT columns FROM view_name[WHERE conditions];
Когда в запросе SQL встречается ссылка на представление, Oracle отыскивает определение этого представления, сохраненное в базе данных. В результате он преобразует пользовательский запрос, ссылающийся на представление, в эквивалентный запрос к исходным таблицам.
Давайте рассмотрим это на примере:
CREATE OR REPLACE VIEW my_employeesAS SELECT * FROM employeesWHERE MANAGER_ID=103;SELECT * FROM my_employees;SELECT * FROM(SELECT * FROM employeesWHERE MANAGER_ID=103);
Оператор CREATE OR REPLACE VIEW создает представление по имени my_employees. Это представление выдает информацию только о сотрудниках, подчиненных конкретному менеджеру. То есть менеджер с идентификатором 103 сможет опрашивать представление my_employees, как если бы это была обычная таблица, но содержащая лишь его сотрудников. Эта выборка обеспечивается с помощью запроса SELECT * FROM my_employees. При обращении к my_employees, Oracle находит сохраненный запрос, связанный с этим именем, преобразует запрос, ссылающийся на представление в эквивалентный запрос к таблице employees.
Этот пример показывает одно из преимуществ представлений: их можно использовать для добавления мер безопасности уровня значений, то есть для ограничения доступа к строкам таблицы. Доступ к строкам таблицы ограничен конструкцией WHERE в определении представления.
Виды представлений
В соответствии с типом запроса можно выделить разные по функциям представления.
Горизонтальные представления
Они как бы разрезают исходную таблицу по горизонтали. В представления входят все столбцы исходной таблицы, и лишь часть ее строк (как и в предыдущем примере, только строки MANAGER_ID=103). Это очень удобно, когда исходная таблица содержит данные, относящиеся к разным организациям или пользователям. Каждый пользователь получит личную таблицу с необходимыми ему строками.
Вертикальные представления
В этом случае доступ ограничивается уже к столбцам таблицы:
Здесь таблица employees содержит заработные платы сотрудников. View может ограничивать доступ к этой информации, предоставляя все необходимые пользователю столбцы, за исключением столбца salary (он не внесен в скрипт создания представления v_employees).
Это может напоминать фильтрацию, но нужно понимать, что фильтрация это выборка. Мы же создаем представление, в котором ее определяем. Таким образом пользователь получит только ту информацию, которую мы определили в конструкции WHERE или в описании столбцов.
Смешанные представления
При создании представлений Oracle не разделяет их на горизонтальные и вертикальные. Эти понятия лишь помогают понять, каким образом из исходной таблицы формируется view. Использование представлений, разделяющих исходную таблицу одновременно как в горизонтальном, так и в вертикальном направлении вполне распространенное явление:
CREATE VIEW v_employeesAS SELECT employee_id, first_name, last_name, email, phone_numberFROM employeesWHERE department_id = 50;SELECT * FROM v_employees;
Данные, полученные при помощи этого представления, представляют собой подмножество строк и столбцов таблицы EMPLOYEES. В результат будут включены только те столбцы, которые указаны в операторах SELECT и WHERE.
Сгруппированные представления
Запрос, определяющий представление, может содержать предложение GROUP BY.
Представления такого типа называются сгруппированными и выполняют ту же функцию, что и запросы с группировкой. В них родственные строки данных объединяются в группы, и для каждой группы в таблице результатов запроса создается одна строка, содержащая итоговые данные по этой группе. Так мы получаем виртуальную таблицу, к которой можно обращаться с запросами:
CREATE OR REPLACE VIEW v_employeesAS SELECT department_id, ROUND(AVG(salary)) AS avg_salary, count(*) AS emp_cntFROM employeesGROUP BY department_id;SELECT * FROM v_employees;
В отличиe от горизонтальных и вертикальных представлений, каждой строке сгруппированного представления не соответствует одна строка исходной таблицы. Оно отображает исходную таблицу в виде резюме, поэтому поддержка такой виртуальной таблицы может потребовать значительного объема вычислений, если данных в таблице очень много. Не обязательно каждый раз выполнять операции avg, count. Если мы обратимся к SELECT * FROM v_employees, то сможем получить визуализацию средней заработной платы более простым путем.
Соединенные представления
В их основе лежат многотабличные запросы. После создания такого представления к нему можно обращаться с помощью однотабличного запроса, что значительно упрощает задачу. В противном случае пришлось бы применять сложные соединения нескольких таблиц. Следующее представление является объединением трех таблиц:
CREATE OR REPLACE VIEW v_employeesAS SELECT e.employee_id, e.first_name, e.last_name, d.department_name, l.city, l.country_idFROM employees e, departments d, locations l WHERE e.DEPARTMENT_ID=d.DEPARTMENT_ID AND d.LOCATION_ID=l.LOCATION_ID;SELECT * FROM v_employees WHERE country_id='US';
С помощью данного запроса мы легко получим список сотрудников, проживающих в определенной стране или городе. Согласитесь, значительно легче сформировать запрос к такому представлению, чем создавать каждый раз эквивалентное соединение трех таблиц.
Обновление представлений
Обычно представления используются для запросов, но при некоторых обстоятельствах их можно использовать в командах INSERT, DELETE и UPDATE:
CREATE OR REPLACE VIEW v_employeesAS SELECT employee_id, first_name, last_name, email, phone_numberFROM employees;INSERT INTO v_employeesVALUES (1001, 'Ivan', 'Ivanov', 'ivanov@gmail.com', '78945612');UPDATE v_employeesSET first_name='Bob' WHERE EMPLOYEE_ID=100;DELETE FROM v_employeesWHERE EMPLOYEE_ID=100;
Допускается выполнение операций над представлениями, которые не имеют в своем определении конструкций GROUP BY, START WITH, CONNECT BY, либо каких-то подзапросов в конструкции SELECT. Проще говоря, с помощью view можно выполнить INSERT и UPDATE, меняя тем самым реальные данные (из таблиц, лежащих в основе этого представления). Однако, такое обновление не всегда можно произвести это зависит от ограничений целостности, наложенных на таблицы.
Разрешение представления (View Resolution)
Всякий раз, когда к представлению нужен доступ, Oracle должен выполнить запрос, по которому оно определено. Этот процесс наполнения представления называется его разрешением и происходит при каждом обращении пользователя:
Для выполнения запросов к сложным представлениям (с множеством конструкций JOIN и GROUP BY, например) Oracle может потребоваться их материализация. Oracle выполняет запрос, определяющий представление, и сохраняет результаты во временной таблице. По окончании обработки запроса временная таблица уничтожается. Процесс разрешения представления может потребовать длительного времени, а большая нагрузка на базу данных чревата снижением производительности. Для решения этих проблем в Oracle используются материализованные представления.
Материализованные представления (Materialized Views)
В отличии от стандартных вьюх, они имеют физическое воплощение, занимают место и требуют хранения, подобно обычным таблицам. При необходимости их можно секционировать и даже создавать на них индексы. Для поддержания точности данных материализованного представления используется журнал MView Log:
Материализованные представления ускоряют запросы и обеспечивают более быстрое их выполнение благодаря перерасчету хранения результатов дорогостоящих соединений и агрегатных операций (их не придется пересчитывать заново). Другими словами, достаточно лишь наполнить материализованное представление данными, чтобы получать оттуда информацию без создания эквивалентных запросов к таблицам в Oracle. В связи с этим возникает другая проблема: данные могут быть не совсем актуальными. Для их обновления мы будем использовать MView Log (журнал материализованного представления), с его помощью можно настроить обновление материализованных представлений по расписанию.
Синтаксис создания материализованного представления:
CREATE MATERIALIZED VIEW view-name BUILD [IMMEDIATE | DEFERRED] REFRESH [FAST | COMPLETE | FORCE ] ON [COMMIT | DEMAND ] [[ENABLE | DISABLE] QUERY REWRITE] AS SELECT ...;
Пример создания журнала материализованного представления:
CREATE MATERIALIZED VIEW LOG ON employees;
Конструкция BUILD IMMEDIATE немедленно наполняет материализованное представление эта опция принята по умолчанию. Прямо во время создания материализованного представления будет происходить его заполнение данными.
Опция BUILD DEFERRED означает, что материализованное представление заполняется при выполнении первого обновления (желаемое время внесения обновления можно задать).
Команда REFRESH COMPLETE подразумевает полное перевычисление результата, все данные удаляются и загружаются заново. Трудоемкость такой операции будет зависеть от размера материализованного представления.
С помощью конструкции REFRESH FAST мы отслеживаем изменения в базовых таблицах (применяя журнал MView Log). Обновляются только лежащие в основе материализованного представления данные.
Опция REFRESH FORCE означает, что при возможности Oracle попытается применить REFRESH FAST (быстрое обновление), а иначе будет использована опция REFRESH COMPLETE.
Часть ON COMMIT конструкции REFRESH указывает на то, что все зафиксированные изменения в базовых таблицах распространялись на МП сразу же после выполнения команды COMMIT. При использовании опции ON DEMAND обновление инициируется запросом вручную или запланированной задачей. Опция ENABLE | DISABLE QUERY REWRITE используется тогда, когда пользователи пишут запросы с обращением к лежащим в основе представления таблицам. Oracle автоматически переписывает их для использования материализованного представления. Такая техника оптимизации называется переписыванием запросов и увеличивает их производительность в базе данных.
Заключение
Когда использовать View
Концепт представления виртуальная таблица на основе запросов. Обычно его используют с целью упростить запросы и видимую структуру базы данных, а также для защиты некоторых строк и столбцов от несанкционированного доступа. Если нам нужны актуальные, сиюминутные данные, то мы используем именно стандартные представления.
Когда использовать Materialized View
Материализованные представления вычисляются и записываются в кэш-память диска заранее. Их мы выберем для работы с большими объемами данных и соединениями таблиц. На моей практике были случаи, когда материализованные представления помогали сделать оптимизацию для систем в разы быстрее и спасали ситуацию.
Надеюсь, пост был полезен.
