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



Дата23.07.2016
Размер95.5 Kb.

Бази от данни

Упражнение 11

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




  1. Изгледи


Какво представляват индексите?

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

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

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


  • За ограничаване на достъпа до данните;

  • За по-лесно писане на сложни заявки;

  • За осигуряване на по-голяма независимост по отношение на структурата на данните;

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

  • За преименуване и пренареждане на колони.

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


Най-общо изгледите могат да бъдат класификацирани в две групи: прости и сложни

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







Прости изглед (simple)

Сложни изглед (complex)

Брой таблици


Една

Една или повече

Съдържат функции

Не

Да

Съдържат групирани данни

Не

Да

Могат да изпълняват DML операции върху изгледа

Да

Понякога

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



Създаване на изглед

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


CREATE VIEW view

[(alias[,alias]...)]

AS subquery

[WHIT CHECK OPTION],
където:

view - е име на изгледа.

alias - определя имената на изразите от заявката на изгледа. Броят псевдоними трябва да отговаря на броя изрази селектирани от изгледа.

subqueryе завършен SELECT оператор (може да използвате псевдоними в SELECT клаузата и да не използвате подобната възможност от синтаксиса на CREATE VIEW). Не е хубаво да се използва ORDER BY клауза при създаването на изгледа. Ако искаме да подредим резултатното множество, то трябва да приложим ORDER BY клауза в заявките към изгледа.

WHIT CHECK OPTION - определя дали само редове достъпни за изгледа могат да бъдат вмъквани и променяни.

Употреба на изгледите

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


Примери:

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



CREATE VIEW v_USA_classes

AS

SELECT *

FROM classes

WHERE country ='USA';
Следващият изглед изкарва средния брой оръдия на произвежданите класове по старни:

CREATE VIEW v_Country_AvgGuns

AS

SELECT avg(numGuns) as average_Guns, country

FROM classes

GROUP BY country;
Алтернативно имената на колоните в резултатната виртуална таблица могат да се зададат по следния начин:

CREATE VIEW v_Country_AvgGuns(average_Guns,country)

AS

SELECT avg(numGuns),country

FROM classes

GROUP BY country;
Резултатът от изпълнението можем да видим посредством заявката:

SELECT *

FROM v_Country_AvgGuns

ORDER BY average_Guns DESC;
Информация за характеристиките на всеки кораб можем да получим посредством изгледа ships_full_info, дефиниран по следния начин:

CREATE VIEW v_ships_full_info

(name,type,numGuns,bore,displacement,country,launched)

AS

SELECT s.name, c.type,c.numGuns,c.bore,c.displacement,c.country,s.launched

FROM classes c,ships s

WHERE c.class=s.class

Модификация на изглед

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



ALTER VIEW view

[(alias[,alias]...)]

AS subquery

[WHIT CHECK OPTION]
С ALTER VIEW изгледът се пресъздава без това да се отрази на обектите зависещи от изгледа (като съхранени процедури, тригери и др.), запазват се и установените права за достъп до изгледа.

Модифициране на данни с използване на изгледи

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

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


  • Изгледи с оператори за множества като UNION, както и другите множествени операции, ако се поддържат от съответата СУБД (INTERSECT, EXTRACT\MINUS).

  • Изгледи с клаузи, съдържащи GROUP BY.

  • Изгледи с групови функции като AVG, SUM или МАХ.

  • Изгледи, използващи функцията DISTINCT.

  • Изгледи, които обединяват таблици (с някои изключения). Може да създавате изгледи, които обединяват няколко таблици и все пак позволяват актуализиране на отделна таблица. Например в някои СУБД е разрешено обновяване, но таблицата, която се актуализира, трябва да се свързва посредством екви-съединение с уникална колона от другата таблица. Дори в този случай всички колони в изгледа не могат да се актуализират.


Правила за изпълняване на DML операции върху изгледи, базирани на една таблица:

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

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

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

Можете да си гарантирате, че DML операците изпълнени върху изгледа ще станат в областта на видимост, определена от изгледа като използвате клаузата WHIT CHECK OPTION.

Пример:

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



CREATE VIEW emp_example

AS

SELECT *

FROM employees

WHERE department_id = 20

WHIT CHECK OPTION;

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



Изтриване на изглед

DROP VIEW view;
Пример:

DROP VIEW emp_example;



  1. Индекси


Какво представляват индексите?

Индексът е:



  • Обект в базата от данни;

  • Използва се от СУБД за да ускори връщането на редове, като използва указатели;

  • Може да намали дисковите Входно/Изходни операции, като използва бърз метод за намиране на местоположението на данните;

  • Използва се и се поддържа автоматично от СУБД.

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



Как се създават индекси?

Автоматично:

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



Експлицитно:

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



Експлицитно създаване на индекс

CREATE [UNIQUE] INDEX index_name

ON table(column[,column]...);
Уникален индекс може да се създаде експлицитно като се зададе ключовата дума UNIQUE. Такъв индекс не допуска наличието на две еднакви стойности в съответната колона/комбинация от колони. Препоръчва се обаче да съдавате уникалните индекси имплицитно (неявно), като зададете съответното PRIMARY KEY / UNIQUE ограничение върху колоните на таблицата.
Пример:

CREATE INDEX idx_ships_classes

ON ships (class);

Кога да създаваме индекси?

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



  • Колоната съдържа голям брой различни стойности;

  • Една или повече колони са често използвани заедно в WHERE клауза или в условие за свързване;

  • Таблицата е голяма и повечето заявки се очаква да върнат по-малко от 20% от редовете.

Повече не винаги значи по-добре!

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



Кога не бива да създаваме индекси?

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



  • Таблицата е малка;

  • Колоните не се използват често в условия в заявките;

  • Повечето заявки се очаква да върнат повече от 20% от редовете в таблицата;

  • Данните в таблицата се променят често;

  • Индексираните колони се използват като част от израз, а не самостоятелно (в този случай може да е удачно да се създаде т.нар. функционален индекс ).

Изтриване на индекс

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


DROP INDEX table_name.index_name;
Обикновено СУБД не предоставя средства за модифициране на индекси. Ако искате да модифицирате индекс, то трябва първо да го изтриете, а после да го пресъздадете.
Пример:

DROP INDEX ships.idx_ships_classes;
Забележка: Когато се говории за индекс в базите данни, без да изрично да се уточнява неговия вид, обикновено се има предвид т.нар. B-tree индекс. Това е индекс, за чиято реализация се използва структурата от данни B дърво (напрактика се използват модификации на B дърво: B*, B+ и др.). Листата на B дърво представляват двусвързан подреден списък от стойностите на индексираната колона/колони. Ако освен стойността на колоната, във възлите на дървото имаме и указател към място от файла с данни, където се намират останалите полета от съответния ред на таблицата, говорим за не-клъстерирани индекс. Ако във възлите на дървото се съхраняват целите редове, то индексът се нарича клъстериран. По този начин всички данни от таблицата са изнесени в индекса. Ето защо една таблица може да има най-много един клъстерен индекс.
B tree structure


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


В последните версии на по-големите СУБД се появиха и т.нар. “материализирани изгледи” – в базата се съхранява не само дефиницията им, но физически се пазят и техните данни. Напрактика такива таблици преставят да бъдат виртуални и се материализират. Оптимизаторите на заявки използват тези изгледи за постигане на по-голямо бързодействие, дори и при изпълнение на заявки, в чийто FROM клаузи не се споменава явно името на изгледа. Съществуват различни режими за синхронизация на данните на изгледа и базовите таблици. В MS SQL Server 2000 подобен ефект на материализация се постига чрез създаване на клъстерен индекс върху изглед. Съществуват редица ограничения, с които трябва да бъде съобразена дефиницията на изгледа, за да се изгради индекс въру него. При промяна на данни в базовата таблица, автоматично се обновява и клъстерния индекс на изгледа. В последствие могат да се дефинират и други неклъстерирани индекси.
  1. Индексни сканирания



Index Scans

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.


Index Range Scans

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.


Index Range Scans Descending

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.


Index Skip Scans

Index skip scans improve index scans by nonprefix columns. Scanning index blocks is often faster than scanning table data blocks. Skip scanning lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped. The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.


The RDBMS Server starts from the root of the index and then proceeds to the left-most branch block. From here, the server identifies a first entry to be F16, goes to the left-most leaf, and starts to scan it because it could contain A25. The server identifies that this is not possible because the first entry is F10. It is thus not possible to find an entry such as F25 in this leaf.

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.


Returning to the first branch block, the server finds that the following two entries have a common prefix of F2. This identifies possible subtrees to scan. The server knows that these subtrees are ordered by age. So the third and forth leaf blocks are scanned and some values are retrieved. By looking at the third entry in the first branch block, the server determines that it is no longer possible to find an F2 entry. Thus, it is not necessary to scan that subtree.

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.


Index Joins

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.


Fast Full Index Scans
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself without accessing the table. It cannot be used to eliminate a sort operation because the data is not ordered by the index key. It reads the entire index using multiblock reads (unlike a full index scan) and can be parallelized





База данных защищена авторским правом ©obuch.info 2016
отнасят до администрацията

    Начална страница