Aracılığıyla paylaş

Dizinli görünümler oluşturma

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen ÖrneğiMicrosoft Fabric'te SQL veritabanı

Bu makalede, görünümde dizin oluşturma açıklanmaktadır. Görünümde oluşturulan ilk dizin benzersiz bir kümelenmiş dizin olmalıdır. Benzersiz kümelenmiş dizin oluşturulduktan sonra, daha fazla kümelenmemiş dizin oluşturabilirsiniz. Görünüm, kümelenmiş dizine sahip bir tablonun depolandığı şekilde veritabanında depolandığından, görünümde benzersiz kümelenmiş dizin oluşturulması sorgu performansını artırır. Sorgu iyileştiricisi, sorgu yürütmeyi hızlandırmak için dizinli görünümleri kullanabilir. Optimizatörün bir değişiklik için bu görünümü dikkate alması için, görünüme sorguda başvuru yapılmasına gerek yoktur.

Adımlar

Dizinlenmiş görünüm oluşturmak için aşağıdaki adımlar gereklidir ve dizinlenmiş görünümün başarılı bir şekilde uygulanması için kritik öneme sahiptir:

  1. Görünümde başvurulacak tüm mevcut tablolar için SET seçeneklerinin doğru olduğunu doğrulayın.
  2. Herhangi bir tablo ve görünüm oluşturmadan önce oturum için SET seçeneklerinin doğru ayarlandığını doğrulayın.
  3. Görünüm tanımının belirlenimci olduğunu doğrulayın.
  4. Görünümün ve temel tablonun aynı sahibe sahip olduğunu doğrulayın.
  5. WITH SCHEMABINDING seçeneğini kullanarak görünümü oluşturun.
  6. Görünümde benzersiz kümelenmiş dizini oluşturun.

UPDATE, DELETE veya INSERT işlemlerini (Veri İşleme Dili veya DML) çok sayıda dizine alınmış görünüm veya daha az ama karmaşık dizinlenmiş görünümler tarafından başvurulan bir tabloda yürütürken, başvurulan dizine alınmış görünümlerin de güncelleştirilmiş olması gerekir. Sonuç olarak, DML sorgu performansı önemli ölçüde düşebilir veya bazı durumlarda sorgu planı oluşturulamaz.

Bu tür senaryolarda üretim kullanımı öncesinde DML sorgularınızı test edin, sorgu planını analiz edin ve DML deyimini ayarlayın/basitleştirin.

Dizinlenmiş görünümler için gerekli SET seçenekleri

Sorgu yürütülürken farklı SET seçenekleri etkin olduğunda, aynı ifadenin değerlendirilmesi Veritabanı Altyapısı'nda farklı sonuçlara neden olabilir. Örneğin, SET seçeneği CONCAT_NULL_YIELDS_NULLONolarak ayarlandıktan sonra, 'abc' + NULL ifadesi NULLdeğerini döndürür. Ancak, CONCAT_NULL_YIELDS_NULLOFFolarak ayarlandıktan sonra aynı ifade abcoluşturur.

Görünümlerin doğru korunaabildiğinden ve tutarlı sonuçlar döndürediğinden emin olmak için, dizine alınan görünümler çeşitli SET seçenekleri için sabit değerler gerektirir. Aşağıdaki tablodaki SET seçenekleri, aşağıdaki koşullar oluştuğunda Required value sütununda gösterilen değerlere ayarlanmalıdır:

  • Görünüm ve görünümdeki sonraki dizinler oluşturulur.
  • Görünüm oluşturulduğunda başvurulan temel tablolar.
  • Dizinli görünüme katılan herhangi bir tabloda herhangi bir ekleme, güncelleştirme veya silme işlemi gerçekleştirildiğinde. Bu gereksinim toplu kopyalama, çoğaltma ve dağıtılmış sorgular gibi işlemleri içerir.
  • Dizine alınan görünüm, sorgu planını oluşturmak için sorgu iyileştiricisi tarafından kullanılır.
SET seçenekleri Gerekli değer Varsayılan sunucu değeri Varsayılan
OLE DB ve ODBC değeri		 Varsayılan
DB-Library değeri
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 Ayarını ANSI_WARNINGSON olarak yapmak, örtük olarak ARITHABORT'ü ONolarak ayarlar.

OLE DB veya ODBC sunucu bağlantısı kullanıyorsanız, değiştirilmesi gereken tek değer ARITHABORT ayarıdır. Tüm DB-Library değerleri, sp_configure kullanılarak sunucu düzeyinde veya SET komutu kullanılarak uygulamadan doğru şekilde ayarlanmalıdır.

Önemli

Sunucudaki herhangi bir veritabanında hesaplanan bir sütun üzerinde ilk dizinlenmiş görünüm veya dizin oluşturulur oluşturulmaz, ARITHABORT kullanıcı seçeneğini sunucu genelinde ON olarak ayarlamanızı kesinlikle öneririz.

Belirleyici görünüm gereksinimi

Dizinli görünümün tanımı belirleyiciolmalıdır. Bir görünüm, seçme listesindeki tüm ifadeler ve WHERE ile GROUP BY ibareleri deterministikse deterministiktir. Belirli bir giriş değerleri kümesiyle değerlendirildiklerinde deterministik ifadeler her zaman aynı sonucu döndürür. Yalnızca belirleyici işlevler, belirleyici ifadelere katılabilir. Örneğin, DATEADD işlevi her zaman üç parametresi için herhangi bir bağımsız değişken değeri kümesi için aynı sonucu döndürdüğünden belirleyicidir. GETDATE, her zaman aynı bağımsız değişkenle çağrıldığı için deterministik değildir, ancak her seferinde çalıştırıldığında döndürdüğü değer değişir.

Görünüm sütununun belirleyici olup olmadığını belirlemek için IsDeterministic işlevinin özelliğini kullanın. Şema bağlamalı bir görünümdeki belirleyici sütunun kesin olup olmadığını belirlemek için IsPrecise işlevinin COLUMNPROPERTY özelliğini kullanın. COLUMNPROPERTY, 1ise TRUE, 0ise FALSE ve geçerli olmayan girişler için NULL döndürür. Bu, sütunun belirleyici olmadığı veya kesin olmadığı anlamına gelir.

İfade belirleyici olsa bile, float ifadeleri içeriyorsa, tam sonuç işlemci mimarisine veya mikro kodun sürümüne bağlıdır. Veri bütünlüğünü sağlamak için, bu ifadeler yalnızca dizine alınan görünümlerin anahtar olmayan sütunları olarak katılabilir. Kayan ifadeler içermeyen deterministik ifadeler, hassas olarak adlandırılır. Yalnızca kesin belirleyici ifadeler, temel sütunlara ve dizinli görünümlerin WHERE veya GROUP BY yan tümcelerine katılabilir.

Ek gereksinimler

SET seçeneklerine ve belirleyici işlev gereksinimlerine ek olarak aşağıdaki gereksinimler de karşılanmalıdır

  • CREATE INDEX yürüten kullanıcının görünümün sahibi olması gerekir.

  • Dizini oluşturduğunuzda, IGNORE_DUP_KEY dizin seçeneği OFF (varsayılan ayar) olarak ayarlanmalıdır.

  • Tablolara görünüm tanımında <schema>.<tablename>iki parçalı adlarla başvurulmalıdır.

  • Görünümde başvuruda bulunan kullanıcı tanımlı işlevler, WITH SCHEMABINDING seçeneği kullanılarak oluşturulmalıdır.

  • Görünümde referans verilen kullanıcı tanımlı işlevlere, <schema>.<function>iki parçalı adlarla atıfta bulunulmalıdır.

  • Kullanıcı tanımlı bir işlevin veri erişim özelliği NO SQLve dış erişim özelliği NOolmalıdır.

  • Ortak dil çalışma zamanı (CLR) işlevleri görünümün seçme listesinde görünebilir, ancak kümelenmiş dizin anahtarının tanımının bir parçası olamaz. CLR işlevleri görünümün WHERE yan tümcesinde veya görünümdeki bir ON işleminin JOIN yan tümcesinde görüntülenemez.

  • Görünüm tanımında kullanılan CLR kullanıcı tanımlı türlerin CLR işlevleri ve yöntemleri, aşağıdaki tabloda gösterildiği gibi ayarlanmış özelliklere sahip olmalıdır.

    Mülk Not
    DETERMINISTİK = DOĞRU Microsoft .NET Framework yönteminin özniteliği olarak açıkça bildirilmelidir.
    KESİN = DOĞRU .NET Framework yönteminin özniteliği olarak açıkça bildirilmelidir.
    VERİYE ERİŞİM = SQL YOK DataAccess özniteliğini DataAccessKind.None ve SystemDataAccess özniteliğini SystemDataAccessKind.Noneolarak ayarlayarak belirlenir.
    Harici Erişim = Hayır Bu özellik, CLR yordamları için varsayılan olarak HAYIR olarak ayarlır.

  • Görünüm, WITH SCHEMABINDING seçeneği kullanılarak oluşturulmalıdır.

  • Görünümün yalnızca görünümle aynı veritabanındaki temel tablolara başvurması gerekir. Görünüm diğer görünümlere başvuramaz.

  • GROUP BY varsa, VIEW tanımı COUNT_BIG(*) içermeli ve HAVINGiçermemelidir. Bu GROUP BY kısıtlamaları yalnızca dizinlenmiş görünüm tanımı için geçerlidir. Sorgu, bu GROUP BY kısıtlamaları karşılamasa bile yürütme planında dizinli bir görünüm kullanabilir.

  • Görünüm tanımı bir GROUP BY yan tümcesi içeriyorsa, benzersiz kümelenmiş dizinin anahtarı yalnızca GROUP BY yan tümcesinde belirtilen sütunlara başvurabilir.

  • Görünüm tanımındaki SELECT deyimi aşağıdaki Transact-SQL söz dizimini içermemelidir:

    Transact-SQL işlevi Olası alternatifler
    COUNT COUNT_BIG kullanma
    ROWSET işlevleri (OPENDATASOURCE, OPENQUERY, OPENROWSETve OPENXML)
    Aritmetik ortalama (AVG) COUNT_BIG ve SUM ayrı sütunlar olarak kullanma
    İstatistiksel toplama işlevleri (STDEV,STDEVP,VAR ve VARP)
    nullable bir ifadeye başvuran SUM işlevi İfadeyi null edilemez hale getirmek için ISNULL’ı SUM() içinde kullanın
    Diğer toplama işlevleri (MIN,MAX,CHECKSUM_AGG ve STRING_AGG)
    Kullanıcı tanımlı toplama işlevleri (SQL CLR)
    SELECT yan tümcesi Transact-SQL öğesi Olası alternatif
    WITH cte AS Ortak tablo ifadeleri (CTE) WITH
    SELECT Alt Sorgular
    SELECT SELECT [ <table>. ] * Sütunları açıkça adlandır
    SELECT SELECT DISTINCT GROUP BY kullanma
    SELECT SELECT TOP
    SELECT Derecelendirme veya toplama penceresi işlevlerini içeren ifade OVER
    FROM LEFT OUTER JOIN
    FROM RIGHT OUTER JOIN
    FROM FULL OUTER JOIN
    FROM OUTER APPLY
    FROM CROSS APPLY
    FROM Türetilmiş tablo ifadeleri (yani SELECT'ın FROM yan tümcesinde kullanılması)
    FROM Kendi kendine birleşimler
    FROM Tablo değişkenleri
    FROM Satır içi tablo değerli fonksiyon
    FROM Çok deyimli tablo değerli fonksiyon
    FROM PIVOT, UNPIVOT
    FROM TABLESAMPLE
    FROM FOR SYSTEM_TIME Zamana bağlı geçmiş tablosunu doğrudan sorgulama
    WHERE Tam metin önkoşulları (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)
    GROUP BY CUBE, ROLLUPveya GROUPING SETS işleçleri her GROUP BY sütun bileşimi için ayrı dizinli görünümler tanımlama
    GROUP BY HAVING
    İşleçleri ayarlama UNION, UNION ALL, EXCEPT, INTERSECT OR yan tümcesinde sırasıyla AND NOT, ANDve WHERE kullanın
    ORDER BY ORDER BY
    ORDER BY OFFSET
    Kaynak sütun türü Olası alternatif
    Kullanım dışı bırakılan büyük değer sütun türleri (metin, ntextve resim) Sütunları sırasıyla varchar(max), nvarchar(max)ve varbinary(max) taşıyın.
    xml veya FILESTREAM sütunlarını
    kayandizin anahtarında 1 sütun
    Seyrek sütun kümeleri

    1 Dizinli görünüm kayan sütun içerebilir; ancak bu tür sütunlar kümelenmiş dizin anahtarına eklenemez.

    Önemli

    Dizine alınan görünümler, zamansal sorguların (FOR SYSTEM_TIME yan tümcesi kullanan sorgular) üzerinde desteklenmez.

datetime ve smalldatetime için öneriler

dizinli görünümlerde datetime ve smalldatetime dize değişmez değerlerine başvurduğunuzda, belirleyici bir tarih biçimi stili kullanarak değişmez değeri istediğiniz tarih türüne açıkça dönüştürmenizi öneririz. Belirleyici olan tarih biçimi stillerinin listesi için bkz. CAST ve CONVERT. Belirleyici ve belirleyici olmayan ifadeler hakkında daha fazla bilgi için bu sayfadaki Önemli Noktalar bölümüne bakın.

Karakter dizelerinin datetime veya smalldatetime'e örtük olarak dönüştürüldüğü ifadeler, belirsiz oldukları kabul edilir. Daha fazla bilgi için bkz. Değişmez tarih dizelerini DATE değerlerine dönüştürme.

Dizinli görünümlerle ilgili performans dikkate alınması gerekenler

DML'yi (örneğin, UPDATE, DELETE veya INSERT) çok sayıda dizinli görünüm veya daha az ama karmaşık dizinlenmiş görünümler tarafından başvuruda bulunılan bir tabloda yürütürken, DML yürütmesi sırasında bu dizinlenmiş görünümlerin de güncelleştirilmesi gerekir. Sonuç olarak, DML sorgu performansı önemli ölçüde düşebilir veya bazı durumlarda sorgu planı oluşturulamaz. Bu tür senaryolarda üretim kullanımı öncesinde DML sorgularınızı test edin, sorgu planını analiz edin ve DML deyimini ayarlayın/basitleştirin.

Veritabanı Altyapısı'nın dizinli görünümleri kullanmasını önlemek için sorguya OPTION (EXPAND VIEWS) ipucunu ekleyin. Ayrıca, listelenen seçeneklerden herhangi biri yanlış ayarlanırsa, bu seçenek iyileştiricinin görünümlerde dizinleri kullanmasını engeller. OPTION (EXPAND VIEWS) ipucu hakkında daha fazla bilgi için SELECT'ye bakınız.

Dikkat edilmesi gereken ek noktalar

  • Dizinli görünümdeki sütunların large_value_types_out_of_row seçeneğinin ayarı, temel tablodaki ilgili sütunun ayarından devralınır. Bu değer sp_tableoptionkullanılarak ayarlanır. İfadelerden oluşturulan sütunlar için varsayılan ayar 0. Bu, büyük değer türlerinin satır içinde depolandığı anlamına gelir.

  • Dizinli görünümler bölümlenmiş bir tabloda oluşturulabilir ve bölümlenebilir.

  • Bir görünüm silindiğinde, görünümdeki tüm dizinler silinir. Kümelenmiş dizin bırakıldığında, görünümdeki tüm kümelenmemiş dizinler ve otomatik olarak oluşturulan istatistikler bırakılır. Görünümde kullanıcı tarafından oluşturulan istatistikler korunur. Kümelenmemiş dizinler tek tek bırakılabilir. Görünüm üzerindeki kümelenmiş indeks kaldırıldığında, depolanan sonuç kümesi kaldırılır ve optimizatör, görünümü standart bir görünüm gibi işlemeye geri döner.

  • Tablo ve görünümlerde dizinler devre dışı bırakılabilir. Bir tablodaki kümelenmiş dizin devre dışı bırakıldığında, tabloyla ilişkili görünümlerde dizinler de devre dışı bırakılır.

İzinler

Görünümü oluşturmak için kullanıcının veritabanında CREATE VIEW iznini tutması ve görünümün oluşturulduğu şemada ALTER izni olması gerekir. Temel tablo farklı bir şemada yer alırsa, tablodaki REFERENCES izni en düşük düzeyde gereklidir. Dizini oluşturan kullanıcı görünümü oluşturan kullanıcılardan farklıysa, yalnızca dizin oluşturma için görünümde ALTER izni gerekir (şemadaki ALTER kapsamındadır).

Dizinler yalnızca atıfta bulunulan tablo veya tablolarla aynı sahibine sahip görünümlerde oluşturulabilir. Bu kavram, tablolar ile görünüm arasında bozulmamış bir sahiplik zinciri olarak da adlandırılır. Genellikle, tablo ve görünüm aynı şema içinde bulunduğunda, aynı şema sahibi şema içindeki tüm nesneler için geçerlidir. Bu nedenle, bir görünüm oluşturmak ancak görünümün sahibi olmamak mümkündür. Öte yandan, şema içindeki tek tek nesnelerin farklı açık sahiplere sahip olması da mümkündür. principal_id'daki sys.tables sütunu, sahip şema sahibinden farklıysa bir değer içerir.

Dizinli görünüm oluşturma: T-SQL örneği

Aşağıdaki örnek, AdventureWorks veritabanında bu görünümde bir görünüm ve dizin oluşturur.

--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

Sonraki iki sorgu, görünüm FROM yan tümcesinde belirtilmemiş olsa bile dizine alınan görünümün nasıl kullanılabileceğini gösterir.

--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;

Son olarak, bu örnek doğrudan dizinlenmiş görünümden sorgulamayı gösterir. Sorgu iyileştiricisi tarafından dizine alınan bir görünümün otomatik kullanımı yalnızca SQL Server'ın belirli sürümlerinde desteklenir. SQL Server Standard sürümünde, dizinli görünümü doğrudan sorgulamak için NOEXPAND sorgu ipucunu kullanmanız gerekir. Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği, NOEXPAND ipucunu belirtmeden dizinlenmiş görünümlerin otomatik kullanımını destekler. Daha fazla bilgi için bkz. Tablo İpuçları (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;

Daha fazla bilgi için bkz. CREATE VIEW .

İlgili içerik

  • Last updated on