Бази от данни Упражнение 11

Бази от данни

Упражнение 11

Изгледи и индекси

1. Изгледи

Изгледите (views) или т.нар. виртуални таблици представляват логическо представяне на подмножество от данни, съхраняващи се в една или повече таблици. Те не съдържат собствени данни, но са като прозорци, през които данните от таблиците могат да бъдат гледани и променяни (с известни ограничения). Таблиците, върху които се изгражда един изглед се наричат базови таблици на изгледа. Всеки изглед се съхраняват като SELECT оператор в речника на данните на съответната СУБД. В този оператор могат да участват както таблици, така и други изгледи т.е. допуска се един изглед да бъде създаден въз основа на друг.

Изгледите имат една основна цел — да преобразуват начина, по който виждате една таблица, част от таблица, или група таблици, без да създавате копия на съответните данни. Те ви дават възможност да използвате данните в по-удобна "опаковка". Например определена таблица съдържа всички валидни поръчки на всеки клиент. Можете да създадете изглед, който да показва само поръчките, валидни за отделен клиент, и да извикате отново този изглед, когато показвате на своя клиент данни от вашето място в Web.

За какво се използват изгледите?

• 
  За ограничаване на достъпа до данните;
  
• 
  За по-лесно писане на сложни заявки;
  
• 
  За осигуряване на по-голяма независимост по отношение на структурата на данните;
  
• 
  За представяне на различни гледни точки върху един и същ набор от данни;
  
• 
  За преименуване и пренареждане на колони.
  

Изгледите ограничават достъпа до данните, защото могат да визуализират само определени колони и редове от дадена таблица. Те могат да бъдат използвани, за да се пишат по-прости и разбираеми заявки и да се връщат резултати от по-сложни такива. Например, могат да се използват изгледи за извличане на информация от множество таблици, без потребителят да знае как се използват операторите за свързване. Изгледи могат да бъдат използвани и за предоставяне на по-голяма независимост между потребители и приложни програми. Ако дадена приложна програма използва данни представени в даден изглед, то в структурата на базовите таблици или във формата на данните им могат да настъпат изменения без това да се отрази на приложната програма, стига изгледът, от който тя черпи данни, да остане непроменен. Посредством изгледите се улеснява предоставянето на данни на групи от потребители, съобразно конкретни критери за достъп.

Основната разлика между тях е свързана с DML (INSERT,UPDATE,DELETE) операциите.

Простите изгледи извличат данните си от само една таблица, не съдържа функции или групи от данни и могат да изпълнява DML операции върху изгледа, докато сложните могат да извличат данните си от много базови таблици, да съдържа функции и групирания и върху тях не винаги могат да се изпълняват DML операции.

Изглед се създава чрез влагане на подзаявка в оператора CREATE VIEW.

Навсякъде, където използвате таблица, може да използвате изглед. Може да използвате изглед в клаузата FROM на запитване на SQL и дори в командите INSERT, UPDATE или DELETE.

Следният изглед предоставя достъп до цялата информация за класовете кораби произвеждани от САЩ:

Модификацията на дефиницията на даден изглед се осъществява по различен начин в различните СУБД. В MS SQL Server имаме отделна команда:

Когато актуализирате данни, използвайки изглед, вие в действителност актуализирате данни от базовата таблица. Това условие се прилага и когато вмъквате или изтривате данни от един изглед.

Не може да използвате следните видове изгледи за модифициране на данни:

• 
  Изгледи с оператори за множества като UNION, както и другите множествени операции, ако се поддържат от съответата СУБД (INTERSECT, EXTRACT\MINUS).
  
• 
  Изгледи с клаузи, съдържащи GROUP BY.
  
• 
  Изгледи с групови функции като AVG, SUM или МАХ.
  
• 
  Изгледи, използващи функцията DISTINCT.
  
• 
  Изгледи, които обединяват таблици (с някои изключения). Може да създавате изгледи, които обединяват няколко таблици и все пак позволяват актуализиране на отделна таблица. Например в някои СУБД е разрешено обновяване, но таблицата, която се актуализира, трябва да се свързва посредством екви-съединение с уникална колона от другата таблица. Дори в този случай всички колони в изгледа не могат да се актуализират.
  

• 
  Не можете да изтривате ред от изглед, ако изгледът съдържа групови функции и групирания на данни, както и ключовата дума DISTINCT.
  
• 
  Не можете да модифицирате ред от изглед, ако той съдържа групови функции, групирания на данни, ключовата дума DISTINCT или колони дефинирани чрез израз.
  
• 
  Не можете да добавяте данни чрез изглед, ако съдържа групови функции, групирания на данни, ключовата дума DISTINCT, колони дефинирани чрез израз или в базовата таблица има NOT NULL колони, които не са включени в изгледа.
  

Следващият изглед изкарва информация от таблицата с работници employees, но само за тези от тях, който работят в отдел номер 20:

Всеки опит да променим номера на отдел в някой от редовете от изгледа ще пропадне, защото това нарушава ограничението наложено от WHIT CHECK OPTION. Тъй като отдели с номера различни от 20 не са в областта на видимост на изгледа, ако подобен UPDATE успее, то посредством изгледа няма как да видим добре резултата от изпълнението му - ефектът би бил изчезване на променените редове от изгледа. Целта на ограничението WHIT CHECK OPTION е недопускането на подобни ефекти.

DROP VIEW emp_example;

2. Индекси

Индексът е:

• 
  Обект в базата от данни;
  
• 
  Използва се от СУБД за да ускори връщането на редове, като използва указатели;
  
• 
  Може да намали дисковите Входно/Изходни операции, като използва бърз метод за намиране на местоположението на данните;
  
• 
  Използва се и се поддържа автоматично от СУБД.
  

Индексите могат да бъдат създавани експлицитно или автоматично. Ако нямате индекс върху дадена колона, тогава за намиране на дадена стойност в колоната се осъществява пълно претърсване на таблицата. Индексът осигурява бърз и директeн достъп до редовете от таблицата. Той е физически независим от таблицата, която индексира. Под независимост се има предвид, че индекси могат да бъдат създавани и изтривани по всяко време без тези операции да оказват влиание върху базовите таблици или другите индекси върху тях. Когато изтриете таблица обаче,съответните индекси също биват изтрити.

Уникален индекс се съдава автоматично, когато се дефинира първичен и уникален ключ върху колоните на дадена таблица. Повечето СУБД поддържат тази функционалност с цел по-лесна поддръжка на съответните ограничения за цялостност. По отношение на уникалните ключове обаче е добре да се провери в документацията на конкретната СУБД, дали автоматично се създава уникален индекс за съответната колона/колони.

Потребителите могат да създават индекси върху дадена колона или множество от колони, за да ускорят достъпа до редовете на таблицата. Например, може да създадете индекс върху колоните от дадена таблица, участващи във FOREIGN KEY ограничение, за да ускорите изпълнението на заявки, в които имате свързвания по съответните колони.

Добре е да създадете индекс, ако:

• 
  Колоната съдържа голям брой различни стойности;
  
• 
  Една или повече колони са често използвани заедно в WHERE клауза или в условие за свързване;
  
• 
  Таблицата е голяма и повечето заявки се очаква да върнат по-малко от 20% от редовете.
  

Повече индексиране съвсем не означава по-бързи заявки. При почти всяка DML операция върху таблица с индекси индексите трябва да бъдат променени. Колкото повече индекси имаме върху таблицата, толкова повече усилия се изискват от СУБД, за да обнови индексите след DML операцията.

Не се препоръчва да се съдава индекс, ако:

• 
  Таблицата е малка;
  
• 
  Колоните не се използват често в условия в заявките;
  
• 
  Повечето заявки се очаква да върнат повече от 20% от редовете в таблицата;
  
• 
  Данните в таблицата се променят често;
  
• 
  Индексираните колони се използват като част от израз, а не самостоятелно (в този случай може да е удачно да се създаде т.нар. функционален индекс ).
  

Изтриването на индекс се осъществява с оператора:

3. Индекси върху изгледи

4. Индексни сканирания

In this method, the indexed column values specified by the statement are used to retrieve the row. An index scan retrieves data from an index based on the value of one or more columns in the index. To perform an index scan, RDBMS searches the index for the indexed column values accessed by the statement. If the statement accesses only columns of the index, then RDBMS reads the indexed column values directly from the index rather than from the table.

The index contains not only the indexed value but also the pointers to rows in the table having that value. Therefore, if the statement accesses other columns in addition to the indexed columns, then RDBMS can find the rows in the table by using either a table access by pointer.
Index Unique Scans

This scan returns, at most, a single POINTER. RDBMS performs a unique scan if a statement contains a UNIQUE or a PRIMARY KEY constraint that guarantees that only a single row is accessed. This access path is used when all columns of a unique (B-tree) index are specified with equality conditions.

An index range scan is a common operation for accessing selective data. It can be bounded (on both sides) or unbounded (on one or both sides). Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by POINTER. If data must be sorted by order, then use the ORDER BY clause and do not rely on an index. If an index can be used to satisfy an ORDER BY clause, then the optimizer uses this option and avoids a sort. The optimizer uses a range scan when it finds one or more leading columns of an index specified in conditions, such as col1 = :b1, col1 < :b1, col1 > :b1, and any combination of the preceding conditions for leading columns in the index. Wildcard searches (col1 like '%ASD') should not be in a leading position, as this does not result in a range scan. Range scans can use unique or nonunique indexes. Range scans avoid sorting when index columns constitute the ORDER BY clause.

An index range scan descending is identical to an index range scan, except that the data is returned in descending order. Indexes, by default, are stored in ascending order. This scan is usually used when ordering data in a descending order to return the most recent data first, or when seeking a value less than a specified value. The optimizer uses index range scan descending when an order by descending clause can be satisfied by an index.

Backtracking to the first branch block, the server identifies that the next subtree (F16) does not need to be scanned because the next entry in that branch block is F20. Because the server is certain that it is not possible to find a 25 between F16 and F20, the second leaf block can be skipped.

The same process continues with the right part of this index. Note that out of a total of 10 leaf blocks, only five are scanned.

An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation.