- •Автор: Заставной д.А., преподаватель кафедры информатики и информационных таможенных технологий Ростовского филиала рта, к.Т.Н.
- •Оглавление
- •Раздел 1. Объекты базы данных
- •Раздел 2. Создание таблиц
- •2.1. Общие сведения
- •2.2. Типы значений
- •2.3. Значение по умолчанию
- •2.4. Ограничения
- •2.5. Создание таблиц при помощи команды select
- •2.6. Изменение определения таблицы
- •Alter table "Все заказы(кп)"
- •Alter table "Клиент(кп)" add constraint "Клиент(кп)_ф_с"
- •Alter table "Клиент(кп)"
- •Раздел 3. Последовательности
- •Раздел 4. Синонимы
- •Раздел 5. Представления
- •Раздел 6. Индексы
- •Explain plan
- •Раздел 7. Системный каталог
- •Заключение
- •Библиографический список
- •Указатели
- •Раздел 1. Объекты базы данных 5
- •Раздел 2. Создание таблиц 6
- •Раздел 3. Последовательности 33
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
-
-
3
TABLE ACCESS
FULL
Клиент
1
После создания следующего индекса при исполнении рассматриваемого запроса полный перебор будет заменен операциями сканирования индекса по диапазону и переходу к записи таблицы по адресу записи (ROWID) :
CREATE INDEX "Клиент_ДТ"
ON "Клиент" ("Дом Телефон" )
-
OPERATION
OPTIONS
OBJECT_NAME
POSITION
SELECT STATEMENT
-
-
2
INDEX
RANGE SCAN
Клиент_ДТ
1
TABLE ACCESS
BY INDEX ROWID
Клиент
1
Если удалить индекс "Клиент_ДТ" и создать уникальный индекс, план исполнения так же изменится. Диапазонный поиск в индексе будет заменен на уникальный, который является более эффективным. Отметим, что уникальный индекс можно создавать и по неуникальным полям, хотя более правильным является наложение ограничения уникальности на исходную таблицу.
DROP INDEX "Клиент_ДТ"
CREATE UNIQUE INDEX "Клиент_ДТ"
ON "Клиент" ("Дом Телефон" )
-
OPERATION
OPTIONS
OBJECT_NAME
POSITION
SELECT STATEMENT
-
-
1
INDEX
UNIQUE SCAN
Клиент_ДТ
1
TABLE ACCESS
BY INDEX ROWID
Клиент
1
В целом, при построении индексов необходимо анализировать запросы, которые выполняются к таблице, и свойства хранимых в ней данных. Сформулируем некоторые рекомендации по построению индексов.
Индексы целесообразно строить в следующих случаях:
Поиск по равенству при высокой селективности поиска
Поля, используемые в эквисоединениях
Поля, сравниваемые со значениями подзапросов.
Индексы так же могут быть полезны при обработке запросов с упорядочиванием и группировкой.
Индексы не следует создавать для следующих запросов:
При поиске на неравенство, больше, меньше и диапазону.
При низкой селективности поиска по равенству (например, при априорной оценке более 10%).
При использовании выражений с полями (в этом случае при необходимости следует построить индекс по этому выражению).
Для удаления индекса используется команда DROP INDEX. Удаление индексов не влияет на данные исходной таблицы.
Раздел 7. Системный каталог
Системным каталогом называется набор таблиц и представлений, в которых содержится полное описание существующих в базе данных объектов, а так же различные характеристики, связанные с функционированием системы. Данные, доступные через объекты системного каталога, используются как самой системой Oracle, например, при исполнении запросов и т.д., так и пользователями, если необходимо получить информацию, например, об существующих таблицах и их структурах. В частности, команда DESCR при исполнении обращается соответствующим представлениями системного каталога; аналогичного результата можно добиться исполнением SELECT-запроса к этим представлениям.
Объекты системного каталога можно разбить на три группы.
1. Системные таблицы, например, SYS.OBJ$. Эти таблицы, называемые так же базовыми, недоступны для непосредственного обращения обычных пользователей и предназначены для непосредственного доступа исключительно самой системой. Как следует из названия, эти таблицы находятся в схеме системной учетной записи SYS.
2. Системные представления (Static Data Dictionary Views). Эти представления доступны для каждой учетной записи, однако их содержание для различных учетных записей в общем случае будет различным. С каждым видом объектов базы данных ассоциировано тройка системных представлений; например, сведения о таблицах можно получить, обращаясь к представлениям USER_TABLES, ALL_TABLES и DBA_TABLES. Префиксы USER, ALL и DBA имеют следующий смысл. USER_-представление описывает объекты, владельцем которой является данная учетная запись (т.е. которые созданы в данной схеме), ALL_-представление – объекты, которые доступны для данной учетной записи (свои собственные и объекты в других схемах), и, наконец, DBA_-представление описывает все объекты во всех схемах. DBA_- представления, однако, существуют только для учетных записей, обладающих административными правами.
3. Так называемые динамические таблицы и представления; их имена начинаются с символов V$ и V_$, например, V$SESSION. Эти представления содержат различные сведения и параметры, касающиеся функционирования системы.
Пользователю не следует пытаться менять данные этих таблиц и представлений и тем более их структуру.
Рассмотрим более подробно работу с системными представлениями. В Таблице 2 приведены некоторые часто используемые представления.
Название представления |
Содержание |
ALL_TABLES |
Таблицы |
ALL_TAB_COLS ALL_TAB_COLUMNS |
Поля таблиц и представлений |
ALL_CONSTRAINTS |
Ограничения, наложенные на поля таблиц |
ALL_INDEXES |
Индексы |
ALL_SEQUENCES |
Последовательности |
ALL_SYNONYMS |
Синонимы |
ALL_USERS |
Учетные записи |
ALL_VIEWS |
Представления |
Ниже приведены типичные запросы, иллюстрирующие обращения к представлениями системного каталога; значения запросов воспроизводятся фрагментарно.
1. Существующие таблицы, доступные текущему данной учетной записи.
SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS
FROM ALL_TABLES
OWNER |
TABLE_NAME |
NUM_ROWS |
BLOCKS |
SYS |
CON$ |
4565 |
23 |
SYS |
UNDO$ |
11 |
1 |
SYS |
CDEF$ |
4564 |
79 |
2. Существующие представления, владельцем которой является данная учетная запись, и из определения.
SELECT VIEW_NAME, TEXT FROM USER_VIEWS
VIEW_NAME |
TEXT |
Все заказы |
select "Имя", "Фамилия", "Клиент", "Дата регистр", "Название", "Количество", "Мера изм", "Дата выполн" from "Клиент", "Товар", "Заказ", "СоставЗак" where "Клиент"."Код Клиента" = "Заказ"."Клиент" and "Заказ"."Код заказа" = "СоставЗак"."Код заказа" and "Товар"."Код товара" = "СоставЗак"."Код товара" |
Мои записи |
select name "Имя", birth "Дата рождения" from "Все записи" where username = USER |
3. Определение полей таблицы CLIENTS, определенной в счхеме учетной записи CUSTOMER.
SELECT OWNER, TABLE_NAME "TABLE",
COLUMN_NAME "COLUMN”, DATA_TYPE, DATA_LENGTH, NULLABLE
FROM ALL_TAB_COLUMNS
WHERE owner = 'CUSTOMER'
AND TABLE_NAME = 'CLIENTS'
TABLE |
COLUMN |
DATA_TYPE |
DATA_LENGTH |
NULLABLE |
CLIENTS |
TITLE |
CHAR |
2000 |
Y |
CLIENTS |
REGISTRDATE |
DATE |
7 |
Y |
CLIENTS |
CITY |
CHAR |
1000 |
Y |
CLIENTS |
STREET |
CHAR |
1000 |
Y |
CLIENTS |
APPERTMENT |
CHAR |
200 |
Y |
Список наложенных на таблицу 'Заказ' ограничений
SELECT TABLE_NAME, CONSTRAINT_NAME,
CONSTRAINT_TYPE, STATUS from user_constraints
WHERE TABLE_NAME = 'Заказ'
TABLE_NAME |
CONSTRAINT_NAME |
CONSTRAINT_TYPE |
STATUS |
Заказ |
SYS_C0010013 |
C |
ENABLED |
Заказ |
SYS_C0010014 |
C |
ENABLED |
Заказ |
SYS_C0010015 |
C |
ENABLED |
Заказ |
Заказ_Д_C |
C |
ENABLED |
Заказ |
SYS_C0010017 |
P |
ENABLED |
Заказ |
Заказ_ДК_U |
U |
ENABLED |
Заказ |
SYS_C0010019 |
R |
ENABLED |
Читателю рекомендуется сравнить полученный список ограничений с определениям таблицы 'Заказ' в разделе 2.5.
5. Список существующих незаблокированнных учетных записей в системе в порядке создания.
SELECT USERNAME, USER_ID, PASSWORD,
ACCOUNT_STATUS AS “STATUS”, CREATED
FROM dba_users
WHERE ACCOUNT_STATUS = 'OPEN'
ORDER BY CREATED
USERNAME |
USER_ID |
PASSWORD |
STATUS |
CREATED |
SYS |
0 |
75800913E1B66343 |
OPEN |
07-FEB-06 |
SYSTEM |
5 |
970BAA5B81930A40 |
OPEN |
07-FEB-06 |
ANONYMOUS |
28 |
anonymous |
OPEN |
07-FEB-06 |
CUSTOMER |
36 |
609B4C253EA8EA39 |
OPEN |
09-JUL-08 |
NEMONOSTRUM |
37 |
EXTERNAL |
OPEN |
09-JAN-09 |