З часом структура бази даних міняється: створюються нові таблиці, а колишні стають непотрібними і віддаляються з бази даних за допомогою оператора:
DROP TABLE ім’я_таблиці [RESTRICT | CASCADE]
Слід зазначити, що ця команда видалить не тільки вказану таблицю, але і всі вхідні в неї рядки даних. Якщо вимагається видалити з таблиці лише дані, зберігши структуру таблиці, слід скористатися командою DELETE.
Оператор DROP TABLE додатково дозволяє указувати, чи слідує операцію видалення виконувати каскадно. Якщо в операторі вказано ключове слово RESTRICT, то за наявності в базі даних хоча б одного об'єкту, існування якого залежить від таблиці, що видаляється, виконання оператора DROP TABLE буде відмінено. Якщо вказано ключове слово CASCADE, автоматично віддаляються і всі інші об'єкти бази даних, чиє існування залежить від таблиці, що видаляється, а також інші об'єкти, залежні від об'єктів, що видаляються. Загальний ефект від виконання оператора DROP TABLE з ключовим словом CASCADE може виявитися вельми відчутним, тому подібні оператори слід використовувати з максимальною обережністю.
Частіше всього оператор DROP TABLE використовується для виправлення помилок, допущених при створенні таблиці. Якщо таблиця була створена з некоректною структурою, можна скористатися оператором DROP TABLE для її видалення, після чого створити таблицю наново.
Індекси в стандарті язика
Індекси є структурою, що дозволяє виконувати прискорений доступ до рядків таблиці на основі значень одного або більш її стовпців. Наявність індексу може істотно підвищити швидкість виконання деяких запитів і скоротити час пошуку необхідних даних за рахунок фізичного або логічного їх впорядковування.
Індекс – це набір посилань, впорядкованих по певному стовпцю таблиці, який в даному випадку називатиметься індексованим стовпцем. Хоча індекс і пов'язаний з конкретним стовпцем (або стовпцями) таблиці, все ж таки він є самостійним об'єктом бази даних.
Фізично індекс – всього лише впорядкований набір значень з індексованого стовпця з покажчиками на місця фізичного розміщення початкових рядків в структурі бази даних. Коли користувач виконує запит, що звертається до індексованого стовпця, СУБД автоматично аналізує індекс для пошуку необхідних значень.
Проте, оскільки індекси повинні обновлятися системою при кожному внесенні змін в їх базову таблицю, вони створюють додаткове навантаження на систему.
Індекси звичайно створюються з метою задоволення певних критеріїв пошуку після того, як таблиця вже знаходилася якийсь час в роботі і збільшилася в розмірах. Створення індексів не передбачено стандартом SQL, проте більшість діалектів підтримує як мінімум наступний оператор:
CREATE [UNIQUE] INDEX ім’я_індекса
ON ім’я_таблиці (ім’я_колонки[ASC|DESC][,...n])
Вказані в операторі стовпці складають ключ індексу. Індекси можуть створюватися тільки для базових таблиць, але не для уявлень. Якщо в операторі вказано ключове слово UNIQUE, унікальність значень ключа індексу автоматично підтримуватиметься системою. Вимога унікальності значень обов'язкова для первинних ключів, а також можлива і для інших стовпців таблиці (наприклад, для альтернативних ключів). Хоча створення індексу допускається у будь-який момент, при його побудові для вже заповненої даними таблиці можуть виникнути проблеми, пов'язані з дублюванням даних в різних рядках. Отже, унікальні індекси (принаймні, для первинного ключа) має значення створювати безпосередньо при формуванні таблиці. В результаті система відразу візьме на себе контроль за унікальністю значень даних у відповідних стовпцях.
Якщо створений індекс згодом виявиться непотрібним, його можна видалити за допомогою оператора
DROP INDEX ім’я_індекса
Індекси в середовищі MS SQL Server
Індекс є засобом, що допомагає прискорити пошук необхідних даних за рахунок фізичного або логічного їх впорядковування. Індекс є набором посилань, впорядкованих по певному стовпцю таблиці, який в даному випадку називатиметься індексованим стовпцем.
Індекси - це набори унікальних значень для деякої таблиці з відповідними посиланнями на дані. Вони розташовані в самій таблиці і є зручним внутрішнім механізмом системи SQL-серверу, за допомогою якого здійснюється доступ до даних самим оптимальним способом. В середовищі SQL Server реалізовані ефективні алгоритми пошуку потрібного значення в строго певній послідовності даних. Прискорення пошуку досягається саме за рахунок того, що дані представляються впорядкованими (хоча фізично, залежно від типу індексу, вони можуть зберігатися відповідно до черговості їх додавання в таблицю ). До теперішнього часу розроблені ефективні математичні алгоритми пошуку даних у впорядкованій послідовності. Найефективнішою структурою для пошуку даних в машинному уявленні є B-деревья – багаторівнева ієрархічна структура із змінною кількістю елементів в кожному вузлі.
Якщо вибірка даних з таблиці вимагає значного часу, це означає, що для неї необхідно створити індекс. Індекси можуть істотно підвищити продуктивність виконання операцій пошуку і вибірки даних. При виборі стовпця для індексу слід проаналізувати, які типи запитів частіше за все виконуються користувачами і які стовпці є ключовими, тобто задаючими критерії вибірки даних, наприклад, порядок сортування.
В середовищі SQL Server реалізовано декілька типів індексів:
кластерні індекси ;
некластерні індекси ;
унікальні індекси.
Некластерний індекс
некластерні індекси – найтиповіші представники сімейства індексів. На відміну від кластерних, вони не перебудовують фізичну структуру таблиці, а лише організовують посилання на відповідні рядки.
Для ідентифікації потрібного рядка в таблиці некластерний індекс організовує спеціальні покажчики, що включають:
інформацію про ідентифікаційний номер файлу, в якому зберігається рядок ;
ідентифікаційний номер сторінки відповідних даних;
номер шуканого рядка на відповідній сторінці;
вміст стовпця.
В більшості випадків слід обмежуватися 4-5 індексами.
Кластерний індекс
Принциповою відмінністю кластерного індексу від індексів інших типів є те, що при його визначенні в таблиці фізичне розташування даних перебудовується відповідно до структури індексу. Логічна структура таблиці в цьому випадку є швидше словником, ніж індексом. Дані в словнику фізично впорядковані, наприклад за абеткою.
Кластерні індекси можуть дати істотне збільшення продуктивності пошуку даних навіть в порівнянні із звичайними індексами. Збільшення продуктивності особливо помітне при роботі з послідовними даними. Якщо в таблиці визначений некластерний індекс, то сервер повинен спочатку звернутися до індексу, а потім знайти потрібний рядок в таблиці. При використовуванні кластерних індексів наступна порція даних розташовується відразу після знайдених раніше даних. Завдяки цьому відпадають зайві операції, пов'язані із зверненням до індексу і новим пошуком потрібного рядка в таблиці.
Природно, в таблиці може бути визначений тільки один кластерний індекс. Відповідно, слід вибирати стовпці, що часто використовуються. При цьому варто слідувати загальним рекомендаціям створення індексів і не індексувати дуже довгі стовпці.Кластерний індекс може включати декілька стовпців. Проте кількість таких стовпців рекомендується за можливості звести до мінімуму.
Необхідно уникати створення кластерного індексу для часто змінюваних стовпців, оскільки сервер повинен буде виконувати фізичне переміщення всіх даних в таблиці, щоб вони знаходилися у впорядкованому стані, як того вимагає кластерний індекс. Для інтенсивно змінюваних стовпців краще підходить некластерний індекс.
При створенні в таблиці первинного ключа (PRIMARY KEY) сервер автоматично створює для нього кластерний індекс, якщо його не існувало раніше або якщо при визначенні ключа не був явно вказаний інший тип індексу.
Коли ж в таблиці визначений ще і некластерний індекс, то його вказівник посилається не на фізичне положення рядка в базі даних, а на відповідний елемент кластерного індексу, що описує цей рядок, що дозволяє не перебудовувати структуру некластерних індексів всякий раз, коли кластерний індекс міняє фізичний порядок рядків в таблиці.
Унікальний індекс
Унікальність значень в стовпці, що індексується, гарантують унікальні індекси. При їх наявності сервер не дозволить вставити нове або змінити існуюче значення так, щоб в результаті цієї операції в стовпці з'явилися два однакові значення.
Унікальний індекс є своєрідною надбудовою і може бути реалізований як для кластерного, так і для некластерного індексу . В одній таблиці може існувати один унікальний кластерний і безліч унікальних некластерних індексів.
Унікальні індекси слід визначати тільки тоді, коли це дійсно необхідне. Для забезпечення цілісності даних в стовпці можна визначити обмеження цілісності UNIQUE або PRIMARY KEY, а не вдаватися до унікальних індексів. Їх використовування тільки для забезпечення цілісності даних є невиправданою витратою простору в базі даних. Крім того, на їх підтримку витрачається і процесорний час.
Засоби язика SQL пропонують декілька способів визначення індексу:
автоматичне створення індексу при створенні первинного ключа;
автоматичне створення індексу при визначенні обмеження цілісності UNIQUE ;
створення індексу за допомогою команди CREATE INDEX.
Остання команда має наступний формат:
<створення_індекса>::=
CREATE [ UNIQUE ]
[ CLUSTERED | NONCLUSTERED ]
INDEX ім’я_індекса ON ім’я_таблиці(ім’я_колонки [ASC|DESC][,...n])
[WITH [PAD_INDEX]
[[,] FILLFACTOR=фактор_заповнення]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE] ]
[ON ім’я_групи_файлів ]
Розглянемо деякі параметри приведеної команди.
Ім'я індексу повинне бути унікальним в межах таблиці, а сам індекс створюється виключно для таблиці поточної бази даних.
Параметр UNIQUE використовується при необхідності введення в певне поле тільки унікальних значень. При вказівці цього ключового слова буде створений унікальний індекс. В стовпці, що індексується, бажано заборонити зберігання значень NULL, щоб уникнути проблем, пов'язаних з унікальністю значень. Після того, як для стовпця з'явиться унікальний індекс, сервер не дозволить виконання команд INSERT і UPDATE, які приведуть до появи дублюючих значень.
Параметр CLUSTERED використовує можливість фізичної індексації даних і дозволяє провести так звану кластерну індексацію, внаслідок чого будуть відсортовані дані в самій таблиці згідно порядку цього індексу, а вся інформація, що додається, стане приводити до зміни фізичного порядку даних. Кластерним може бути тільки один індекс в таблиці.
Параметр NONCLUSTERED дозволяє створювати некластерні індекси.
Параметр FILLFACTOR здійснює настройку розбиття індексу на сторінки і помітно оптимізує роботу SQL-серверу. Коефіцієнт FILLFACTOR визначає в процентному співвідношенні розмір створюваних індексних сторінок. При цьому є назад пропорційна залежність частоти роботи з таблицею і коефіцієнта FILLFACTOR.
Параметр PAD_INDEX визначає заповнення внутрішнього простору індексу і застосовується спільно з FILLFACTOR.
Параметр DROP_EXISTING при використовуванні кластерного індексу визначає його повторне створення, що дозволяє запобігти небажаному оновленню кластерних індексів.
Параметр STATISTICS_NORECOMPUTE визначає функції автоматичного оновлення статистики для таблиці.
Параметр ім’я_групи_файлів дозволяє здійснити вибір файлівої групи, в якій знаходитиметься створюваний індекс. Використовування індексу з іншої файлівої групи підвищує продуктивність некластерних індексів у зв'язку з паралеллю виконання процесів уведення-виведення і роботи з самим індексом.
Видалення індексу виконується командою
DROP INDEX 'ім’я_індекса'[,...n]
Приклад 4.5. Створити унікальний кластерний індекс для таблиці Клієнт по стовпцю Прізвище в первинній групі файлів.
CREATE UNIQUE CLUSTERED INDEX index_klient1
ON Клієнт (Прізвище)
WITH DROP_EXISTING
ON PRIMARY
Приклад 4.5. Створення унікального кластерного індексу.
Приклад 4.6. Створити унікальний некластерний індекс для таблиці Клієнт по стовпцях Прізвище і Ім'я в первинній групі файлів. Крім того, елементи індексу будуть впорядковані по убуванню. Також заборонимо автоматичне оновлення статистики при зміні даних в таблиці і встановимо фактор заповнення індексних сторінок на рівні 30%.
CREATE UNIQUE NONCLUSTERED INDEX index_klient2
ON Клієнт (Прізвище DESC,Ім’я DESC)
WITH FILLFACTOR=30
STATISTICS_NORECOMPUTE
ON PRIMARY
Приклад 4.6. Створення унікального некластерного індексу.
Контрольні питання
Дайте загальну характеристику процесу створення бази даних.
З чого починається створення бази даних в середовищі My SQL Server?
Наведіть формальний синтаксис оператору створення бази даних в My SQL Server.
Поясніть, що означають параметри наведеного оператора.
Яким чином визначається файл бази даних в My SQL Server?
Наведіть формальний синтаксис та поясніть параметри оператора.
Яким чином можна змінити поточну базу даних?
В чому полягає сенс даної зміни?
Наведіть оператор видалення бази даних та поясніть його параметри.
Що таке таблиця з точки зору бази даних?
Чим необхідно керуватися при створення таблиці бази даних?
Наведітьсинтаксис створення таблиці в My SQL Server.
Які саме зміни можна застосувати до таблиці?
Як саме це зробити?
Дайте визначення індекса.
Перерахуйте відомі вам різновиди індексів.
Чим відрізняються названі індекси?
Як можна створити індекс?
В чому полягає призначення індексу?
Наведіть формальний синтаксис створення індексу і поясніть параметри оператору створення.