Indexed View ها در SQL Server

یکشنبه 2 خرداد 1395

View ها جداول مجازی هستند که یک یا چند جدول را در خود نگه میدارند و در دیتابیس ذخیره میشوند. View خود به خود فاقد داده هستند . این مقاله درباره ی Indexed View ها در SQL Server بحث میشود.

Indexed View ها در SQL Server

View  ها جداول مجازی هستند که یک یا چند جدول را در خود نگه میدارند و در دیتابیس ذخیره میشوند. View خود به خود فاقد داده است، آنها مجموعه ای از query هاهستند  که به یک یا چند جدول که در دیتابیس  به عنوان یک شی ذخیره شده اند،استفاده شود. View  ها برای اهداف امنیتی در پایگاه داده ها استفاده می شود، View را می توان با استفاده از جداول از همان دیتابیس  و یا دیتابیس های مختلف ایجاد کرد. از  آن برای اجرای مکانیزم های امنیتی در SQL Server استفاده می شود.

indexed view چیست؟

indexed view یک شاخص خوشه ای منحصر به فرد است. شاخص خوشه منحصر به فرد  در SQL Server ذخیره می شود و مانند هر شاخص خوشه های دیگر به روزرسانی میشود. indexed view نسبت به view های استاندارد که شامل پردازش پیچیده ای در زمانی که  تعداد زیادی ردیف وجود دارد، است. مانند جمع آوری مقدار زیادی از داده ها، و یا پیوستن به بسیاری از ردیفهای  مهم تر است. اگر این قبیل view ها غالبا در query ها اشاره شده باشند، ما می توانیم عملکرد را با ایجاد یک شاخص خوشه منحصر به فرد در view بهبود بخشیم. برای اینکه مجموعه نتیجه viewاستاندارد در دیتابیس ذخیره نشود ، جای این مجموعه نتایج را برای هر کوئری محاسبه میکنیم. اما در مورد  شاخص خوشه مجموعه نتایج در دیتابیس درست مثل یک جدول با یک ایندکس خوشه ذخیره شده محاسبه می شود.Queries ها به طور خاص ار Index view  استفاده نمیکنند و حتی می تواند از وجود ایندکس خوشه از view بهره مند شوند.

در توسعه و عرضه  نسخه های SQL سرور، optimizer می تواند ایندکس هایی از View ها برای  بهینه سازی query ها که از indexed view  های مشخص نیستند، استفاده کند.در نسخه های دیگر از SQL Server ،کوئری باید شامل indexed view باشد  و NOEXPAND  را برای بهره بردن از ایندکس بر روی view تعین کند.

 نحوه ی ایجاد indexed View ها

برای ایجاد یک  indexed view  ، در ابتدا ما نیاز به ایجاد یک view با امکانات طرح و شمای schema binding  داریم که  بعد از آن  یک شاخص یا ایندکس در view  ایجاد کنیم. برای درک بهتر اجازه دهید نمونه ای از آن را ببینیم.

table

ما indexed view  برای جدول فوق ایجاد میکنیم. در ابتدا ما یک view در جدول  Employee ایجاد میکنیم،

    CREATEVIEWVW_EmployeeWITHSCHEMABINDINGAS  
    SELECTe.Emp_Id,e.EmployeeName,e.EmpSalary,e.StateId,e.CityId  
    FROMdbo.Employee e  
    WHEREe.EmployeeNameLIKE'[A-P]%'  

در query بالا یک  view با طرح یا شمای binding option  ایجاد کرده ایم. آن برای indexed view که view باید با امکانات   schema binding ایجاد شود ، مهم است.

اگر ما سعی در  ایجاد یک  indexed view  بدون امکانات schemabinding داشته باشیم،  SQL Server  خطایی با عنوان  “Cannot create index on view 'VW_Employee' because the view is not schema bound” نمایش میدهد.

نمونه :

code

در تصویر بالا روشن است که  indexed view نمیتواند بدون گزینه Schema binding ، ایجاد شود. هنگامی که این شاخص ایجاد میشود ، نتیجه ی  این  View در دیتابیس مانند هر خوشه ایندکس دیگر ذخیره میشود.

نحوه ی کار  Indexed View

هر زمان که ما یک ایندکس خوشه ای منحصر به فرد به یک    view اضافه کنیم ، materialize view ایجاد می شود. materialize view  بر اساس دیسک  وبه صورت دوره ای براساس تعریف کوئری  بروزرسانی  میشوند.

result

result

در query بالا ما می توانید ببینید که query را برای اسکن ایندکس خوشه بهینه سازی میکند.

اگر ما یک ایندکس خوشه  در یک view ایجاد کنیم ، اجباری به بهینه سازی همیشه کوئری مورد استفاده در ایندکس خوشه نیست. با بهینه سازی کوئری میتوانیم طرح اجرایی دیگری را پیدا کنیم که نسبت به ایندکس خوشه  کارآمد تر باشد و از آن استفاده کنیم.

مثال:

Query:

    /*Create View*/  
    CREATE VIEW VI_DemoWITHSCHEMABINDINGas  
    SELECT tcgi.Company_Id,tcgi.Company_Name,tcgi.Contact_Person,tmmc.Category_Name,tcgi.Establish_Year,tcgi.Address+', '+tcgi.PincodeAS Address_,tcgi.Mobile_Number+ISNULL(','+tcgi.Landline_Number,'')ASContact_Info  
    ,ISNULL(tcgi.Website,'Not Available')ASWebsite,ISNULL(tcgi.Email_Id,'Not Available')ASEmail,tcgi.Latitude,tcgi.Longitude,ISNULL(tcgi.Facebook_Id,'Not Available')ASFacebook_Id,  
    ISNULL(tcgi.Linkedin_Id,'Not Available')ASLinkedin_Id,ISNULL(tcgi.Twitter_Id,'Not Available')ASTwitter_Id,ISNULL(tcgi.Google_Plus_Id,'Not Available')ASGoogle_Plus_Id  
    FROM dbo.TblCompany_General_Infotcgi  
    INNERJOIN  
    dbo.TblMaster_Main_Categorytmmc  
    ON  
    tcgi.Category_Id=tmmc.Category_Id  
    INNERJOIN  
    dbo.TblUser_Profiletup  
    ON  
    tup.User_Id=tcgi.User_Id  
    WHERE  
    tcgi.Company_Id>1  
    /*Create Clustered Index*/  
    CREATEUNIQUECLUSTEREDINDEXidx_MyViewONVI_Demo(Company_Id)  
    /*Select Data from view*/  
    SELECT*FROMVI_Demo  

طرح اجرایی:

plan

 می بینیم که بهینه ساز کوئری از ایندکس خوشه استفاده نمی کند چرا که  این اتفاق اغلب مواردی که  بهینه ساز فکر می کند می تواند بهترین جداول مبنا باشد ، رخ میدهد.

بهینه ساز کوئری  مجبور  به استفاده از "شاخص خوشه (Clustered Index) است ،  همیشه از گزینه ی "NOEXPAND" مانند زیر استفاده میکند

NOEXPAND

 بهینه ساز کوئری همیشه از ایندکس خوشه حتی اگر طرح اجرایی بهتر باشد ، استفاده میکند.پس اجبار بهینه ساز کوئری برای استفاده از گزینه ی NOEXPAND در ایندکس خوشه ،مناسب نیست.

در کجا از Indexed View استفاده میشود

Indexed view ها هم سودمند و هم هزینه بر هستند. بهینه ساز کوئری نتایجی  کارآمد تر و سریعتر برای نمایش داده های پیچیده و زائد را فراهم می کند. هزینه یک Indexed view  در نگهداری از ایندکس خوشه ای است.

زمانیکه  از کوئری های پیچیده بر روی بسیاری از جداول، چند بار استفاده میشود.

 هنگامی که سیستم جدید نیاز به خواندن داده های جدول های قدیمی دارد

انبار داده ها محیطی ،  که بهترین و مناسبترین انتخاب  برای indexed view ها  ،  data mart ها، پایگاه داده OLAP  است اما محیط کاربردی،کمی مناسب برای indexed view  است.

محدودیت  Indexed View

     Viewباید با SCHEMABINDING ایجاد می شود.
    
توابع در تعریف View باید با SCHEMABINDING ایجاد شده استفاده شوند.
    
جداول پایه باید با تنظیم ANSI_NULLS مناسب ایجاد شوند.


نتیجه گیری:

اگر در حال ایجاد یک View برای query پیچیده  هستید ،در حال اجرا اغلب به جای View نرمال ،همیشه  از INDEXED View استفاده کنید. در سایر موارد باقی مانده View های نرمال مفید هستند. قبل از استفاده از INDEXED View ، موارد نیاز خود را در نظر گرفته و پس از برای استفاده از ایندکس خوشه تصمیم گیری کنید.

آموزش نصب sql server 2017

برنامه نویسان

نویسنده 3355 مقاله در برنامه نویسان

کاربرانی که از نویسنده این مقاله تشکر کرده اند

در صورتی که در رابطه با این مقاله سوالی دارید، در تاپیک های انجمن مطرح کنید