 |
Раздел 3. Последовательности
|
|
|
|
Полезными объектами схемы являются так называемые генераторы последовательностей (англ. SEQUENCE), называемые так же просто последовательностями. Эти объекты предназначены для создания последовательности чисел, которые обычно используются в качестве значений суррогатных ключей таблиц; функциональным аналогом последовательностей являются поля с автонумерацией, как в системах Microsoft SQL Server и т.д. Отличительными особенностями последовательностей является, во-первых, их независимость от какой-либо одной таблицы, и, во-вторых, их глобальность (потенциальная доступность всем пользователям).
Последовательность можно представить как некоторую псевдотаблицу с двумя полями NEXTVAL и CURRVAL. Обращение к первому полю создает новое целое число и возвращает его, а обращение ко второму полю возвращает последнее сгенерированное число. Для корректной работы с последовательностями необходимо в рамках одной транзакции сначала обратиться к полю NEXTVAL, а затем можно получать этот номер по полю CURRVAL. Обращение любого пользователя к полю NEXTVAL генерирует следующий номер, а обращение к полю CURRVAL всегда возвращает текущий.
Следует иметь в виду, что, поскольку к последовательности могут обращаться одновременно несколько пользователей, значения, которые поле NEXTVAL возвращает конкретному пользователю, могут идти не подряд.
При создании последовательности обычно указывается, помимо имени, начальное значение, шаг, и некоторые другие параметры. Ниже приведен пример создания последовательности и обращение к полям NEXTVAL и CURRVAL
CREATE SEQUENCE "Клиент_sec" MINVALUE 0
START WITH 0 INCREMENT BY 1
SELECT "Клиент_sec". NEXTVAL,
"Клиент_sec". CURRVAL FROM Dual
|
|
|
Теперь расмотрим пример типичного использования последовательности. Напомним, что в рассматриваемой схеме в таблице "Клиент" поле "Код Клиента" как раз является суррогатным ключем, это же значение переносится и в таблицу "Заказ" в поле "Клиент" для моделирования связи между записями таблицы (на поле "Клиент" наложено ограничение внешнего ключа). Приводимые ниже две команды INSERT добавляют две записи в эти таблицы; для поддерживания связи между записями используется последовательность "Клиент_sec":
INSERT INTO "Клиент" VALUES
('Ада', 'Вонг', 'ж', NULL, NULL, NULL,
'Номер', "Клиент_sec".nextval)
INSERT INTO "Заказ" VALUES ('0067', DEFAULT,
"Клиент_sec".currval, DEFAULT)
Команды ALTER SEQUENCE позволяет изменить параметры последовательности (кроме начального значение), например,
ALTER SEQUENCE "Клиент_sec" INCREMENT BY 2
Наконец, команда удаления последовательности:
DROP SEQUENCE "Клиент_sec"
Если необходимо изменить начальное значение последовательности, следует ее удалить и создать заново. Удаление последовательности, конечно же, не влияет на значения полей записей, которым значения последовательности были присвоены ранее.
Раздел 4. Синонимы
Специальным объектами базы данных являются так называемые синонимы, которые используются для упрощенного обращения к таблицам и другим объектам, созданными другими пользователями и соответственно хранящимися в их схемах. Для обращения к чужим объектам необходимо указывать полное имя объекта, состоящего из имени схемы и собственно имени объекта.
Например, при обращении к таблице clients пользователя с именем учетной записи system следует использовать запрос:
SELECT * FROM system.clients
Можно создать синоним для этой таблицы следующим образом:
CREATE PUBLIC SYNONYM clients FOR system.clients
Здесь PUBLIC используется для создания синонима, доступного для всех учетных записей системы.
|
|
|
Теперь к этой таблице можно обращаться через ее синоним:
SELECT * FROM clients
Следует иметь в виду, что многие системные таблицы и представления доступны пользователям именно через синонимы, поскольку при обращении к таким объектам не используются имена схем SYS и SYSTEM. Список существующих синонимов можно получить из представлений *_synonyms.
Раздел 5. Представления
Представление (англ. VIEW), называемое иногда так же виртуальной таблицей, является объектом базы данных, который можно использовать, наряду с таблицами, в запросах как источник данных, и, с рассматриваемыми далее ограничениями, в командах обновления. В отличие от таблицы, представление на содержит записей, и представляет собой, по существу, именованный SELECT-запрос, который “подставляется” в SQL-команды при обращении к этому представлению. Таблицы, которые используются в запросе представления, называются базовыми.
Представления могут быть полезны в следующих случаях. Прежде всего, представления используются для именования или сокрытия сложных, громоздких и часто используемых SQL-выражений. Вернемся снова к следующему запросу:
SELECT "Имя", "Фамилия", "Клиент",
"Дата регистр", "Название", "Количество", "Мера изм", "Дата выполн"
FROM "Клиент", "Товар", "Заказ", "СоставЗак"
WHERE "Клиент"."Код Клиента" = "Заказ"."Клиент" AND "Заказ"."Код заказа"="СоставЗак"."Код заказа" AND "Товар"."Код товара"="СоставЗак"."Код товара"
Этот неоднократно рассматриваемый запрос является, конечно, широко употребительным и вместе с тем очень громоздким; следует обратить так же внимание на отсутствие какой-либо селекции. На основании этого “общего” запроса целесообразно определить представление, которое далее использовать в многочисленных запросах. Ниже приведено определение такого представления.
CREATE VIEW "Все заказы" AS
SELECT "Имя", "Фамилия", "Клиент",
"Дата регистр", "Название", "Количество", "Мера изм", "Дата выполн"
FROM "Клиент", "Товар", "Заказ", "СоставЗак"
WHERE "Клиент"."Код Клиента"="Заказ"."Клиент"
|
|
|
AND "Заказ"."Код заказа"="СоставЗак"."Код заказа" AND "Товар"."Код товара"="СоставЗак"."Код товара"
Теперь это представление можно использовать в SELECT-запросах, например:
SELECT * FROM "Все заказы"
При вычислении этого запроса Oracle определит, что объект "Все заказы" является именно представлением, а не таблицей, далее найдет в системном каталоге его определение и выполнит входящий в него SELECT-запрос.
В запросах с представлениями можно, конечно же, использовать и прочие возможности команды SELECT, включая селекцию, группировку, упорядочивание, и т.д.
SELECT * FROM "Все заказы"
WHERE "Имя" = 'Лена' AND "Фамилия" = 'Шварц'
SELECT "Имя", "Фамилия", COUNT(*)
FROM "Все заказы"
GROUP BY "Имя", "Фамилия"
Другое использование представлений связано с представление данных базовых таблиц в более удобном для пользователя виде; например, с содержательными заголовками на русском языке, и т.д. Типичным примером таких представлений являются представления системного словаря типа ALL_TABLES, USER_TABLES и т.д., извлекающие данные из системной таблицы OBJ$, которая является малоподходящей для непосредственной работы. Читателю рекомендуется для визуального анализа исполнить следующий запрос:
SELECT * FROM SYS.OBJ$
Ниже приведен запрос к представлению ALL_VIEWS из системного каталога, извлекающий определение этого же представления (включая текст SELECT-запроса); читатель может оценить достоинства упрощенного обращения к системному каталогу при помощи представлений.
SELECT OWNER, VIEW_NAME, TEXT
FROM all_views
WHERE VIEW_NAME ='ALL_VIEWS'
| OWNER | VIEW_NAME | TEXT |
| SYS | ALL_VIEWS | select u.name, o.name, v.textlength, v.text, t.typetextlength, t.typetext, t.oidtextlength, t.oidtext, t.typeowner, t.typename, decode(bitand(v.property, 134217728), 134217728, (select sv.name from superobj$ h, obj$ sv where h.subobj# = o.obj# and h.superobj# = sv.obj#), null) from sys.obj$ o, sys.view$ v, sys.user$ u, sys.typed_view$ t where o.obj# = v.obj# and o.obj# = t.obj#(+) and o.owner# = u.user# and (o.owner# = userenv('SCHEMAID') or o.obj# in (select oa.obj# from sys.objauth$ oa where oa.grantee# in (select kzsrorol from x$kzsro)) or /* user has system privileges */ exists (select null from v$enabledprivs where priv_number in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */))) |
|
|
|
Длинное значение по поле text является текстом определения SELECT-запроса для представления ALL_VIEWS.
Третьим типичным использованием представлений является сокрытие каких-либо столбцов и строк базовых таблиц. Представления типа USER_TABLES из системного каталога снова оказываются типичным примером. Приведем, однако, еще один пример построения представления, скрывающего некоторые строки базовой таблицы.
Сначала создадим некоторую базовую таблицу:
CREATE TABLE "Все записи"
(username CHAR(20) DEFAULT USER
NOT NULL UNIQUE,
name CHAR(20),
birth DATE)
Обратите внимание, что поле username содержит присваиваемое по умолчанию значение – название учетной записи пользователя, доступное через системную функцию USER; прочие поля содержат ползовательскую информацию.
Теперь создадим представление со следующим определением:
CREATE OR REPLACE VIEW "Мои записи"
AS SELECT name "Имя", birth "Дата рождения"
FROM "Все записи"
WHERE username = USER
Обратите внимание, что поля username в списке извлекаемых полей в запросе не присутствует, и поэтому пользователю будет недоступно. Далее, условие селекции предоставляет доступ только к тем записям, которые были добавлены пользователем с той же учетной записью. Если пользователь не имеет доступа на чтение и обновление к таблице "Все записи" (такое ограничение доступа может быть реализовано), он не сможет через представление "Мои записи" получить доступ к чужим записям.
Обратите внимание, что по имени источника данных невозможно определить, является ли он таблицей или представление, или каким-либо другим объектом схемы:
SELECT * FROM "Мои записи"
Это представление можно так же использовать и для удаления, добавления и изменения записей, но с некоторыми ограничениями, например:
INSERT INTO "Мои записи" VALUES ('Ада Вонг', NULL)
При добавлении строки в представление, при этом, естественно, запись будет добавлена в базовую таблицу, причем поле username будет инициализирована значеним по умолчанию:
| USERNAME | NAME | BIRTH |
| CUSTOMER | Ада Вонг | - |
Здесь значение CUSTOMER – имя учетной записи.
Теперь рассмотрим вопрос о модификации данных базовых таблиц через обращение к ним через представления; пример добавления новой записи приведен выше. В целом, добавление новых записей и изменение и удаление существующих возможно, если, говоря неформально, между строками представления и строками базовых таблиц существует прямое соответствие, равно как и между полям представления и таблицы. Такое соответствие присутствует в представлении "Мои записи". Данные нельзя модифицировать, если запрос представления содержит, в том числе, выражения, вычисляемые на основании полей исходный таблицы, опцию DISTINCT, упорядочивание, группировку и вычисление агрегатных функций, операцию UNION, а так же многотабличные запросы с соединением (есть исключения). Модифицировать данные рассматриваемого выше представления "Все заказы", конечно же, нельзя.
|
|
|
Можно явным образом запретить изменение данных любого представления, используя опцию WITH READ ONLY, например:
CREATE OR REPLACE VIEW "Мои записи" AS
SELECT name "Имя", birth "Дата рождения"
FROM "Все записи"
WHERE username = USER
WITH READ ONLY
В завершении рассмотрим еще одну возможность работы с представлениями; изменим несколько определение представления "Мои записи", добавив в список полей новое поле:
CREATE OR REPLACE VIEW "Мои записи" AS
SELECT name "Имя", birth "Дата рождения",
User “Пользователь”
FROM "Все записи"
WHERE username = USER
Для этого представления будет корректно исполняться следующая команда добавления новой строки:
INSERT INTO "Мои записи"
VALUES ('Кристи', NULL, ‘Un Hombre’)
Однако эта запись, естественно, не будет содержаться в значении выборки SELECT * FROM "Мои записи", поскольку не сответствует условию селекции запроса. Чтобы устранить подобные противоречивые ситуации, следует к определению запроса добавить опцию WITH CHECK OPTION, при использовании которой происходит проверка изменений условиям запроса:
CREATE OR REPLACE VIEW "Мои записи" AS
SELECT name "Имя", birth "Дата рождения",
User “Пользователь”
FROM "Все записи"
WHERE username = USER
WITH CHECK OPTION
Команда ALTER VIEW предназначена для изменения наложенных ограничений на представление (в данном пособии не рассматриваемых); для изменения запроса необходимо представление создать заново. Удаление представления при помощи команды DROP VIEW на данные базовых таблиц не влияет.
Раздел 6. Индексы
Специальным видом объектов базы данных является индекс. Каждый индекс ассоциируется с некоторой таблицей и используется для ускорения доступа к строкам этой таблицы. Индексы, в отличие от таблиц и представлений, не используются в качестве источника данных в запросах.
Использование индексов может существенно ускорить доступ к данным (строкам таблицы), однако их использование не является обязательным и не влияет на значение запросов. Иными словами, запрос вычисляется правильно вне зависимости от того, используются индексы для ускорения доступа или нет, однако использование индексов при правильном использовании способно радикальным образом ускорить выполнение запросов и тем самым улучшить производительность приложений. Поэтому построение индексов для созданных таблиц является безусловно необходимой задачей.
Индексация таблиц является составной частью процесса проектирования баз данных и настройки производительности системы; последовательное изложение этих разделов технологии современных баз данных существенно выходят за пределы данного пособия. В этом разделе приведены наиболее основное сведения о командах для построения индексов и рекомендации по их построению.
В системе Oracle существуют различные виды индексов, к наиболее распространенными по употреблению и универсальности являются B-индексы. При создании индекса указываются индексируемая таблица и группа полей, по которым строится индекс. Если индекс построен по группе полей таблицы, он называется составным.
B-индекс с точки зрения структуры напоминает сбалансированное бинарное дерево, в листьях которых записаны значения одного или нескольких полей строки таблицы, с которой этот индекс ассоциирован, и ссылка на эту запись в таблице. Индекс этого вида наиболее эффективно применяется при поиске записи в таблице по совпадению значений группы полей, по которым индекс простроен.
Для создания индекса используется команда CREATE INDEX:
CREATE INDEX <имя индекса> ON <имя таблицы>
(<список полей таблицы или выражений>)
Приведем несколько примеров команд по созданию индексов; таблицами для индексирования являются таблицы из примера раздела 2.4.
CREATE INDEX "Клиент_ДР"
ON "Клиент" ("Дата Рождения")
CREATE INDEX "Код заказа_СКК"
ON "СоставЗак"
("Код заказа", "Код товара", "Количество")
Здесь индекс "Клиент_ДР" является простым, а "Код заказа_СКК" – составным, поскольку создается по трем полям. Отметим, что составной индекс совершенно не эквивалентен группе простых индексов, созданных по каждому полю. Кроме того, при создании составных индексов важен порядок перечисления полей; индексы, построенные по одному набору полей, но в различных сочетаниях, являются различными.
Обратите внимание на имена, присваиваемые создаваемым индексам в этом примере. Присваиваемые имена могут быть произвольными, но рекомендуется эти имена создавать по какому-либо принципу, что в дальнейшем упростит использование этих индексов. Индексы, конечно же, должны иметь различные имена в схеме.
Следует иметь в виду, что для полей и групп полей, на которые наложены ограничения PRIMARY KEY и UINQUE, индексы создаются автоматически, и, следовательно, для них создавать индексы явным образом не следует. Более того, запрещается создавать индексы по одинаковым группам полей (с учетом порядка полей в списке). Имя индекса совпадает с именем ограничения, что является еще одним аргументом присваивать ограничениям явные явным образом.
Проверить, какие индексы существуют для некоторой таблицы, можно при помощи следующего запроса, обращающегося к представлений системного каталога ALL_INDEXES и USER_INDEXES:
SELECT INDEX_NAME, UNIQUENESS
FROM user_indexes
WHERE TABLE_NAME = 'Клиент'
| INDEX_NAME | UNIQUENESS |
| Клиент_ДР | NONUNIQUE |
| Клиент_Ф_С | UNIQUE |
| SYS_C009987 | UNIQUE |
SELECT INDEX_NAME, UNIQUENESS
FROM user_indexes
WHERE TABLE_NAME = 'СоставЗак'
| INDEX_NAME | UNIQUENESS |
| Код заказа_СКК | NONUNIQUE |
| СоставЗак_КК_U | UNIQUE |
Здесь имя SYS_C009987 соответствует индексу, автоматически созданному по полю "Код Клиента", поскольку на это поле было наложено ограничение уникальности; это имя было сгенерировано системой автоматически. Поле UNIQUENESS обозначает, является ли индекс уникальными или нет; это определяется наличием ограничения уникальности на поля. Уникальные индексы более эффективны.
Проиллюстрирует ожидаемый рост производительности системы при выполнении следующего запроса:
SELECT * FROM “Клиент”
WHERE "Дом Телефон" = ‘4953453214’
Если таблицы "Дом Телефон" содержит 1000000 записей, то поиск записей, соответствующих этому условию селекции, потребует просмотр всех записей таблицы. Этот способ вычисления запроса называется полным перебором (FULL ACCESS), априорная оценка стоимости вычисления данного запроса будет равной 1000000 в общем случае, или 1000000/2 = 500000 в случае, если на поле "Дом Телефон" наложено ограничений уникальности.
Если по этому полю создан индекс (и он действительно применяется системой при вычислении запроса), то вместо просмотра записей таблицы происходит сканирование индекса от вершины к листу, в котором должно находиться значение ‘4953453214’, если оно действительно существует в индексе. Если такой узел в индексе найдет, по адресу записи в этом узле происходит обращение непосредственно к записи в таблице. Учитывая структуру индекса, скорость поиска в индексе очень высокая (логарифмическая), и априорная оценка для запроса будет пропорциональна ln 1000000 ~~ 14. Скорость в индексе будет так же выше при ограничении уникальности.
Впрочем, подобные априорные оценки лишь частично отражают реальный процесс вычисления запросов.
Способ вычисления, состоящий в сканировании индекса, называется RANGE SCAN и UNIQUE SCAN для неуникального и уникального индексов соответственно.
Поиск в индексе является более быстрым так же благодаря тому, что индекс является более компактной структурой данных в сравнении с таблицей, и занимает меньше места в памяти.
Следует иметь в виду, что индексы можно строить не только по полям таблицы, но по производным от них выражениям и функциям. В функциях можно обращаться, в том числе, к длинным полям (LOB); строить индексы непосредственно по полям длинных типов нельзя. Например:
CREATE INDEX "Товар_Ф"
ON "Товар"(LENGTH("Фото"))
При изменении данных таблицы – удалении и добавлении записей и изменении значений полей, по которым построены индексы, происходит автоматическое изменение данных в индексах. Индексы можно создавать одновременно с таблицей, когда записи еще не добавлены, или позднее. Изменение данных в таблице будет отражаться и в данных индексов.
Построение и применение индексов, помимо положительного влияния на скорость исполнения запросов, имеет и некоторые негативные аспекты. Первый из них связан с необходимостью изменения индексов при изменении строк таблицы, о чем говорилось выше, причем, учитывая сложную структуру индекса, внесении изменения в индекс является более дорогостоящей операцией, чем изменение строк. Если данные таблиц изменяются слишком активно, большая часть вычислительных ресурсов системы будет тратиться на обновление индексов. Поэтому следует минимизировать количество создаваемых индексов, в особенности, если ожидается частая модификация данных. Во-вторых, даже если индекс существует, из этого не следует, что он действительно будет использоваться при вычислении запросов, поскольку на процесс обработки запросов оказывают влияние многие дополнительные факторы.
В частности, одним из важных факторов, характеризующих применяемость индекса, является его селективность, которую можно определить как отношение записей, удовлетворяющих условию селекции, к общему количеству записей в таблице. Например, если поиск выполняется по значению уникального поля, построенный по этому полю индекс будет обладать самой высокой селективность, а селективность поиска по полю, например, "Пол" таблицы “Клиент”, будет в среднем 50%, и такой индекс, очевидно, бесполезен.
Для проверки, каким образом вычисляется SELECT-запрос и какие индексы используются, а какие – нет, следует использовать средства Oracle для просмотра плана выполнения запросов. Этот план описывает, в том числе, какие действия выполняются при вычислении конкретного запроса, используется ли полный перебор, доступ при помощи индекса и т.д. Существуют средства, визуализирующие план исполнения средствами графического интерфейса, а так же базовое средство, встроенное в Oracle SQL – команда EXPLAIN PLAN. Ниже приведена стандартный сценарий ее использования (за детальным описанием этой команды и интерпретаций ее результатов следует обращаться к документации к системе Oracle и специализированной литературе).
EXPLAIN PLAN
SET STATEMENT_ID = 'SEL1'
INTO plan_table
FOR SELECT * FROM "Клиент"
WHERE "Дом Телефон" = '4953453214'
Эта команда анализирует указанный запрос и сохраняет в таблице plan_table (эта таблица автоматически создается в схеме каждой схеме) информацию и плане исполнения, который далее можно вывести при помощи специализированного SELECT-запроса приведенного вида:
SELECT operation, options, object_name, position
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id AND statement_id = 'SEL1'
ORDER BY position desc;
| OPERATION | OPTIONS | OBJECT_NAME | POSITION |
| SELECT STATEMENT | - | - | |
| TABLE ACCESS | FULL | Клиент |
После создания следующего индекса при исполнении рассматриваемого запроса полный перебор будет заменен операциями сканирования индекса по диапазону и переходу к записи таблицы по адресу записи (ROWID):
CREATE INDEX "Клиент_ДТ"
ON "Клиент" ("Дом Телефон")
| OPERATION | OPTIONS | OBJECT_NAME | POSITION |
| SELECT STATEMENT | - | - | |
| INDEX | RANGE SCAN | Клиент_ДТ | |
| TABLE ACCESS | BY INDEX ROWID | Клиент |
Если удалить индекс "Клиент_ДТ" и создать уникальный индекс, план исполнения так же изменится. Диапазонный поиск в индексе будет заменен на уникальный, который является более эффективным. Отметим, что уникальный индекс можно создавать и по неуникальным полям, хотя более правильным является наложение ограничения уникальности на исходную таблицу.
DROP INDEX "Клиент_ДТ"
CREATE UNIQUE INDEX "Клиент_ДТ"
ON "Клиент" ("Дом Телефон")
| OPERATION | OPTIONS | OBJECT_NAME | POSITION |
| SELECT STATEMENT | - | - | |
| INDEX | UNIQUE SCAN | Клиент_ДТ | |
| TABLE ACCESS | BY INDEX ROWID | Клиент |
В целом, при построении индексов необходимо анализировать запросы, которые выполняются к таблице, и свойства хранимых в ней данных. Сформулируем некоторые рекомендации по построению индексов.
Индексы целесообразно строить в следующих случаях:
1. Поиск по равенству при высокой селективности поиска
2. Поля, используемые в эквисоединениях
3. Поля, сравниваемые со значениями подзапросов.
Индексы так же могут быть полезны при обработке запросов с упорядочиванием и группировкой.
Индексы не следует создавать для следующих запросов:
1. При поиске на неравенство, больше, меньше и диапазону.
2. При низкой селективности поиска по равенству (например, при априорной оценке более 10%).
3. При использовании выражений с полями (в этом случае при необходимости следует построить индекс по этому выражению).
Для удаления индекса используется команда DROP INDEX. Удаление индексов не влияет на данные исходной таблицы.
|
|
|
