Что такое код create view


Содержание

Представления и табличные объекты

Представления

Представления или Views представляют виртуальные таблицы. Но в отличии от обычных стандартных таблиц в базе данных представления содержат запросы, которые динамически извлекают используемые данные.

Представления дают нам ряд преимуществ. Они упрощают комплексные SQL-операции. Они защищают данные, так как представления могут дать доступ к части таблицы, а не ко всей таблице. Представления также позволяют возвращать отформатированные значения из таблиц в нужной и удобной форме.

Для создания представления используется команда CREATE VIEW , которая имеет следующую форму:

Например, пусть у нас есть три связанных таблицы:

Теперь добавим в базу данных, в которой содержатся данные таблицы, следующее представление:

То есть данное представление фактически будет возвращать сводные данные из трех таблиц. И после его создания мы сможем его увидеть в узле Views у выбранной базы данных в SQL Server Management Studio:

Теперь используем созданное выше представление для получения данных:

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

Представления могут иметь не более 1024 столбцов и могут обращать не более чем к 256 таблицам.

Также можно создавать представления на основе других представлений. Такие представления еще называют вложенными (nested views). Однако уровень вложенности не может быть больще 32-х.

Команда SELECT , используемая в представлении, не может включать выражения INTO или ORDER BY (за исключением тех случаев, когда также применяется выражение TOP или OFFSET ). Если же необходима сортировка данных в представлении, то выражение ORDER BY применяется в команде SELECT, которая извлекает данные из представления.

Также при создании представления можно определить набор его столбцов:

Изменение представления

Для изменения представления используется команда ALTER VIEW . Эта команда имеет практически тот же самый синтаксис, то и CREATE VIEW :

Например, изменим выше созданное представление OrdersProductsCustomers:

Удаление представления

Для удаления представления вызывается команда DROP VIEW :

Также стоит отметить, что при удалении таблиц также следует удалить и представления, которые используют эти таблицы.

Команда создания представления – CREATE VIEW

Представление есть виртуальная таблица, доступ к которой осуществляется так же как к реально существующей таблице.

Синтаксис создания представления:

CREATE VIEW пользователь.имя_представления

Оператор CREATE VIEW создает “логическое окно” (виртуальную таблицу, представление данных) для одной или нескольких таблиц или таких представлений.

В нижеприведенном примере мы создаем представление с именем «v_fiz_lic», которое содержит все столбцы и данные таблицы «s_fiz_lic».

CREATE VIEW V_FIZ_LIC AS (SELECT * FROM S_FIZ_LIC);

Рассмотрим более сложный пример создания представления с именем «v_fiz_lic2». Создаваемое представление основывается на двух таблицах «Физических лиц» и «Должностей», после служебного слова FROM через запятую перечисляют таблицы, в результате мы пересекаем строки 2-х таблиц и оставляем те записи, у которых значение поля «KOD» таблицы «DOLJ» равно значению поля «KOD_DOLJ» таблицы «S_FIZ_LIC3».

CREATE VIEW V_FIZ_LIC2 (KOD, FIO, POL, DOLJ, DATA_R) AS (SELECT d.KOD, d.FIO, d.POL, f.naimen, d.DATA_R FROM S_FIZ_LIC3 d, dolj f where d.kod_dolj=f.kod);

Данное представление позволяет отобразить список физических лиц и их должности из таблицы «Должности».

Команда изменения таблицы – ALTER TABLE

Оператор ALTER TABLE позволяет добавлять в таблицу столбцы и ограничения, изменять размер и тип данных существующих столбцов, изменять установки NULL/NOT NULL, удалять ограничения, изменять схему хранения таблицы.

Синтаксис изменения таблицы:

ALTER TABLE имя_пользователя.таблица

ADD [CONSTRAINT] описание_столбца | ограничение_таблицы,

MODIFY [RENAME| CONSTRAINT] описание_столбца, описание_столбца.

DROP COLUMN|CONSTRAINT ограничение, ограничение.

Рассмотрим примеры использования команды ALTER применительно к таблице. В нижеприведенном примере мы производим добаление столбца «sem_pol» с типом данных NUMBER. При добавлении столбца можно использовать дополнитльные опции, как при создании таблицы (DEFAULT, NOT NULL и т.д.), кроме командой ALTER можно добавить несколько столбцов одной командой, для этого нужно их перечислить через запятую.

ALTER TABLE s_fiz_lic3 ADD sem_pol NUMBER(1);

В следующий пример проиллюстрировано, как произвести перемименование столбца. В этом прмере меняем имя созданного столбца с «sem_pol» на «sem_poloj».

ALTER TABLE s_fiz_lic3 RENAME COLUMN sem_pol TO sem_poloj;

В третьем примере мы производим учеличение размерности «sem_poloj» с 1 до 10. Небходимо отметить, что если столбец таблицы содержит данные, то мы можем только увеличить значение размерности, уменьшить размерность мы можем в том случаи, если все строки по этому столбу содержат значение NULL.

ALTER TABLE s_fiz_lic3 MODIFY sem_poloj NUMBER(10);


В данный пример показывает как проивзодить удаление столбца таблицы.

ALTER TABLE s_fiz_lic3 DROP COLUMN sem_poloj;

Для индентификации строк и оптимизации таблицы используют первичный ключ. Первичный ключ может состоять из одного столбца или быть составным (несколько столбцов). Такой уникальный столбец (или уникальная группа столбцов), используемый для идентификации каждой строки и хранения всех строк по отдельности, называется первичным ключом таблицы.

Первичные ключи таблицы — важный элемент в структуре базы данных. Первичный ключ гарантируют, что ваши данные имеют определенную целостность. Если первичный ключ правильно используется и поддерживается, вы будете знать, что нет пустых строк таблицы, и что каждая строка отличается от любой другой строки.

Следующий пример показывает, как нужно добавлять перчиный ключ таблицы. Служебные слова USING INDEX используются опцинально, указывая эти слова, производится автоматическое создание системного индекса по данному столбцу таблицы.

ALTER TABLE s_fiz_lic ADD PRIMARY KEY (KOD) USING INDEX;

В следующем примере мы производим удаление вторичного (внешнего) ключа.

ALTER TABLE s_fiz_lic3 DROP CONSTRAINT fk_dolj;

В следующем примере мы производим создание вторичного ключа, которые представляет собой ограничение целостности для изменяемой таблицы. Вторичный ключ определяет перечень тех значений, которые будет принимать столбец изменяемой таблицы. Общий синтаксис:

ALTER TABLE имя_таблицы ADD CONSTRAINT имя_вторичного_ключа FOREIGN KEY (список_столбцов) REFERENCES имя_родит_таблицы (список_столбцов);

При создании вторичного (внешенго) ключа необходимо учесть, что тип данных и размерность столбцов обеих таблиц должны совпадать. У родительской таблицы (таблица, которая определяет перечень значений) по этому столбцу должен быть создан первичый ключ, данный столбец является родительским по отношению к столбцу другой таблицы.

ALTER TABLE s_fiz_lic3 ADD CONSTRAINT fk_dolj FOREIGN KEY (kod_dolj) REFERENCES dolj (KOD);

Два следующих примера иллюстрируют, как производить включение и отключение вторичного ключа. Часто требуется производить отключением проверки вводимых значений, для этого необходимо произвести отключение вторичного ключа.

ALTER TABLE s_fiz_lic3 MODIFY CONSTRAINT fk_dolj DISABLE; ALTER TABLE s_fiz_lic3 MODIFY CONSTRAINT fk_dolj ENABLE;

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: На стипендию можно купить что-нибудь, но не больше. 8992 — | 7237 — или читать все.

188.64.174.135 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

Отключите adBlock!
и обновите страницу (F5)

очень нужно

Представления (VIEW) в MySQL (исходники)

В комментариях Хабра упоминались вопросы по использованию представлений. Данный топик является обзором представлений, появившихся в MySQL версии 5.0. В нем рассмотрены вопросы создания, преимущества и ограничения представлений.

Что такое представление?

Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.

Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.

Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).

Преимущества использования представлений:


  1. Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление. Это очень удобно в случае если пользователю нужно дать права на отдельные строки таблицы или возможность получения не самих данных, а результата каких-то действий над ними.
  2. Позволяет разделить логику хранения данных и программного обеспечения. Можно менять структуру данных, не затрагивая программный код, нужно лишь создать представления, аналогичные таблицам, к которым раньше обращались приложения. Это очень удобно когда нет возможности изменить программный код или к одной базе данных обращаются несколько приложений с различными требованиями к структуре данных.
  3. Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.

Ограничения представлений в MySQL

В статье приведены ограничения для версии MySQL 5.1 (в дальнейшем их число может сократиться).

  • нельзя повесить триггер на представление,
  • нельзя сделать представление на основе временных таблиц; нельзя сделать временное представление;
  • в определении представления нельзя использовать подзапрос в части FROM,
  • в определении представления нельзя использовать системные и пользовательские переменные; внутри хранимых процедур нельзя в определении представления использовать локальные переменные или параметры процедуры,
  • в определении представления нельзя использовать параметры подготовленных выражений (PREPARE),
  • таблицы и представления, присутствующие в определении представления должны существовать.
  • только представления, удовлетворяющие ряду требований, допускают запросы типа UPDATE, DELETE и INSERT.

Создание представлений

Для создания представления используется оператор CREATE VIEW, имеющий следующий синтаксис:

CREATE [OR REPLACE]
[ALGORITHM = ]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED / LOCAL] CHECK OPTION]

view_name — имя создаваемого представления. select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении

Оператор CREATE VIEW содержит 4 необязательные конструкции:

  1. OR REPLACE — при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано. В противном случае возникнет ошибка, информирующая о сществовании представления с таким именем и новое представление создано не будет. Следует отметить одну особенность — имена таблиц и представлений в рамках одной базы данных должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.
  2. ALGORITM — определяет алгоритм, используемый при обращении к представлению (подробнее речь об этом пойдет ниже).
  3. column_list — задает имена полей представления.
  4. WITH CHECK OPTION — при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. В случае несоответствия данное изменение не будет выполнено. Обратите внимание, что при указании данной конструкции для необновляемого представления возникнет ошибка и представление не будет создано. (подробнее речь об этом пойдет ниже).

По умолчанию колонки представления имеют те же имена, что и поля возращаемые оператором SELECT в определении представления. При явном указании имен полей представления column_list должен включать по одному имени для каждого поля разделенных запятой. Существует две причины по которым желательно использовать явное указание имен полей представления:


    Имена полей представления должны быть уникальны в пределах данного представления. При создании представления основанного на нескольких таблицах возможна ситуация повторения имен полей представления. Например:

CREATE VIEW v AS SELECT a.id, b.id FROM a,b;

CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;

CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;

CREATE VIEW v AS SELECT group_concat(DISTINCT column_name oreder BY column_name separator `+`) FROM table_name;


Для просмотра содержимого представления мы используем оператор SELECT (полностью аналогично как в случае простой таблицы), с другой строны, оператор SELECT есть в самом определении представления, т.е. получается вложенная конструкция — запрос в запросе. При этом, некоторые конструкции оператора SELECT могут присутствовать в обоих операторах. Возможны три варианта развития событий: они обе будут выполнены, одна из них будет проигнорированна и результат неопределен. Рассмотрим подробнее эти случаи:

  1. Если в обоих операторах встречается условие WHERE, то оба этих условия будут выполнены как если бы они были объединены оператором AND.
  2. Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.
  3. При наличии в обоих операторах модификаторов, влияющих на механизм блокировки, таких как HIGH_PRIORITY, результат их совместного действия неопределен. Для избежания неопределенности рекомендуется в определении представления не использовать подобные модификаторы.

Алгоритмы представлений

Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE.

В случае алгоритма MERGE, MySQL при обращении к представлению добавляет в использующийся оператор соответствующие части из определения представления и выполняет получившийся оператор.

В случае алгоритма TEMPTABLE, MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.
Обратите внимание : в случае использования этого алгоритма представление не может быть обновляемым (см. далее).

При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ALGORITHM = ]
UNDEFINED означает, что MySQL сам выбирает какой алгоритм использовать при обращении к представлению. Это значение по умолчанию, если данная конструкция отсутствует.

Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает отношение числа просмотров к числу ответов для тем форума:

CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0;

Для данного представления каждая строка соответствует единственной строке из таблицы topics, т.е. может быть использован алгоритм MERGE. Рассмотрим следующее обращение к нашему представлению:

SELECT subject, param FROM v WHERE param>1000;

В случае MERGE алгоритма MySQL включает определение представления в использующийся оператор SELECT: заменяет имя представления на имя таблицы, заменяет список полей на определения полей представления и добавляет условие в части WHERE с помощью оператора AND. Итоговый оператор, выполняемый затем MySQL, выглядит следующим образом:

SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;

Если в определении представления используются групповые функции (count, max, avg, group_concat и т.д.), подзапросы в части перечисления полей или конструкции DISTINCT, GROUP BY, то не выполняется требуемое алгоритмом MERGE соответствие 1 к 1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает количество тем для каждого форума:

CREATE VIEW v AS SELECT forum_id, count(*) AS num FROM topics GROUP BY forum_id;

Найдем максимальное количество тем в форуме:

Если бы использовался алгоритм MERGE, то этот запрос был бы преобразован следующим образом:

SELECT MAX(count(*)) FROM topics GROUP BY forum_id;

Выполнение этого запроса приводит к ошибке «ERROR 1111 (HY000): Invalid USE of GROUP function», так как используется вложенность групповых функций.

В этом случае MySQL использует алгоритм TEMPTABLE, т.е. заносит содержимое представления во временную таблицу (данный процесс иногда называют «материализацией представления»), а затем вычисляет MAX() используя данные временной таблицы:

CREATE TEMPORARY TABLE tmp_table SELECT forum_id, count(*) AS num FROM topics GROUP BY forum_id;
SELECT MAX(num) FROM tmp_table;
DROP TABLE tpm_table;

Подводя итог, следует отметить, что нет серьезных причин явно указывать алгоритм при создании представления, так как:

  1. В случае UNDEFINED MySQL пытается использовать MERGE везде где это возможно, так как он более эффективен чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым.
  2. Если вы явно указываете MERGE, а определение представления содержит конструкции запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND.

Обновляемость представлений

Представление называется обновляемым, если к нему могут быть применимы операторы UPDATE и DELETE для изменения данных в таблицах, на которых основано представление. Для того, чтобы представление было обновляемым должно быть выполнено 2 условия:

  1. Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
  2. Поля представления должны быть простым перечислением полей таблиц, а не выражениеями col1/col2 или col1+2.

Обратите внимание : встречающиеся в русско-язычной литературе требования, чтобы обновляемое представление было основано на единственной таблице и присутствие в числе полей представления первичного ключа физичекой таблицы не являются необходимыми. Скорее всего требование единственной таблицы является ошибкой перевода. Дело в том, что через представление, основанное на нескольких таблицах, может обновлять только одну таблицу за запрос, т.е. конструкция SET оператора UPDATE должна перечислять колонки только одной таблицы из определения представления. Кроме того, чтобы представление, основанное на нескольких таблицах, было обновляемым, таблицы в его определении должны быть объединены только с помощью INNER JOIN, а не OUTER JOIN или UNION.

Обновляемое представление может допускать добавление данных (INSERT), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию.

Обратите внимание : для представлений, основанных на нескольких таблицах, операция добавления данных (INSERT) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных (DELETE) для таких представлений не поддерживается.

При использовании в определении представления конструкции WITH [CASCADED / LOCAL] CHECK OPTION все добавляемые или изменяемые строки будут проверяться на соответствие определению представления.

  • Изменение данных (UPDATE) будет происходить только если строка с новыми значениями удовлетворяет условию WHERE в определении представления.
  • Добавление данных (INSERT) будет происходить только если новая строка удовлетворяет условию WHERE в определении представления.

Иными словами, нельзя добавить или изменить данные в представлении таким образом, чтобы они не были доступны через представление.

Ключевые слова CASCADED и LOCAL определяют глубину проверки для представлений основанных на других представлениях:

  • Для LOCAL происходит проверка условия WHERE только в собственном определении представления.
  • Для CASCADED происходит проверка для всех представлений на которых основанно данное представление. Значением по умолчанию является CASCADED.

Рассмотрим пример обновляемого представления, основанного на двух таблицах. Пусть наше представление выбирает темы форума с числом просмотров более 2000.

punbb >CREATE OR REPLACE VIEW v AS
-> SELECT forum_name, `subject`, num_views FROM topics,forums f
-> WHERE forum_ >2000 WITH CHECK OPTION;
Query OK, 0 rows affected (0.03 sec)

punbb >UPDATE v SET num_views=2003 WHERE subject=`test`;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 WARNINGS: 0

punbb >SELECT subject, num_views FROM topics WHERE subject=`test`;
+———+————+
/ subject / num_views /
+———+————+
/ test / 2003 /
+———+————+
1 rows IN SET (0.01 sec)

Однако, если мы попробуем установить значение num_views меньше 2000, то новое значение не будет удовлетворять условию WHERE num_views>2000 в определении представления и обновления не произойдет.

punbb >UPDATE v SET num_views=1999 WHERE subject=`test`;
ERROR 1369 (HY000): CHECK OPTION failed `punbb.v`

Не все обновляемые представления позволяют добавление данных:


punbb >INSERT INTO v (subject,num_views) VALUES(`test1`,4000);
ERROR 1369 (HY000): CHECK OPTION failed `punbb.v`

Причина в том, что значением по умолчанию колонки forum_ >

punbb >INSERT INTO v (forum_id,subject,num_views) VALUES(1,`test1`,4000);
ERROR 1054 (42S22): Unknown COLUMN `forum_id` IN `field list`

С другой строны:

punbb >INSERT INTO v (forum_name) VALUES(`TEST`);
Query OK, 1 row affected (0.00 sec)

Таким образом, наше представление, основанное на двух таблицах, позволяет обновлять обе таблицы и добавлять данные только в одну из них.

SQL — Использование представлений

Дата публикации: 2020-12-11

От автора: представление — это не что иное, как оператор SQL, который хранится в базе данных с соответствующим именем. На самом деле представление является определенным структурированным набором данных таблицы в виде предопределенного SQL-запроса.

Представления SQL могут содержать все строки таблицы или определенные выбранные строки. Представление может быть создано из одной или нескольких таблиц, это зависит от составленного для представления SQL-запроса.

Представления, которые являются типом виртуальных таблиц, позволяют пользователям делать следующее:

Структурировать данные таким образом, который будет наиболее естественным или интуитивным для пользователей или классов пользователей.

Ограничивать доступ к данным таким образом, чтобы пользователь мог находить и изменять (в некоторых случаях) только те данные, которые ему нужны, и не более того.

Как создать сайт самому?

Какие технологии и знания необходимы сегодня, чтобы создавать сайты самостоятельно? Узнайте на интенсиве!

Обобщать данные из разных таблиц, чтобы использовать их для различных отчетов.

Создание представлений

Представления базы данных создаются с помощью инструкции CREATE VIEW. Представления можно создать из одной таблицы, нескольких таблиц или другого представления.

Пользователь должен иметь соответствующие системные привилегии в зависимости от конкретной реализации.
Основной синтаксис CREATE VIEW следующий:

Что такое код create view

CREATE VIEW — создать представление

Синтаксис

Описание

CREATE VIEW создаёт представление запроса. Создаваемое представление лишено физической материализации, поэтому указанный запрос будет выполняться при каждом обращении к представлению.

Команда CREATE OR REPLACE VIEW действует подобным образом, но если представление с этим именем уже существует, оно заменяется. Новый запрос должен выдавать те же столбцы, что выдавал запрос, ранее определённый для этого представления (то есть, столбцы с такими же именами должны иметь те же типы данных и следовать в том же порядке), но может добавить несколько новых столбцов в конце списка. Вычисления, в результате которых формируются столбцы представления, могут быть совершенно другими.

Если задано имя схемы (например, CREATE VIEW myschema.myview . ), представление создаётся в указанной схеме, в противном случае — в текущей. Временные представления существуют в специальной схеме, так что при создании таких представлений имя схемы задать нельзя. Имя представления должно отличаться от имён других представлений, таблиц, последовательностей, индексов или сторонних таблиц в этой схеме.

Параметры

С таким указанием представление создаётся как временное. Временные представления автоматически удаляются в конце сеанса. Существующее постоянное представление с тем же именем не будет видно в текущем сеансе, пока существует временное, однако к нему можно обратиться, дополнив имя указанием схемы.

Если в определении представления задействованы временные таблицы, представление так же создаётся как временное (вне зависимости от присутствия явного указания TEMPORARY ). RECURSIVE

Создаёт рекурсивное представление. Синтаксис

Для рекурсивного представления обязательно должен задаваться список с именами столбцов. имя

Имя создаваемого представления (возможно, дополненное схемой). имя_столбца

Необязательный список имён, назначаемых столбцам представления. Если отсутствует, имена столбцов формируются из результатов запроса. WITH ( имя_параметра_представления [= значение_параметра_представления ] [, . ] )

В этом предложении могут задаваться следующие необязательные параметры представления:

Этот параметр может принимать значение local (локально) или cascaded (каскадно) и равнозначен указанию WITH [ CASCADED | LOCAL ] CHECK OPTION (см. ниже). Изменить этот параметр у существующего представления с помощью ALTER VIEW нельзя. security_barrier ( boolean )

Этот параметр следует использовать, если представление должно обеспечивать безопасность на уровне строк. За дополнительными подробностями обратитесь к Разделу 39.5.

Команда SELECT или VALUES , которая выдаёт столбцы и строки представления. WITH [ CASCADED | LOCAL ] CHECK OPTION

Это указание управляет поведением автоматически изменяемых представлений. Если оно присутствует, при выполнении операций INSERT и UPDATE с этим представлением будет проверяться, удовлетворяют ли новые строки условию, определяющему представление (то есть, проверяется, будут ли новые строки видны через это представление). Если они не удовлетворяют условию, операция не будет выполнена. Если указание CHECK OPTION отсутствует, команды INSERT и UPDATE смогут создавать в этом представлении строки, которые не будут видны в нём. Поддерживаются следующие варианты проверки:

Новые строки проверяются только по условиям, определённым непосредственно в самом представлении. Любые условия, определённые в нижележащих базовых представлениях, не проверяются (если только в них нет указания CHECK OPTION ). CASCADED

Новые строки проверяются по условиям данного представления и всех нижележащих базовых. Если указано CHECK OPTION , а LOCAL и CASCADED опущено, подразумевается указание CASCADED .

Указание CHECK OPTION нельзя использовать с рекурсивными представлениями.

Заметьте, что CHECK OPTION поддерживается только для автоматически изменяемых представлений, не имеющих триггеров INSTEAD OF и правил INSTEAD . Если автоматически изменяемое представление определено поверх базового представления с триггерами INSTEAD OF , то для проверки ограничений автоматически изменяемого представления можно применить указание LOCAL CHECK OPTION , хотя условия базового представления с триггерами INSTEAD OF при этом проверяться не будут (каскадная проверка не будет спускаться к представлению, модифицируемому триггером, и любые параметры проверки, определённые для такого представления, будут просто игнорироваться). Если для представления или любого из его базовых отношений определено правило INSTEAD , приводящее к перезаписи команды INSERT или UPDATE , в перезаписанном запросе все параметры проверки будут игнорироваться, в том числе проверки автоматически изменяемых представлений, определённых поверх отношений с правилом INSTEAD .

Замечания


Для удаления представлений применяется оператор DROP VIEW .

Будьте аккуратны в определении представления, чтобы получить желаемые имена и типы столбцов. Например, такая команда:

создаст представление с двумя недостатками: именем столбца по умолчанию будет ?column? , а типом данных — unknown (неизвестный). Если вы хотите получить в представлении строковую константу, лучше сделать так:

Доступ к таблицам, задействованным в представлении, определяется правами владельца представления. В некоторых случаях это позволяет организовать безопасный, но ограниченный доступ к нижележащим таблицам. Однако учтите, что не все представления могут быть защищёнными; за подробностями обратитесь к Разделу 39.5. Функции, вызываемые в представлении, выполняются так, как будто они вызываются непосредственно из запроса, обращающегося к представлению. Поэтому пользователь представления должен иметь все права, необходимые для вызова всех функций, задействованных в представлении.

При выполнении CREATE OR REPLACE VIEW для существующего представления меняется только правило SELECT, определяющее представление. Другие свойства представления, включая владельца, права и правила, кроме SELECT, остаются неизменными. Чтобы изменить определение представления, необходимо быть его владельцем (или членом роли-владельца).

Изменяемые представления

Простые представления становятся изменяемыми автоматически: система позволит выполнять команды INSERT , UPDATE и DELETE с таким представлением так же, как и с обычной таблицей. Представление будет автоматически изменяемым, если оно удовлетворяют одновременно всем следующим условиям:

Список FROM в запросе, определяющем представлении, должен содержать ровно один элемент, и это должна быть таблица или другое изменяемое представление.

Определение представления не должно содержать предложения WITH , DISTINCT , GROUP BY , HAVING , LIMIT и OFFSET на верхнем уровне запроса.

Определение представления не должно содержать операции с множествами ( UNION , INTERSECT и EXCEPT ) на верхнем уровне запроса.

Список выборки в запросе не должен содержать агрегатные и оконные функции, а также функции, возвращающие множества.

Автоматически обновляемое представление может содержать как изменяемые, так и не изменяемые столбцы. Столбец будет изменяемым, если это простая ссылка на изменяемый столбец нижележащего базового отношения; в противном случае этот столбец будет доступен только для чтения, и если команда INSERT или UPDATE попытается записать значение в него, возникнет ошибка.

Если представление автоматически изменяемое, система будет преобразовывать обращающиеся к нему операторы INSERT , UPDATE и DELETE в соответствующие операторы, обращающиеся к нижележащему базовому отношению. При этом в полной мере поддерживаются операторы INSERT с предложением ON CONFLICT UPDATE .

Если автоматически изменяемое представление содержит условие WHERE , это условие ограничивает набор строк, которые могут быть изменены командой UPDATE и удалены командой DELETE в этом представлении. Однако UPDATE может изменить строку так, что она больше не будет соответствовать условию WHERE и, как следствие, больше не будет видна через представление. Команда INSERT подобным образом может вставить в базовое отношение строки, которые не удовлетворят условию WHERE и поэтому не будут видны через представление ( ON CONFLICT UPDATE может подобным образом воздействовать на существующую строку, не видимую через представление). Чтобы запретить командам INSERT и UPDATE создавать такие строки, которые не видны через представление, можно воспользоваться указанием CHECK OPTION .

Если автоматически изменяемое представление имеет свойство security_barrier (барьер безопасности), то все условия WHERE этого представления (и все условия с герметичными операторами ( LEAKPROOF )) будут всегда вычисляться перед условиями, добавленными пользователем представления. За подробностями обратитесь к Разделу 39.5. Заметьте, что по этой причине строки, которые в конце концов не были выданы (потому что не прошли проверку в пользовательском условии WHERE ), могут всё же остаться заблокированными. Чтобы определить, какие условия применяются на уровне отношения (и, как следствие, избавляют часть строк от блокировки), можно воспользоваться командой EXPLAIN .

Более сложные представления, не удовлетворяющие этим условиям, по умолчанию доступны только для чтения: система не позволит выполнить операции добавления, изменения или удаления строк в таком представлении. Создать эффект изменяемого представления для них можно, определив триггеры INSTEAD OF , которые будут преобразовывать запросы на изменение данных в соответствующие действия с другими таблицами. За дополнительными сведениями обратитесь к CREATE TRIGGER . Так же есть возможность создавать правила (см. CREATE RULE ), но на практике триггеры проще для понимания и применения.

Учтите, что пользователь, выполняющий операции добавления, изменения или удаления данных в представлении, должен иметь соответствующие права для этого представления. Кроме того, владелец представления должен иметь сопутствующие права в нижележащих базовых отношениях, хотя пользователь, собственно выполняющий эти операции, может этих прав не иметь (см. Раздел 39.5).

Примеры

Создание представления, содержащего все комедийные фильмы:

Эта команда создаст представление со столбцами, которые содержались в таблице film в момент выполнения команды. Хотя при создании представления было указано * , столбцы, добавляемые в таблицу позже, частью представления не будут.

Создание представления с указанием LOCAL CHECK OPTION :

Эта команда создаст представление на базе представления comedies , выдающее только комедии ( kind = ‘Comedy’ ) универсальной возрастной категории . Любая попытка выполнить в представлении INSERT или UPDATE со строкой, не удовлетворяющей условию , будет отвергнута, но ограничение по полю kind (тип фильма) проверяться не будет.

Создание представления с указанием CASCADED CHECK OPTION :

Это представление будет проверять, удовлетворяют ли новые строки обоим условиям: по столбцу kind и по столбцу classification .

Создание представления с изменяемыми и неизменяемыми столбцами:

Это представление будет поддерживать операции INSERT , UPDATE и DELETE . Изменяемыми будут все столбцы из таблицы films , тогда как вычисляемые столбцы country и avg_rating будут доступны только для чтения.

Создание рекурсивного представления, содержащего числа от 1 до 100:

Заметьте, что несмотря на то, что имя рекурсивного представления дополнено схемой в этой команде CREATE , внутренняя ссылка представления на себя же схемой не дополняется. Это связано с тем, что имя неявно создаваемого CTE не может дополняться схемой.

Совместимость

Команда CREATE OR REPLACE VIEW — языковое расширение PostgreSQL . Так же расширением является предложение WITH ( . ) и концепция временного представления.

Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Базы данных

Это учебное пособие Oracle объясняет, как использовать, как создавать, обновлять и удалять Oracle VIEW (представление) с синтаксисом и примерами.

Что такое VIEW в Oracle?

В Oracle VIEW в сущности, представляет собой виртуальную таблицу, которая физически не существует. Скорее всего, она создается с помощью запроса соединяющего одну или несколько таблиц.

CREATE VIEW

Синтаксис

Синтаксис CREATE VIEW в Oracle:

Параметры или аргументы

view_name
Наименование Oracle VIEW, которое вы хотите создать.
WHERE conditions
Необязательный. Условия, которые должны быть выполнены для записей, которые будут включены в VIEW.

Пример


Этот пример Oracle CREATE VIEW создаст виртуальную таблицу на основе результирующего набора SELECT. Теперь вы можете запросить VIEW Oracle следующим образом:

Update VIEW

С помощью Oracle CREATE OR REPLACE VIEW вы можете изменить определенное в Oracle VIEW не удаляя его.

Синтаксис

view_name
Наименование представления Oracle, которое вы хотите создать или изменить.

Пример

Этот пример Oracle CREATE OR REPLACE VIEW обновит определенное в Oracle представление sup_orders без его удаления. Если Oracle VIEW еще не существовало, то представление будет создано впервые.

Drop VIEW

После того, как VIEW было создано, вы можете удалить его с помощью DROP VIEW.

Синтаксис

view_name
Наименование представления Oracle, который вы хотите создать или заменить.

Пример

Ниже приведен пример того, как использовать Oracle DROP VIEW:

Этот пример Oracle DROP VIEW удалит представление с названием sup_orders .

Часто задаваемые вопросы

Вопрос: Можно ли обновить данные в VIEW?
Ответ: Представление в Oracle создается путем объединения одной или нескольких таблиц. При обновлении записи (ей) в VIEW, обновляются записи в базовых таблицах, которые составляют VIEW.
Так что, да, вы можете обновить данные в Oracle VIEW при наличии у вас соответствующих привилегий в таблицах базы Oracle.

Вопрос: Будет ли существовать Oracle VIEW, если таблица удалится из базы данных?
Ответ: Да в Oracle VIEW продолжает существовать даже после того, как одна из таблиц (на которой основано VIEW) удаляется из базы данных. Тем не менее, если вы попытаетесь запросить VIEW Oracle после того, как таблица была удалена, вы получите сообщение о том, что Oracle VIEW содержит ошибку.
Если восстановить таблицу (таблицу которую удалили), то Oracle VIEW снова будет в порядке.

Как увидеть код CREATE VIEW для представления в PostgreSQL?

Есть ли простой способ увидеть код, используемый для создания представления, используя клиент командной строки PostgreSQL?

Что-то вроде SHOW CREATE VIEW от MySQL.

5 ответов

Мне нужно вернуться сюда, чтобы посмотреть pg_get_viewdef (как это помнить!!), поэтому искал более запоминающуюся команду. и получил ее:

Вы можете увидеть похожие команды, набрав \? в командной строке pgsql.

Бонусный совет: команда emacs sql-postgres делает pgsql намного приятнее (редактирование, копирование, вставка, история команд).

CREATE VIEW

English-Russian SQL Server dictionary . 2013 .

Смотреть что такое «CREATE VIEW» в других словарях:

Create (SQL) — Правильный заголовок этой статьи CREATE. Он показан некорректно из за технических ограничений. CREATE DDL оператор языка SQL, используемый для создания объектов базы данных. Различные СУБД работают с различными объектами. Содержание 1 … Википедия

View (database) — In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document oriented database composed of the result set of a query or map and reduce functions. Unlike ordinary… … Wikipedia

View (Datenbank) — Eine View (deutsch Sicht) ist eine logische Relation (auch virtuelle Relation oder virtuelle Tabelle) in einem Datenbanksystem. Diese logische Relation wird über eine im Datenbankmanagementsystem (DBMS) gespeicherte Abfrage definiert. Der… … Deutsch Wikipedia

Create-a-wrestler — (CAW) is the name commonly given to a game mode in professional wrestling v >Wikipedia

Create a Comic Project — Author(s) John Baird Website http://ccproject.comicgenesis.com/ Current status / schedule Updates every weekday Launch date Jul … Wikipedia

View-Master — reg; is a trademark for a device for viewing seven 3 D images (also known as stereo images) on a paper disk. Although it is now cons >Wikipedia

View Askew Productions — is a film production company, created by Kevin Smith and Scott Mosier in 1994, responsible for such cult films as Clerks , Mallrats , Chasing Amy , Dogma , Jay and Silent Bob Strike Back , and Clerks II . Together, these films create the View… … Wikipedia

Create, read, update and delete — In computer programming, create, read, update and delete (CRUD) are the four basic functions of persistent storage.[1] Sometimes CRUD is expanded with the words retrieve instead of read or destroy instead of delete. It is also sometimes used to… … Wikipedia

View camera — The view camera is a type of camera first developed in the era of the DaguerreotypeStroebel, L. D. (1986). View Camera Technique , 5th ed., p. 212. Boston: Focal Press. ISBN 0 240 51711 3] and still in use today, though with many refinements. It… … Wikipedia

База данных Oracle Database для начинающих: основы базы данных

Представление (view), или виртуальная таблица в базе данных Oracle Database — это специфический образ таблицы или набора таблиц, определенный оператором SELECT. Представление не существует физически как обычная таблица, являющаяся частью табличного пространства.Фактически представление создает виртуальную таблицу или подтаблицу только с теми строками и/или столбцами, которые нужно показать пользователю.

Представление Oracle — результат хранимого запроса, поэтому в словаре данных сохраняется только определение представления. При экспорте базы данных Oracle можно видеть предложение “exporting views” (“экспорт представлений”), но под этим имеется в виду только определения представлений, а не физические объекты.

При условии, что пользователь имеет соответствующие права доступа к лежащим в основе представления таблицам, можно запрашивать представления или даже модифицировать, удалять либо добавлять данные с использованием операторов UPDATE, DELETE и INSERT. Например, если выдать только привилегию INSERT для базовой таблицы, на которой определено представление, то этот пользователь сможет только вставлять строки в таблицу, но не сможет ни выбирать, ни вставлять, ни удалять строки.

Кончено же у читателя моего блога встает самый главный и насущный вопрос. Зачем все же нужны представления в базе данных Oracle? Представления Oracle используются в приложениях по нескольким причинам, включая перечисленные ниже:

  • уменьшение сложности;
  • повышение безопасности;
  • повышение удобства;
  • переименование столбцов таблицы;
  • настройка данных для пользователей;
  • защита целостности данных.


Представления создаются с помощью оператора SQL, описывающего композицию представления. При вызове представления выполняется запрос, по которому оно определено, и затем возвращается результат. Запрос, адресованный к представлению,выглядит в точности как обычный запрос, но база данных преобразует его в идентичный запрос к лежащим в его основе таблицам. Чтобы создать представление в своей схеме, необходимо иметь системную привилегию CREATE VIEW, а чтобы создать представление в любой схеме, а не только в собственной, понадобится системная привилегия CREATE ANY VIEW. Вдобавок нужно либо владеть лежащими в основе таблицами,либо иметь права на операции SELECT, INSERT, UPDATE и DELETE со всеми таблицами,на которых определено представление. Представление можно использовать для добавления к таблице мер безопасности уровня столбца или уровня значения. Безопасность уровня столбца обеспечивается созданием представлений, которые дают доступ лишь к избранным столбцам таблицы. Безопасность уровня значений включает применение конструкции WHERE в определении представления, которое отображает лишь избранные строки базовых таблиц. Чтобы использовать представление, пользователю нужны привилегии для доступа к нему, а не к базовым таблицам, на которых оно определено.

Следующий оператор создает представление по имени MY_EMPLOYEES, которое выдает информацию только о сотрудниках, подчиненных конкретному менеджеру:

Совет. Добавление к оператору CREATE VIEW конструкции WITH READ ONLY гарантирует, что пользователи смогут только осуществлять выборку данных из представления. Это означает, что пользователи не смогут модифицировать представление и тем самым неявно обновлять, вставлять или удалять строки базовых таблиц. В противном случае по умолчанию Oracle позволяет обновлять представление.

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

При создании представления в конструкции FROM можно специфицировать несколько базовых таблиц или даже других представлений. Созданные подобным образом представления называются соединенными (joined views), и следующий пример демонстрирует создание такого представления:

Хотя представления используются в основном для запросов, при некоторых обстоятельствах их можно также применять в командах INSERT, DELETE и UPDATE. Например,допускается выполнять операции DML над представлениями, которые не имеют в своем определении конструкций GROUP BY, START WITH или CONNECT BY, либо каких-то под-запросов в своей конструкции SELECT. Однако поскольку представление в действительности не существует как отдельная физическая сущность, на самом деле происходит модификация данных лежащих в его основе таблиц, и само представление будет, таким образом, субъектом тех же ограничений целостности, что и таблицы, на которых оно основано. В следующем примере показано, как вставлять строки в представление по имени sales_view, которое зависит от таблицы employees.

Приведенный оператор вставляет новую строку в базовую таблицу по имени employees. Обновления, удаления и вставки в представления подчиняются нескольким ограничениям. Например, при использовании ограничения CHECK при создании представления не получится вставить строку или обновить базовую таблицу этой строкой,если данное представление не может извлечь упомянутую строку из базовой таблицы.

Уничтожается представление с помощью команды DROP VIEW, как показано ниже:

Вместо уничтожения и пересоздания представления можно воспользоваться конструкцией OR REPLACE для переопределения представления, например:

Если в базе данных есть другие представления, которые зависят от заменяемого,они станут недействительными. Недействительные представления следует перекомпилировать с помощью оператора ALTER VIEW. Если программная единица PL/SQL, такая как процедура или функция, зависит от представления, то база данных может сделать ее недействительной, если изменения в новом представлении касаются количества столбцов или их имен либо же их типов данных.

Использование материализованных представлений

Всякий раз, когда нужен доступ к представлению, Oracle должен выполнить запрос,по которому определено представление, и вернуть результат. Этот процесс наполнения представления называется разрешением представления (view resolution) и он повторяется при каждом обращении пользователя к представлению. Если вы имеете дело с представлениями с множеством конструкций JOIN и GROUP BY, то этот процесс разрешения представления может потребовать очень длительного времени. Если нужно часто обращаться к представлению, будет весьма неэффективно каждый раз повторять разрешение представления.

Материализованные представления Oracle предлагают выход из этого затруднения.Упомянутые представления можно воспринимать как специализированные представления, в отличие от обычных представлений, имеющие физическое воплощение. Они занимают место и требуют хранения подобно обычным таблицам. Материализованные представления можно даже секционировать и при необходимости создавать на них индексы.

На заметку! Представление всегда вычисляется на лету, и его данные не хранятся отдельно от таблиц, на которых оно определено. Таким образом, запросы, использующие представления,по определению гарантированно вернут самые свежие данные. Материализованные представления в базе данных Oracle Database, с другой стороны, являются статическими объектами, которые наследуют свои данные от лежащих в их основе базовых таблиц. Если вы будете обновлять свои материализованные представления нечасто, то данные в них могут устареть по отношению к данным таблиц, на которых они основаны.

Традиционно хранилища данных и прочие крупные базы данных для выполнения своей работы всегда нуждались в итоговых или агрегатных таблицах. Определение таких итоговых таблиц и постоянное поддержание их в актуальном состоянии — непростая задача. При каждом добавлении данных к таблице деталей необходимо вручную обновлять итоговые таблицы и их индексы. Материализованные представления Oracle предлагают способ упрощения управления итоговой информацией в крупных базах данных. Материализованные представления в таких средах называются также итогами (summaries), поскольку хранят итоговые данные.

В качестве источника для материализованного представления могут служить таблицы, представления, а также другие материализованные представления. Исходные таблицы называются главными таблицами (master tables), а в средах хранилищ данных их часто также называют таблицами деталей. При создании материализованного представления Oracle автоматически создает внутреннюю таблицу для хранения данных этого материализованного представления. Таким образом, материализованное представление будет занимать физическое место в базе данных, в то время как обычное представление — нет, поскольку последнее является всего лишь выводом запроса SQL.

Над материализованными представлениями можно делать следующие действия:

  • создавать индексы на материализованном представлении;
  • создавать материализованное представление на секционированной таблице;
  • секционировать само материализованное представление.

Совет. Индекс для доступа к материализованному представлению можно использовать непосредственно, как это делается в отношении таблицы. Аналогично, можно также обращаться к материализованному представлению непосредственно в операторе INSERT, UPDATE или DELETE.Однако в Oracle не рекомендуют поступать подобным образом; напротив, следует позволить стоимостному оптимизатору Oracle (Cost Based Optimizer — CBO) принять решения относительно необходимости переписать обычные запросы, что обеспечит возможность воспользоваться преимуществами материализованного представления. Если план выполнения, применяющий материализованное представление, имеет меньшую стоимость доступа по сравнению с прямым обращением к таблицам, то Oracle автоматически использует его.

В материализованном представлении допустимы различные типы агрегации, вроде SUM, COUNT(*), AVG, MIN и MAX. В определении материализованного представления так-же можно использовать соединения множества таблиц.

Создание материализованного представления очень просто, но его оптимизация может оказаться довольно сложной. Оптимизация материализованного представления включает как проверку того, переписывает ли стоимостной оптимизатор Oracle запросы пользователя для использования ранее созданного материализованного представления, так и поддержку данных материализованного представления в актуальном состоянии. Давайте кратко рассмотрим эти два аспекта оптимизации материализованных представлений.

Переписывание запросов

В крупных базах данных Oracle с интенсивными действиями, затратными по времени и вычислительной мощности процессоров, такими как соединение таблиц и использование агрегатных функций вроде SUM, материализованные представления ускоряют запросы.Материализованные представления обеспечивают более быстрое выполнение запросов за счет перерасчета и хранения результатов дорогостоящих соединений и агрегатных операций. Прелесть материализованных представлений Oracle заключается в том, что при их создании можно указать, что база данных должна автоматически обновлять материализованные представления, когда происходят изменения в положенных в их основу таблицах. Материализованные представления полностью прозрачны для пользователей. Если пользователи пишут запросы с обращением к лежащим в основе таблицам, то Oracle автоматически переписывает их для использования материализованных представлений, и такая техника оптимизации запросов называется переписыванием запроса (query rewrite). Стоимостной оптимизатор Oracle автоматически распознает необходимость в переписывании запроса для использования материализованного представления вместо исходных таблиц, если оценочная стоимость такого запроса оказывается ниже. Под стоимостью запроса здесь подразумевается объем ввода-вывода, а также затраты времени процессора и памяти, связанные с обработкой SQL-запроса. Сложные соединения таблиц обходятся в этом смысле дорого, а применение материализованных представлений позволяет использовать уже сохраненную информацию в предварительно вычисленном виде, и запросы требуют гораздо меньше ресурсов и потому выполняются намного быстрее.

Прием автоматической оптимизации с переписыванием запроса лежит в основе применения материализованных представлений. Параметр инициализации QUERY_REWRITE_ENABLED позволяет включать и отключать это средство на глобальном уровне.

Этот параметр может принимать следующие значения.

  • FALSE. База данных не переписывает никакие запросы.
  • TRUE. База данных сравнивает стоимость запроса с переписыванием и без, и выбирает наиболее дешевый метод.
  • FORCE. База данных всегда переписывает запрос, не оценивая стоимости.Используйте вариант FORCE, если уверены, что это даст выигрыш во времени получения ответа.

Значением по умолчанию для этого параметра является TRUE, как в ситуации, если установить QUERY_REWRITE_ENABLED в 10.0.0 и выше (значение равно FALSE, если установить QUERY_REWRITE_ENABLED в 9.2.0 и ниже); это означает, что Oracle автоматически использует средство переписывания запроса. Когда упомянутый параметр установлен в TRUE, Oracle оценит стоимость запроса в исходном виде и в переписанном,и выберет вариант с минимальной стоимостью. Включение переписывания запросов действует на уровне системы, т.е. для всей базы данных.

Значение FORCE для параметра QUERY_REWRITE_ENABLED должно специфицироваться, только если есть абсолютная уверенность, что это принесет выгоду. Чтобы разрешить переписывание запроса для определенного материализованного представления,необходимо явно указать конструкцию ENABLE QUERY REWRITE при создании материализованного представления.

Подсказка Rewrite_or_Error

Предположим, что после создания нового материализованного представления обнаружено, что нужные запросы не переписываются, и преимущества нового материализованного представления не задействуются. Если выполнение запроса без материализованного представления требует слишком много времени, можно заставить Oracle прекратить выполнение запроса без материализованного представления. Чтобы заставить Oracle генерировать ошибку вместо выполнения непереписанного запроса, используется подсказка (создаваемая пользователем директива, которая служит указанием для стоимостного оптимизатора; это средство детально рассматривается в главе 19).Подсказка называется REWRITE_ON_ERROR и применяется так:

Если запрос не переписывается, вы увидите следующую ошибку:

После получения такой ошибки с помощью процедуры DBMS_MVIEW.EXPLAIN_REWRITE можно узнать, почему запрос не переписывается, и решить проблему так, чтобы он был переписан и, таким образом, преимущества материализованного представления были задействованы.

Целостность при переписывании

После настройки переписывания запроса Oracle по умолчанию использует только свежие данные из материализованных представлений. Затем он использует только ограничения первичного, уникального или внешнего ключа типа ENABLED VALIDATED.Параметр инициализации QUERY_REWRITE_INTEGRITY задает поведение оптимизатора в этом отношении. Поведение по умолчанию известно как режим ENFORCED. Кроме этого режима параметр QUERY_REWRITE_INTEGRITY может принимать еще два значения.

TRUSTED. В этом режиме оптимизатор принимает во внимание несколько отношений помимо тех, что приняты в режиме ENFORCED. Так, например, оптимизатор принимает наряду с декларируемыми и принудительные отношения, но не ограничения первичного или уникального ключа ENABLED VALIDATED. Поскольку вы позволяете оптимизатору принимать отношения на веру (не принудительно), то большинство запросов могут быть подвергнуты переписыванию.

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

Обновление данных материализованного представления

Поскольку материализованное представление определяется на основе главных таблиц, при изменении данных этих таблиц материализованное представление устаревает.Чтобы справиться с этой проблемой, материализованные представления обновляются,синхронизируя их с содержимым главных таблиц. В следующих разделах описаны опции обновления материализованных представлений.

Режим обновления

При обновлении можно выбирать между режимами ON COMMIT и ON DEMAND.

  • ON COMMIT. В этом режиме при всякой фиксации изменений данных в главных таблицах материализованное представление обновляется автоматически, отражая эти изменения.
  • ON DEMAND. В этом режиме для обновления материализованного представления потребуется выполнить процедуру типа DBMS_MVIEW.REFRESH.


По умолчанию принимается режим ON DEMAND.

Тип обновления

Доступен выбор одного из следующих четырех типов обновлений.

  • COMPLETE. Эта опция обновления полностью заново вычисляет запрос, лежащий в основе материализованного представления. Таким образом, материализованное представление, на создание которого ушло 12 часов, потребует почти такого же времени для перестройки. Очевидно, что не особенно эффективно использовать эту опцию при каждом обновлении, удалении или вставке данных в таблицы.
  • FAST REFRESH. Для реализации механизма быстрого обновления Oracle использует журнал материализованного представления для регистрации всех изменений в главных таблицах. Затем журнал материализованного представления применяется для обновления этого материализованного представления. Упомянутый журнал представляет собой таблицу, основанную на ассоциированном материализованном представлении. Каждая из таких таблиц, соединенная с материализованным представлением, нуждается в собственном журнале материализованного представления, чтобы фиксировать изменения в таблицах. Для быстрого обновления материализованного представления Oracle также может использовать данные из операций обслуживания разделов или загрузки данных, выполненной с применением метода загрузки в прямом режиме SQL*Loader.
  • FORCE. В случае выбора этой опции Oracle попытается применить механизм быстрого обновления (fast refresh). Если по некоторым причинам он не может быть использован, применяется метод полного обновления.
  • NEVER. Эта опция никогда не обновляет материализованное представление.Очевидно, что это неподходящий вариант для материализованных представлений, чьи главные таблицы со временем подвергаются серьезным изменениям.

Типом обновления по умолчанию является FORCE.

Использование пакета DBMS_MVIEW

Даже после того, как вы специфицируете механизм обновления запроса, стоимостной оптимизатор Oracle не всегда сможет автоматически переписать запрос, и обратится к главным таблицам вместо материализованного представления. Таким образом,даже несмотря на наличие материализованного представления, оптимизатор игнорирует его, сводя на нет смысл создания и обслуживания материализованного представления. Оптимизатор Oracle поступает так потому, что некоторые условия для переписи запросов могут быть не выполнены. Для диагностики этой и прочих проблем материализованного представления служит поставляемый Oracle пакет DBMS_MVIEW.

Процедуры пакета DBMS_MVIEW используются следующим образом.

  • Процедура EXPLAIN_MVIEW применяется для того, чтобы увидеть, какие типы переписывания запросов возможны.
  • Процедура EXPLAIN_REWRITE служит для определения, почему определенный запрос не переписан с использованием материализованного представления.
  • Процедура TUNE_MVIEW используется для включения переписывания запроса. Эта процедура подскажет, как следует изменить материализованное представление,чтобы сделать его доступным для переписывания запросов. Процедура TUNE_MVIEW также сообщит, каким образом удовлетворить требования быстро обновляемого материализованного представления. Процедура примет ввод и построит сценарий создания материализованного представления (вместе со всеми необходимыми журналами материализованного представления), готового к реализации.

Создание материализованных представлений

В этом разделе будет показано, как создать базовое материализованное представление с использованием некоторых опций, описанных в предыдущих разделах. Если вы не уверены в том, какие материализованные представления нужно создавать, можете воспользоваться инструментом Oracle SQL Access Advisor, который предоставит ценные рекомендации относительно индексов и материализованных представлений. SQL Access Advisor спроектирует материализованное представление и сообщит о его готовности для участия в переписанных запросах. В разделе “Использование SQL Access Advisor” далее в главе мы детально опишем этот инструмент.

Для ввода в действие материализованного представления необходимо выполнить следующие три шага, хотя само его создание достаточно просто.

  1. Выдать необходимые привилегии.
  2. Создать журнал материализованного представления (предполагая использование опции обновления FAST).
  3. Создать материализованное представление.

Выдача необходимых привилегий

Первым делом потребуется выдать необходимые привилегии пользователю, создающему материализованные представления. Главные привилегии — это те, что позволяют создавать материализованное представление. Вдобавок необходимо выдать пользователю привилегию QUERY REWRITE, используя для этого либо привилегию GLOBAL QUERY REWRITE, либо специфические привилегии QUERY REWRITE для каждого объекта, не являющегося частью пользовательской схемы. Ниже приведены операторы GRANT, которые позволяют пользователю создавать материализованное представление в его схеме:

В дополнение, если пользователь еще не имеет их, потребуется выдать права на создание таблиц с помощью следующего оператора GRANT:

Если пользователь не владеет никакими главными таблицами, являющимися частью определения материализованного представления, необходимо выдать ему привилегию на SELECT в отношении этих индивидуальных таблиц или же сделать так:

Создание журнала материализованного представления

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

Для использования механизма быстрого обновления материализованного представления сначала нужно создать журналы материализованного представления для каждой из таблиц — частей этого материализованного представления. В нашем случае это таблицы products и sales. В дополнение необходимо специфицировать конструкцию ROWID в операторе CREATE MATERIALIZED VIEW LOG. Также следует перечислить все столбцы, упоминаемые в материализованном представлении, и предусмотреть конструкции SEQUENCE и INCLUDING NEW VALUES, например:

Этот пример демонстрирует создание двух журналов материализованного представления для фиксации изменений в главных таблицах products и sales. В следующем разделе будет показано, как создать само материализованное представление.

Создание материализованного представления

Теперь все готово для создания материализованного представления. В примере, показанном в листинге 7.17, с помощью конструкции FAST REFRESH специфицируется механизм обновления материализованного представления.

Совет. Если в базе данных уже есть таблицы, содержащие некоторого рода агрегаты или итоговые результаты, можно воспользоваться оператором CREATE MATERIALIZED VIEW с конструкцией ON PREBUILT TABLE для регистрации имеющейся итоговой таблицы в качестве материализованного представления.

Рассмотрим некоторые важные конструкции оператора CREATE MATERIALIZED VIEW.

  • BUILD IMMEDIATE немедленно наполняет материализованное представление; эта опция принята по умолчанию. Альтернатива заключается в использовании опции BUILD DEFERRED, которая в действительности загружает материализованное представление данными позднее, в указанное время.
  • REFRESG FAST специфицирует, что материализованное представление должно использовать метод обновления FAST, что для фиксации всех изменений главных таблиц требует наличия двух журналов материализованных представлений, которые были созданы на предыдущем шаге. Часть COMMIT конструкции REFRESH указывает на то, что все зафиксированные изменения главных таблиц распространялись на материализованное представление немедленно после фиксации этих изменений.
  • ENABLE QUERY REWRITE означает, что оптимизатор Oracle прозрачно перепишет все запросы для использования материализованных представлений вместо лежащих в основе главных таблиц.
  • Подзапрос AS определяет материализованное представление. Oracle сохранит вывод этого подзапроса в материализованном представлении, которое вы создаете.Здесь допустим любой подзапрос SQL.
  • Последние четыре строки кода содержат действительный запрос, определяющий материализованное представление; они извлекают вывод из главных таблиц и делают его частью материализованного представления.

На заметку! Из-за ограниченности объема книги здесь был представлен только простейший пример создания материализованного представления и его журналов. В действительности, чтобы иметь возможность создавать такие объекты, может понадобиться удовлетворить дополнительным требованиям. Например, чтобы иметь возможность создания быстро обновляемых материализованных представлений с журналами, вы должны удовлетворять специальным требованиям. Полный список этих требований можно найти в руководствах Oracle (в частности, в Data Warehousing Guide).

Обратите внимание на две возможности включения переписывания запросов: указание конструкции ENABLE QUERY REWRITE при создании материализованного представления (см. листинг 7.16) или применение оператора ALTER MATERIALIZED VIEW с этой конструкцией после того, как материализованное представление уже существует.

Для просмотра предложенного плана выполнения запроса вместо процедуры EXPLAIN_REWRITE из пакета DBMS_MVIEW можно воспользоваться инструментом EXPLAIN PLAN. При этом EXPLAIN PLAN не должен отображать никаких ссылок на лежащие в основе базовые таблицы. Если запрос был действительно переписан с использованием нового материализованного представления, там должно присутствовать обращение к этому представлению.

Совет. Соберите статистику оптимизатора (см. главу 19) для материализованного представления сразу после его создания. Это поможет Oracle оптимизировать процесс переписывания запросов.

Если вы считаете, что материализованное представление не нужно, можете уничтожить его с помощью оператора DROP MATERIALIZED VIEW:

Создание представления с использованием хранимой процедуры

Эти вопросы задавали несколько раз раньше, к сожалению, я не получил ответа на мои вопросы.

Ну, у меня есть две таблицы SQL (SQL SERVER 2008), Employee и Employee expens, где Employee Id — это первичный ключ и внешний ключ соответственно.

Столбцы таблицы сотрудников, 1. Идентификатор сотрудника (P Key) 2. Менеджер 3. Расположение 4. Дата регистрации 5. Имя

Столбцы расходов сотрудника, 1. Идентификатор расхода (ключ P) 2. Идентификатор сотрудника (ключ F) 3. Тип расхода 4. Сумма расходов 5. Дата сметы.

Вопрос в том, что я хочу создать представление, которое будет использоваться в веб-части SharePoint, где я буду запрашивать обе таблицы. Поэтому моим требованием является создание представления с использованием следующих столбцов,

От сотрудника мне нужны идентификаторы и имя сотрудника. Из расходов сотрудника мне нужен тип расходов, сумма расходов, дата платежа.

а. Если у меня есть несколько записей для сотрудника в таблице Employee Expense, многие строки не должны присутствовать в представлении

б. Даже если у меня нет записи в таблице Employee Expense, тогда также я должен получить строку для этого конкретного Employee в представлении с нулевым значением для столбцов таблицы Employee Expense.

Пожалуйста, помогите мне продолжить.

Редактирование Чтобы добавить требуемый код просмотра в соответствие с инструкциями по переполнению стека!

Илон Маск рекомендует:  Что такое код getfatd
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL