Формулы для оценки размера БД
С целью упрощения вычислений размера базы данных, в настоящем разделе мы будем проводить вычисления на примере СУБД SQLBASE. Размер базы данных может быть оценен по формуле
Перед тем как вычислить размер таблицы, необходимо вычислить размеры всех ее колонок.
Вычисление размера колонки. Вычисление размера колонки зависит от типа домена колонки. Размер колонки или столбца таблицы - это число символов, которое отводится СУБД для хранения колонки заданного типа.
Как правило, в определении таблицы задаются максимальные размеры полей указанного типа для данной предметной области. Например, предполагается, что колонка адреса компании не будет занимать более 50 символов. С другой стороны, на практике, на реальных данных средний размер колонки адреса компании может составлять 30 символов. Расчет размера базы данных целесообразно проводить исходя из среднего размера колонок таблиц.
Типичные размеры колонок заданного типа приведены в таблице 13.2 ниже.
| Character | Число символов в строке |
| Number | [(NumberOfDigits + 2)/ + 1 байт |
| Date | 5 байт |
| DateTime | 12 байт |
| Long varchar | 12 байт плюс число сраниц для хранения данных |
Размер строки таблицы определяется как сумма размеров всех ее колонок по формуле
Data _ Length =
Вычисление размера таблицы. Основываясь на значении Data_Length можно оценить размер обычной таблицы или хэш-таблицы. Формулы для выполнения такой оценки приведены в таблицах 13.3 и 13.4. Различие в методике расчета размера хэш-таблицы заключается в необходимости учитывать параметр загрузки хэш-таблицы (packing_density), который устанавливается при определении такой таблицы.
| Row_Lenght | Длина строки на физической странице включает в себя длину заголовка и размер строки таблицы, которая вычисляется по формуле Row_Lenght = 18 + (2 * число_колонок) + Data_Lenght |
| Row_Lenght_with_Stack | Длина строки с размером стека Row_Lenght_with_Stack = Row_Lenght * 100 (100 - PCTFREE) |
| Usable_Row_Page_Size | Используемая СУБД длина строки на странице. В SQLBASE длина заголовка страницы равна 86 байт Usable_Row_Page_Size = 1024 - 86 = 936 байт |
| Rows_per_Page | Число строки на страницу: Rows_per_Page = [Usable_Row_Page_Size / Row_Lenght_with_Stack] |
| Nbr_Row_Pages | Число строк на странице: Nbr_Row_Pages = [NbrOfRows / Rows_per_Page],где NbrOfRows - предполагаемое число строк в таблице |
| Nbr_Long_Pages | Число страниц, занимаемых длинными строками: Nbr_Long_Pfge = NbrOfRows * Nbr_Long_Pages_per_Long_Col, Nbr_Long_Pages_per_Long_Col - число длинных строк на страницу |
| Total_Data_Page | Число страниц данных: Total_Data_Page = Nbr_Row_pages + Nbr_Long_Pages |
table class="xml_table" cellpadding="2" cellspacing="1">
Таблица 13.4. Оценки размера хэш-таблицы
| Key_Lenght | Длина ключа равна сумме средних длин колонок, которые составляют данный ключ |
| Index_Entry_Lenght | Длина размера строки индекса: Index_Entry_Lenght = 9 + число_колонок_ключа_индекса + Key_Lenght |
| Usable_Index_Page_Size | Используемый СУБД размер страницы индекса: Usable_Index_Page_Size = (1024 - 74)* (100 - PCTFREE)/100 |
| Index_Entry_per_Page | Число входов индекса на страницу: Index_Entry_per_Page = [Usable_Index_Page_Size / Index_Entry_Lenght |
| Nbr_Index_Pages | Число страниц, занимаемых индексом Nbr_Index_Pages = [NbrOfRows / Index_Entry_per_Page], где NbrOfRows - предполагаемое число строк в таблице |
Вычисление размера заголовка представления. Для каждого представления существует фиксированная часть заголовка и переменная часть заголовка, которая зависит от его сложности. Формулы для расчета размера заголовка представления приведены в таблице 13.6.
| Fixed_Overhead | = 12 * 1024 |
| Variable_Overhead | = 150 * число_таблиц + 170 * число_колонок |
| Variable_Overhead_all_Views |  |
| Total_View_overhead_in_Page | = [(Fixed_Overhead + Variable_Overhead + Variable_Overhead_all_Views)/1024] |
Total_Fixed_Overhead_Pages = 12*число_таблиц + 2*число_хэш_индексов + 602112/1024
Назначение привилегий
С каждой учетной записью связана так называемая область привилегий, которая остается пустой при создании ее с помощью команды CREATE USER.
Привилегия - это поддерживаемый системой признак, который определяет, может ли конкретный пользователь выполнить конкретную операцию, т.е. разрешение на выполнение в системе определенного действия. Таким образом, имеется несколько типов привилегий, соответствующих нескольким типам операций, и, следовательно, предполагается, что пользователь в системе будет обладать определенным набором привилегий.
Для понимания действия этого механизма определения привилегий важно понятие владельца таблицы. Будучи пользователем, определенным в базе данных, вы являетесь владельцем любой созданной вами таблицы. Вы можете разрешить доступ к вашей таблице другим пользователям, определяя им привилегии прав доступа к своим таблицам.
Множество базовых привилегий определено стандартом ANSI SLQ, но, как правило, в конкретных СУБД поддерживаются дополнительные типы привилегий. Например, в СУБД Oracle их около сотни.
Все привилегии могут быть разделены на два класса: системные привилегии и привилегии прав доступа к объектам базы данных.
Системная привилегия - это привилегия, которая дает пользователю право на выполнение какой-либо операции в масштабе базы данных. Например, привилегия SELECT ANY TABLE дает пользователю право выполнять выборку из любой таблицы базы данных.
Привилегия прав доступа к объекту - это разрешение пользователю на выполнение определенной операции над определенным объектом базы данных. Например, пользователю может быть предоставлена привилегия SELECT на выполнение выборки из конкретной таблицы.
Для предоставления привилегий или, как еще говорят, авторизации доступа в SQL предусмотрена команда GRANT - ее может выполнить обычно только системный администратор, который предопределен в системе как SYSADM (наивысший уровень доступа, при котором возможно выполнение всех операций над БД). В СУБД Oracle такой пользователь должен обладать привилегией GRANT ANY RPIVILEGE.
Команда GRANT для определения системных привилегий имеет следующий синтаксис:
GRANT системная_привилегия [ { , системная_привиления } ѕ] TO { пользователь | PUBLIC } [{, пользователь} ѕ ] [WITH ADMIN OPTION]
В СУБД Oracle привилегии прав доступа к объекту могут быть предоставлены двум объектам системы: пользователям и ролям. Роль представляет собой поименованный набор привилегий. Предложения команды GRANT в данном случае управляют разграничением доступа к объектам базы данных: таблицам, представлениям, процедурам и т.д. Синтаксис команды имеет вид
GRANT {привилегия_доступа_к_объекту | ALL PRIVILEGES} [ имя_столбца [{ , имя_столбца } ѕ] ] [{ , привилегия_доступа_к_объекту } ѕ ] ON [имя_схемы.]имя_объекта TO { пользователь | PUBLIC } [WITH GRANT OPTION]
Список привилегий доступа показан в таблице 13.1 ниже.
| SELECT | Выполнение вставки данных из соответствующего объекта |
| INSERT | Выполнение вставки данных в соответствующий объект или его элемент |
| UPDATE | Выполнение модификации данных в соответствующем объекте или его элементе |
| REFERENCES | Определение столбцов как родительских ключей по отношению к внешним ключам в таблицах, ссылки, по которой производится контроль целостности объекта или егоэлемента |
| DELETE | Выполнение удаления данных из соответствующего объекта |
| EXECUTE | Выполнение действия с соответствующим объектом, например, вызов процедуры |
| INDEX | Выполнение индексирования для соответствующего объекта |
GRANT SELECT ON EMPLOYEE TO PETROV;
Вы можете также дать привилегии на выполнение обновления, добавления, удаления данных в таблицах и виртуальных таблицах (UPDATE, INSERT, DELETE), а также для изменения структуры таблицы (ALTER) и право на использование индекса (INDEX).
При определении привилегии на обновление вы можете указать список колонок, обновлять которые вы разрешаете другим пользователям таблицы.
Можно разрешить доступ на выборку таблицы и группе пользователей. Допустим, что вы желаете разрешить доступ к таблице EMPLOYEE всем пользователям, но исключить при этом доступ к колонкам SAL, COMM и FINE. Хорошим решением в данном случае будет создать виртуальную таблицу и разрешить доступ к ней всем пользователям:
CREATE VIEW EMP AS SELECT EMPNO,ENAME,JOB,AGE,HIREDATE,DEPNO FROM EMPLOYEE;
Теперь вы можете разрешить доступ всем пользователям к созданной виртуальной таблице, используя ключевое слово PUBLIC.
GRANT SELECT ON EMP TO PUBLIC;
Вы можете организовать и более сложные механизмы защиты данных. Например, вы можете, используя виртуальные таблицы, ограничить доступ к данным таблицы EMPLOYEE руководителям подразделений таким образом, что они не будут иметь доступа к информации по другим подразделениям. При этом удобно воспользоваться ключевым словом USER для идентификации пользователя:
CREATE VIEW EMPDEPT AS SELECT * FROM EMPLOYEE WHERE DEPNO IN ( SELECT DEPNO FROM EMPLOYEE WHERE ENAME = USER );
Теперь вы можете определить привилегии доступа для всех руководителей организации:
GRANT SELECT,UPDATE ON EMPDEPT TO IVLEV
Для отмены привилегий доступа в SQL предназначена команда REVOKE. Эта команда, так же как и команда GRANT, включает предложения ON и TO. Предложение REVOKE определяет отменяемую привилегию. Чтобы отменить привилегии на добавление строк в таблицу пользователю PETROV, нужно выполнить команду
REVOKE INSERT ON DEPARTAMENT TO PETROV;
Оценка размера базы данных
Базы данных существуют и функционируют в конкретной программно-аппаратной среде. Компьютер представляет собой аппаратную среду, состоящую из множества различных компонент - центрального процессора, оперативной и внешней памяти и т.д. Для обеспечения взаимодействия аппаратных компонент служит операционная система. Операционные системы варьируются в зависимости от используемой аппаратуры. Операционная система представляет собой промежуточный уровень между компьютерной аппаратурой и программами. СУБД - это программа для поддержки базы данных. База данных хранится в файлах операционной системы, которые располагаются во внешней памяти компьютера, на жестких дисках. Файлы операционной системы обычно располагаются внутри логически поименованных областей жесткого диска, которые называются каталогами. Приложения базы данных взаимодействуют с данными в ней через цепочку программных взаимодействий:
Размер базы данных во многих практических ситуациях является критическим параметром ее эксплуатации. Проектировщик БД должен уметь оценивать как размер БД, так и возможный ее рост во времени.
Отправной точкой решения задачи оценки размера БД является вычисление размера каждой таблицы БД. Чтобы это сделать, необходимо вычислить размер каждой колонки в таблице и на основании этих данных оценить размер самой таблицы. Далее следует оценить размер всех индексов, представлений и других объектов.
Поскольку ОС распределяет память под файлы в страницах, то при вычислении размера базы данных необходимо оценить число страниц, которое она занимает. Для ОС типа Windows размер страницы составляет 1024 байта.
Подготовка скрипта создания физической базы данных
Рассмотрим решение этой задачи на учебном примере, который мы использовали в предыдущих лекциях.
Создание реляционных таблиц. Сначала проектировщик базы данных собирает команды создания таблиц базы данных, которые в нашем случае могут иметь вид
CREATE TABLE DEPARTAMENT ( DEPNO integer NOT NULL, DNAME char(20), LOC char(20), MANAGER char(20), PHONE char(15), PRIMARY KEY (DEPNO) определение первичного ключа );
CREATE TABLE EMPLOYEE ( EMPNO integer NOT NULL, ENAME char(25), LNAME char(10), DEPNO int, SSECNO char(10), JOB char(25), AGE date, HIREDATE date NOT NULL WITH DEFAULT, SAL dec(9,2), COMM dec(9,2), FINE dec(9,2), PRIMARY KEY (EMPNO) );
CREATE TABLE PROJECT ( PROJNO char(8) NOT NULL, PNAME char(25), BUDGET dec(9,2), PRIMARY KEY (PROJNO) ); CREATE TABLE EMP_PRJ ( EMPNO integer NOT NULL, PROJNO char(8) NOT NULL, WORKS number, PRIMARY KEY (EMPNO, PROJNO), FOREING KEY (EMPNO) REFERENCES EMPLOYEE, FOREING KEY (PROJNO) REFERENCES PROJECT ); Создание индексов. На втором шаге проектировщик базы данных собирает команды создания индексов, которые он решил построить. В нашем случае проектировщик мог принять решения не строить дополнительных индексов, а СУБД Oracle индекс первичного ключа строится автоматически. Поэтому этот раздел скрипта у нас пуст.Создание представлений. Проектировщик базы данных принял решение создать внешнюю схему для пользователей базы данных и разработал следующий фрагмент скрипта:
CREATE VIEW DEPARTAMENT_V AS SELECT DEPNO, DNAME, LOC, MANAGER, PHONE FROM DEPARTAMENT;
CREATE VIEW EMPLOYEE_V AS SELECT EMPNO, ENAME, LNAME, DEPNO, SSECNO, JOB, AGE, HIREDATE, SAL, COMM, FINE FROM EMPLOYEE; CREATE VIEW PROJECT_V AS SELECT PROJNO, PNAME, BUDGET FROM PROJECT;
Кроме этого, проектировщик решил добавить в физическую модель базы данных еще четыре представления:
CREATE VIEW PERSPROJ AS SELECT ENAME, JOB, PNAME FROM EMPLOYEE, PROJECT, EMPL_PROJ WHERE EMPLOYEE.EMPNO= EMPL_PROJ.EMPNO AND EMPL_PROJ.PROJNO=PROJECT.PROJNO;
CREATE VIEW EMPLIST AS SELECT DEPNO, EMPNO, ENAME, JOB FROM EMPLOYEE GROOP BY DEPNO, EMPNO, ENAME, JOB;
CREATE VIEW PERSPROJ AS SELECT ENAME, JOB, PNAME FROM EMPLOYEE, PROJECT WHERE EMPLOYEE.PROJNO=PROJECT.PROJNO;
CREATE VIEW CURPROJ AS SELECT * FROM PROJECT WHERE START_DATE < SYSDATE WITH CHECK OPTION; Создание синонимов. Проектировщик базы данных решил создать один синоним и добавил в скрип команду его создания:
CREATE PUBLIC SYNONYM EMP FOR EPMPLOYEE; Создание пользователей и предоставление привилегий. Проектировщик базы данных решил создать трех пользователей базы данных и не определять никаких ролей, поэтому добавил в скрипт следующие команды:
CREATE USER Ivan IDENTIFIED BY EXTERNALLY; CREATE USER Peter IDENTIFIED BY EXTERNALLY; CREATE USER Sidorov IDENTIFIED BY 'alsy_';
Проектировщик базы данных определил пользователям следующие привилегии:
GRANT SELECT ON EMPLOYEE, DEPARTAMENT, PROJECT, EMP_PRJ TO Ivan, Peter, Sidorov;
GRANT INSERT,UPDATE ON EMPLOYEE, DEPARTAMENT, PROJECT, EMP_PRJ TO Ivan;
Никаких других проектных решений проектировщик базы данных принимать не стал.
Далее он составил подробный отчет по каждому объекту базы данных, фрагмент отчета для реляционной таблицы DEPARTAMENT (таблица 13.15) и предал полученные результаты руководителю ИТ-проекта.
| Наименование | DNAME | char(20) |
| Размещение | LOC | char(20) |
| Руководитель | MANAGER | char(25) |
| Телефон | PHONE | char(15) |
Литература: [7], [8], [23], [38], [39], [45].
Пример расчета размера базы данных
Рассмотрим базу данных, которая состоит из таблицы CUSTOMER (ПОКУПАТЕЛЬ), таблицы CONTACT (КОНТАКТ), индекса NDX_CONTACT и представления BAD_CUSTOMER. Команды создания базы данных приведены ниже:
CREATE TABLE CUSTOMER (CUSTOMER_ID CHAR(5) NOT NULL, CUSTOMER_NAME VARCHAR(25), CUSTOMER_ADDR VARCHAR(50), CUSTOMER_RATING CHAR(10), PRIMARY KEY(CUSTOMER_ID)) PCTFREE 15;
CREATE TABLE CONTACT (CUSTOMER_ID CHAR(5) NOT NULL, CONTACT_NAME VARCHAR(25) NOT NULL, CONTACT_PHONE DECIMAL(10,0), CONTACT_TEXT LONG VARCHAR, PRIMARY KEY (CUSTOMER_ID, CONTACT_NAME) FOREIGN KEY CUSTKEY (CUSTOMER_ID) REFERENCES CUSTOMER ON DELETE RESTRICT) PCTFREE 15;
CREATE UNIQUE CLUSTERED HASHED INDEX NDX_CUSTOMER ON CUSTOMER (CUSTOMER_ID) SIZE 47628;
CREATE UNIQUE INDEX NDX_CONTACT ON CONTACT ON CONTACT (CUSTOMER_ID,CONTACT_NAME) PCTFREE 10;
CREATE VIEW BAD_CUSTOMER AS SELECT CUSTOMER_NAME, CUSTOMER_ADDR FROM CUSTOMER WHERE CUSTOMER_RATING='POOR';
Оценим размер базы данных в предположении, что она создана под управлением СУБД SQLBASE. Ожидаемое число строк в таблице CUSTOMER - порядка 50000, а в таблице CONTACT - 175000. После загрузки базы данных была выполнена оценка средней длины полей, которая приведена в таблице 13.7.
| CUSTOMER | CUSTOMER_ID | 5 | 5 |
| CUSTOMER_NAME | 25 | 10 | |
| CUSTOMER_ADDR | 50 | 30 | |
| CUSTOMER_RATING | 10 | 5 | |
| CONTACT | CUSTOMER_ID | 5 | 5 |
| CONTACT_NAME | 25 | 15 | |
| CONTACT_PHONE | 10 | 10 | |
| CONTACT_TEXT | 500 | 10 |
Оценка размера базы данных:
Таблица CUSTOMER:
Data_Length = 5 + 10 + 30 + 5 = 50 Row_Length = 18 + 6 + (2*4) + 50 = 82 Row_Length_with_Stack = (82*100)/85 =97 Rows_per_Page = (1024 - 86)/97 = 9 Nbr_Row_Pages =50000/9 = 5556 Nbr_Hashed_Table_Pages = 5556/0,7 = 7938 Total_Data_Page = 7938
Так как в этой таблице нет колонок типа LONG VARCHAR, то общее число страниц данных этой таблицы будет равно числу страниц хэш-таблицы.
Таблица CONTACT:
Data_Length = 5 + 15 + (((10 + 2)/2 + 1) + 12 = 39 Row_Length = 18 + (2*4) + 39 = 65 Row_Length_with_Stack = (65*100)/75 = 87 Rows_per_Page = (1024 - 86)/87 = 10 Nbr_Row_Pages =175000/10 = 17500 Nbr_Long_Pages = 17500 * 1 = 175000 Total_Data_Page = 175000 + 17500 = 192500 Индекс NDX_CONTACT:
Key_Length = 5 +15 = 20 Index_Entry_Length = 9 + 2 + 20 = 31 Usable_Index_Page_Size = (1024 - 74)*(100 - 10)/100 = 855 Index_Entry_per_Page = 855/31 = 27 Nbr_Index_Pages = 175000/27 = 6482
Представление BAD_CUSTOMER
Fixed_Overhead = 12*1024 = 12288 байт Variable_Overhead = 1*150 + 2*170 = 490 байт Variable_Overhead_all_Views = 0 Total_View_overhead_in_Pages = (12288 + 490 + 0)/1024 = 13 страниц Оценка размера фиксированной системной области:
Total_Fixed_Overhead_Pages = 2*12 + 1*2 + 602112/1024 = 614 страниц Оценка размера базы данных:
Размер базы данных = 7938 + 192500 + 6482 + 13 + 614 = 207560 страниц или 203 Мб.
Проверка физической модели реляционной базы данных
Перед созданием физической базы данных проектировщик базы данных должен проверить созданную им модель физической структуры базы данных на полноту и корректность.
Полнота в данном случае означает, что следует убедиться в том, что:
все сущности логической модели базы данных нашли свое отражение в объектах физической модели;в соответствии анализом транзакций системы и требованиями производительности приняты соответствующие проектные решения, которые получили свое отражение в объектах физической структуры;определен круг пользователей системы, их роли, и созданы соответствующие объекты базы данных для поддержки;оценен потенциальный размер базы данных и ее возможный рост, а полученные оценки нашли свое отражение в объектах базы данных, связанных с управлением файловым пространством базы данных.
Корректность означает, что в процессе денормализации логической модели базы данных при ее преобразовании в физическую с учетом требований по производительности не была потеряна реляционность отношений базы данных на физическом уровне модели.
Заметим, что наших лекциях не нашли своего отражения ряд вопросов проектирования базы данных, а именно - связанных с выбором программно-аппаратной архитектуры (например, трехзвенная архитектура "клиент-сервер"), настройкой производительности, работой с физическим пространством базы данных и т.д. Как правило, эти вопросы, по большей части связанные с конкретной реализацией базы данных, решаются непосредственно администраторами баз данных, а проектировщики редко привлекаются к решению, в том числе и этих задач, на стадии управления изменениями в процессе начального, тестового, опытного этапов эксплуатации базы данных. В наших лекциях мы акцентировали внимание на тех задачах проектировщика базы данных, которые в основном не зависят от учета конкретной программно-аппаратной среды функционирования базы данных.
На этом этапе проектирования базы данных важно завершить документирование модели. Все объекты и их элементы физической модели должны быть описаны и прокомментированы.
В результате должен быть создан подробный отчет по физической модели. В этом случае неоценимую поддержку в работе проектировщика оказывают CASE-средства поддержки процесса проектирования, например, хорошо известный Erwin или PowerDesigner, которые позволяют в автоматическом режиме создавать такие отчеты.
После разработки или генерирования скрипта создания физической базы данных, загрузки в нее реальных или тестовых данных, проектировщик базы данных обязан с помощью средств СУБД получить информацию о параметрах объектов базы данных, чтобы сопоставить ее с предполагаемыми параметрами, и при необходимости выполнить их корректировку (не забывая отразить эти действия в отчете). Основные параметры объектов, которые следовало бы проконтролировать после создания физической базы данных, приведены в таблицах 13.9.- 13.14. Это касается основных объектов - таблиц, индексов, представлений, синонимов, ролей и пользователей.
| Таблица | Имя таблицы | |
| Столбец | Имя столбца | |
| Default | Устанавливает для столбца значение по умолчанию, которое будет использоваться при отсутствии значения в операторе insert | |
| Ограничение_столбца_ref | Содержит ссылку на ограничение другого столбца, которое должно применяться к данному столбцу | |
| Ограничение_столбца | Устанавливает ограничения целостности как часть определения столбца | |
| Тип_данных | Задает тип данных - числовой, символьный, большой объем и т.д. | |
| Ограничение_таблицы | Устанавливает ограничения целостности для все таблицы | |
| Ограничение_таблицы_ref | Содержит ссылку на ограничение другой таблицы, которое должно применяться к данной таблице | |
| Tablespace | Табличное пространство, в которое должна быть помещена таблица | Табличное пространство по умолчанию, назначенное владельцу таблицы |
| Logging/NoLogging | Указывает, должна ли информация об объекте отслеживаться в файле журнала повтора | Logging |
| Petfree | Указывает, сколько процентов свободного пространства должно сохраняться в каждом блоке данных для будущих обновлений строк таблицы | Диапазон 1-99, по умолчанию 10 % |
| Petused | Задает минимальный объем использованного пространства, поддерживаемый в каждом блоке данных таблицы | Диапазон 1-100, по умолчанию 40 % |
| Initrans | Задает начальное количество транзакционных записей, выделяемых в каждом блоке данных таблицы | Диапазон 1-255, по умолчанию 1 (2 для кластера или индекса) |
| Maxtrans | Задает максимальное количество параллельных транзакций, которые могут обновлять блок данных таблицы (не применяется к запросам) | Диапазон 1-255, значение по умолчанию зависит от размера блока данных |
| Конструкция_хранения | Те же параметры, что и для табличного пространства |
| Unigue | Указывает, что значения столбца (столбцов) индекса должны быть уникальны | Nonunigue |
| Bitmap | Указывает, что будет битовым, а не индексом В-дерева (используется для столбцов с низкой кардинальностью) | В-дерево |
| Схема | Указывает имя владельца таблицы | Схема создателя индекса |
| Имя_индекса | Задает имя индекса | |
| Конструкция кластерного индекса | Указывает, что индекс должен быть построен для кластера, и содержит список кластерных атрибутов | |
| Конструкция индекса таблицы | Указывает таблицу, для которой строится индекс, в том числе любые псевдонимы таблицы, список индексных выражений, а также является ли индекс локальным или глобальным (для разделенных индексов) | По умолчанию используется схема создателя индекса, индекс создается как глобальный |
| Список индексных выражений | Определяет либо столбцы, по которым выполняется индексирование, либо список выражений для создания функционального индекса | Для регулярного индекса не более 32 столбцов; для битового индекса не более 30 |
| ASC/DESC | Указывает, в каком порядке будет создаваться индекс - возрастающем или убывающем | По возрастанию |
| Список физических атрибутов | Те же атрибуты, что и для таблицы: pctfree, pctused, initrans, maxtrans, конструкция_хранения | |
| Logging/Nologging | Указывает, будет ли информация об объекте отслеживаться в файле журнала повтора | Logging |
| Online | Указывает, должен ли индекс быть доступен сразу после создания | Online |
| Compute statistics | Указывает, должна ли генерироваться статистика по индексу | |
| Tablespace | Указывает табличное пространство, в котором будет храниться индекс | Табличное пространство по умолчанию, назначенное создателю индекса |
| Compress/Nocompress | Позволяет исключить повторяющиеся ключевые слова | Nocompress |
| Nosort | Указывает, что значения должны вставляться в порядке возрастания, - Oracle не будет сортировать строки при вставке | |
| Reverce | Сохраняет байты индекса в обратном порядке, за исключением идентификатора строки (row ID) - не может использоваться совместно с nosort |
В результате должен быть создан подробный отчет по физической модели. В этом случае неоценимую поддержку в работе проектировщика оказывают CASE-средства поддержки процесса проектирования, например, хорошо известный Erwin или PowerDesigner, которые позволяют в автоматическом режиме создавать такие отчеты.
После разработки или генерирования скрипта создания физической базы данных, загрузки в нее реальных или тестовых данных, проектировщик базы данных обязан с помощью средств СУБД получить информацию о параметрах объектов базы данных, чтобы сопоставить ее с предполагаемыми параметрами, и при необходимости выполнить их корректировку (не забывая отразить эти действия в отчете). Основные параметры объектов, которые следовало бы проконтролировать после создания физической базы данных, приведены в таблицах 13.9.- 13.14. Это касается основных объектов - таблиц, индексов, представлений, синонимов, ролей и пользователей.
| Таблица | Имя таблицы | |
| Столбец | Имя столбца | |
| Default | Устанавливает для столбца значение по умолчанию, которое будет использоваться при отсутствии значения в операторе insert | |
| Ограничение_столбца_ref | Содержит ссылку на ограничение другого столбца, которое должно применяться к данному столбцу | |
| Ограничение_столбца | Устанавливает ограничения целостности как часть определения столбца | |
| Тип_данных | Задает тип данных - числовой, символьный, большой объем и т.д. | |
| Ограничение_таблицы | Устанавливает ограничения целостности для все таблицы | |
| Ограничение_таблицы_ref | Содержит ссылку на ограничение другой таблицы, которое должно применяться к данной таблице | |
| Tablespace | Табличное пространство, в которое должна быть помещена таблица | Табличное пространство по умолчанию, назначенное владельцу таблицы |
| Logging/NoLogging | Указывает, должна ли информация об объекте отслеживаться в файле журнала повтора | Logging |
| Petfree | Указывает, сколько процентов свободного пространства должно сохраняться в каждом блоке данных для будущих обновлений строк таблицы | Диапазон 1-99, по умолчанию 10 % |
| Petused | Задает минимальный объем использованного пространства, поддерживаемый в каждом блоке данных таблицы | Диапазон 1-100, по умолчанию 40 % |
| Initrans | Задает начальное количество транзакционных записей, выделяемых в каждом блоке данных таблицы | Диапазон 1-255, по умолчанию 1 (2 для кластера или индекса) |
| Maxtrans | Задает максимальное количество параллельных транзакций, которые могут обновлять блок данных таблицы (не применяется к запросам) | Диапазон 1-255, значение по умолчанию зависит от размера блока данных |
| Конструкция_хранения | Те же параметры, что и для табличного пространства |
| Unigue | Указывает, что значения столбца (столбцов) индекса должны быть уникальны | Nonunigue |
| Bitmap | Указывает, что будет битовым, а не индексом В-дерева (используется для столбцов с низкой кардинальностью) | В-дерево |
| Схема | Указывает имя владельца таблицы | Схема создателя индекса |
| Имя_индекса | Задает имя индекса | |
| Конструкция кластерного индекса | Указывает, что индекс должен быть построен для кластера, и содержит список кластерных атрибутов | |
| Конструкция индекса таблицы | Указывает таблицу, для которой строится индекс, в том числе любые псевдонимы таблицы, список индексных выражений, а также является ли индекс локальным или глобальным (для разделенных индексов) | По умолчанию используется схема создателя индекса, индекс создается как глобальный |
| Список индексных выражений | Определяет либо столбцы, по которым выполняется индексирование, либо список выражений для создания функционального индекса | Для регулярного индекса не более 32 столбцов; для битового индекса не более 30 |
| ASC/DESC | Указывает, в каком порядке будет создаваться индекс - возрастающем или убывающем | По возрастанию |
| Список физических атрибутов | Те же атрибуты, что и для таблицы: pctfree, pctused, initrans, maxtrans, конструкция_хранения | |
| Logging/Nologging | Указывает, будет ли информация об объекте отслеживаться в файле журнала повтора | Logging |
| Online | Указывает, должен ли индекс быть доступен сразу после создания | Online |
| Compute statistics | Указывает, должна ли генерироваться статистика по индексу | |
| Tablespace | Указывает табличное пространство, в котором будет храниться индекс | Табличное пространство по умолчанию, назначенное создателю индекса |
| Compress/Nocompress | Позволяет исключить повторяющиеся ключевые слова | Nocompress |
| Nosort | Указывает, что значения должны вставляться в порядке возрастания, - Oracle не будет сортировать строки при вставке | |
| Reverce | Сохраняет байты индекса в обратном порядке, за исключением идентификатора строки (row ID) - не может использоваться совместно с nosort |
Создание пользователей
Одной из задач, которая чаще всего решается в процессе эксплуатации базы данных, является создание пользователей базы данных. В контексте баз данных термин "пользователь" имеет два основных значения:
это человек, который входит в базу данных для выполнения работы;это область схемы приложения, в которой хранятся объекты, используемые этим приложением.
На самом деле под созданием пользователя базы данных понимают создание учетной записи пользователя (user account) в словаре данных, с помощью которой пользователь может войти в базу данных для выполнения своей работы. Учетные записи представляют собой способ организации доступа пользователя к базе данных, выдачи разрешений на выполнение требуемых задач, а также отслеживания выполняемых пользователем действий. Такая учетная запись включает имя пользователя базы данных, которое присваивают пользователю в базе данных, пароль, указание которого разрешает доступ к объектам базы данных, уровень полномочий или уровень доступа и еще ряд опциональных параметров. Уровень доступа означает тип операций, которые данный пользователь может выполнять (создавать таблицу, определять пользователей и т.д).
Пользователь является объектом базы данных. При создании пользователя базы данных обязательным является указание имени. Остальные параметры могут быть установлены администратором базы данных позже с помощью команды SQL ALTER USER.
Создание пользователей называется еще задачей авторизации или аутентификации пользователей. Решение этой задачи возлагается на администраторов базы данных. Проектировщик во время проектирования базы данных, совместно с администратором базы данных, может создать всех потенциальных пользователей. Хорошим тоном проектирования является создание проектировщиком следующих пользователей: разработчиков приложения базы данных и тестировщиков базы данных. Именно эти лица получают спроектированную базу данных в опытную эксплуатацию.
Чтобы создать пользователей базы данных, проектировщик должен иметь список лиц, которым будут разрешен доступ к базе данных.
Для создания пользователей базы данных или учетной записи пользователя используется команда SQL CREATE USER, неполный синтаксис которой приведен ниже:
CREATE USER имя_пользователя IDENTIFIED BY [пароль|EXTERNALLY];
Ключевое слов EXTERNALLY указывает, что должна быть использована аутентификация операционной системы.
Для исключения пользователей из базы данных применяется команда DROP USER.
В качестве примера создадим пользователей нашей учебной базы данных. Допустим, что у нас есть следующий фрагмент списка лиц, которые в организации будут пользоваться базой данных:
... Иванов А.А. - разработчик приложения Петров В.В. - разработчик приложения Сидоров С.С. - тестировщик базы данных …
Иванов и Петров уже имеют системную аутентификацию, и администратор базы данных решил, что учетная запись для их доступа к базе данных будет такой же. Их имена для входа в систему - Ivan и Peter, соответственно. Сидоров - это новый сотрудник, который будет принят на работу через 1,5 месяца специально для тестирования базы данных. Совместно с администратором базы данных было решено, что его имя для входа в базу данных будет Sidorov, а пароль - alsy_. Тогда проектировщик базы данных может включить в свой скрипт следующие команды:
CREATE USER Ivan IDENTIFIED BY EXTERNALLY; CREATE USER Peter IDENTIFIED BY EXTERNALLY; CREATE USER Sidorov IDENTIFIED BY 'alsy_';
Создание табличных пространств
Проектировщик базы данных работает с логическими объектами - табличными пространствами, таблицами, представлениями, индексами и т.д., так называемыми логическими файлами СУБД Oracle. Информация о содержимом логических файлов хранится в словаре данных.
Одним из самых трудных для понимания и объяснения объектов реляционной базы данных Oracle является табличное пространство (tablespace). Табличным пространством в Oracle называется логическая область хранения данных, размер которой ограничен размером используемого жесткого диска. Физические файлы создаются на уровне табличного пространства.
Выделением места для табличных пространств и способом их совместного использования управляют администраторы базы данных. Поэтому проектировщики базы данных должны при использовании табличных пространств работать в тесном сотрудничестве с ними.
Табличные пространства можно создавать, менять и удалять. Для создания табличных пространств в СУБД Oracle предусмотрена команда SQL CREATE TABLESPACE, параметры которой приведены в таблице 13.8.
| Имя_табличного_пространства | Имя, присваемое табличному пространству. Оно должно отражать назначение этого табличного пространства | |
| Спецификация файла данных | ||
| Местонахождение файла | Полный путь к каталогу и имя файла | |
| size | Полный начальный размер файла данных, в соответствии с которым выделяется дисковое пространство | |
| reuse | Если файл данных существует, его нужно использовать повторно, указав этот параметр, в противном случае возникнет ошибка. Размер файла должен совпадать с указанным в параметре size | |
| autoextend | Разрешает или запрещает автоматическое увеличение размера файла данных.Может принимать значения on и off. Для on существуют дополнительные параметры: next: величина приращения файла (в байтах). maxsize: наибольший допустимый размер файла данных. Может быть неограниченным (unlimited) | ON |
| minimum extend | Предназначен для управления фрагментацией - определяет минимальный размер экстента | |
| default предложение хранения | объем пространства, выделяемого объекту при отсутствии явно указанной конструкции хранения | |
| initial | Объем пространства, выделяемого для первого экстента страниц | 5 |
| next | Объем для пространства, выделяемого для второго и последующих экстентов | 5 физических страниц |
| minextens | Минимальное количество выделяемых экстентов | 1 |
| maxextents | Максимальное количество выделяемых экстентов - может быть неограниченным (intimated) | 121 |
| petincrease | Коэффициент приращения размера (в %) для каждого следующегоэкстента после next | 50 |
| freelist groops | Используется в режиме параллельного сервера и указывает количество списков свободных блоков для объектов, созданных в данном табличном пространстве без использования конструкций хранения | |
| freelist | Указывает количество списков свободных блоков, созданных в данном табличном пространстве без использования конструкций хранения | |
| optimal | Применяется только к сегментам отката и определяет минимальный объем пространства, до которого сокращается сегмент отката после расширения за пределы оптимального значения | |
| online/offline | Указывает, режим (оперативный/автономный, в котором изначально должно находиться табличное пространство | online |
| permanent/temporary | Указывает, будет ли табличное пространство содержать объекты или только временные сегменты | permanent |
| Предложение управления экстентами | ||
| dictionary | Указывает, что управление экстентами осуществляется только через словарь данных | dictionary |
| local | Указывает, что некоторая часть табличного пространства зарезервирована для битовых карт | user |
| plugged_in | Используется с переносимыми табличными пространствами и указывает, что табличное пространство может быть "подключено" к базе данных | NO |
В качестве примера создадим табличное пространство, не используя никаких параметров, как показано ниже.
CREATE TABLESPACE my_ts DATAFILE 'c:\ora9i\oradata\orcr\myfile01.dbf' SIZE 2M;
Ключевое слово TABLESPACE задает имя табличной области (my_ts), ключевое слово DATAFILE задает спецификацию файла операционной системы ('c:\ora9i\oradata\orcr\myfile01.dbf'), в котором будут размещаться данные создаваемой табличной области, ключевое слово SIZE задает размер табличного пространства в мегабайтах. Остальные значения параметров принимаются по умолчанию. В частности, поскольку значение по умолчанию для параметра AUTOEXTEND есть ON (см. таблицу 13.8), то разрешено автоматическое расширение пространства, выделенного для данного табличного пространства. По умолчанию созданное табличное пространство переходит в оперативный режим (ONLINE) и является постоянным табличным пространством (PERMANENT).
Для изменения параметров табличного пространства используется команда ALTER TABLESPACE, а для удаления - команда DROP.
Чтобы разместить объект базы данных в определенном табличном пространстве, необходимо явно указать это табличное пространство в командах SQL. Например, команда
CREATE TABLE CUSTOMER (CUSTOMER_ID CHAR(5) NOT NULL, CUSTOMER_NAME VARCHAR(25), CUSTOMER_ADDR VARCHAR(50), CUSTOMER_RATING CHAR(10)) TABLESPASE my_st, PCTFREE 15;
размещает таблицу CUSTOMER и табличном пространстве my_st.
Средства разграничения доступа в СУБД Oracle
Современный уровень развития средств вычислительной техники характеризуется высокой степенью распределенной обработки данных, когда логически единая информационная база данных размещается в сетевой среде. Вычислительные сети характеризуются объединением различных компьютеров на основе программно-аппаратной платформы, асинхронной многопользовательской обработкой и развитыми средствами разграничения доступа. При этом реальная сложность организации управления доступа скрыта от пользователя. Логическое пространство базы данных должно выглядеть для пользователя так, как если бы вся база данных располагалась на его локальном компьютере.
При работе с базой данных должна быть обеспечена высокая степень безопасности данных практически без усложнения работы пользователя. Одной из неотъемлемых функций любой промышленной СУБД, в том числе и Oracle, является обеспечение защиты информации на уровне доступа к данным. Для этого в СУБД Oracle используется система избирательного управления доступом. Это означает, что администратор базы данных создает пользователей и управляет их полномочиями на выполнение конкретных операций с конкретными объектами в базе данных.
Задача распознавания пользователей и разграничения доступа между различными пользователями базы данных решается как на уровне средств операционной системы, так и на уровне СУБД. Стандартным подходом к решению этой задачи на обоих уровнях является поддержка механизма создания (регистрации) пользователя и назначения ему привилегий. С другой стороны, каждый пользователь, создающий некоторый объект, должен иметь право на управление доступом к этому объекту. Типичное решение состоит в реализации концепции привилегий или прав доступа. Привилегия - это некоторый поддерживаемый СУБД или операционной системой признак, который определяет, может ли конкретный пользователь выполнить конкретную операцию.
Для этого администраторы базы данных создают пользователей базы данных и предоставляют им некоторые полномочия по доступу к объектам базы данных.
Обеспечение правомерности доступа пользователя реализуется средствами аутентификации системы, простейшим из которых является использование пароля, подтверждающего подлинность пользователя. Для входа в базу данных пользователь должен ввести имя и подтвердить свою подлинность паролем.
В СУБД Oracle реализована поддержка принципа безопасности по умолчанию, который состоит в назначении наименьших привилегий. Т.е. пользователь может получить доступ к объекту базы данных или выполнить определенные действия в базе данных, только если ему это явно разрешено.
Концепция обеспечения безопасности доступа, принятая в Oracle, предусматривает выполнение следующих действий:
идентификацию и аутентификацию пользователей;контроль глобальных действий в базе данных, определяющих доступность ее объектов;контроль доступа к объектам базы данных;контроль операций, производимых над объектами базы данных.
Проектировщик базы данных решает эти задачи, исходя из предположения, что база данных доступна. В терминах СУБД Oracle это означает, что администратор базы данных запустил экземпляр (instance) сервера базы данных.
