Студопедия

КАТЕГОРИИ:


Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748)

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

 

Представлением называется именованный запрос на выборку, сохраненный в базе данных. Иногда представления называют “виртуальными таблицами”, содержимое которых определяется запросом. Для пользователя базы данных представление выглядит подобно обычной таблице, состоящей из строк и столбцов. Однако, в отличие от таблицы, представление как совокупность значений в базе данных реально не существует. Строки и столбцы данных, которые пользователь видит с помощью представления, являются результатами запроса, лежащего в его основе. При создании представление получает имя, и его определение сохраняется в базе данных.

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

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

- ограничивают доступ к данным, разрешая пользователям видеть только некоторые из строк и столбцов таблицы;

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

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

 

CREATE OR REPLACE VIEW view_name [(view_col [, view_col...])]

AS SELECT [WITH CHECK OPTION];

 

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

Когда СУБД встречает в инструкции SQL ссылку на представление, она отыскивает его определение, сохраненное в базе данных, преобразует пользовательский запрос, ссылающийся на представление, в эквивалентный запрос к исходным таблицам представления, и выполняет этот запрос. Таким образом, СУБД создает иллюзию существования представления в виде отдельной таблицы и в то же время сохраняет целостность исходных таблиц.

Если определение представления простое, то СУБД формирует каждую строку представления "на лету", извлекая данные из исходных таблиц. Если же определение сложное, СУБД приходится материализовывать представление. Это означает, что СУБД выполняет запрос, определяющий представление, и сохраняет его результаты во временной таблице. Из нее СУБД берет данные для формирования результатов пользовательского запроса, а когда временная таблица становится ненужной, удаляет ее. Но независимо от того, как СУБД выполняет инструкцию, являющуюся определением представления, для пользователя результат будет одним и тем же. Ссылаться: на представление в инструкции SQL можно так же, как если бы оно было реальной таблицей базы данных. Так после определения представления к нему можно обращаться с помощью инструкции SELECT как к обычной таблице:

 

select * from view_name;

 

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

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

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

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

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

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

- Целостность данных. Если доступ к данным или ввод данных (возможно не для всех представлений) осуществляется с помощью представления, СУБД может автоматически проверять, выполняются ли определенные условия целостности.

Недостатки представлений. Наряду с перечисленными выше преимуществами, представления обладают и двумя существенными недостатками:

- Производительность. Представление создает лишь видимость существования соответствующей таблицы, и СУБД приходится преобразовывать запрос к представлению в запрос к исходным таблицам. Если представление отображает многотабличный запрос, то простой запрос к представлению становится сложным объединением и на его выполнение может потребоваться много времени.

- Ограничения на обновление. Когда пользователь пытается обновить строки представления, СУБД должна установить их соответствие строкам исходных таблиц, а также обновить последние. Это возможно только для простых представлений; сложные представления обновлять нельзя, они доступны только для выборки.

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

 

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

 

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

create OR REPLACE view Minsk AS

select * from staff

where bno in (select bno

from branch

where city ='Minsk');

 

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

Пример. Создать представление, показывающее информацию о ФИО и должности служащих.

create OR REPLACE view info AS

select fname, lname, position

from staff;

 

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

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

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

 

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

create OR REPLACE view average_salary as

select bno, avg(salary) Srednya_zarplata

from staff

group by bno;

 

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

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

1. Должен отсутствовать предикат DISTINCT, т.е. повторяющиеся строки не должны исключаться из результата запроса.

2. В предложении FROM должна быть задана только одна таблица или представление.

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

4. В предложении WHERE не должен содержаться подчиненный запрос.

5. В запросе не должны присутствовать предложения GROUP BY и HAVING.

Обобщая сказанное можно отметить, что если между строками представления и строками исходной таблицы есть соответствие “один – к - одному”, то такое представление можно считать обновляемым. Если между строками представления и исходной таблицы нет однозначного соответствия, то добавление, удаление и изменение строк представления не имеет смысла и поэтому запрещены. Однако некоторые коммерческие СУБД позволяют обновлять “неоднозначные” представления. Так в ORACLE такие обновления возможны посредством триггеров INSTEAD OF.

В ORACLE инструкция имеет вид:

 

CREATE OR REPLACE [FORCE|NOFORCE] VIEW view-name

AS sql-запрос [WITH CHECK OPTION [CONSTRAINT имя_ограничения]] [WITH READ ONLY];

 

где OR REPLACE – пересоздает представление, если оно уже существует. Можно использовать эту опцию для изменения определения представления без того, чтобы удалять его, создавать заново и вновь назначать все объектные привилегии, которые были назначены по данному представлению;

FORCE – создает представление независимо от того, существуют ли базовые таблицы этого представления, и от того, имеет ли владелец схемы, содержащей представление, привилегии по этим таблицам. Необходимо чтобы оба названных условия были удовлетворены, прежде чем по данному представлению можно будет выдавать любые предложения SELECT, INSERT, UPDATE или DELETE. По умолчанию применяется параметр NOFORCE;

NOFORCE – создает представление только в том случае, если существуют базовые таблицы этого представления, а владелец схемы, содержащей представление, имеет привилегии по этим таблицам;

WITH CHECK OPTION – указывает, что вставки и обновления, которые будут осуществляться через этот запрос, должны давать в результате только такие строки, которые могут быть выбраны запросом этого же представления. Другими словами, если представление создается посредством запроса с предложением WHERE, то в представлении будут видны только строки, удовлетворяющие условию отбора. Остальные строки в исходной таблице присутствуют, но в представлении их нет.

Например, представление Minsk содержиттолько строки таблицы staff с определенными значениями в столбце bno.

Это представление является обновляемым как по стандарту ANSI/ISO, так и в ORACLE, следовательно, в него можно добавить информацию о новом служащем посредством инструкции INSERT:

 

insert into Minsk (sno, fname, lname, address, position, sex, dob, salary, bno)

values (‘s129’, ‘…’, ‘…’, ‘…’, ‘менеджер’, ‘f’, ’01.01.81’, 300, 1).

 

СУБД добавит новую строку в исходную таблицу staff; она будет видна также в представлении Minsk. Также без опции WITH CHECK OPTION ничего не препятствует выполнению следующей инструкции:

 

insert into Minsk (sno, fname, lname, address, position, sex, dob, salary, bno)

values (‘s129’, ‘…’, ‘…’, ‘…’, ‘менеджер’, ‘f’, ’01.01.81’, 300, 2).

 

После этого в запросе select * from Minsk добавленная строка будет отсутствовать. Тот факт, что в результате выполнения инструкции INSERT или UPDATE из представления исчезают строки, в лучшем случае вызывает замешательство.

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

CONSTRAINT имя_ограничение – задает имя, которое присваивается ограничению CHECK OPTION. Если этот идентификатор опущен, то ORACLE автоматически назначает этому ограничению уникальное имя.

 

create OR REPLACE view Minsk as

select * from staff

where bno in (select bno

from branch

where city ='Minsk')

with check option;

 

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

WITH READ ONLY – запрещает операции DML над представлением. Если команды обновления DML (INSERT, UPDATE, DELETE) можно применить к представлению, то говорят, что представление является обновляемым (updatable); в противном случае оно является только читаемым (read-only).

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

- представление должно включать первичный ключ таблицы;

- не должно содержать полей, полученных в результате применения функций агрегирования;

- не должно содержать DISTINCT, GROUP BY, HAVING в своем определении;

- может быть определено на другом представлении, но это представление должно быть обновляемым;

- не может содержать константы, строки или выражения (например, comm*100) в списке выбираемых выходных полей;

Удаляются представления посредством инструкции DROP VIEW. Так представление Minsk может быть удалено при выполнении инструкции:

 

drop view Minsk;

 

 

23. ПСЕВДОНИМЫ ТАБЛИЦ (ИНСТРУКЦИИ CREATE / DROP SYNONYM)

 

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

Для решения этой проблемы во многих СУБД вводится понятие псевдонима или синонима. Псевдоним – это назначаемое пользователем имя, которое заменяет имя некоторой таблицы.

В ORACLE для создания псевдонимов используется инструкция CREATE SYNONYM. В других СУБД для аналогичных целей используется инструкция CREATE ALIAS.

 

CREATE [PUBLIC] SYNONYM имя_синонима

FOR [ схема. ] имя_таблицы [@ связь_БД ]

 

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

 

24. ИНДЕКСЫ (ИНСТРУКЦИИ CREATE/DROP INDEX)

 

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

Наличие или отсутствие индекса совершенно незаметно для пользователя, обращающегося к таблице.

Например, в случае поиска сотрудника по фамилии будем использовать столбец lname. Если бы индекса для столбца не существовало, то СУБД была бы вынуждена выполнять запрос путем последовательного “сканирования” таблицы staff, строка за строкой, просматривая в каждой строке значения столбца lname. Для получения гарантии того, что найдены все строки, удовлетворяющие условию отбора, СУБД должна просмотреть каждую строку таблицы. Если таблица имеет сотни тысячи строк, то ее просмотр может занять достаточно много времени.

Если для столбца lname имеется индекс, СУБД находит требуемые данные, просматривая индекс, чтобы найти требуемое значение, а затем с помощью указателя находит требуемую строку (строки) таблицы. Поиск в индексе осуществляется достаточно быстро, так как индекс отсортирован (сортировка “строковых” столбцов осуществляется в лексикографическом порядке) и его строки достаточно коротки. Переход от индекса к строке (строкам) также происходит довольно быстро, поскольку в индексе содержится информация о том, где на диске располагается эта строка (строки).

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

В общем-то, полезно создавать индекс лишь для тех столбцов, которые часто используются в условиях отбора. Индексы удобны также в тех случаях, когда инструкции SELECT обращаются к таблице гораздо чаще, чем инструкции INSERT и UPDATE. СУБД всегда создает индекс для первичного ключа таблицы, так как ожидает, что доступ к таблице чаще всего будет осуществляться через первичный ключ.

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

На практике в большинстве популярных СУБД (включая ORACLE, SQServer, INFORMIX, SYBASE) для создания индекса используется та или иная форма инструкции CREATE INDEX. В инструкции указывается имя индекса и таблица, для которой он создается, индексируемый столбец и порядок его сортировки (по возрастанию или убыванию).

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

 

CREATE [OR REPLACE] [UNIQUE | BITMAP] INDEX [ схема. ] имя_индекса

ON [ схема. ] имя_таблицы [ псевдоним ] (столбец | выражение_для_столбца [ASC | DESC][, …]);

 

где UNIQUE – означает, что значения столбцов, на которые ссылается индекс, должны быть уникальными; BITMAP – изменение структуры индекса со сбалансированного дерева на структуру растровой карты.

В следующем примере создадим индекс с именем lname_IDX, для столбца таблицы staff:

 

CREATE INDEX lname_IDX ON staff (lname)

TABLESPACE ALL_ORACLE_TBS

STORAGE (INITIAL 20K

NEXT 20K

PCTINCREASE 75

PCTFREE 0);

 

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

Индексы могут быть уникальными или неуникальными. Уникальные индексы гарантируют отсутствие двух строк в таблице с одинаковыми значениями в ключевом столбце или столбцах. Неуникальные индексы не накладывают никаких ограничений на значения столбцов.

Для создания уникального индекса используется оператор – CREATE UNIQUE INDEX. В следующем примере покажем как это сделать:

 

CREATE UNIQUE INDEX ANIKNAME_UNIQUE_IDX ON ALL_ORACLE_ADMIN (ANIKNAME)

TABLESPACE ALL_ORACLE_IDX;

 

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

<== предыдущая лекция | следующая лекция ==>
Условия целостности данных | Создание индексов связанных с ограничением целостности
Поделиться с друзьями:


Дата добавления: 2014-01-04; Просмотров: 1097; Нарушение авторских прав?; Мы поможем в написании вашей работы!


Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет



studopedia.su - Студопедия (2013 - 2024) год. Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав! Последнее добавление




Генерация страницы за: 0.082 сек.