Создание других объектов схемы

1. Создание других объектов схемы

© Oracle, 2007. Все права защищены.

2. Цели

Изучив материалы этого занятия, вы сможете делать
следующее:
• создавать простые и комплексные представления
• извлекать данные из представлений
• создавать, поддерживать и использовать последовательности
• создавать и поддерживать индексы
• создавать частные и общие синонимы
11 - 2
© Oracle, 2007. Все права защищены.

3. План занятия

• Обзор представлений:
– создание, изменение и извлечение данных из представления
– операции языка манипулирования данными (DML)
с представлением
– удаление представления
• Обзор последовательностей:
– создание, использование и изменение последовательности
– кэширование значений последовательности
– псевдостолбцы NEXTVAL и CURRVAL
• Обзор индексов
– создание и удаление индексов
• Обзор синонимов
– создание и удаление синонимов
11 - 3
© Oracle, 2007. Все права защищены.

4. Объекты базы данных

11 - 4
Объект
Описание
Таблица
Основной элемент хранения, состоит из строк
Представление
Логически представляет подмножества данных из одной
или нескольких таблиц
Последовательность
Создает числовые значения
Индекс
Ускоряет обработку запросов на извлечение
данных
Синоним
Присваивает объекту альтернативные имена
© Oracle, 2007. Все права защищены.

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

Таблица EMPLOYEES
11 - 5
© Oracle, 2007. Все права защищены.

6. Преимущества представлений

Ограничение
доступа
к данным
Упрощение сложных
запросов
Отображение одних
и тех же данных
в различных
представлениях
Обеспечение
независимости
данных
11 - 6
© Oracle, 2007. Все права защищены.

7. Простые и комплексные представления

11 - 7
Свойство
Простые представления
Комплексные представления
Число таблиц
1
1 или несколько
Содержат функции
Нет
Да
Содержат группы данных
Нет
Да
Операции DML при помощи
представления
Да
Не всегда
© Oracle, 2007. Все права защищены.

8. Создание представления

• В инструкцию CREATE VIEW помещается подзапрос:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
• Подзапрос может содержать синтаксис комплексной
инструкции SELECT.
11 - 8
© Oracle, 2007. Все права защищены.

9. Создание представления

• Создание представления EMPVU80, содержащего
подробные сведения о сотрудниках отдела 80:
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM
employees
WHERE
department_id = 80;
• Описание структуры представления при помощи
команды iSQL*Plus DESCRIBE:
DESCRIBE empvu80
11 - 9
© Oracle, 2007. Все права защищены.

10. Создание представления

• Создание представления с использованием в подзапросе псевдонимов столбцов:
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM
employees
WHERE
department_id = 50;
• Выбор столбцов данного представления по указанным
именам псевдонимов.
11 - 10
© Oracle, 2007. Все права защищены.

11. Извлечение данных из представления

SELECT *
FROM
salvu50;
11 - 11
© Oracle, 2007. Все права защищены.

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

• Изменение представления EMPVU80 при помощи
предложения CREATE OR REPLACE VIEW. Добавление
псевдонима для имен каждого из столбцов:
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' '
|| last_name, salary, department_id
FROM
employees
WHERE
department_id = 80;
• Псевдонимы столбцов в предложении CREATE OR
REPLACE VIEW перечисляются в том же порядке, что
и столбцы подзапроса.
11 - 12
© Oracle, 2007. Все права защищены.

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

Создание комплексного представления, содержащего
групповые функции для отображения данных из двух
таблиц:
CREATE OR REPLACE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT
d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM
employees e JOIN departments d
ON
(e.department_id = d.department_id)
GROUP BY d.department_name;
11 - 13
© Oracle, 2007. Все права защищены.

14. Правила выполнения операций DML для представления

• Для простых представлений обычно можно
выполнять операции DML.
• Строку нельзя удалить, если представление содержит:
–
–
–
–
11 - 14
групповые функции
предложение GROUP BY
ключевое слово DISTINCT
ключевое слово псевдостолбца ROWNUM
© Oracle, 2007. Все права защищены.

15. Правила выполнения операций DML для представления

Нельзя изменять данные при помощи представления,
если оно содержит:
• групповые функции
• предложение GROUP BY
• ключевое слово DISTINCT
• ключевое слово псевдостолбца ROWNUM
• столбцы, определенные в виде выражений
11 - 15
© Oracle, 2007. Все права защищены.

16. Правила выполнения операций DML для представления

Нельзя добавлять данные при помощи представления,
если оно содержит:
• групповые функции
• предложение GROUP BY
• ключевое слово DISTINCT
• ключевое слово псевдостолбца ROWNUM
• столбцы, определенные в виде выражений
• столбцы NOT NULL в базовых таблицах, выбор которых
не предусмотрен в представлении
11 - 16
© Oracle, 2007. Все права защищены.

17. Использование предложения WITH CHECK OPTION

• При помощи предложения WITH CHECK OPTION можно
гарантировать, что операции DML, выполняемые при
помощи представления, останутся в его домене:
CREATE OR REPLACE VIEW empvu20
AS SELECT
*
FROM
employees
WHERE
department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;
• Любая попытка выполнения операции INSERT для
строки со значением department_id, отличным от 20,
или операции UPDATE для номера отдела любой строки
представления закончится неудачей, поскольку она
нарушает ограничение WITH CHECK OPTION.
11 - 17
© Oracle, 2007. Все права защищены.

18. Запрет операций DML

• Добавив в определение представления параметр WITH
READ ONLY, можно гарантировать, что операции DML
выполняться не будут.
• В этом случае любая попытка выполнения операции
DML с любой строкой представления приведет к ошибке
сервера Oracle.
11 - 18
© Oracle, 2007. Все права защищены.

19. Запрет операций DML

CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT
employee_id, last_name, job_id
FROM
employees
WHERE
department_id = 10
WITH READ ONLY ;
11 - 19
© Oracle, 2007. Все права защищены.

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

Представление можно удалить без какой-либо потери
данных, поскольку оно создается на основе соответствующих таблиц базы данных.
DROP VIEW view;
DROP VIEW empvu80;
11 - 20
© Oracle, 2007. Все права защищены.

21. Упражнение 11: обзор части 1

Это упражнение охватывает следующие темы:
• создание простого представления
• создание комплексного представления
• создание простого представления с ограничением
проверки
• попытка изменения данных при помощи представления
• удаление представлений
11 - 21
© Oracle, 2007. Все права защищены.

22. План занятия

• Обзор представлений:
– создание, изменение и извлечение данных из представления
– операции DML с представлением
– удаление представления
• Обзор последовательностей:
– создание, использование и изменение последовательности
– кэширование значений последовательности
– псевдостолбцы NEXTVAL и CURRVAL
• Обзор индексов
– создание и удаление индексов
• Обзор синонимов
– создание и удаление синонимов
11 - 22
© Oracle, 2007. Все права защищены.

23. Последовательности

Объект
Описание
Таблица
Основной элемент хранения, состоит из строк
Представление
Логически представляет подмножества данных из одной
или нескольких таблиц
Последовательность
Создает числовые значения
Индекс
Повышает производительность некоторых
запросов
Синоним
Присваивает объекту альтернативные имена
11 - 23
© Oracle, 2007. Все права защищены.

24. Последовательности

Последовательность:
• может автоматически генерировать уникальные числа
• является объектом с общим доступом
• может использоваться для генерации значений
первичного ключа
• заменяет код приложения
• при кэшировании значений последовательности
в памяти повышает эффективность доступа к ним
2
1
11 - 24
4
3
6
5
8
7
© Oracle, 2007. Все права защищены.
10
9

25. Инструкция CREATE SEQUENCE: синтаксис

Определение последовательности для автоматической
генерации последовательных чисел:
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
11 - 25
© Oracle, 2007. Все права защищены.

26. Создание последовательности

• Создание последовательности с именем
DEPT_DEPTID_SEQ для использования в первичном
ключе таблицы DEPARTMENTS.
• Параметр CYCLE не используется.
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
11 - 26
© Oracle, 2007. Все права защищены.

27. Псевдостолбцы NEXTVAL и CURRVAL

• NEXTVAL возвращает следующее доступное значение
последовательности. При каждой ссылке на него, даже
разных пользователей, возвращается уникальное значение.
• CURRVAL получает текущее значение последовательности.
• Для данной последовательности ссылку на NEXTVAL
необходимо сделать прежде, чем значение появится
в CURRVAL.
11 - 27
© Oracle, 2007. Все права защищены.

28.

Использование последовательности
• Добавление нового отдела с именем «Support»
и идентификатором расположения 2500:
INSERT INTO departments(department_id,
department_name, location_id)
VALUES
(dept_deptid_seq.NEXTVAL,
'Support', 2500);
• Отображение текущего значения для
последовательности DEPT_DEPTID_SEQ:
SELECT
FROM
11 - 29
dept_deptid_seq.CURRVAL
dual;
© Oracle, 2007. Все права защищены.

29. Использование последовательности

Кэширование значений последовательности
• Кэширование значений последовательности в памяти
позволяет ускорить доступ к ним.
• В последовательности значений могут возникать
пропуски, если:
– выполнялась процедура отката
– произошел сбой системы
– последовательность использовалась для другой таблицы
11 - 30
© Oracle, 2007. Все права защищены.

30. Кэширование значений последовательности

Изменение последовательности
Изменение значения приращения, максимального
и минимального значений, параметров циклического
возобновления и кэширования:
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
11 - 31
© Oracle, 2007. Все права защищены.

31. Изменение последовательности

Указания по изменению последовательности
• Изменить последовательность может только ее
владелец или пользователь с привилегией ALTER.
• Изменение повлияет только на будущие номера
последовательности.
• Чтобы вновь начать последовательность с другого
номера, ее необходимо удалить и создать заново.
• При изменении выполняются некоторые процедуры
проверки.
• Для удаления последовательности используйте
инструкцию DROP:
DROP SEQUENCE dept_deptid_seq;
11 - 32
© Oracle, 2007. Все права защищены.

32. Указания по изменению последовательности

План занятия
• Обзор представлений:
– создание, изменение и извлечение данных из представления
– операции DML с представлением
– удаление представления
• Обзор последовательностей:
– создание, использование и изменение последовательности
– кэширование значений последовательности
– псевдостолбцы NEXTVAL и CURRVAL
• Обзор индексов
– создание и удаление индексов
• Обзор синонимов
– создание и удаление синонимов
11 - 33
© Oracle, 2007. Все права защищены.

33. План занятия

Индексы
Объект
Описание
Таблица
Основной элемент хранения, состоит из строк
Представление
Логически представляет подмножества данных из одной
или нескольких таблиц
Последовательность
Создает числовые значения
Индекс
Ускоряет обработку некоторых запросов
Синоним
Присваивает объекту альтернативные имена
11 - 34
© Oracle, 2007. Все права защищены.

34. Индексы

Индекс:
• это объект схемы
• может использоваться сервером Oracle для ускорения
выборки строк при помощи указателя
• может снижать объем операций дискового ввода-вывода
(I/O), используя для ускорения поиска данных метод пути
быстрого доступа
• не зависит от таблиц, которые индексирует
• используется и поддерживается сервером Oracle
автоматически
11 - 35
© Oracle, 2007. Все права защищены.

35. Индексы

Как создаются индексы?
• Автоматически: уникальный индекс создается
автоматически при указании PRIMARY KEY или
ограничения UNIQUE в определении таблицы.
• Вручную: пользователи могут создавать неуникальные
индексы для столбцов, чтобы ускорить доступ к строкам.
11 - 36
© Oracle, 2007. Все права защищены.

36. Как создаются индексы?

Создание индекса
• Создание индекса для одного или нескольких столбцов:
CREATE [UNIQUE][BITMAP]INDEX index
ON table (column[, column]...);
• Увеличение скорости доступа для запроса к столбцу
LAST_NAME таблицы EMPLOYEES:
CREATE INDEX emp_last_name_idx
ON
employees(last_name);
11 - 37
© Oracle, 2007. Все права защищены.

37. Создание индекса

Указания по созданию индексов
Индекс целесообразно создать, если:
столбец содержит значения в широком диапазоне
столбец содержит большое число значений NULL
один или несколько столбцов часто используются вместе
в предложении WHERE или в условии объединения
таблица большая и ожидается, что большинство запросов будут
извлекать менее 2-4% ее строк
Не следует создавать индекс, если:
столбцы используются в условиях запроса достаточно редко
размер таблиц невелик или ожидается, что большинство
запросов будут извлекать более 2-4% строк таблицы
таблица часто обновляется
индексированные столбцы упоминаются в составе выражений
11 - 38
© Oracle, 2007. Все права защищены.

38. Указания по созданию индексов

Удаление индекса
• Индекс удаляется из словаря данных при помощи
команды DROP INDEX:
DROP INDEX index;
• Удаление из словаря данных индекса
emp_last_name_idx:
DROP INDEX emp_last_name_idx;
• Удалить индекс может только его владелец или
пользователь с привилегией DROP ANY INDEX.
11 - 39
© Oracle, 2007. Все права защищены.

39. Удаление индекса

План занятия
• Обзор представлений:
– создание, изменение и извлечение данных из представления
– операции DML с представлением
– удаление представления
• Обзор последовательностей:
– создание, использование и изменение последовательности
– кэширование значений последовательности
– псевдостолбцы NEXTVAL и CURRVAL
• Обзор индексов
– создание и удаление индексов
• Обзор синонимов
– создание и удаление синонимов
11 - 40
© Oracle, 2007. Все права защищены.

40. План занятия

Синонимы
Объект
Описание
Таблица
Основной элемент хранения, состоит из строк
Представление
Логически представляет подмножества данных из одной или
нескольких таблиц
Последовательность
Создает числовые значения
Индекс
Ускоряет обработку некоторых
запросов
Синоним
Присваивает объекту альтернативные имена
11 - 41
© Oracle, 2007. Все права защищены.

41. Синонимы

Создание синонима объекта
Создание синонима (другого имени объекта) упрощает
доступ к объектам. При помощи синонимов можно:
• создавать более простые ссылки на таблицы, которыми
владеет другой пользователь
• делать более компактными длинные имена объектов
CREATE [PUBLIC] SYNONYM synonym
FOR
object;
11 - 42
© Oracle, 2007. Все права защищены.

42. Создание синонима объекта

Создание и удаление синонимов
• Создание более компактного имени для представления
DEPT_SUM_VU:
CREATE SYNONYM d_sum
FOR dept_sum_vu;
• Удаление синонима:
DROP SYNONYM d_sum;
11 - 43
© Oracle, 2007. Все права защищены.

43. Создание и удаление синонимов

Заключение
На этом занятии вы узнали, как:
• создавать, использовать и удалять представления
• автоматически генерировать порядковые номера при
помощи генератора последовательности
• создавать индексы для ускорения выполнения запросов
• использовать синонимы для предоставления
альтернативных имен объектов
11 - 44
© Oracle, 2007. Все права защищены.

44. Заключение

Упражнение 11: обзор части 2
Это упражнение охватывает следующие темы:
• создание последовательностей
• использование последовательностей
• создание неуникальных индексов
• создание синонимов
11 - 45
© Oracle, 2007. Все права защищены.