Apr 6 2010

View-urile indexate

Categorie: Clustered | Index | SQL Server | T-SQL | TutorialCatalin Dumitru @ 06:47

View-urile pot fi unelte foarte eficiente pentru cresterea vitezei de executie a interogarilor sau pentru a simplifica interogari complexe. In acest articol vom discuta despre un anumit tip de view-uri si anume view-urile indexate. O sa vedem ce sunt, cum se creaza, unde pot fi folosite si ce constrangeri avem atunci cand le folosim.

View-urile sunt tabele virtuale definite de interogarea uneia sau mai multor tabele. Rezultatul unui view standard nu este stocat in baza de date, in schimb, rezultatul este determinat la momentul executiei.
Crearea unui index clustred unic transforma view-ul standard intr-unul indexat. Indexul clustered este stocat in  baza de date si va fi actualizat la fel ca orice alt index clustered, oferind opimizatorului inca o potentiala sansa de a optimiza executia unei interogari utilizand indexul.

Interogarile in care nu se specifica utilizarea indexului pot beneficia de ajutorul acestuia, in versiunile enterprise si developer, optimizatorul va decide daca foloseste sau nu indexul, in alte versiuni de SQL Server, va trebui folosit hint-ul NOEXPAND pentru a beneficia de index.

Daca view-ul poate beneficia de ajutorul mai multor indecsi, atunci pot fi creati mai multi indecsi non-clustered. Acestia vor oferi mai mult ajutor optimizatorului pentru a executa mai rapid interogarile care se bazeaza pe coloane view-ului incluse in indecsi.

Un view indexat are beneficii si costuri. Costul unui view indexat se afla pe intretinerea indexului (a tuturor indecsilor atat a celui clustered cat si a celor non-clustered). Cel mai greu de intretinut este atunci cand tabela sau tebelele peste care sunt create unul sau mai multe view-uri indexate fac obiectul operatiunilor de adaugare, modificare, stergere (insert, update, delete) de date. Trebuie foarte mare atentie atunci cand se decide adaugarea unui index pe un view astfel incat beneficiul obtinut din rularea mai rapida a view-ului sa fie net superior costului (adaugare, modificare, stergere de date mai incete).

Tipic pentru acest gen de view-uri sunt mediile precum depozitele de date sau a subseturilor (datawarehouse, data marts), mediilor OLAP (on line analytical processing) si altele. Mediile tranzactionale sunt mai putin specifice view-urilor indexate. In localizarea unui potential index ne putem uita dupa utilizarea de join-uri repetitive utilizand aceleasi coloane, join-uri intre tabele mari (cu numar mare de coloane), agregari de tabele mari si repetarea de interogari. Atentie asupra view-urilor care produc mai multe randuri decat numarul de randuri din tabelele sursa.

Un view care urmeaza sa fie indexat trebuie sa fie creat cu optiunea schemabinding. Asta inseamna ca odata ce view-ul indexat este creat, tabelele care fac obiectul definitiei view-ului nu mai pot fi alterate in niciun fel care sa afecteze indexul decat daca view-ul este sters si apoi recreat. Mai inseamna ca fiecare tabela trebuie prefixata de schema din care acestea fac parte.

Un exemplu de utilizare a unui view indexat este redat mai jos:

CREATE TABLE Produse(
 ID              INT PRIMARY KEY,
 Produs       VARCHAR(20),
 Cantitate   INT
)

GO
CREATE VIEW wProduse WITH SCHEMABINDING AS
SELECT ID, Produs, Cantitate
  FROM dbo.Produse
 WHERE Cantitate >= 10

GO
CREATE UNIQUE CLUSTERED INDEX idx_wProduse ON wProduse(Cantitate)

Odata ce indexul a fost creat, rezultatul indexului este stocat in baza de date la fel ca orice alt index clustered. Orice interogare care va utiliza indexul explicit va beneficia de avantajele utilizarii indexului. Chiar daca o interogare nu foloseste indexul, optimizatorul are optiunea de a-l folosi daca acesta ofera o performanta sporita. Daca se doreste ca optimizatorul sa foloseasca mereu indexul atunci trebuie precizat hint-ul NOEXPAND. Iar daca se doreste sa se vada cum se executa un view indexat dar fara sa se tina cont de index se poate folosi hint-ul EXPAND VIEWS, acest hint ne scuteste de rularea interogarii din definitia view-ului.
Un index nu poate fi creat pe orice view. Cateva constrangeri trebuiesc satisfacute pentru ca un index sa fie creat. Deja am precizat despre optiunea WITH SCHEMABINDING si despre faptul ca tabelele referite trebuie sa fie prefixate de schema din care acestea fac parte. In afara de aceste conditii mai trebuiesc satisfacute si urmatoarele:

  1. QUOTED_IDENTIFIER si CONCAT_NULL_YIELDS_NULL trebuiesc setate pe ON;
  2. Sesiunea care creaza indexul trebuie sa indeplineasca punctul 1 (nu doar sesiunile care folosesc efectiv view-ul);
  3. Orice functie definita de utilizator (UDF) care utilizeaza view-ul trebuie sa aibe optiunea WITH SCHEMABINDING;
  4. View-ul trebuie sa fie deterministic (rularile multiple cu acelasi input trebuie sa genereze acelasi set de rezultate);
  5. Crearea tabelelor referite trebuie sa aibe setata corect setarea ANSI_NULLS;
  6. Rezultatul rularii unui view este stocat fizic in baza de date, de aceea, spatul de stocare poate fi considerat ca fiind o constrangere.


In afara de aceste constrangeri mai sunt si constrangeri legate de continutul unui view. Spre exemplu, view-ul nu trebuie sa contina EXISTS sau NOT EXISTS, OUTER JOIN, COUNT(*), MIN, MAX, subinterogari, hint-uri la tabele, TOP, UNION si altele.

View-urile indexate sunt un mod interesant prin care se poate creste performanta interogarilor in anumite circumstante. Totusi, trebuie constientizat si efectul negativ pe care il poate aduce in mediile tranzactionale. Daca mediul in care se creaza un view este mai degraba unul pe care ruleaza introgari si mai putin instructiuni de alterare de date, view-urile indexate reprezinta o buna alegere de crestere a performantei. 

Etichete: , , ,