WWW.DISSERS.RU

БЕСПЛАТНАЯ ЭЛЕКТРОННАЯ БИБЛИОТЕКА

   Добро пожаловать!

Pages:     | 1 |   ...   | 2 | 3 || 5 |

«С. Н. Смирнов, И. С. Задворьев Учебное пособие 2-е издание исправленное и дополненное Москва 2002 УДК 681.3.06 32.973.2 57 Смирнов С. И. С. ...»

-- [ Страница 4 ] --

процедуры PROCEDURE или функции или доступ к элемен ту спецификации произвольного пакета.

GRANT PROCEDURE Передача привилегий с использо ванием предложения GRANT на объекты: процедура, функция или пакет. Отзыв привилегий с исполь зованием предложения REVOKE на объекты: процедура, функция или пакет.

GRANT SEQUENCE Передача привилегий с использо ванием предложения GRANT на последовательность. Отзыв приви легий с использованием предло жения REVOKE на последова тельность.

SELECT SEQUENCE Выполнение выборки элементов произвольной последовательности использованием CURRVAL или NEXTVAL В Oracle предусмотрено использование обобщенных имен групп событий для фиксации более широких множеств событий, чем те, что приведены в таблицах 25 и 26. Отметим, что поддержка обобщенных имен групп событий в будущих версиях Oracle не гарантируется.

Таблица 27. Таблица обобщенных имен групп событий Oracle Имя группы Фиксируемые действия CONNECT CREATE SESSION RESOURCE CREATE TABLE CREATE VIEW CREATE PROCEDURE CREATE SEQUENCE CREATE SYNONYM ALTER SYSTEM CREATE DATABASE LINK CREATE TABLESPACE DBA Все привилегии, задаваемые предложением SYSTEM GRANT, a также CREATE USER CREATE ROLE AUDIT SYSTEM CREATE PUBLIC DATABASE LINK CREATE PUBLIC SYNONYM доступа в Oracle ALL Все привилегии, перечисленные в таблице 25 (но не в таблице 26).

ALL PRIVILEGES Все системные привилегии.

Аудит с к объекту Для регистрации событий, связанных с доступом к кон кретному объекту Oracle, пользователь должен иметь приви легию AUDIT ANY. Модификация перечня фиксируемых со бытий, связанных с доступом объекту, может быть выпол нена и в период, когда служба аудита не активизирована. При этом, естественно, записи о регистрируемых событиях начнут появляться только после активизации службы.

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

AUDIT [ }... ] ON [BY { SESSION | ACCESS }] [WHENEVER [NOT] SUCCESSFUL] Параметр шля может прини мать значения, приведенные в таблице 28 и зависящие от типа объекта.

Конструкция BY SESSION указывает на то, что в таблицу аудита заносится единственная запись о регистрируемом дей ствии для каждого сеанса пользователя. Значение использует ся по умолчанию.

Конструкция BY ACCESS указывает на то, что для каж дого выполнения действия в аудита заносится запись о фиксируемом действии. Заметим, что в случае, если в предложении определена фиксация выполне ния предложений подъязыка определения данных, то незави от указанного значения параметра | принудительно выполняются действия, определяе мые конструкцией BY ACCESS.

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

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

Если конструкция WHENEVER... не указана, то ауди торская запись вносится независимо от успешности выполне ния операции. Действие команды аудита к объекту начинается немедленно после ее выполнения, то есть в теку щей сессии.

Таблица 28. Таблица значений параметра применимого к объектам Oracle Имя действия объекты Oracle SELECT Таблица, представление, снимок, по следовательность.

Таблица, представление, снимок.

UPDATE Таблица, представление, снимок.

DELETE Таблица, представление, снимок.

EXECUTE Процедура, функция, пакет.

GRANT Таблица, представление, снимок, по следовательность, процедура, пакет, функция.

AUDIT Таблица, представление, снимок, по следовательность, процедура, функция, пакет.

ALTER Таблица, снимок, последовательность.

RENAME Таблица, представление, снимок, про цедура, функция, пакет.

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

CONNECT Connected.

SQL> AUDIT INSERT ON TAB 2 WHENEVER NOT SUCCESSFUL;

Audit succeeded.

SQL> CONNECT U1/U1PSW@EDUC SQL> INSERT INTO INTO * ERROR at line table or view does not SQL> CONNECT Connected.

INSERT ON Noaudit succeeded.

SELECT USERNAME, ACTION, 2 TIMESTAMP FROM ACTION TIMESTAMP 103 12-04- SELECT USERNAME, 2 FROM SES_ACTIONS. F Протокол регистрации неуспешных по пыток вставки строк в таблицу Прекращение Прекращение регистрации системных событий быть выполнено в любое время пользователем, имеющим привилегию AUDIT SYSTEM.

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

\ [BY пользователь [WHENEVER [NOT] SUCCESSFUL] Список значений, которые может принимать параметр приведен в таблицах 25 и 26. Список не является исчерпывающим, полный можно получить из документа Oracle Server SQL Reference.

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

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

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

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

Если конструкция WHENEVER... не указана, то ауди торская запись вносится независимо от выполне ния операции.

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

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

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

ON [WHENEVER [NOT] SUCCESSFUL] Параметр имя может прини мать значения, приведенные в таблице 28 и зависящие от типа объекта.

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

Конструкция WHENEVER NOT SUCCESSFUL указывает на то, что прекращается фиксация действий, приведших к возникновению ошибки.

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

Действие команды аудита доступа к объекту начинается немедленно после ее выполнения.

данных аудита Для обработки данных, накопленных в ходе аудита, обычно используются представления USER_AUDIT_TRAIL, USER_AUDIT_SESSION, а также Наиболее полную информацию об аудите содержит пред ставление USER_AUDIT_TRAIL. В таблице 29 перечислены имена атрибутов и их содержательные описания.

Таблица 29. Имена атрибутов и их содержательное описание для представления Имя атрибута Тип данных Описание VARCHAR2(255) Имя пользова теля, под кото рым он зареги стрировался в операционной системе.

VARCHAR2(30) Имя пользова теля, под он зареги стрировался в СУБД.

V ARCH AR2( 128) Идентификатор экземпляра ба зы с доступа в Oracle которым рабо тает поль зователь.

TERMINAL VARCHAR2(255) Идентификатор клиентского.

терминала пользователя.

TIMESTAMP DATE Дата и время создания реги страционной записи.

OWNER Владелец объ екта, с ко взаимо действует пользователь (для аудита объектов Oracle).

OBJ_NAME VARCHAR2(128) Имя объекта, с которым взаи модействует пользователь (для аудита объектов Oracle).

ACTION NUMBER Числовой код регистрируемо го действия пользователя.

ACTION_NAME Название реги стрируемого действия поль зователя.

NEW OWNER Владелец пере именованного объекта.

Новое имя реименованно го объекта.

Привилегии доступа к объ екту, которые были предо ставлены или отозваны.

VARCHAR2(40) Системные привилегии, которые были или отозваны.

VARCHAR2(1) Индикатор предоставле ния привиле гий с парамет ром WITH e ADMIN OPTION (при нимает значе ния Y и N) GRANTEE VARCHAR2(30) Имя пользова теля, у которо го изменены привилегии ко мандами GRANT или REVOKE.

AUDIT_OPTION VARCHAR2(40) Параметры ко манды AUDIT.

Средства доступа в Oracle SES_ACTIONS Строка симво лов, описы вающая ус пешное или неуспешное выполнение команды.

DATE Дата и время завершения сессии пользо вателем.

NUMBER Число опера ций логическо го чтения, вы полненных за сессию.

LOGOFF_PREAD NUMBER Число опера ций физиче ского чтения, выполненных за сессию.

LOGOFF NUMBER Число опера ций записи, выпол ненных за сес сию.

LOGOFF DLOCK VARCHAR2(40) Число тупико вых ситуаций, зафиксирован ных в сессии.

NUMBER Числовой идентификатор сессий.

NUMBER Числовой идентификатор регистра ционной запи си.

STATEMENTID NUMBER Числовой идентификатор каждой вы полняемой ко манды.

NUMBER Код возврата для каждой выполняемой команды.

VARCHAR2(40) Системные привилегии, используемые для исполнения регистриру емой команды.

OBJECT_LABEL RAW MLSLABEL Метка объекта (только для Trusted Oracle Server).

SESSION_LABEL RAW MLSLABEL Метка сессии (только для Trusted Oracle Server.) Представление USER_AUDIT_OBJECT предназначено для доступа к информации о регистрируемых событиях, свя занных с конкретными объектами.

Представление USER_AUDIT_SESSION предназначено для доступа к информации о регистрируемых сессиях.

Средства разграничения в Oracle Представление предназна чено для доступа к информации о предоставлении и отзыве привилегий, выполнении команд AUDIT, NOAUDJT и ALTER SYSTEM пользователем.

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

Профили как повышения защищенности системы В качестве дополнительного средства повышения защи щенности системы можно рассматривать поддержку в систе ме Oracle профилей пользователя.

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

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

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

Оператор создания профиля Oracle использует следую щий синтаксис:

CREATE PROFILE Параметр может принимать значения, приведенные в таблице 30. Список не является ис черпывающим, полный список может быть взят из документа Oracle Server SQL Reference.

Таблица 30. Значения параметра для команды создания профиля пользователя Имя параметра Описание SESSION PER USER Максимальное число одновре менных сессий для данного пользователя.

CPU PER SESSION Максимальное суммарное время процессора (в сотых до лях секунды), выделяемое для одной сессии данному пользо вателю.

CPU PER CALL Максимальное время процес сора на операцию (в сотых до лях секунды), выделяемое данному пользователю.

CONNECT TIME Максимальное суммарное время на период сессии (в ми нутах), выделяемое данному пользователю.

IDLE TIME Максимальное время непре рывного неактивного состоя ния пользователя в минутах (время исполнения длитель ных операторов не считается Средства доступа в Oracle Максимальное суммарное число блоков данных, ных за период сессии, выде ляемое данному пользовате лю.

LOGICAL_READS_PER Максимальное суммарное CALL число блоков данных, считанных за время вы полнения одного предложения SQL, выделяемое данному пользователю.

Параметр может при нимать одно из трех значений:

— целое_число — UNLIMITED — DEFAULT.

Как правило, изначально в системе существует один про филь DEFAULT, у которого отсутствуют ограничения на ис пользуемые ресурсы. Обычно этот профиль назначается всем пользователям по умолчанию. Для введения ограничений на ресурсы его требуется изменить или создать новые профили и назначить их пользователям. Для включения системы ограни чения использования ресурсов необходимо выполнить коман ду ALTER SYSTEM SET = TRUE или изменить значение соответствующего параметра в файле па раметров базы данных.

В примере, представленном в листинге 175, создается профиль с ограничением на время соединения.

CONNECT ALTER SYSTEM SET = System altered.

SQL> CREATE PROFILE pfl LIMIT 1;

Profile created.

SQL> ALTER USER PROFILE User altered.

CONNECT Connected.

-- ожидаем SQL> SELECT. * FROM SELECT * FROM dual * ERROR at line 1:

exceeded maximum connect time, you are being logged off Листинг 175, Пример создания и назначения пользо вателю ограничения на используемые ресурсы сведения Для большой корпоративной системы разграни чения доступа может оказаться затрудненным. Причиной мо жет оказаться большое число объектов, пользователей, ролей, привилегий (системных и доступа к объектам). В этом случае проблема состоит не в том, что невозможно предоставить все необходимые привилегии, а в том, что в результате операций взаимно назначаемых привилегий, ролей и т. д. пользователь может получить несанкционированный доступ к данным. Для преодоления проблемы рекомендуется использовать специ альные программные продукты третьих фирм, которые по зволяют по словарю данных сформировать подробные отчеты по актуальным привилегиям. Также существуют специальные утилиты анализа таблиц аудита.

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

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

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

Создание приложений на языке Java Стандартным интерфейсом для взаимодействия прило жений, написанных на языке с является JDBC (Java DataBase Connectivity). Спецификации интерфейса JDBC определяются организацией JavaSoft и поддерживаются всеми основными производителями СУБД.

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

приложений и к базам данных Широкое распространение Всемирной паутины (WWW) и систем обработки данных, построенных на Web технологиях, привело к тому, что все производители СУБД на языке Java промышленного уровня одновременно являются производи телями "сопутствующих" средств разработки приложений и их интеграции с серверами баз данных. Современная модель информационных технологий обычно включает тонкого кли ента на основе браузера, сервер баз данных и набор компо нент промежуточного уровня, использующие технологии распределенной обработке данных.

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

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

Тонкий клиент, характерный для Web-технологий, явля ется только средством просмотра информации, подготовлен ной и отправленной в среду доставки соответствующими сер верами.

Задачей, характерной для Web-технологий, является деление процессов, выполняемых на клиенте и сервере. Раз деление процессов является способом эффективной органи зации распределенной обработки данных. Оптимальный уро вень производительности достигается на любой конфигура ции аппаратных средств за счет адекватного разделения ре сурсов.

Каким же образом это обеспечивается? По определению тонкий клиент обладает минимальным набором программных и аппаратных ресурсов, обеспечивающих только поддержку сетевого транспорта и интерпретацию данных, получаемых от Web-приложений. По сути, тонкий клиент должен поддержи вать HTTP протокол, интерпретатор языка HTML и вирту альную Java-машину. может быть реализован не толь ко на традиционном персональном компьютере, но и на кар манном компьютере или даже на сотовом телефоне.

. Расширение функциональности тонкого клиента возмож но с помощью использования на языке Java.

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

Одной из наиболее простых и распространенных Web технологий является CGI (Common Gateway Interface). Эта технология обеспечивает тонкому клиенту возможность фор мировать запросы на доступ к требуемой информации через Web-сервер. После получения запроса посредством CGI, ис полняемый модуль возвращает ответ в форме HTML-текста.

Создание приложений на Java Объектно-ориентированным интерфейсом прикладного программирования (API), обеспечивающим взаимодействие с Oracle приложений, написанных на языке Java, является JDBC. Интерфейс JDBC определяет классы объектов Java, характерных для выполнения операций с базами данных: со единение, подготовка и выполнение SQL-операций, доступ к результатам, доступ к метаданным и т. п.

Набор классов, реализующий интерфейс JDBC для Oracle, стандартно размещается в файле или classesl (в от версии JDK), который обыч но располагается в каталоге Для разрешения соответствующей ссылки нужно указать место приложений на Java размещения библиотеки, задав соответствующим образом переменную CLASSPATH.

Для операционных систем Windows значение переменной CLASSPATH может быть определено явно в файле Для UNIX-систем дополнительно должны быть определены пути доступа к необходимым библиотекам.

Для создания приложений, использующих интерфейс JDBC, важно уяснить назначение и методы классов Connect, Statement и ResultSet. Прежде чем обращаться к соответст вующим методам, необходимо загрузить драйвер JDBC.

Простейшее приложение на Java Класс Connect обеспечивает инициализацию взаимодействия пользователя с сервером Oracle, включая ау тентификацию и авторизацию, установку параметров сессии (режим выполнения транзакций и т. п.).

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

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

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

// Пример программы установления соединения с Oracle // import.

public class TestCon // Шаг Зарегистрировать JDBC драйвер Oracle // Класс драйвера должен быть загружен до // выполнения операций с объектами // static try { ora cle } catch e) { регистрации драйвера public static void main(String ) { try // Шаг 2: Создание соединения ft String Connectstring = String = String Psw = Connection = DriverMan ager UserlD, // // Шаг З: Показать информацию о соединении, // используя метод // на Java + ) : + Version : + // // Шаг 4: Закрыть соединение // e) { // Шаг Х: Универсальный обработчик исключительных ситуаций как + + Листинг 176. Текст приложения, инициализирующего сессию и выводящего информацию о параметрах сервера баз данных Предположим, что компиляция приложения осуществля ется в каталоге а текст приложения, представлен ного в листинге 176, сохранен в файле TestCon.java. Компи ляция приложения выполняется командой:

Javac После успешной компиляции в текущем каталоге должен появиться файл с именем Выполнение осуще ствляется командой:

Java TestCon При отсутствии сбоев в работе приложения на терминал выводятся сообщения, представленные в листинге Java TestCon ConnectString :

ora Oracle Version : Enterprise Edition Re lease - Production With the Objects op tion PL/SQL Release - Production 177. Протокол штатного при ложения Когда установить соединение с сервером невозможно, выводятся сообщения об ошибках. Если, например, экземпляр сервера не запущен, то на терминал выводятся сообщения, представленные в листинге Java Хотели как null SQLCODE:

Connection Листинг Протокол регистрации ошибки при ус тановлении соединения приложений на Java Если, в приложении указано неправильные имя пользова теля или пароль, то на терминал выводятся сообщения, пред ставленные в листинге TestCon Хотели как SQLState: SQLCODE: invalid logon denied 179. Протокол регистрации ошибки при ус тановлении соединения апплет Отличие от представленных программ состоит в том, что всегда является расширением специального класса, описание которого хранится в библиотеке java.applet, и апплет запускается из некоторой HTML-страницы. Рас смотрим пример апплета, который обеспечивает инициализа цию сессии взаимодействия пользователя с сервером Oracle, включая аутентификацию пользователя и вывод информации о параметрах сессии, то есть полный аналог функций прило жения, представленного в листинге 176. Текст примера ап плета приведен в листинге Отличие состоит в описании основного класса (являюще гося расширением класса Applet) и использовании класса paint для вывода данных.

// Апплет Апплет выполняет // соединение с Oracle import j import import public class TestACon extends Applet // Шаг 1: Зарегистрировать JDBC драйвер Oracle // Класс драйвера должен быть загружен до // выполнения операций с объектами // public void {• try { ora } catch e). { регистрации драйвера // Метод обеспечивает вывод // public void g) { // Шаг Создание объекта соединения (Connection) String ConnectString = String = String приложений на Java Т Connection con = // Шаг З: Показать информацию о // используя метод DatabaseMetaData = : + 10, : + 10, Version : + 10, // // Шаг Закрыть соединение // } e) // Шаг Универсальный обработчик // исключительных ситуаций // как +,10, + Листинг Текст апплета, выполняющего соедине _ ние с сервером баз данных _ Пусть, как и ранее, компиляция апплета осуществляется в каталоге Текст представленного в лис тинге должен быть записан в файле то Раздел есть совпадать с именем класса. Компиляция приложения вы полняется командой:

Javac Для запуска обеспечивающего инициализацию сессии взаимодействия пользователя с сервером Oracle, необ ходимо подготовить соответствующую HTML-страницу. Ва риант текста страницы, из которой выполняется запуск ап плета, приведен в листинге Предполагается, что байт-код апплета получен в результате, приведенной выше команды и располагается в том же каталоге, что и текст страницы (параметр code тэга applet). Размер окна для выво да данных в примере определен 400 на 200 пикселов.

апплета соединения с Oracle Листинг 181. Текст простейшей HTML-страницы, в запускается апплет с сервером Oracle Сохраним представленный в листинге текст HTML страницы, например, в файле После этого можно открывать этот файл в окне браузера и получать при различных условиях результаты, аналогичные результатам, представленным в листингах Перейдем к обсуждению программ, осуществляющих об работку данных.

352 ' ' на языке Java Выполнение создания ввода и модификации данных Выполнение на языке SQL, не содержащих подставляемых параметров, осуществляется в два этапа: сна чала с помощью метода класса Connect соз дается объект — предложение SQL, а потом оно выполняется вызовом соответствующего метода класса Statement.

Основным методом, предназначенным для выполнения SQL-предложений, осуществляющих создание и уничтожение объектов, является метод executeUpdate (String).

Рассмотрим пример приложения на языке Java, которое создает с заранее определенным именем и атрибута ми. Инициализация сессии взаимодействия с сервером Oracle осуществляется по технологии, представленной выше и про иллюстрированной в листинге 176.

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

// Пример приложения, создающего таблицу import class TestCre // Шаг 1: Зарегистрировать JDBC драйвер Oracle / / static try, № ora catch { регистрации драйвера } • public static void args [ { try { // // Шаг 2: Создание объекта соединения // String ConnectString = String UserlD = String Psw = Connection con // Шаг З: Подготовить предложение создания // таблицы, используя метод // createStatement // String CreTab = "CREATE TABLE Statement cS = // Шаг Выполнить • // используя метод executeUpdate // // // Шаг 5: Освободить ресурсы // приложений на Java close } e) // Шаг Х: Универсальный обработчик исключи тельных ситуаций // как + ) + Листинг 182. создающего таблицу в базе данных Текст приложения, в листинге дол жен быть сохранен в файле TestCre.java. После успешной компиляции в текущем каталоге должен появиться файл с именем TestCre.class.

В нормальном варианте работы приложения на терминал выводятся сообщения, представленные в листинге Java TestCre Таблица Листинг 183. Протокол штатного режима работы при ложения, создающего таблицу В том случае, когда с именем уже сущест вует в базе данных (например, при втором запуске приложе ния), выводится сообщение ошибке.

12* Java TestCon Хотели, как лучше...

name is already used by an existing object Листинг 184. Протокол регистрации ошибки при по пытке создания таблицы с именем, уже существующим Для выполнения вставки, удаления и модификации строк таблиц используется метод executeUpdate (String).

Например, для вставки строки в таблицу, созданную при ложением, представленном в листинге 182, выполним сле дующие изменения. Строку String CreTab = "CREATE TABLE At2 Inte необходимо заменить на строку String InsTab = "INSERT INTO TABLE Tabl VALUES строки заменить на строки V приложений на Java После выполнения этих действий можно откомпилиро вать и выполнить приложение, осуществляющее встав ку данных.

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

Класс ResultSet используется для отображения результа тов выполнения запроса. С каждым экземпляром класса ассо циируется положение курсора.

Для перемещения курсора по экземпляру класса зуется метод класса ResultSet. Применение этого мето да приводит к перемещению курсора к следующей строке.

После выполнения запроса курсор находится перед первой строкой результата.

Извлечение данных, полученных в результате выполне ния запроса, обеспечивается специальным методом. Для каж дого типа данных языка Java используется ассоциированный метод get. To есть для типа данных STRING используется ме тод getString, для типа данных INTEGER используется метод и т.п.

Рассмотрим пример приложения на языке Java, которое осуществляет выборку данных из таблицы, созданной прило жением, представленным в листинге Предполагается, что предварительно выполнена вставка строки ('abc', 10) как ука зано выше.

// // Пример приложения, осуществляющего выборку import public class TestSel { // // Шаг Зарегистрировать JDBC драйвер Oracle // static { try { ora dbc. OracleDriver () ) catch (Exception e) { регистрации драйвера •} } public static void { { // // Шаг объекта соединения // String ConnectString = String UserlD = String Psw = Connection con = DriverMan // // Шаг З: Подготовить предложение // выборки из используя // метод // String SelTab = "SELECT * FROM Statement cS = con. createStatement // // Шаг 4: Выполнить операцию, // используя метод executeQuery на Java // ResultSet RS = ' out. + At ----------- --- while { + // Шаг Освободить ресурсы } { // Шаг Х: Универсальный обработчик исключи тельных ситуаций // как..

+ ) + Листинг 185. приложения, осуществляющего выборку из таблицы В нормальном варианте работы приложения, когда база данных ORCL доступна и в ней существует таблица с именем на терминал выводятся сообщения, представленные в листинге 186.

Java TestSel Листинг 186. Протокол штатного режима работы приложения, осуществляющего простую выборку Приложение, представленное в листинге 185, имеет су щественное функциональное ограничение в том смысле, что информация о структуре таблицы жестко встроена в приложение. Если структура таблицы изменится, то текст приложения нужно будет изменить, а приложение пере компилировать.

•Более гибким будет решение, основанное на извлечении метаданных о структуре таблицы и именах столбцов из сло варя данных. Изменим фрагмент текста шага 4 приложения, представленного в листинге 185, на фрагмент, представлен ный в листинге и назовем новое приложение TestSell (сохранив его в файле // Шаг Выполнить операцию, // используя метод // ResultSet RS = int i;

// // Получить доступ к метаданным // RSMD = // /У Определить число столбцов таблицы // int numCols = // // Вывести имена столбцов // for (i 1;

i <= numCols;

{ приложений на Java if (i > 1) } out ( ------ - ------- - // // Вывести содержимое таблицы // while { for (i = 1;

i <= if i > } Листинг 187. Изменения, связанные получением метаданных о структуре таблицы и именах столбцов при осуществлении простой выборки Запуск модифицированного приложения приводит к тем же результатам (представленным в листинге Пусть таблица с именем модифицирована так, что она теперь включает три столбца: — содержащий строки переменной длины не более 5 символов, At2 — содержащий числа и At3 — содержащий даты. Предположим, что допол нительно введена строка 20, SYSDATE).

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

При этом в нормальном режиме работы приложения, моди фицированного в соответствии с листингом будут выве дены сообщения, представленные в листинге Java TestSell AT abc 10 null 20 2002-03- Протокол штатного режима работы мо дифицированного приложения, выполняющего выборку с использованием метаданных запросы SQL-предложение может содержать подставляемые па раметры. Такое предложение готовится вызовом метода prepareStatement().

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

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

Рассмотрим пример, когда критерий содержит параметр. Пусть запрос формулируется как SELECT * FROM WHERE At2 > X, где значение параметра X вводится пользователем с терминала. По сути изменения коснулись только шага 3 базового приложения. Текст приложения, вы полняющего подстановку вводимого значения в параметри ческий запрос, приведен в листинге // Пример приложения, выполняющего // параметрический запрос import j приложений на Java public class TestSel { // // Шаг 1: Зарегистрировать JDBC драйвер Oracle // static try { ora cle ) catch (Exception e) { регистрации драйвера } } public static void { try // Шаг 2: Создание объекта соединения // String = String = String Psw = Connection con - DriverMan ager.

// // Шаг З: Подготовить предложение создания // таблицы, используя метод // // String SelTab "SELECT * FROM WHERE At2 > ?

pS Раздел // Шаг 3.1 Ввести значение порога // InputStreamReader isr = new InputStream Reader br = new Buffere String st;

пороговое зна чение st // // Шаг Выполнить подстановку // // // Шаг 4: Выполнить операцию, // используя метод ResultSet RS = // // Получить доступ к метаданным // ResultSetMetaData RSMD = // Определить число столбцов таблицы // int numCols // // Вывести имена столбцов int for (i = 1;

i <= numCols;

i++) { if (i > 1) ) } // // Вывести содержимое таблицы // while { for = 1;

i <= numCols;

{ на Java if (i > } // Шаг Освободить ресурсы // } e).

{ // Шаг Х: Универсальный обработчик исключи тельных ситуаций // как.

+ ) } ioe) Листинг Приложение, осуществляющее парамет рический запрос, значение параметра для ко торого вводится пользователем В нормальном режиме работы приложения, представлен ного листингом на терминал будут выведены следующие сообщения.

Java TestSel Введите пороговое значение АТЗ 20 2002-03- Протокол штатного режима работы при ложения, выполняющего параметрический за прос Хранимые процедуры на языке Java языка Java встроена также в ядро сервера (наличие поддержки зависит от версии сервера и комплекта поставки). В настоящее время активно обсуждает ся использование Java для написания хранимых программ в качестве альтернативы PL/SQL. К преимуществам Java перед PL/SQL относятся, в частности, большое количество готовых библиотек, динамическая загрузка расширенные возможности для работы в сети. К преимуществам PL/SQL следует отнести его изначальную ориентированность на рабо ту базы данных, огромное количество строк кода различных приложений, сопровождаемого большим числом программистов.

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

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

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

— — все привилегии;

— — ограниченный набор привилегий;

— JAVASYSPRIV — расширенный набор привилегий.

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

с помощью SQL-предложений и с помощью инструкций Java.

Для первого способа используется специальный пакет Приведем пример предоставления пользователю U1 при вилегий на чтение/запись для каталога SQL> 5 END;

6 / PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit 191. Предоставление привилегий с помощью пакета DBMS JAVA После предоставления/отмены привилегий необходимо выполнить фиксацию транзакций, так как все изменения от ражаются в специальной таблице SYS.JAVA$POLICY$ (там можно посмотреть назначенные привилегии, однако прямое изменение в таблице не рекомендуется). Обратите внимание при работе с привилегиями с помощью операторов GRANT и REVOKE фиксация транзакций производится ав томатически.

Для разграничения доступа также можно использовать Java-класс с аналогичными методами.

Создание хранимой программы с использованием Java включает в себя несколько этапов:

— создание Java-кода;

помещение Java-кода в базу данных;

— предоставление доступа к Java-программе.

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

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

— с использованием специальных операторов SQL;

— с использованием загрузчика loadjava (с предвари тельной компиляцией (загружается или без (загружает ся Для первого способа применяется SQL-оператор CREATE JAVA. Для работы с ним необходимо иметь систем ные привилегии CREATE PROCEDURE и CREATE TABLE.

Существует три типа команды CREATE JAVA (CLASS, SOURCE, RESOURCE) для создания соответственно класса, исходного Java-кода и Java-ресурса (рисунка, файла свойств и т.п.) из значения типа LOB или текста самой команды. Для создания хранимой процедуры можно использовать CREATE JAVA SOURCE (загружается и компилируется ис ходный код) или CREATE JAVA CLASS (загружается отком пилированный класс из значения типа BFILE). Пример созда ния кода хранимой процедуры представлен в листинге приложений на языке Java SQL> CREATE OR REPLACE JAVA SOURCE NAMED 2 as public class jcl{ •3 public static String 4 return "Hello from Oracle Java Support 5 }} 6 / Java Листинг 192. Пример загрузки в базу данных Java с помощью оператора CREATE JAVA SOURCE Для загрузки кода с помощью утилиты требуется те же привилегии, что и для применения оператора CREATE JAVA, а также привилегии для операций с записями в табли це в которой хранятся сведения о классах. Эти сведения используются для контроля измене ний в классах, чтобы при необходимости перекомпилировать классы или заново не загружать старую версию при повтор ной загрузке.

Утилита loadjava имеет такие же параметры, что и опера тор CREATE JAVA. К дополнительным относят ся: строка соединения с базой данных;

используемый при этом драйвер;

логические параметры: загружать все указан ные или только обновленные;

создавать ли общедос тупные синонимы для загружаемых объектов и т. п. Пример использования утилиты loadjava приведен в листинге -user ul/ul@educ verbose arguments:

created creating : class created loading : class Листинг 193. класса с помощью утилиты loadjava Данная команда указывает утилите loadjava соединиться с базой данных, используя учетную запись пользователя U1, и загрузить в его схему класс Параметр VERBOSE специ фицирует вывод на экран протокола загрузки.

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

Удаление объектов Java из базы данных производится оператором DROP JAVA (CLASS, SOURCE, RESOURCE) или утилитой dropjava. Пример удаления Java-объектов приведен в листинге 194.

SQL> DROP JAVA SOURCE Java dropped.

SQL> DROP JAVA CLASS Java dropped.

Листинг 194. Пример удаления Java-кода из базы данных За этапом загрузки следует этап публикации, который представляет собой создание хранимой программы, ссылаю щейся на статический метод загруженного класса. Эта про грамма может представлять собой хранимую процедуру или функцию, процедуру или функцию в пакете или метод объек та. Синтаксис команд создания Java-процедур такой же, как и при создании обычной хранимой процедуры, отличается приложений на Java только описание тела процедуры. В листинге представле на публикация метода Say() класса После публикации ее можно использовать как обычную хранимую функцию. Ком пиляция класса будет произведена при первом обращении к нему.

SQL>CREATE OR REPLACE FUNCTION RETURN VARCHAR 2 LANGUAGE JAVA NAME 3 return 4 / Function created.

SELECT fl FROM dual;

Fl Hello from Oracle Java Support 195. использование Java-кода пример создания хранимой процедуры с пере даваемыми и возвращаемыми параметрами. Пусть таблица создана CREATE TABLE SQL> CREATE OR REPLACE JAVA SOURCE NAMED AS 2 import 3 public class store_proc{ 4 private static String query = 5 public static void 6 try{ 7 = 8 Connection conn = 9 DriverMan 10 Statement = 11 ResultSet rset =. 13 = 14. } 15 else { 16 = 17 } 18 stmt = 19 INTO VAL UES [0] ) 20 } 23 } 24 } 26 public static void 28 } 30 } 31 / Java created.

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

372.

приложений на Java Для публикации этого класса выполним следующие дей ствия:

SQL> CREATE OR REPLACE PROCEDURE 2 IN varchar2, 3 answer IN OUT varchar2) 4 as language name 6 / Procedure created.

OR REPLACE PROCEDURE selData AS 2 LANGUAGE JAVA NAME 3 / Procedure created.

Листинг Публикация класса store_proc Проверим функционирование опубликованного класса.

Результаты работы его методов представлены в листинге 198.

SQL> DECLARE 2 str 3 BEGIN 4 FROM '. 6 END;

7 / PL/SQL procedure successfully completed.

SQL> SELECT * FROM 12-03- 12-03- 198. Результат работы методов класса store_proc В заключение рассмотрим использование в Java статических операторов SQL SQL in Java" или SQLJ). Для того чтобы SQL выражений в Java-коде была так же естественна, как и в про граммах PL/SQL, применяется следующая технология:

операторы SQL внутри кода Java записываются по опреде ленным правилам, затем транслируются в "чистый" код Java (один или несколько классов) и только после этого компили руются в байт-код.

Пусть таблица создана и заполнена следующими предложениями:

CREATE TABLE Tab INSERT INTO Tab2 VALUES INSERT INTO Tab2 VALUES В листинге представлена программа, с помощью ко торой выводятся все записи из таблицы с использовани ем технологии SQLJ.

SQL> CREATE OR REPLACE JAVA SOURCE NAMED AS 2 public class 3 public static iterator objec-.

4 public static String 5 throws String answer = 8 String Atl = 9 String = 10 #sql oi = from 11 while (true) FETCH -:oi INTO 12 if ) break;

13 14 return 15 } приложений на Java 16 / Java created.

SQL> CREATE FUNCTION giveList RETURN VARCHAR 2 AS LANGUAGE JAVA NAME 3 return Function created.

SELECT giveList FROM dual;

GIVELIST A - В;

С Листинг 199. Использование технологии SQLJ Раздел Средства обеспечения целостности данных Современный уровень развития распределенной обработ ки данных характеризуется размещением логически единой информационной базы в сетевой среде и асинхронной много пользовательской обработкой с развитыми средствами обес печения целостности данных. Наличие единой информацион ной базы и асинхронной обработки данных ставит сложную задачу поддержки согласованности данных независимо вносимых изменений различными пользователями в различ ное время.

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

Источниками соответствующих угроз являются:

— нарушения нормального режима функционирования аппаратуры;

— ошибки, связанные с необходимостью обеспечить па раллельное проведение операций с единой информационной базой;

— ошибки, присутствующие программном обеспече нии;

— администраторов, осуществляющих штатные операции с базами данных;

— ошибки пользователей при выполнении операций ввода и модификации данных.

Средства обеспечения целостности данных в Oracle Защита данных от проблем, вызванных нарушениями нормальной работы аппаратуры, обеспечивается осуществлением процедур резервного копирования баз дан ных. Для создания резервных копий баз данных используются две основные технологии: оперативное копирование файлов (или содержимого псевдоустройств для UNIX-систем) и экс порт баз данных.

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

Физическое копирование файлов выполняется быстрее, но доступ к копии требует воссоздания операционной среды:

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

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

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

Ограничения целостности могут быть сформулированы в статической (декларативной) или динамической форме. Спо собы описания ограничений в статической форме определены в стандарте SQL92 и более поздних стандартах в области об работки данных.

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

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

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

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

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

До тех пор пока транзакция не ее можно "откатить" (ROLLBACK), то есть все сделанные операторами из транзакции изменения в базе данных. Обра обеспечения в Oracle внимание, что смысл фразы "SQL-операторы транзакции успешно завершены" отличается от смысла фразы "транзак ция зафиксирована" (COMMITTED). Успешное выполнение SQL-операторов означает, что операторы проанализированы, интерпретированы как правильные, а затем безошибочно ис полнены. "Зафиксировать транзакцию" означает сделать изменения, выполненные данной транзакцией в базе данных, постоянными. транзакция не зафиксирована, результат ни одного из ее действий не виден другим пользователям.

Например, пусть регистрируется переход работника из одного подразделения в другое оформленный в виде транзак ции, изменения в базе данных организации либо будут прове дены в совокупности, либо будут отменены, если в процессе их проведения будет зафиксирована ошибка. То есть такой ситуации, когда зарегистрирован в двух под разделениях сразу (обычно этому соответствует две записи), не может быть, если удаление "старой" и вставка "новой" за писей представлено как транзакция. Отметим, что для систем типа dBase, изначально ориентированных на ведение локаль ных данных, проблема согласованности данных, даже в сетевой среде, либо не возникает вообще, либо решается с помощью средств операционной системы.

Начало и окончание Транзакция начинается при поступлении первого SQL оператора и завершается при появлении одного из следую щих событий:

— выдана команда COMMIT или ROLLBACK;

выдана одна из таких команд описания данных (DDL), как CREATE или DROP (при фиксируется пре дыдущая транзакция);

— завершился оператор DDL (транзакция, содержащая оператор языка описания данных, автоматически фиксирует ся);

завершил сеанс (последняя транзакция автоматически фиксируется);

— процесс пользователя аварийно завершен (транзакция автоматически откатывается).

. Как только транзакция завершена, следующий SQL оператор начинает новую транзакцию. Если в конце транзак ции отсутствуют операторы COMMIT или ROLLBACK, то нормальное завершение программы приведет к фиксации транзакции, а аварийное завершение (вызванное, например, разрывом связи в локальной сети) вызовет откат Если произошел сбой на стороне сервера, то откат транзакции с использованием сведений из журналов транзакций будет автоматически выполнен при запуске экземпляра после вос становления нормального режима работы сервера.

Предложения акциями Для фиксации или отката транзакции в языке SQL ис пользуются предложения COMMIT WORK, SAVEPOINT и ROLLBACK WORK.

Предложение COMMIT фиксирует транзакцию. Предло жение SAVEPOINT сохраняет промежуточную копию со стояния задействованных в транзакции объектов базы данных для того, чтобы впоследствии, при необходимости, можно было вернуться к состоянию в точке сохранения. Предложе ние ROLLBACK выполняет откат транзакции, то есть отме няет изменения, выполненные данной транзакцией в базе дан ных. Откат транзакции обычно выполняется в том случае, когда произошла ошибка в приложении, обнаружено наруше ние ограничений целостности или обнаружен клинч систем ных ресурсов. Для иллюстрации понятия клинча системных ресурсов пример.

целостности в Oracle Пусть процесс А в момент времени запросил в моно польное использование ресурс X. Ресурс X был предоставлен процессу А. В момент времени t2 > процесс В запросил в монопольное использование ресурс Y. Ресурс Y был предос тавлен процессу В. В момент времени > процесс А запро сил в монопольное использование ресурс Y. Поскольку ре сурс Y находится в монопольном использовании процесса В, то процесс А переводится в состояние ожидания. В момент времени > t2 процесс В в монопольное исполь зование ресурс X. Поскольку ресурс X находится в моно польном использовании процесса А, то процесс В переводит ся в состояние ожидания. В результате — клинч. Оба процес са могут находиться в состоянии ожидания сколь угодно дол Предложенный Н. формальный алгоритм разре шения таких конфликтов не всегда приемлем, так как требует единовременного запроса на все монопольно используемые ресурсы. Для СУБД подход мог бы привести к потере эффективности обработки данных. Альтернативный подход состоит в том, что пользовательские приложения должны са ми определять ситуацию клинча и выполнять транзак ции.

Предложение COMMIT WORK Предложение COMMIT WORK имеет следующий син таксис:

COMMIT [WORK] Ключевое слово WORK может быть опущено. Предложе ние COMMIT WORK обеспечивает выполнение действий:

— фиксируются все изменения в базе данных, сделанные в текущей транзакции;

— завершается транзакция;

— уничтожаются все точки сохранения для данной транзакции;

— освобождаются объекты, заблокированные в процессе выполнения транзакции.

Хорошим стилем разработки кода прикладных программ является явное завершение транзакций. Рекомендуется выда вать предложение COMMIT WORK и перед завершением се анса. Использование такой практики обеспечит автоматиче ский откат неуспешной транзакции прогнозируемым ре зультатом при аварийном завершении прикладного процесса, вызванного программным или аппаратным сбоем.

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

— создается копия данных в сегментах отката;

— выполняется формирование соответствующих записей в журнальный файл;

— производятся изменения в буферах базы данных.

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

транзакция помечается как зафиксированная;

— если записи из журнального файла еще не записаны в файлы базы данных, то выполняется запись в долговремен ную память;

— заблокированные объекты системы освобождаются.

Команда COMMIT также позволяет указывать значение параметра COMMENT для комментирования подтверждае мой транзакции.

Средства обеспечения целостности данных в Oracle предложения SAVEPOINT Точки сохранения (SAVEPOINT) используются для раз деления длительной транзакции на более мелкие элементы. В длительной транзакции, выполняющей значительные измене ния в базе данных, целесообразно сохранять частичные изме нения в определенных точках, чтобы при возникновении ошибки или клинча системных ресурсов выполнять откат не к точке старта транзакции, а к последней успешно выполнен ной точке сохранения.

Предложение SAVEPOINT следующий синтаксис:

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

Предложение ROLLBACK WORK Это предложение служит для отката транзакции к исход ному состоянию или точке сохранения и имеет следующий синтаксис:

ROLLBACK [WORK] TO Использование ключевого слова WORK необязательно.

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

— завершению выполнения транзакции;

— всех изменений в текущей транзакции;

— отмене всех блокировок транзакции.

Проиллюстрируем назначение команд SAVEPOINT и, ROLLBACK следующими примерами.

б Пример 1 Пример INSERT INTO SQL> INSERT INTO 1 row created. 1 row created.

ROLLBACK;

SQL> SAVEPOINT Rollback complete. created.

SQL> SELECT * FROM INSERT INTO Tabl no rows selected 1 row created.

SELECT COUNT (*) FROM SQL> ROLLBACK TO Rollback complete.

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

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

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

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

— "проблема пропавшего обновления" (два пользователя запрашивают из базы одни и те же данные и используют их для каких-либо расчетов, а затем пытаются обновить эти дан ные);

— "проблема промежуточных данных" (пользователь в ходе выполнения своей работы имел доступ к промежуточ ным данным, внесенным другим пользователем и на их осно ве произвел ошибочное обновление);

— "проблема строк-фантомов" (один и тот же запрос, пользователем дважды в течение одной тран закции, возвращает два различных результата).

Подобные ситуации подробно в стандартах SQL и имеют собственные названия.

Очевидным решением проблемы согласованного изме нения базы данных является формирование очереди № 1628.

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

Сервер Oracle использует иной метод решения сформу лированной проблемы — многоверсионную модель, при торой в системе может одновременно существовать несколь ко различающихся версий данных. Механизм версионности реализован в Oracle через сегменты отката, в которых хранят ся старые версии данных. В случае когда оператор пытается прочитать данные, модифицированные другими пользовате лями за время его работы, старые версии данных на момент начала запроса считываются из сегментов отката. При этом Oracle не обеспечивает гарантированного чтения предыдущих версий данных из-за возможного их затирания в сегментах отката. В случае затирания в сегментах отката старых версий данных выдается ошибка Snapshot too old, rollback segment too small". Эта ситуация характерна для выполняю щихся длительное время сложных запросов в динамично из меняющейся базе данных. Чем дольше выполняется оператор, больше вероятность затирания старых версий данных.

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

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

Всего существует четыре уровня изоляции: SERIALIZABLE;

READ;

COMMITTED;

READ UN COMMITTED.

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

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

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

Уровень изоляции "незавершенное чтение" (READ UNCOMMITTED) является наиболее низким уровнем изоля 13* ции. В этом режиме на выполнение транзакции могут повли ять как окончательные, так и промежуточные результаты дру гих транзакций. В общем случае этот уровень подходит толь ко для приложений со специальными запросами, где пользователь может позволить, чтобы результаты запро сов содержали "грязные" данные. Если важно, чтобы резуль таты запроса предоставляли только ту информацию, является окончательной на текущий момент для базы данных, то не следует использовать этот режим.

В инструкции SET TRANSACTION можно указать, опе рации какого типа осуществляет транзакция: выполняет ли она только запросы на выборку данных (атрибут READ ONLY) или может выполнять как выборку, так и изменение (атрибут READ WRITE). Сервер использует эту информацию со сведениями об уровне изоляции для оптимизации работы базы данных.

Oracle поддерживает следующие типы транзакций: Тран закция, читающая зафиксированные другими транзакциями данные во время ее работы. Пользователь может явно начать такую транзакцию при помощи оператора SET TRANSACTION READ WRITE. Транзакция по умолчанию (от начала сеанса до первой команды от команды COMMIT/ROLLBACK до следующей команды или до конца сеанса) является тран закцией этого типа. Перед началом любой транзакции по умолчанию неявно выполняется оператор SET TRANSACTION READ WRITE. Если два оператора SELECT в разных частях транзакции этого типа обращаются к одним и тем же строкам в таблице, то возможно получение противо речивых результатов. Противоречие возникнет в том случае, если какой-либо пользователь модифицировал и зафиксиро вал изменения этих строк на интервале времени между двумя вышеупомянутыми операторами SELECT. To есть в рамках одной транзакции возможно чтение данных, модифицирован ных зафиксировавшим изменения другим пользователем.

Пример таких транзакций приведен ниже.

Средства обеспечения целостности данных в SQL> SET TRANSACTION SQL> CREATE TABLE READ Tabl(Atl Table, SQL> SELECT * FROM no rows selected.

INSERT INTO Tabl 1 row created.

Commit complete.

SELECT * FROM 1 a Транзакция для Внутри такой транзак ции допустимы только запросы, другие операторы языка DML недопустимы. При обращении операторов SELECT из различных транзакции к одним и тем же строкам дан ных Oracle выдает либо одинаковые непротиворечивые дан ные, находившиеся в базе данных на момент начала транзак ции, либо ошибку Пользователь может начать та кую транзакцию при помощи оператора SET TRANSACTION READ ONLY и закончить оператором SQL> SET TRANSACTION SQL> CREATE TABLE READ ONLY;

Table created.

SQL> SELECT * FROM Tabl;

no rows selected.

SQL> INSERT INTO Tabl 1 row created.

COMMIT;

Commit complete.

SQL> SELECT * FROM no rows selected.

.

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

Способ выполнения набора транзакций называется сериаль ным, если результат совместного выполнения транзакций эк вивалентен результату некоторого последовательного выпол нения этих же транзакций. При помощи этих транзакций, од новременно выполняемых пользователями, резуль тат модификации данных в базе таков, как если бы соблюда лась очередность, то есть сначала один пользователь начал и закончил транзакцию, после него второй провел свою тран закцию, потом третий и т. д. Вообще говоря, процесс сериа лизации очень сложен, и не всегда возможно се одновременно проводимые несколькими пользо вателями транзакции. транзакции всегда непротиворечивы, то есть все выборки получают данные из "снимка" базы, сделанного на момент выдачи оператора SET TRANSACTION ISOLATION LEVEL При условии, что запись осуществляется только в таблицы данно го пользователя, можно трактовать транзак ции как транзакции только для чтения (READ ONLY) с воз можностями выдачи операторов DML. тран закция начинается оператором SET TRANSACTION ISOLATION LEVEL SERIALIZABLE.

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

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

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

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

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

Чаще всего пользователи блокируют определенные стро ки таблиц, используя оператор SELECT... FOR UPDATE, или полностью таблицы, используя оператор LOCK Блокировка данных может иметь различный тип. Тип блокировки определяет, какие действия можно выполнять с заблокированным ресурсом другим пользователям. Напри мер, если один процесс получил исключительную блокировку некоторой таблицы, никакой другой процесс не сможет изме нять строки в этой таблице. Основные типы блокировки при ведены в таблице Использовать блокировки следует в зависимости от логи ки приложения. Например, программа пакетного обновления, которая обращается к каждой строке таблицы, в процессе ра боты будет блокировать таблицу по частям. Для более быст рого выполнения программе следует явно заблокировать всю таблицу с помощью команды LOCK TABLE, выполнить все необходимые обновления, а затем разблокировать ее. Блоки ровка всей таблицы имеет два преимущества:

— устраняются затраты, связанные с блокировкой строк;

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

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

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

TABLE IN { } MODE Таблица Основные типы блокировок ресурсов Oracle Тип блокировки Содержательный смысл Exclusive (X) Исключительные блокировки разре (исключительная) шают выполнять запросы для кированных объектов, но запреща ют любые другие действия.

Share (S) Разделяемые блокировки разрешают (разделяемая) запросы, но запрещают изменения в таблицах.

Row Share (RS) Блокировка типа разделения строк (разделение строк) разрешает параллельный доступ к целостности в Oracle таблице. Этот запрещает другим пользователям блокировать таблицу в исключительном режиме.

Row Exclusive Исключительные для строк блоки (исключительная ровки аналогичны блокировкам ти для строк) па разделения строк, но, кроме того, запрещают блокировку в разделяе мом режиме. Эта блокировка при меняется при изменении, удалении и вставке строк.

Share Row Блокировки типа исключительного Exclusive (SRX) разделения строк используются для (исключительное просмотра всей таблицы для выбо разделение строк) рочных изменений и позволяют другим пользователям просматри вать строки в этой таблице, но не позволяют блокировать таблицу с разделяемом типом или с типом для обновления строк.

Примеры блокировки данных для изменения в условиях многопользовательского доступа приведены в части раздела "PL/SQL — процедурное расширение языка SQL".

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

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

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

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

— ограничение на определенность значения атрибута (NOT NULL);

— ограничение на уникальность значения атрибутов (UNIQUE);

— ограничение — первичный ключ;

— ограничение внешний ключ;

— ограничение целостности, задаваемое предикатом.

Ограничения связываются с конкретной таблицей либо в момент создания, либо создаются оператором ALTER TABLE. Ограничения целостности могут быть поименованы с помощью ключевого слова CONSTRAINT. Именование огра ничений обычно используется для локализации нарушенного ограничения в большой системе. Система всегда присваивает некоторое имя каждому ограничению целостности, но реко мендуется явно назначать унифицированные имена ограниче ниям, скажем, первичные ключи именовать Пример имени, сформированного по умолчанию, представ лен в листинге 201 (U1.SYS_C005873). Пример более Средства обеспечения целостности данных в Oracle ленного именования ограничений целостности приведен в листинге Наличие ограничения NOT NULL для некоторого атрибу та приводит к автоматическому запрещению изменений или вставок строк, содержащих неопределенные значения этого атрибута. В листинге 200 представлен пример ограничения на определенность значения атрибута:

SQL> CREATE TABLE NOT NULL, 2 At Table S,QL> INSERT INTO Tabl VALUES ;

I row created.

SQL> INSERT INTO Tabl INSERT INTO Tabl 1) * ERROR at line 1:

cannot insert NULL into Листинг 200. Пример ограничения на определенность _ значения атрибута _ Если множество атрибутов (или атрибут) описаны, как уникальные, сервер автоматически запретит выполнение опе рации по вставке строки, содержащей значение атрибутов, совпадающие с ранее введенными.

Для создания ограничений целостности UNIQUE и PRIMARY KEY требуется, чтобы владелец таблицы имел ли бо квоту для табличного пространства, в котором должен быть создан ассоциированный индекс, либо системную при вилегию UNLIMITED TABLESPACE.

В листинге 201 представлен пример ограничения на уни кальность значения атрибута.

SQL> CREATE TABLE 2 INTEGER Table created.

INSERT INTO 1 row created.

INSERT INTO Tabl INSERT INTO Tabl 1) * ERROR at line unique constraint violated Листинг Пример проверки ограничения на уни кальность значения атрибута Ограничение — ключ описывает совокуп ность атрибутов или атрибут, который для данной таблицы выбран в качестве первичного ключа. Отметим, что в таблице быть только один первичный ключ. Первичный ключ может состоять как из одного так и из нескольких, т.е. быть составным. Для первичный ключ может со держать не более столбцов.

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

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

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

Рассмотрим пример автоматической проверки ограниче ний, связанных с выбором атрибута в качестве первичного ключа. Попытка создать еще один первичный ключ отверга Средства обеспечения в Oracle системой. Попытка изменить значение атрибута At2 так, чтобы значения первичных ключей совпадали, также отверга ется системой. Протокол взаимодействия с системой пред ставлен в листинге SQL> CREATE TABLE (Atl 2 INTEGER, 3 CONSTRAINT pk_tabl PRIMARY KEY Table created.

SQL> TABLE 2 ADD PRIMARY KEY ADD KEY (Atl) ERROR line table can have only primary key SQL> INSERT INTO Tabl VALUES 1 row created.

INSERT INTO Tabl 1 row created.

SQL> UPDATE Tabl SET At2=2 WHERE UPDATE Tabl SET At2=2 WHERE At2=l * ERROR at line constraint (U1.PK_TAB1) vio lated Листинг 202. Пример связанного с вы бором первичного ключа — внешний ключ связывает значения атрибутов двух таблиц: базовой TABLE) и про изводной (CHILD TABLE). В частном случае одна таблица может быть и базовой и производной одновременно. Ограни чения, определяемые внешними ключами, обычно называют поддержкой ссылочной целостности.

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

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

Рассмотрим пример. Определим таблицу у которой значение атрибута At4 является ключом по отноше нию к первичному ключу таблицы Ввод данных со зна чением внешнего совпадающим с одним из значений первичного ключа таблицы выполняется успешно. По пытка ввести строку в таблицу со значением атрибута At4, которому не соответствует ни одно значение первичного ключа таблицы приводит к сообщению об ошибке. Про токол взаимодействия с системой представлен в листинге SQL> CREATE TABLE ТаЫ NOT NULL, 2 INTEGER, 3 CONSTRAINT pk_tabl PRIMARY Table created.

CREATE TABLE Tab2 (At 2 CONSTRAINT fk_tab2 REFERENCES Table created.

INSERT INTO Tab INSERT INTO Tab2 10) * ERROR at line 1:

обеспечения данных в Oracle integrity constraint vio lated - parent key not found Листинг 203. Пример проверки ограничения — внеш него ключа Ограничение ссылочной целостности не только опреде ляет допустимые значения во ключе производной таблицы, но и действие при операции с первичным ключом базовой таблицы.

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

SQL> DROP TABLE Table dropped.

SQL> CREATE TABLE Tab2 (At 2 CONSTRAINT fk_tab 3 REFERENCES ON DELETE Table created.

SQL> 1 row created.

INSERT INTO Tab2 1);

1 row created.

SQL> SELECT * FROM АТЗ АТ В DELETE FROM WHERE 1 row deleted.

SELECT * FROM no rows selected Листинг Пример использования ограничения внешнего ключа с опцией каскадного удаления Если по внешнему ключу не определены никакие допол нительные ограничения, то любое количество строк в подчи ненной таблице может ссылаться на одно и же значение первичного ключа. В этом случае устанавливается отношение "один ко многим". Когда по внешнему ключу определено ог UNIQUE, лишь одна строка в подчиненной табли це может ссылаться на данное значение первичного ключа.

Таким образом устанавливается связь "один к одному" между внешним ключами. Для удаления таблицы, на строки которой ссылаются строки другой таблицы, требуется указание конструкции CASCADE CONSTRAINTS. Пример удаления таблицы, для которой существует зависимая представлен в листинге DROP TABLE tabl;

DROP TABLE tabl * ERROR at line 1:

ORA-02449: unique/primary keys in table refer enced by foreign keys DROP tabl CASCADE CONSTRAINTS;

Table dropped.

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

Ограничение CHECK имеет следующие ограничения:

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

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

SQL> ALTER TABLE ТаЫ 2 ADD CONSTRAINT Atl_check CHECK Table -altered.

INSERT INTO VALUES INSERT INTO VALUES 1) * line check constraint vio lated SQL> INSERT INTO Tabl 1);

1 row created.

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

SQL> CREATE TABLE UNIQUE 2 INITIALLY DEFERRED Table created.

SQL> INSERT INTO Tabl 1 row created.

SQL> INTO Tabl 1 row created.

SQL> COMMIT;

COMMIT * ERROR at line 1:

transaction rolled back unique constraint Листинг Пример выполнения отложенной ки ограничений целостности.

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

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

Средства обеспечения целостности данных в Oracle CONSTRAINT DEFERRABLE] [INITIALLY ] По умолчанию ограничения целостности создаются не откладываемыми. Для создания откладываемого ограничения при его объявлении надо использовать ключевое слово DEFERRABLE. Если транзакция начинается с команды SET CONSTRAINTS, то для откладываемого ограничения Oracle использует стандартный алгоритм проверки. При этом проверяются на соответствие ограничению в конце каждой команды DML. Если при определении ограничения были использованы ключевые слова INITIALLY DEFERRED, данные проверяются на соответствие ограничениям при фик сации транзакции.

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

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

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

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

загрузки данных предназначена утилита Oracle SQL*Loader. SQL*Loader способен загружать данные самых различных форматов. Кроме того, загрузкой записи можно логически объединять и обрабатывать средствами язы ка SQL.

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

Например, В качестве параметров здесь указан пароль и имя пользо вателя, который будет производить загрузку, управляющий файл и файл протокола. Для настройки параметров загрузки предназначен управляющий файл (control file). В нем указы ваются символьный набор, тип и способ размещения данных, таблицы, в которые будет осуществляться загрузка и т. п. Па раметры, которые обычно передаются SQL*Loader в команд ной строке, также могут быть указаны в управляющем файле.

Форматы данных, используемые при загрузке, могут быть самыми различными — файлы с записями, dbf файлы, загружаемые как LOB и т. п. Наиболее часто используются следующие форматы: текстовые файлы с запи сями переменной длины и текстовые файлы с записями фик сированной длины.

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

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

Пусть таблица создана следующим выражением:

CREATE TABLE (Atl NUMBER, NUMBER, Требуется загрузить в нее данные из файла.dat, который имеет такое содержимое:

Пример управляющего файла для загрузки записей пере менной длины в таблицу представлен в листинге LOAD DATA CHARACTERSET INTO TABLE Tabl TRUNCATE FIELDS TERMINATED BY TRAILING NULLCOLS • (Atl INTEGER EXTERNAL, SEQUENCE Управляющий файл для данных переменной длины После загрузки таблице ТаЫ находятся следующие данные:

* FROM AT2 AT 1 1 Александр 2 2 Михаил 3 3 Мария 4 4 Ирина Листинг 209. Таблица после загрузки Управляющий файл начинается с конструкции LOAD DATA. Параметр определяет файл, в котором разме щаются данные. Если данные находятся нескольких файлах, требуется последовательность параметров INFILE.

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

Параметр INTO TABLE определяет в которую будут загружены данные. Таблица должна быть создана зара нее. После названия таблицы указывается способ вставки в нее записей: APPEND (записи добавляются в таблицу, кото рая, возможно, уже содержит данные), INSERT (перед загруз кой таблица не должна содержать записей), REPLACE (перед загрузкой все записи в таблице удаляются с использованием команды DELETE), TRUNCATE (перед загрузкой все записи в таблице удаляются с использованием команды TRUNCATE).

Далее в управляющем файле следует перечисление атри бутов записей, накладываются на них логические условия, приводятся ссылки на вложенные данные, специфицируется использование встроенного генератора последовательностей, И т. д. С полным списком параметров SQL*Loader можно оз накомиться в руководстве Oracle Utilities, Release 8.

В ходе загрузки SQL*Loader ведет протокол. Его распо ложение специфицируется параметром командной строки.

Средства целостности данных в Oracle Протокол загрузки из предыдущего примера приведен в лис тинге Release - Production Feb 14 13:54:29 2002 (c) Copyright 1998 Oracle Corporation. All rights reserved.

Control. File:

Character Set specified for all in put.

Data File:

Bad File:

Discard File: none specified (Allow all dis Number to load: ALL Number to skip: Errors allowed: Bind array: 64 rows, maximum of bytes none specified Path Conventional Table TAB1, loaded from every logical Insert option in effect for this TRUNCATE TRAILING NULLCOLS option in effect Column Position Len Datatype, FIRST * CHARACTER 2 SEQUENCE (MAX, 1) AT3 NEXT * CHARACTER Table 4 Rows successfully 0 Rows not loaded due to data 0 Rows not loaded because all WHEN clauses were 0 Rows not loaded because all fields were null.

Space allocated for bind array: Space allocated for memory besides bind array:

Total logical records skipped: Total logical records read: Total logical records rejected: Total logical records discarded: Run began on 13:54:29 Run ended on Thu Feb 14 13:54:32 Elapsed time was: 00:00:03. CPU time was: 00:00:00. Листинг 210. Протокол загрузки данных При загрузке SQL*Loader производит проверку данных- в две стадии. Сначала проверяется соответствие форматов и типов данных в файле форматам и типам данных, указанным в управляющем файле. Если какая-либо запись не удовлетво ряет этому условию, то она загрузки и поме щается в файл не прошедших проверку записей (Bad File). В противном случае производится попытка вставки данных в таблицы базы данных. При этом производится проверка огра ничений целостности. Если данные не соответствуют им, то они также отстраняются от загрузки и помещаются файл плохих записей. При достижении специфицированного коли чества ошибок загрузка прекращается. Число допустимых ошибок регулируется параметром ERRORS (по умолчанию он равен 50). В самом управляющем файле тоже могут быть на ложены условия на значения атрибутов записей. Если записи не удовлетворяют им, то они помещаются в файл некоррект ных данных (Discard File). Форматы обоих файлов идентичны формату исходных файлов данных. Поэтому после отгрузки их можно отредактировать и загрузить повторно.

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

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

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

данных Одна из важных задач, решаемых администратором баз данных, — обеспечение восстановления базы после аппарат ного или программного сбоя. Решение задачи восстановления данных может быть выполнено различными способами. В качестве необходимой основы для выполнения операции вос становления должна быть некоторая логическая копия базы данных.

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

Для реализации данных предназначены утилиты Oracle IMPORT и EXPORT. Результат экспорта представляет собой совокупность файлов в бинарном форма те, поддерживаемом Oracle. По умолчанию создается файл с именем expdat.dmp.

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

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

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

— FULL — экспортируются все объекты, структуры и данные в пределах базы данных. При экспорте данного вида экспортируются и табличные При этом необ ходимо, чтобы пользователь обладал ролью По умолчанию этой обладает пользователь SYSTEM;

Средства обеспечения данных в Oracle — OWNER — экспортируются только объекты, принад лежащие указанному пользователю (объекты его схемы);

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

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

Export: Release - Production on Feb 14 15:21:54 (c) Copyright 1998 Oracle Corporation. All rights reserved.

Connected to: Enterprise Edition Release 8.0.5.0. 15:

With the Objects option PL/SQL Release - Production Enter array buffer size: 4096 > Export file: > (l)E(ntire database), (2)U(sers), or (2)U > Т Export table data yes > yes Compress extents yes > yes.

Export done in character set and NCHAR character set About to export specified tables via Conventional Path or to be exported: (RE TURN to > TAB '.. exporting table TAB1 4 rows exported or to be exported: (RE TURN to quit) > Export terminated successfully without warnings.

Листинг Использование утилиты экспорта в ин терактивном • Для автоматического режима параметры экспорта специ фицируются в специальном файле, название которого переда ется утилите EXPORT в командной строке.

Рассмотрим структуру файла параметров экспорта с базо вым набором параметров. Полный список параметров файлов утилиты EXPORT, а также дополнительная информация для пользователя представлены в руководстве Oracle Utilities, Release 8.

Параметр | CUMULATIVE | INCREMENTAL) — указывает тип экспорта (инкременталь ный, кумулятивный или полный). При полном экспорте экс портируется вся база данных. При — толь ко те таблицы базы данных, которые изменялись с момента последнего инкрементального экспорта. В том случае, если такой экспорт не проводился, экспортируются те таблицы, которые изменились, начиная с последнего кумулятивного экспорта. При кумулятивном экспорте обрабатываются все таблицы, изменившиеся с момента последнего полного экс порта. С помощью гибкой стратегии создания файлов экспор та различных типов достигается возможность гарантирован ного восстановления базы данных на некоторый момент вре мени и экономия пространства для хранения копий.

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

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

Параметр INDEXES определяет, создавать ли ин дексы при последующем импорте. Следует отметить, что ин которые автоматически создаются Oracle, например индексы, ассоциированные с первичными ключами, экспор тируются независимо от значения этого параметра.

Параметр STATISTIC определяет, собирать ли ста тистику для оптимизатора после импорта.

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

Параметр CONSISTENT — экспортировать ли дан ные на момент экспорта. В противном случае в резервной ко пии будут данные, которые изменялись в течение экспорта.

По умолчанию устанавливается N.

Параметр требуется для подключения к базе данных. В целях безопасности рекомендуется передавать его в командной строке, а не указывать в файле За дается как Параметр ROWS — будут ли экспортироваться данные таблиц и объектов. В противном случае будут экспор тированы только определения объектов.

Параметр в который осуществляется экспорт.

Параметр — файл, из которого бу дут считываться параметры.

Параметр GRANTS — будут ли экспортироваться привилегии для объектов.

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

TABLES=Tabl INDEXES=N Файл параметров экспорта Протокол этой операции в листинге 213.

Connected to: Enterprise Edition Release Production.

With the Objects option PL/SQL Release 8.0.5.1.0 - Production Export done in character set set Note: indexes on tables will not be exported About to export specified tables via Conventional Path.. exporting table TAB1 4 rows exported Export terminated successfully without warnings.

Листинг 213. Протокол экспорта Для восстановления базы данных использованием ре зервных копий, созданных утилитой EXPORT, служит утили IMPORT. Во многом порядок работы с ней аналогичен ра боте с EXPORT, но вместе с тем имеется ряд особенностей.

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

Средства целостности данных в Oracle Аналогично EXPORT, утилита IMPORT поддерживает три режима экспортирования (в зависимости от заданных па раметров). Виды операции для экспорта и импорта, естест венно, совпадают. Существенное различие состоит лишь в том, что для импорта вида FULL требуется наличие роли Рассмотрим синтаксис команды разгрузки данных с базо вым набором параметров. Полный список параметров файлов утилиты IMPORT, а также дополнительная информация пред ставлены в руководстве Oracle Utilities, Release 8.

Параметр ANALYZE — проводить ли для импор тируемых таблиц сбор статистики для оптимизатора.

Параметр в котором находятся импор тируемые объекты базы данных.

Параметр INDEXES — создаются ли индексы импортируемых таблиц.

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

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

Параметр TOUSER — пользователь, в схему которого будет произведен импорт.

Параметр FROMUSER — импортируются те объекты, владельцем которых является этот пользователь.

Параметр LOG определяет нахождение файла, в который будет вывод протокола экспорта.

Виды импорта устанавливаются параметрами FROMUSER, TABLES, FULL. В режиме владельцев импор тируются таблицы, владельцем которых является указанный Раадел В режиме таблиц >уются указанные таблицы. По умолчанию данные импортируются в схему вы полняющего импорт пользователя.

Пример файла параметров импорта представлен в лис тинге INDEXES=n 214. Файл параметров импорта процессе импорта данных все действия производятся в определенной последовательности. Сначала создаются таб лицы, потом в них загружаются данные, затем создаются или перестраиваются индексы. Ограничения целостности для всех таблиц включаются в последнюю очередь.

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

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

Как правило, используется комплексный подход. Под понимается оптимизация всех звеньев системы — сер верной, клиентской и сетевой части. Существует целый способов настройки производительности: настройка рабочих станций клиентов, сетевого транспорта, оптимизация клиент ских приложений, оптимизация серверного PL/SQL-кода и SQL-выражений. Для настройки клиентских компьютеров и сети требуется обновление аппаратной части и системного программного обеспечения. Для работы клиент ских приложений необходимо переработать их исходный код, как правило, написанный на языке высо кого уровня, или применить специальные средства оптимиза ции программ для конкретной аппаратной конфигурации. Та 14. № 1628.

• - -..

кие средства практически всех современных интегри рованных средах разработки.

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

Далее будут рассмотрены вопросы оптимизации взаимо действия с сервером базы данных с помощью SQL выражений. Объясняется это тем, что, как показывает опыт, это наиболее "узкое" место. По разным оценкам, общая про изводительность системы на 15-20% зависит от настройки базы данных, а остальные приходятся на приложе ние. Поэтому, прежде всего, требуется настроить приложение в процессе его создания.

Оптимизатор В разделе "Архитектура распределенных систем обработ ки данных" рассматривалась последовательность ния оператора SQL архитектуру сервера Oracle. После этапа синтаксического анализа на основе информации из сло варя базы данных для разобранного оператора создается план его выполнения. Эту работу выполняет оптимизатор.

Оптимизатор представляет собой программный продукт, который является важнейшей частью сервера Oracle и пред назначен для оптимизации — поиска наиболее эффективного способа доступа к данным. Оптимизатор должен выбрать та кую последовательность действий, которая обеспечит самый эффективный путь доступа к данным, и сформировать план выполнения запроса, основанный на найденных методах дос тупа. Под методом доступа (access path) подразумевается вариант алгоритма а под планом выполнения повышения (execution plan) — последовательность выполняемых вий, которые обеспечивают выбранные методы доступа. Су ществует два основных вида оптимизаторов:

Оптимизатор по правилам (rule-based) — оптимизатор, основанный на анализе жестко заданных Этот опти мизатор выбирает методы доступа на основе предположения о статичности базы данных и в соответствии с заданной сис темой правил выбора методов доступа.

Оптимизатор по стоимости (cost-based optimizer) — оп тимизатор, основанный анализе накладных затрат систе мы. Для этого оптимизатора выбор метода доступа основан на хранимой внутренней статистике. Под статистикой пони маются точные или аппроксимированные сведения о распре делении значений данных в таблицах. Сервер Oracle может собирать статистику двумя способами: путем оценки, осно ванной на произвольной выборке данных, и путем точных вычислений.

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

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

SELECT /*+ At2,At 2 FROM WHERE Листинг 215. Запрос с подсказкой для оптимизатора Подсказка является частью следующего сразу же после начала оператора SQL (ключевых слов 14* UPDATE, SELECT), и обозначается символом после конструкции, начинающей комментарий. В предыду примере подсказка начинается с символов за кото рыми следуют символы пробел и заканчивается симво лами Пример другой записи подсказки приведен в тинге 216.

SQL> SELECT index 2 At3 FROM 216. Другой способ записи подсказки Оптимизатор воспринимает только один набор подсказок, оформленный как комментарий, для данного SQL-выражения или блока выражений. Например, для составного со стоящего из двух подзапросов, объединенных оператором UNION, подсказка в первом выражении будет при формировании плана только для него и не будет учиты ваться для второго. Подсказки с неверным синтаксисом игно начиная с места ошибки.

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

повышения Методы доступа Ранг Метод 1 Одна строка по ее идентификатору (по ROWID) 2 Одна строка по объединению кластеров 3 Одна строка по кластера с уникальным или первичным ключом 4 Одна строка по уникальному или 5 Объединение кластеров 6 Ключ 7 Ключ индексированного кластера 8 Составной индекс 9 Индекс на основе одного столбца 10 Ограниченный диапазон поиска по индексированным столбцам И Неограниченный диапазон поиска по индексированным столбцам 12 Соединение через сортировку слиянием 13 Поиск максимального или минимального значения по индексированным столбцам 14 Упорядочение по индексированным столбцам 15 Полное сканирование таблицы Таким образом, если оптимизатор основан на анализе правил, то он будет выбирать из всех доступных для данного запроса планов выполнения наиболее эффективный, опираясь на возможные методы доступа и ранги этих С мощью некоторых изменений записи выражений SQL можно способствовать более удачному выбору метода доступа. При мер в листинге содержит тот же запрос, что и в предыду щем примере, но с измененным оператора критерием отбора данных.

SELECT index 2 FROM Tabl WHERE Листинг 217. с указанием неограниченного диапазона поиска При использовании стратегии, основанной анализе правил, и наличии первичного ключа — столбца для вы полнения этого запроса будет применен метод доступа (неограниченный диапазон поиска индексированным столбцам).

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

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

SELECT FROM Tabl 2 WHERE AND Листинг 218. Использование ограниченного диапазо на поиска указанием гипотетического мак симума Методы повышения Анализ запросов с повышения скорости их выполнения Изменение SQL-выражений на основе знаний о данных, индексах, связях таблиц для повышения эффективности их выполнения, называется коррекцией запросов (query rewriting). Изменение предложений SQL отличается от напи сания новых предложений. Для того чтобы эффективно пере писывать запросы, необходимо в течение некоторого времени знания о системе. Сюда относятся сведения о том, какие предложения SQL нуждаются в переписывании в связи с их частым использованием или использованием ими значи тельных ресурсов, какие данные ими обрабатываются, каковы характеристики и распределение этих данных, какие логиче ские условия в выражениях можно убрать или трансформиро вать в связи с логикой функционирования системы. При ре шении задач оптимизации проблемных запросов необходимо следовать следующим рекомендациям:

Во-первых, при необходимости доступа к значительной части строк таблицы полное сканирование (full scan) является более эффективным, чем использование ин дексов. Граница применения данных методов доступа в об щем случае составляет 5-10% записей таблицы, к которым обращается запрос. Дело в том, что для сканирования индекса и извлечения строки требуются, по крайней мере, две опера ции чтения для каждой строки (одна — для чтения индекса, другая для чтения данных из таблицы). А при полном скани ровании таблицы для извлечения строки требуется только одна операция чтения. При доступе к большому количеству строк становится очевидной неэффективность использования индекса по сравнению с полным сканированием таблицы, при котором строки считываются непосредственно из таблицы.

Раздел Для небольших таблиц полное сканирование практически всегда оказывается эффективнее использования индекса.

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

В-третьих, при использовании различных видов подза просов на основе знаний о данных следует учитывать особен ности вычисления специальных предикатов и применения операторов теоретико-множественных операций. Например, оператор MINUS может выполняться гораздо быстрее, чем запросы с WHERE NOT IN (SELECT) или WHERE NOT EXISTS.

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

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

Для указания режима оптимизации на уровне сессии следует Методы производительности использовать выражение следующего вида: ALTER SESSION SET Заданный таким образом режим оптимизации будет ис пользоваться только для данной сессии, при начале другой его требуется указать вновь. В качестве режима оптимизатора может быть задано одно из следующих значений:

"CHOOSE" указании этого значения будет выбра на стоимостная оптимизация, основанная на анализе затрат. В противном случае будет использована оптимизация, основан ная на анализе правил.

"RULE" — при указании этого значения будет использо вана оптимизация, основанная на анализе правил. Следует отметить, что при указании только этого значения (или соот ветствующей подсказки) будет использован оптимизатор по правилам.

— это значение используется для мини мизации времени отклика, то есть для сведения минимуму временного интервала между началом выполнения запроса и появлением результатов на экране. Это значение следует ис пользовать в системах, где критичным является время реак ции. Оптимизатор игнорирует эту подсказку для предложе ний DELETE и UPDATE, а также в тех предложениях SELECT, которые содержат хотя бы одну конструкцию вида UNION, INTERSECT, MINUS, UNION ALL, GROUP BY, UPDATE, DISTINCT или групповые функции. Как уже отме чалось ранее, при вычислении этих выражений неявно произ водится и будет извлечен весь набор данных.

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

слияния. Операция сортировки-слияния быстрее возвратит весь результат запроса, тогда как операция вложенных цик лов может быстрее возвратить первую строку. Поэтому, если целью является лучшая пропускная оптимизатор скорее выберет соединение через Если в запросе указана подсказка или FIRST_ROWS, но словарь данных не статистики для таблиц, перечисленных во фразе FROM, то оптимизатор воспользуется сведениями о параметрах этих таб лиц, чтобы статистику и на этой основе выбрать план исполнения. Для задания режима оптимизации на уровне вы ражения следует использовать ключевые слова методов дос тупов, которые содержит таблица 33, либо использовать одно из приведенных выше значений (CHOOSE, RULE, FIRST_ROWS, ALL_ROWS). При использовании в выражении любой подсказки, отличной от RULE, осуществ ляется автоматический выбор оптимизатора по Таблица 33. Подсказки, специфицирующие метод дос тупа Подсказка Описание ROWID Использование идентификатора.

CLUSTER Сканирование ключа кластера.

HASH Сканирование хэш-индекса.

INDEX Сканирование индекса.

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

Сканирование индекса в порядке убыва ния.

INDEX FFS Быстрое полное сканирование индекса.

AND_EQUAL Использование нескольких индексов со слиянием результатов.

FULL Полное сканирование таблицы.

Методы Обзор индексов Oracle Понятие индекса было введено при изложении назначе ния основных объектов базы данных Oracle. Напомним, что индекс — это объект базы данных, предназначенный для по вышения производительности при проведении выборки дан ных. Цель использования индекса состоит в том, чтобы полу чить требуемые в запросе данные более эффективным, по сравнению с полным просмотром таблицы, способом.

сы улучшают производительность тех запросов, которые вы бирают небольшой процент строк из таблицы с помощью от носительно простых условий во фразе WHERE. Индексиро вать целесообразно лишь столбцы, обладающие высокой се лективностью.

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

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

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

— двоичные древовидные индексы — индексы (hash);

— двоичные масочные индексы (bitmap).

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

В*-Тгее-индекс содержит по одному индексному элемен ту для каждой строки таблицы, в которой имеется непустое (NOT NULL) индексное значение. В*-Тгее-индекс состоит из блоков-ветвей, которые значения индекса и адреса - или других блоков-ветвей, или блоков-листьев. Листовые блоки содержат значения индекса и идентификаторы строк (ROWID). Листовые блоки образуют дважды связанный спи сок, так что листья могут просматриваться в любом направ повышения лении (как по возрастанию, так и по убыванию значений ин дексного столбца). Oracle автоматически балансирует глуби ну дерева так, чтобы все листовые блоки были на одной и той же глубине, и поэтому для доступа к ним требовалось бы од но и то же число операций чтения. Однако сбалансированное дерево автоматически не уравновешивает распределение ключей в пределах дерева так, чтобы половина ключей нахо дилась бы на одной стороне а другая поло вина — на другой. Очевидно, что нет необходимости пере страивать дерево всякий раз, когда добавляются или удаля ются ключи. Однако если ключи добавляются или удаляются только на одной стороне то распределение индексных ключей может стать неравномерным, с изрядным числом раз реженных и даже опустошенных блоков по одну сторону де рева. В этом случае индекс рекомендуется перестроить.

На для извлечения данных по запросу может использоваться механизм быстрого полного просмот ра (fast full scan). Этот механизм дает существенные преиму если все запрошенные из конкретной таблицы дан ные могут быть получены только из индекса. При быстром полном просмотре эффективный многоблочный обычно применяемый для полных просмотров ис пользуется для прочтения всех листовых блоков число листовых блоков индекса, скорее всего, намного меньше, чем блоков данных в таблице, для выполнения запроса требуется просмотреть меньшее число блоков. Поэтому просмотр индекса совершится значительно быстрее, чем полный просмотр таблицы, хотя иногда нерав номерное распределение ключей снижает эффективность бы строго полного просмотра, поскольку требуется просмотреть большее число листовых блоков (содержащих малое или во обще нулевое число элементов). При этом следует наличие или отсутствие в таблице пустых значений, которые, как было сказано выше, в индекс заносятся. * можно использовать для поиска данных как по условиям равенства, так и по условиям неравенства.

Это единственный тип индексов, который можно вать для предикатов неравенства: LIKE, BETWEEN, Исключение представляет случай использования предиката LIKE при сравнении с шаблоном вида или Хэшированные и двоичные масоч ные индексы работают только с предикатами хранят только непустые значения так что можно построить разреженный В*-Тгее-индекс.

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

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

На любой таблице можно построить только один хэширован индекс.

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

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

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

Двоичные масочные индексы обеспечивают быстрое об ращение к данным больших таблиц, когда доступ организует ся по столбцам с низкой или средней селективностью с ис пользованием различных сочетаний условий равенства. Дво ичный масочный индекс построен в виде двоичной карты (bitmap) по значениям ключа. Это означает, что для каждой строки таблицы в двоичной карте, то есть в определенном бите некоторой последовательности байтов, поставлена 1 или О ("да" "нет") в соответствии со значением ключа кон кретной строки. Например, если столбец содержит мые значения и размерность множества возможных значений не очень большая (скажем, цвета или ученые звания и степе ни).

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

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

Двоичные масочные индексы меньше по объему, чем В* и значительно меньше по сравнению с хэширо кластерами. Двоичные масочные индексы не следу ет строить по столбцам с высокой селективностью. В этом случае использовать или Двоичные масочные индексы можно использо вать для поиска только по условиям равенства IN). Если необходим доступ по интервалу индексированных значений, то предпочтительнее использовать Отметим, что возможности по управлению индексами в значительно расширены и допускается указание не скольких десятков параметров, специфицирующих тип, свой ства индексов, способ их построения, хранения и т. п. Опера тор создания индекса использует следующий синтаксис:

Раздел CREATE [UNIQUE I BITMAP] INDEX ON } | { (, Проиллюстрируем использование оператора CREATE INDEX для создания индексов различных типов для таблицы созданной следующим выражением:

CREATE TABLE Tab2 At2 DATE, At3 NUMBER CHECK IN Протокол взаимодействия с системой представлен в лис тинге 219.

— создадим для столбцов Atl, At CREATE INDEX Tab2$Atl$At2 ON Tab Index created.

— создадим еще один для Atl, SQL> CREATE INDEX Tab2$Atl$At3 ON Tab Index created.

— создадим еще один индекс для уже индексированных столбцов SQL> CREATE INDEX 2 ON Tab CREATE INDEX Tab2$Atl$At3_2 ON Tab * ERROR at line 1:

such column list already indexed — создадим масочный индекс для столбца At SQL> CREATE BITMAP INDEX Tab2$At3 ON Index created.

219. Примеры создания различных индексов - и масочного) Методы повышения Для изменения индексов используется оператор ALTER INDEX. С его помощью можно изменить параметры хранения индексов или перестроить их после интенсивных операций по вводу, модификации и удалению данных. Пример перестрой ки индекса приведен в листинге 220.

SQL> ALTER INDEX Tabl$Atl$At2 REBUILD;

Index altered.

220. Пример изменения индекса с помощью оператора ALTER INDEX Для удаления индекса используется оператор "DROP INDEX. Оператор удаления индекса Oracle использует сле дующий синтаксис:

DROP INDEX Пример удаления индекса приведен в листинге Есте ственно, на данные в таблице удаление индекса никакого влияния не оказывает.

SQL> DROP INDEX Index dropped.

221. Пример удаления индекса оператором DROP INDEX Эффективное Почти все типичные проблемы, возникающие при на стройке производительности, связаны с неэффективным ко дированием SQL-выражений либо с неоптимальным исполь индексов.

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

SELECT FROM WHERE SELECT At2/Atl FROM WHERE Очевидно, что требуемые результаты могут быть получе ны с помощью одного сканирования:

SELECT FROM Tabl WHERE Также не следует выбирать данные из таблицы, если идентичные сведения можно вывести каким-то другим спосо бом. Следует руководствоваться следующим общим прави лом: лучше пожертвовать расходами на дополнительное вы числение, чем на дополнительные операции с устройствами хранения. Исключение составляют выражения, в которых большое число раз вызываются встроенные функции SQL (значения которых вычисляются для каждой строки больших таблиц или их соединений). В этом случае следует пересмот реть выражение и исключить лишние вызовы.

Особенно внимательно следует отнестись к использова нию операторов DISTINCT, ORDER BY и UNION, поскольку при выполнении этих операций происходит создание времен ных таблиц дополнительными накладными расходами на выполняемую сортировку. При необходимости использования Методы повышения оператора DISTINCT желательно сохранять результаты его выполнения для последующего использования. Вместо опе ратора UNION рекомендуется применять оператор UNION ALL (если это допускается логикой запроса), при использова нии которого не выполняется исключение дубликатов и, со ответственно, сортировка.

Рассмотрим использование операторов теоретико множественных операций MINUS и INTERSECT. Как было указано ранее, оператор UNION и подзапросы могут в значи тельной степени повлиять на производительность приложе поэтому вместо него использовать операторы MINUS и INTERSECT. Рассмотрим в качестве примера такой запрос:

SELECT Atl FROM WHERE Atl NOT IN Atl FROM Его можно переписать в таком виде:

SELECT Atl FROM Atl FROM Оба этих запроса фактически возвращают один результат (если результирующий набор содержит дубликатов). С по мощью просмотра статистических сведений о выполнении запросов можно обнаружить, что общее количество логиче ских операций чтения для первого варианта запроса гораздо больше, чем для второго. Использование оператора MINUS может оказаться очень эффективным.

Логические выражения используются для оценки истин ности (с результатом TRUE) или ложности (с результатом FALSE) высказываний. Использование встроенных функций Oracle для гибкой подстановки в логические выражения зна чений данных может значительно улучшить производитель ность системы. Рассмотрим в качестве примера таблицу, в которой регистрируется наступление некоторых событий.

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

SELECT FROM Tab2 WHERE < SELECT FROM Tab2 WHERE 2 BETWEEN AND SELECT FROM Tab2 WHERE > Для получения всей необходимой информации, скорее всего, придется три раза выполнить полное сканирование таблицы Этот пример хорош тем, что в нем приходится иметь дело с тремя разными интервалами, которым должно принадлежать значение столбца At2 и, на первый взгляд, не понятно, каким образом можно применить здесь двузначную логику? Прежде всего, следует выполнить логическое преоб разование. В Oracle для этой цели предусмотрена специальная функция DECODE. При внимательном ознакомлении с при веденным ниже примером ее использования можно обнару жить, что данный запрос приводит к тем же результатам, но за счет всего одного сканирования:

SELECT sum2, TO_DATE( FROM повышения Эффективность выполнения данного запроса, по сравне нию с исходными тремя, будет просто невероятной. Однако читаемость программы и удобство ее сопровождения заметно ухудшаются. Хотя, если вспомнить определения функций DECODE, SIGN и семантику операций с датами в Oracle, ло гика вычисления выражений sum2, sum3 становится прозрачной.

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

Pages:     | 1 |   ...   | 2 | 3 || 5 |



© 2011 www.dissers.ru - «Бесплатная электронная библиотека»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.