Megosztás a következőn keresztül:

Indexelt nézetek létrehozása

  • Cikk

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseAzure SQL Felügyelt Példány

Ez a cikk azt ismerteti, hogyan hozhat létre indexeket egy nézetben. A nézetben létrehozott első indexnek egyedi fürtözött indexnek kell lennie. Az egyedi fürtözött index létrehozása után több nem fürtözött indexet is létrehozhat. Ha egyedi fürtözött indexet hoz létre egy nézetben, az javítja a lekérdezési teljesítményt, mivel a nézet ugyanúgy tárolódik az adatbázisban, mint egy fürtözött indexet tartalmazó tábla. A lekérdezésoptimalizáló indexelt nézetekkel felgyorsíthatja a lekérdezés végrehajtását. A nézetre nem kell hivatkoznia a lekérdezésben ahhoz, hogy az optimalizáló ezt a nézetet helyettesítőként vegye figyelembe.

Lépések

Az indexelt nézet létrehozásához az alábbi lépések szükségesek, és kritikus fontosságúak az indexelt nézet sikeres megvalósítása szempontjából:

  1. Ellenőrizze, hogy a SET beállítások helyesek-e a nézetben hivatkozott összes meglévő táblához.
  2. A táblák és a nézet létrehozása előtt ellenőrizze, hogy a munkamenet SET beállításai megfelelően vannak-e beállítva.
  3. Ellenőrizze, hogy a nézetdefiníció determinisztikus-e.
  4. Ellenőrizze, hogy az alaptábla tulajdonosa megegyezik-e a nézet tulajdonosával.
  5. Hozza létre a nézetet a WITH SCHEMABINDING beállítással.
  6. Hozza létre az egyedi klaszteres indexet a nézetben.

Ha UPDATE, DELETE vagy INSERT műveleteket (Adatkezelési nyelv vagy DML) hajt végre nagy számú indexelt nézetet vagy kevesebb, de összetett indexelt nézetet tartalmazó táblán, az indexelt nézetekre hivatkozó nézeteket is frissíteni kell. Ennek eredményeképpen a DML-lekérdezési teljesítmény jelentősen csökkenhet, vagy bizonyos esetekben a lekérdezési terv nem is hozható létre.

Ilyen esetekben éles használat előtt tesztelje a DML-lekérdezéseket, elemezze a lekérdezési tervet, és hangolja/egyszerűsítse a DML-utasítást.

Az indexelt nézetekhez szükséges SET-beállítások

Ugyanazon kifejezés kiértékelése eltérő eredményeket eredményezhet az adatbázismotorban, ha a lekérdezés végrehajtásakor különböző SET beállítások aktívak. Ha például a SET beállítás CONCAT_NULL_YIELDS_NULLONértékre van állítva, a 'abc' + NULL kifejezés a NULLértéket adja vissza. Ha azonban CONCAT_NULL_YIELDS_NULLOFFértékre van állítva, ugyanaz a kifejezés abchoz létre.

Annak érdekében, hogy a nézetek megfelelően karbantarthatók legyenek, és konzisztens eredményeket adjanak vissza, az indexelt nézetek több SET beállításhoz rögzített értékeket igényelnek. Az alábbi táblázatban szereplő SET beállításokat a Required value oszlopban látható értékekre kell állítani, amikor a következő feltételek lépnek fel:

  • Létrejön a nézet és a hozzá tartozó indexek.
  • A nézet létrehozásakor a nézetben hivatkozott alaptáblák.
  • Ha bármilyen beszúrási, frissítési vagy törlési műveletet hajt végre az indexelt nézetben részt vevő bármely táblán. Ez a követelmény olyan műveleteket is tartalmaz, mint a tömeges másolás, a replikáció és az elosztott lekérdezések.
  • A lekérdezésoptimalizáló az indexelt nézetet használja a lekérdezésterv létrehozásához.
BEÁLLÍTÁSOK Kötelező érték Alapértelmezett kiszolgálóérték Alapértelmezett
OLE DB és ODBC érték		 Alapértelmezett
DB-Library érték
ANSI_NULLS ON ON ON OFF
ANSI_PADDING ON ON ON OFF
ANSI_WARNINGS 1 ON ON ON OFF
ARITHABORT ON ON OFF OFF
CONCAT_NULL_YIELDS_NULL ON ON ON OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER ON ON ON OFF

1 A ANSI_WARNINGS beállítása implicit módon beállítja ONARITHABORT elemet ON értékre.

Ha OLE DB- vagy ODBC-kiszolgálókapcsolatot használ, az egyetlen módosítandó érték a ARITHABORT beállítás. Minden DB-Library értéket helyesen kell beállítani a kiszolgáló szintjén a sp_configure vagy az alkalmazásból a SET paranccsal.

Fontos

Javasoljuk, hogy a ARITHABORT felhasználói beállítást állítsa ON kiszolgálószintűre, amint a számított oszlop első indexelt nézete vagy indexe létrejön a kiszolgáló bármely adatbázisában.

Determinisztikus nézetkövetelmény

Az indexelt nézet definíciójának determinisztikuskell lennie. A nézet determinisztikus, ha a kiválasztási listában szereplő összes kifejezés, valamint a WHERE és GROUP BY záradékok determinisztikusak. A determinisztikus kifejezések mindig ugyanazt az eredményt adnak vissza, amikor egy adott bemeneti értékkészlettel értékelik ki őket. A determinisztikus kifejezésekben csak determinisztikus függvények vehetnek részt. A DATEADD függvény például determinisztikus, mert a három paraméterhez tartozó argumentumértékek adott halmazához mindig ugyanazt az eredményt adja vissza. GETDATE nem determinisztikus, mert mindig ugyanazzal az argumentummal hívja meg, de a visszaadott érték minden végrehajtáskor megváltozik.

Annak megállapításához, hogy egy nézetoszlop determinisztikus-e, használja a IsDeterministic függvény tulajdonságát. Annak megállapításához, hogy a sémakötéssel rendelkező nézetben egy determinisztikus oszlop pontos-e, használja a IsPrecise függvény COLUMNPROPERTY tulajdonságát. COLUMNPROPERTY 1 ad vissza, ha TRUE, 0FALSE, és NULL érvénytelen bemenet esetén. Ez azt jelenti, hogy az oszlop nem determinisztikus vagy nem pontos.

Még ha egy kifejezés determinisztikus is, ha lebegőpontos kifejezéseket tartalmaz, a pontos eredmény a processzor architektúrájától vagy a mikrokód verziójától függ. Az adatintegritás biztosítása érdekében az ilyen kifejezések csak indexelt nézetek nem kulcsoszlopaiként vehetnek részt. A lebegőpontos kifejezéseket nem tartalmazó determinisztikus kifejezéseket pontosnevezik. Csak pontos determinisztikus kifejezések vehetnek részt a kulcsoszlopokban és az indexelt nézetek WHERE vagy GROUP BY záradékaiban.

További követelmények

A SET lehetőségek és a determinisztikus függvénykövetelmények mellett a következő követelményeknek is teljesülniük kell

  • A CREATE INDEX végrehajtó felhasználónak kell lennie a nézet tulajdonosának.

  • Az index létrehozásakor az IGNORE_DUP_KEY index beállításának OFF (az alapértelmezett beállítás) értékre kell állítania.

  • A táblákra a nézetdefinícióban kétrészes neveknek kell hivatkoznia, <schema>.<tablename>.

  • A nézetben hivatkozott felhasználó által definiált függvényeket a WITH SCHEMABINDING beállítással kell létrehozni.

  • A nézetben hivatkozott összes felhasználó által definiált függvényre kétrészes névvel kell hivatkozni, <schema>.<function>.

  • A felhasználó által definiált függvény adathozzáférési tulajdonságának NO SQLkell lennie, a külső hozzáférési tulajdonságnak pedig NOkell lennie.

  • A közös nyelvi futtatókörnyezeti (CLR) függvények megjelenhetnek a nézet kiválasztási listájában, de nem részei a fürtözött indexkulcs definíciójának. A CLR-függvények nem jelennek meg a nézet WHERE záradékában vagy a ON művelet JOIN záradékában a nézetben.

  • A nézetdefinícióban használt, felhasználó által definiált CLR-típusok CLR-függvényeinek és metódusainak az alábbi táblázatban látható tulajdonságoknak kell lenniük.

    Ingatlan Jegyzet
    DETERMINISZTIKUS = IGAZ Explicit módon deklarálni kell a Microsoft .NET Framework metódus attribútumaként.
    PREZÍC = IGAZ Kifejezetten a .NET-keretrendszer metódus attribútumaként kell deklarálni.
    ADATHOZZÁFÉRÉS = NEM SQL A DataAccess attribútum DataAccessKind.None és SystemDataAccess attribútum SystemDataAccessKind.Noneértékre állításával határozható meg.
    KÜLSŐ HOZZÁFÉRÉS = NEM Ez a tulajdonság alapértelmezés szerint NEM értékű a CLR-rutinok esetében.

  • A nézetet a WITH SCHEMABINDING beállítással kell létrehozni.

  • A nézetnek csak olyan alaptáblákra kell hivatkoznia, amelyek ugyanabban az adatbázisban találhatók, mint a nézet. A nézet nem hivatkozhat más nézetekre.

  • Ha GROUP BY van jelen, a VIEW definíciónak COUNT_BIG(*) kell tartalmaznia, és nem tartalmazhat HAVING. Ezek a GROUP BY korlátozások csak az indexelt nézetdefinícióra vonatkoznak. Egy lekérdezés akkor is használhat indexelt nézetet a végrehajtási tervében, ha nem felel meg ezeknek a GROUP BY korlátozásoknak.

  • Ha a nézetdefiníció tartalmaz egy GROUP BY záradékot, az egyedi fürtözött index kulcsa csak a GROUP BY záradékban megadott oszlopokra hivatkozhat.

  • A nézetdefiníció SELECT utasítása nem tartalmazhat a következő Transact-SQL szintaxist:

    Transact-SQL függvény Lehetséges alternatívák
    COUNT A(z) COUNT_BIG használata
    ROWSET függvények (OPENDATASOURCE, OPENQUERY, OPENROWSETés OPENXML)
    Számtani középérték (AVG) COUNT_BIG és SUM használata különálló oszlopokként
    Statisztikai összesítő függvények (STDEV,STDEVP,VARésVARP)
    null értékű kifejezésre hivatkozó SUM függvény A ISNULL belüli SUM() használata a kifejezés null értékűvé nyilvánításához
    Egyéb összesítő függvények (MIN,MAX,CHECKSUM_AGGésSTRING_AGG)
    Felhasználó által definiált összesítő függvények (SQL CLR)
    SELECT záradék Transact-SQL elem Lehetséges alternatíva
    WITH cte AS Gyakori táblakifejezések (CTE) WITH
    SELECT Albekérdezések
    SELECT SELECT [ <table>. ] * Az oszlopok kifejezett elnevezése
    SELECT SELECT DISTINCT A(z) GROUP BY használata
    SELECT SELECT TOP
    SELECT OVER klauzula, amely magában foglalja a rangsorolási vagy összesített ablakfüggvényeket
    FROM LEFT OUTER JOIN
    FROM RIGHT OUTER JOIN
    FROM FULL OUTER JOIN
    FROM OUTER APPLY
    FROM CROSS APPLY
    FROM Származtatott táblakifejezések (azaz SELECT használata a FROM záradékban)
    FROM Öncsatlakozások
    FROM Táblaváltozók
    FROM Beágyazott táblaértékfüggvény
    FROM Többutasítássoros táblaértékes függvény
    FROM PIVOT, UNPIVOT
    FROM TABLESAMPLE
    FROM FOR SYSTEM_TIME Az időelőzménytáblázat közvetlen lekérdezése
    WHERE Teljes szöveges predikátumok (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
    GROUP BY CUBE, ROLLUPvagy GROUPING SETS operátorok Külön indexelt nézetek definiálása GROUP BY oszlopok egyes kombinációihoz
    GROUP BY HAVING
    Operátorok beállítása UNION, UNION ALL, EXCEPT, INTERSECT Használja a OR, AND NOTés AND értékeket a WHERE záradékban, ebben a sorrendben.
    ORDER BY ORDER BY
    ORDER BY OFFSET
    Forrásoszlop típusa Lehetséges alternatíva
    Elavult nagy értékű oszloptípusok (szöveg, ntextés kép) Oszlopok áttelepítése a varchar(max), nvarchar(max), és varbinary(max) típusokra.
    XML vagy FILESTREAM oszlopok
    lebegőpontos1 oszlop az indexkulcsban
    Ritka oszlopkészletek

    1 Az indexelt nézet lebegőpontos oszlopokat tartalmazhat; az ilyen oszlopok azonban nem vehetők fel a klaszterezett indexkulcsba.

    Fontos

    Az indexelt nézetek nem támogatottak az időleges lekérdezéseken (FOR SYSTEM_TIME záradékot használó lekérdezéseken).

Javaslatok a datetime és a smalldatetime szolgáltatáshoz

Ha datetime és smalldatetime karakterlánc literálokra hivatkozik indexelt nézetekben, javasoljuk, hogy egyértelmű dátumformátum-stílus használatával explicit módon konvertálja a literálokat arra a dátumtípusra, amelyet szeretne. A determinisztikus dátumformátum-stílusok listáját CAST és CONVERTcímű témakörben találja. A determinisztikus és nemdeterminista kifejezésekről a lap Szempontok szakaszában talál további információt.

A datetime vagy smalldatetime kifejezések, amelyek karaktersztringek implicit konvertálását tartalmazzák, nemdeterminisztikusnak számítanak. További információ: Konstans dátumsztringek nemdeterminisztikus konvertálása DÁTUM értékekké.

Teljesítménybeli szempontok indexelt nézetekkel

Ha DML-t (például UPDATE, DELETE vagy INSERT) hajt végre nagy számú indexelt nézet vagy kevesebb, de összetett indexelt nézet által hivatkozott táblán, ezeket az indexelt nézeteket is frissíteni kell a DML végrehajtása során. Ennek eredményeképpen a DML-lekérdezési teljesítmény jelentősen csökkenhet, vagy bizonyos esetekben a lekérdezési terv nem is hozható létre. Ilyen esetekben éles használat előtt tesztelje a DML-lekérdezéseket, elemezze a lekérdezési tervet, és hangolja/egyszerűsítse a DML-utasítást.

Ha meg szeretné akadályozni, hogy az adatbázismotor indexelt nézeteket használjon, adja meg a OPTION (EXPAND VIEWS) tippet a lekérdezéshez. Ha a felsorolt beállítások bármelyike helytelenül van beállítva, ez a beállítás megakadályozza, hogy az optimalizáló az indexeket használja a nézetekben. További információ a OPTION (EXPAND VIEWS) tippről: SELECT.

További szempontok

  • Az indexelt nézetben lévő oszlopok large_value_types_out_of_row beállításának beállítása az alaptábla megfelelő oszlopának beállításától öröklődik. Ez az érték sp_tableoptionhasználatával van beállítva. A kifejezésekből formázott oszlopok alapértelmezett beállítása a 0. Ez azt jelenti, hogy a nagy értéktípusok egymás után vannak tárolva.

  • Az indexelt nézetek particionált táblákon hozhatók létre, és maguk is particionálhatók.

  • A nézet összes indexe eltávolításra kerül, amikor a nézetet eltávolítják. A fürtözött index törlésekor a rendszer törli a nézet összes nem fürtözött indexét és automatikusan létrehozott statisztikáit. A nézet felhasználó által létrehozott statisztikái megmaradnak. A nem klaszterezett indexek egyenként törölhetők. A fürtözött index nézetben való elvetése eltávolítja a tárolt eredményhalmazt, és az optimalizáló visszatér a nézet normál nézetként való feldolgozásához.

  • A táblákon és nézeteken lévő indexek letilthatók. Ha egy tábla klaszterezett indexe le van tiltva, a táblához társított nézetek indexei is le lesznek tiltva.

Engedélyek

A nézet létrehozásához a felhasználónak rendelkeznie kell a CREATE VIEW engedéllyel az adatbázisban, és ALTER engedélyt arra a sémára, amelyben a nézet létrejön. Ha az alaptábla egy másik sémán belül található, a tábla REFERENCES engedélye minimálisan szükséges. Ha az indexet létrehozó felhasználó eltér a nézetet létrehozó felhasználóktól, az index létrehozásához csak a nézet ALTER engedélyére van szükség (a séma ALTER vonatkozik rá).

Indexek csak olyan nézeteken hozhatók létre, amelyek tulajdonosa megegyezik a hivatkozott táblával vagy táblázatokkal. Ezt a fogalmat a nézet és a táblák közötti érintetlen tulajdonjogi láncnak is nevezik. Ha a tábla és a nézet ugyanabban a sémában található, általában ugyanaz a sématulajdonos vonatkozik a sémán belüli összes objektumra. Ezért létrehozhat egy nézetet, és nem lehet a nézet tulajdonosa. Másfelől előfordulhat, hogy a sémán belüli egyes objektumoknak eltérő explicit tulajdonosai vannak. A principal_id oszlop a sys.tables-ben akkor tartalmaz értéket, ha a tulajdonos eltér a séma tulajdonosától.

Indexelt nézet létrehozása: T-SQL-példa

Az alábbi példa létrehoz egy nézetet és egy indexet ezen a nézetben, a AdventureWorks adatbázisban.

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
    ANSI_WARNINGS,
    CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,
    QUOTED_IDENTIFIER,
    ANSI_NULLS ON;

--Create view with SCHEMABINDING.
IF OBJECT_ID('Sales.vOrders', 'view') IS NOT NULL
    DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
    WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate,
    ProductID,
    COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od,
    Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate,
    ProductID;
GO

--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.vOrders (
    OrderDate,
    ProductID
);
GO

A következő két lekérdezés bemutatja, hogyan használható az indexelt nézet, annak ellenére, hogy a nézet nincs megadva a FROM záradékban.

--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
    OrderDate,
    ProductID
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '05/01/2012', 101)
WHERE od.ProductID BETWEEN 700
        AND 800
GROUP BY OrderDate,
    ProductID
ORDER BY Rev DESC;
GO

--This query will also use the above indexed view.
SELECT OrderDate,
    SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
    ON od.SalesOrderID = o.SalesOrderID
        AND o.OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
        AND o.OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
GROUP BY OrderDate
ORDER BY OrderDate ASC;

Végül ez a példa közvetlenül az indexelt nézetből való lekérdezést mutatja be. Az indexelt nézet lekérdezésoptimalizáló általi automatikus használata csak az SQL Server adott kiadásaiban támogatott. Az SQL Server Standard kiadásban a NOEXPAND lekérdezési tippet kell használnia az indexelt nézet közvetlen lekérdezéséhez. Az Azure SQL Database és az Azure SQL Managed Instance támogatja az indexelt nézetek automatikus használatát a NOEXPAND tipp megadása nélkül. További információkért lásd: Tábla-javallatok (Transact-SQL).

--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
    AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;

További információ: CREATE VIEW.

Kapcsolódó tartalom