Transact-sql, індексовані уявлення

Як ви вже знаєте, існує кілька спеціальних типів індексів. Одним з таких спеціальних типів індексів є індексовані уявлення, які й розглядаються в цій статті.

Визначення уявлення завжди містить запит, який грає роль фільтра. Якщо уявлення не має індексів, то компонент Database Engine динамічно створює результуючий набір з усіх запитів, які звертаються до подання. (Вираз "динамічно" тут означає, що модифіковане вміст таблиці буде завжди відображатися у відповідному поданні.) Крім цього, якщо уявлення містить обчислення по одному або більше стовпців таблиці, то ці обчислення виконуються при кожному зверненні до подання.

Якщо інструкція SELECT уявлення обробляє велику кількість рядків з однієї або більше таблиць, динамічне створення результуючого набору запиту може знизити рівень продуктивності запиту. Якщо подібне уявлення часто використовується в запитах, рівень продуктивності можна значно підвищити, створивши кластерізованний індекс для цього подання. Створення кластерізованного індексу означає, що система матеріалізує динамічні дані в сторінках вузлів структури індексу.

Компонент Database Engine дозволяє створювати індекси для уявлень. Такі уявлення називаються індексованими уявленнями. Результуючий набір, що повертається поданням з кластерізованний індексом, зберігається в базі даних таким же чином, як і таблиця з кластерізованний індексом. Це означає, що вузли листя B + -дерева кластерізованного індексу містять сторінки даних.

Індексовані уявлення створюються за допомогою синтаксичних розширень інструкцій CREATE INDEX і CREATE VIEW. В інструкції CREATE INDEX замість імені таблиці вказується ім'я уявлення. Синтаксис інструкції CREATE VIEW розширюється пропозицією SCHEMABINDING.

Створення індексованого подання

Індексовані уявлення створюється в два етапи:

Створюється уявлення за допомогою інструкції CREATE VIEW з пропозицією SCHEMABINDING.

Створюється кластерізованний індекс для цього подання.

У прикладі нижче показаний перший крок створення індексованого подання - створення уявлення. У цьому прикладі передбачається, що таблиця Works_on має дуже великий розмір.

Таблиця Works_on бази даних SampleDb містить стовпець EnterDate, який представляє дату початку роботи співробітника над відповідним проектом. Всіх співробітників, які почали працювати над проектами в зазначений місяць, можна вибрати за допомогою уявлення, представленого в прикладі. Для вибірки цього результуючого набору Database Engine не може використовувати індекс таблиці, оскільки індекс для стовпця EnterDate визначатиме значення цього стовпця по повній датою, а не тільки по місяцю. У такому випадку можна скористатися індексованих поданням, створення якого показано в прикладі нижче:

Щоб створити уявлення індексованих, необхідно створити однозначну (унікальний) кластерізованний індекс для стовпця (стовпців) цього подання. (Як уже згадувалося раніше, кластерізованний індекс є єдиним типом індексу, який містить значення даних в своїх сторінках вузлів.) Після створення такого індексу система баз даних виділяє пам'ять для цього подання, після чого можна створювати будь-яку кількість некластерізованних індексів, оскільки тепер це уявлення розглядається як (базова) таблиця.

Індексовані уявлення можна створити тільки в тому випадку, якщо воно є детермінованим, тобто уявлення завжди повертає один і той же результуючий набір. Для цього такими параметрами інструкції SET потрібно присвоїти значення ON:

Крім цього, параметру NUMERIC_ROUNDABORT потрібно присвоїти значення off.

Перевірити, чи встановлені належним чином параметри в попередньому списку, можна кількома способами, які розглядаються пізніше. Щоб створити індексовані подання, подання повинно відповідати наступним вимогам:

всі використовувані в поданні функції (як системні, так і визначені користувачем) повинні бути детермінованими, тобто для одних і тих же аргументів вони завжди повинні повертати один і той же результат;

подання має посилатися тільки на базові таблиці;

уявлення і посилання на базову таблицю (таблиці) повинні мати одного власника і належати до однієї і тієї ж бази даних;

подання має бути створено з опцією SCHEMABINDING. Ця опція пов'язує уявлення зі схемою, що містить базові таблиці, що лежать в основі уявлення;

певні користувачем функції, на які посилається уявлення, повинні бути створені з пропозицією SCHEMABINDING;

інструкція SELECT в поданні не повинна містити наступні пропозиції, параметри та інші елементи: DISTINCT, UNION, TOP, ORDER BY, MIN, MAX, COUNT, OUTER, SUM (для виразів, що допускають значення NULL), підзапити або похідні таблиці.

Задоволення всіх цих вимог можна перевірити за допомогою функції властивостей objectproperty з параметром властивостей IsIndexable, як показано в прикладі нижче. Якщо функція повертає значення 1, то уявлення задовольняє всім вимогам для створення для нього індексу:

Зміна структури індексованого подання

Щоб видалити однозначний кластерізованний індекс в індексованих поданні, необхідно також видалити всі його некластерізованний індекси. Після видалення кластерізованного індексу уявлення система розглядає його як звичайне уявлення. При видаленні індексованого подання також видаляються всі його індекси.

Якщо ви хочете змінити традиційне уявлення на індексовані, то для нього вам потрібно створити кластерізованний індекс. Щоб зробити це, ви спочатку повинні вказати пропозицію SCHEMABINDING. Подання можна видалити, а потім відтворити, вказавши пропозицію SCHEMABINDING в інструкції CREATE SCHEMA, або ж можна створити інше уявлення, яке має такий же текст, як і існуюче уявлення, але має інше ім'я. При створенні вистави за іншим ім'ям необхідно забезпечити, щоб це подання відповідало всім вимогам для індексованих уявлень, описаних в попередньому розділі.

Редагування інформації, пов'язаної з індексованими уявленнями

Перевірити, активований чи будь-якої параметр інструкції SET. можна за допомогою функції властивостей sessionproperty. Якщо функція повертає значення 1, то зазначений параметр встановлений (тобто має значення on). У прикладі нижче показано використання цієї функції для перевірки значення параметра QUOTED_IDENTIFIER:

Найбільш простим способом є використання динамічно адміністративного уявлення sys.dm_exec_session. оскільки воно дозволяє отримати значення всіх параметрів інструкції SET, використовуючи тільки один запит. (Знову ж, якщо значення стовпця дорівнює 1, то відповідний параметр активовано.) У прикладі нижче демонструється використання цієї функції для отримання значень перших чотирьох параметрів інструкції SET:

Дізнатися, матеріалізована чи уявлення, тобто використовує воно дисковий простір чи ні, можна за допомогою системної процедури sp_spaceused. Результат виконання запиту в прикладі нижче показує, що уявлення view_enter_month використовує область пам'яті як для даних, так і для певного індексу:

Цей запит повертає наступний результуючий набір:

Переваги індексованих уявлень

Крім можливого підвищення рівня продуктивності для складних уявлень, до яких часто звертаються запити, застосування індексованих уявлень має два інших переваги:

всі зміни даних відображаються у відповідних індексованих уявленнях.

Можливо, найважливішою особливістю індексованих уявлень є те, що у запиту в поданні немає явного вказівки на використання індексу в цьому поданні. Іншими словами, якщо запит містить посилання на стовпці в базовій таблиці (або таблицях), які також існують в індексованих уявленнях, і оптимізатор запитів визначить, що найкращим способом виконання запиту буде використання індексованого подання, то він вибирає індекси уявлення таким чином, як і індекси таблиць, коли запит не посилається на них явно.

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

Крім всіх переваг, які можна отримати завдяки використанню індексованих уявлень, є також і потенційний недолік: індекси індексованих уявлень зазвичай більш складні в обслуговуванні, ніж індекси базових таблиць. Причиною цього є те, що структура однозначного кластерізованного індексу індексованого подання складніша, ніж структура відповідного індексу базової таблиці.

Можна значно підвищити рівень продуктивності наступних далі запитів, якщо проіндексувати уявлення, до яких вони звертаються:

запити, які обробляють велику кількість рядків і містять операції з'єднання або агрегатні функції;

операції з'єднання і агрегатні функції, які часто виконуються в одному або декількох запитах.

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

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