Ерл. Первая нормальная форма атомарные атрибуты
 Скачать 0.73 Mb.
|
|
[ WITH ( атрибут [. ...] ) ] - атрибут может принимать два значения: iscachable и isstrict. iscachable. Оптимизатор может использовать предыдущие вызовы функций для ускоренной обработки будущих вызовов с тем же набором аргументов. Кэширование обычно применяется при работе с функциями, сопряженными с большими затратами ресурсов, но возвращающими один и тот же результат при одинаковых значениях аргументов. isstrict. Функция всегда возвращает NULL в случае, если хотя бы один из ее аргументов равен NULL. При передаче атрибута isstrict результат возвращается сразу, без фактического выполнения функции. Анонимные блоки Ответ: Анонимные блоки - это блоки, которые не имеют имени. Анонимные блоки не могут быть вызваны другими блоками, так как у них нет имени, на которое можно ссылаться. Триггеры Oracle Forms и Reports, которые также называются клиентскими триггерами, являются анонимными блоками PL/SQL. Триггеры базы данных и сценарии в SQL*Plus, заключенные в операторские скобки BEGIN и END, также являются анонимными блоками. Ниже приведена структура анонимного блока:  DECLARE - раздел объявлений. В этом разделе идентифицируются переменные, курсоры и вложенные блоки, на которые имеются ссылки в исполняемом блоке и блоке исключений. Этот раздел необязательный. BEGIN - END - исполняемый раздел. В этом разделе находятся операторы, которые выполняются ядром PL/SQL во время работы вашего приложения. Этот раздел является обязательным. EXCEPTION - раздел исключений. В этом разделе обрабатываются различные исключения, предупреждения и ошибки. Этот раздел необязателен     CTE Ответ: Common Table Expression (CTE) — результаты запроса, которые можно использовать множество раз в других запросах. То есть, запросом мы достаем данные, и они помещаются в пространство памяти, аналогично временному представлению, которое физически не сохраняется в виде объектов. Далее мы работаем с получившейся конструкцией как с таблицей, используя такие конструкции как select, update, insert и delete. Основные способы использования: для улучшения читаемости запроса в случае сложных запросов (разительно уменьшают размер кода); в случаях, когда нужно много раз обращаться к одним и тем же таблицам/выборкам из таблиц; для создания представлений (VIEW) в части select; для написания рекурсивных запросов. Отличия от вложенного запроса: вложенный запрос повторяется для каждой строки из нашей выборки, что повышает стоимость выполнения запроса. Отличия от временной таблицы: заполнение временной таблицы при больших объемах создает нагрузку на диск; исполнение запросов с использованием временной таблицы увеличивает время их выполнения из-за места хранения данного типа таблиц (tempdb). Мы познакомились с обобщенными табличными выражениями и убедились в том, что использование данного инструмента, совместно с остальными методами оптимизации запросов, помогает увеличить эффективность извлечения и обработки данных. Управление транзакциями Ответ: Транзакция — это группа инструкций одной или нескольких баз данных, которые либо полностью фиксируются, либо полностью откатываются. Транзакции атомарны, согласованы, изолированы и устойчивы (atomic, consistent, isolated, durable — ACID). Если транзакция выполнена успешно, все инструкции в ней фиксируются. Если транзакция завершается ошибкой, то если хотя бы одна инструкция в группе завершается ошибкой, выполняется откат всей группы. Начало и конец транзакции зависят от параметра AUTOCOMMIT и инструкций BEGIN TRANSACTION, COMMIT и ROLLBACK. Azure Synapse Analytics поддерживает следующие типы транзакций: Явные транзакции начинаются с инструкции BEGIN TRANSACTION и заканчиваются инструкцией COMMIT или ROLLBACK. Транзакции с автофиксацией автоматически запускаются в рамках сеанса и не начинаются с инструкции BEGIN TRANSACTION. Если для параметра AUTOCOMMIT установлено значение ON, каждая инструкция выполняется в транзакции, и явные инструкции COMMIT или ROLLBACK не требуются. Если для параметра AUTOCOMMIT установлено значение OFF, для определения результата транзакции требуется инструкция COMMIT или ROLLBACK. В Azure Synapse Analytics транзакции с автофиксацией выполняются сразу после инструкции COMMIT или ROLLBACK или после инструкции SET AUTOCOMMIT OFF. Управление блокировками Работа планировщика запросов Ответ: Планировщик запросов представляет собой упорядоченный набор шагов, используемых для доступа к данным в системе управления реляционными базами данных SQL. Это конкретный случай концепции реляционных моделей планов доступа. Поскольку SQL является декларативным, обычно существует большое количество альтернативных способов выполнения заданного запроса с широким диапазоном производительности. Когда запрос отправляется в базу данных, оптимизатор запросов оценивает некоторые из различных, правильных возможных планов выполнения запроса и возвращает то, что он считает лучшим вариантом. Поскольку оптимизаторы запросов несовершенны, пользователям баз данных и администраторам иногда приходится вручную проверять и настраивать планы, созданные оптимизатором, для повышения производительности. План исполнения Составные запросы Ответ: В SQL существует механизм вложенного запроса, позволяющий использовать результаты одного запроса в другом запросе. Этот механизм играет важную роль в SQL по следующим причинам: – вложенные запросы соответствуют словесному описанию запроса и поэтому являются самым естественным способом выражения запроса; – вложенные запросы позволяют структурировать запрос путем разбиения на части (на главный запрос и вложенные запросы); – существуют ситуации, когда невозможно обойтись без вложенных запросов. Вложенным называется запрос, содержащийся в предложении WHERE или HAVING другого запроса. Вложенные запросы всегда входят в предложение WHERE или HAVING и заключаются в круглые скобки. В предложении WHERE они отбирают из таблицы отдельные строки, а в предложении HAVING группы строк. Подчиненные запросы имеют ту же структуру, что и инструкция SELECT, содержащая предложение FROM и необязательные предложения WHERE, GROUP BY и HAVING. Однако между вложенным запросом и инструкцией SELECT имеются отличия: – таблица результатов вложенного запроса всегда состоит из одного столбца, поэтому в предложении SELECT вложенного запроса всегда следует указывать только один возвращаемый столбец; – во вложенный запрос не может входить предложение ORDER BY, так как результаты вложенного запроса используются только внутри главного запроса и для пользователя остаются невидимыми. Поэтому нет смысла их сортировать. Чаще всего вложенные запросы используются в предложении WHERE и участвуют в процессе отбора строк. В простейшем случае вложенный запрос возвращает значение, позволяющее проверить истинность или ложность условия отбора. Вложенный запрос всегда является частью условия отбора в предложении WHERE и HAVING. В SQL используются следующие условия отбора вложенного во вложенном запросе: – сравнение с результатом подчиненного запроса: значение выражения сравнивается с одним значением, вычисленным вложенным запросом. Эта проверка представляет собой простое сравнение; – проверка на принадлежность результатам вложенного запроса: значение выражения проверяется на равенство одному из множества значений, возвращаемых вложенным запросом. Эта проверка представляет собой проверку на членство в множестве; – проверка на существование: проверяется наличие строк в таблице результатов вложенного запроса; – многократное сравнение: значение выражения сравнивается с каждым из множества значений, возвращаемых вложенным запросом. Сравнение с результатом полученного запроса В данном условии отбора значение выражения сравнивается со значением, вычисленным вложенным запросом. В случае совпадения значений проверка дает результат TRUE, а в случае несовпадения FALSE. Этот вид условия отбора используется для сравнения значения из проверяемой строки с одним значением, полученным от вложенного запроса. Виды join Ответ: Как уже было описано выше, операция объединения соединяет данные из двух таблиц, формируя пары связанных строк из этих таблиц. При внутреннем объединении все записи, для которых не находится пары в другой таблице, просто игнорируются. Поэтому если строка одной из таблиц не имеет пары, то такое объединение (внутреннее) может привести к неожиданным результатам. Самый простой вид соединения INNER JOIN – внутреннее соединение. Этот вид джойна выведет только те строки, если условие соединения выполняется (является истинным, т.е. TRUE). В запросах не обязательно прописывать INNER – если написать только JOIN, то СУБД по умолчанию выполнить именно внутреннее соединение. Левое и правое соединения еще называют внешними. Главное их отличие от внутреннего соединения в том, что строка из левой (для LEFT JOIN) или из правой таблицы (для RIGHT JOIN) попадет в результаты в любом случае. Давайте до конца определимся с тем, какая таблица левая, а какая правая. Левая таблица та, которая идет перед написанием ключевых слов [LEFT | RIGHT| INNER] JOIN, правая таблица – после них Еще один вид соединения, который осталось рассмотреть – полное внешнее соединение. Этот вид джойна вернет все строки из всех таблиц, участвующих в соединении, соединив между собой те, которые подошли под условие ON. Соответствие операций реляционной алгебры и операций SQL Ответ: Реляционная алгебра была представлена E. F. Codd в 1972 году. Она состоит из множества операций над отношениями: ВЫБОРКА(SELECT) (σ): извлечь кортежи из отношения, которые удовлетворяют заданным условиям. Пусть R - таблица, содержащая атрибут A. σA=a(R) = {t ∈ R ∣ t(A) = a} где t обозначает кортеж R и t(A) обозначает значение атрибута A кортежа t. ПРОЕКЦИЯ(PROJECT) (π): извлечь заданные атрибуты (колонки) из отношения. Пусть R отношение, содержащее атрибут X. πX(R) = {t(X) ∣ t ∈ R}, где t(X) обозначает значение атрибута X кортежа t. ПРОИЗВЕДЕНИЕ(PRODUCT) (×): построить декартово произведение двух отношений. Пусть R - таблица, со степенью k1 и пусть S таблица со степенью k2. R × S - это множество всех k1 + k2 - кортежей, где первыми являются k1 элементы кортежа R и где последними являются k2 элементы кортежа S. ОБЪЕДИНЕНИЕ(UNION) (∪): построить теоретико-множественное объединение двух таблиц. Даны таблицы R и S (обе должны иметь одинаковую степень), объединение R ∪ S - это множество кортежей, принадлежащих R или S или обоим. ПЕРЕСЕЧЕНИЕ(INTERSECT) (∩): построить теоретико-множественное пересечение двух таблиц. Даны таблицы R и S, R ∪ S - это множество кортежей, принадлежащих R и S. Опять необходимо, чтобы R и S имели одинаковую степень. ВЫЧИТАНИЕ(DIFFERENCE) (− или ∖): построить множество различий двух таблиц. Пусть R и S опять две таблицы с одинаковой степенью. R - S - это множество кортежей R,не принадлежащих S. СОЕДИНЕНИЕ(JOIN) (∏): соединить две таблицы по их общим атрибутам. Пусть R будет таблицей с атрибутами A,B и C и пусть S будет таблицей с атрибутами C,D и E. Есть один атрибут, общий для обоих отношений, атрибут C. R ∏ S = πR.A,R.B,R.C,S.D,S.E(σR.C=S.C(R × S)). Что же здесь происходит? Во-первых, вычисляется декартово произведение R × S. Затем, выбираются те кортежи, чьи значения общего атрибута C эквивалентны (σR.C = S.C). Теперь мы имеем таблицу, которая содержит атрибут C дважды и мы исправим это, выбросив повторяющуюся колонку. Запросы с подзапросами Ответ: SQL подзапросы — или внутренние запросы, или вложенные запросы — это запрос внутри другого запроса SQL, который вложен в условие WHERE. Подзапрос используется для возврата данных, которые будут использоваться в основном запросе, в качестве условия для дальнейшей фильтрации данных, подлежащих извлечению. уществует несколько правил, которые применяются к подзапросам: Подзапросы должны быть заключены в круглые скобки. Подзапрос может иметь только один столбец в условии SELECT, если только несколько столбцов не указаны в основном запросе для подзапроса для сравнения выбранных столбцов. Команда ORDER BY не может использоваться в подзапросе, хотя в основном запросе она использоваться может. В подзапросе может использоваться команда GROUP BY для выполнения той же функции, что и ORDER BY. Подзапросы, которые возвращают более одной строки, могут использоваться только с несколькими операторами значений, такими как оператор IN. Список SELECT не может содержать ссылки на значения, которые вычисляются для BLOB, ARRAY, CLOB или NCLOB. Подзапрос не может быть сразу заключен в функцию set. С подзапросом не может использоваться оператор BETWEEN. Однако оператор BETWEEN может использоваться внутри подзапроса. Запросы с группировкой Ответ: Иногда требуется получить агрегированные значения (минимум, максимум, среднее) не по всему результирующему набор данных, а по каждой из входящих в него групп записей, характеризующихся одинаковыми значениями какого-либо столбца. Для применения агрегатных функций предполагается предварительная операция группировки. В чем состоит суть операции группировка? При группировке все множество кортежей отношения разбивается на имеющие одинаковые значения атрибутов, которые заданы в списке группировки. Эту возможность предоставляет предложение GROUP BY инструкции SELECT. На логическом уровне запрос выполняется следующим образом: 1. Заказы делятся на группы, по одной для каждого служащего. В каждой группе все заказы имеют одно и то же значение в столбце ID_SLZH. 2. Для каждой группы вычисляется среднее значение столбца PRICE по всем строкам, входящим в группу, и генерируется одна итоговая строка результатов. Эта строка содержит значение столбца ID_SLZH для группы и среднюю стоимость заказа для данной группы. Если в результирующих наборах данных нужно выдавать агрегацию не по всем группам, а только по тем из них, которые отвечают некоторому условию, после предложения GROUP BY указывают предложение HAVING <агрегатная функция> <отношение> <значение> агрегатная функция − одна из функций MIN, MAX, AVG, SUM; отношение − одна из операций отношения =, <>, <, >, <=, >=; значение − константа, результат вычисления выражения или единичное значение, возвращаемое вложенным оператором SELECT. Таким образом, после HAVING указываются условия, которые отличаются от условий, определяемых в предложении WHERE, одним важным обстоятельством: в них обязательно должна быть указана одна из агрегатных функций, в то время как в предложении WHERE такие функции указывать нельзя. Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT, так и в выражении условия обработки сформированных групп HAVING. В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций, могут быть использованы для вывода соответствующих результатов или для условия отбора групп. Работа с индексами(создание, удаление, использование) Ответ: Индексы позволяют осуществлять быстрый поиск необходимой информации из таблиц. Рассмотрим их принцип работы на примере таблицы ТОВАРЫ и два созданных для нее индекса. Один из индексов обеспечивает быстрый доступ к таблице на основе столбца DESCRIPTION (описание). Другой обеспечивает доступ на основе первичного ключа таблицы, представляющего собой комбинацию столбцов ID_MFR (марка) и ID_PRD (номер изделия) В индексе хранятся значения данных и указатели на строки, где эти данные встречаются. Данные в индексе располагаются в убывающем или возрастающем порядке, чтобы СУБД могла быстро найти требуемое значение. Затем по указателю СУБД может быстро локализовать строку, содержащую искомое значение. Наличие или отсутствие индекса совершенно незаметно для пользователя, обращающегося к таблице. Рассмотрим, например, такую инструкцию SELECT: Найти количество и цену изделия 2А34. SELECT COUNT, PRICE FROM TOVARY WHERE DESCRIPTION = `2А34` Если бы индекса для столбца DESCRIPTION не существовало, то СУБД была бы вынуждена выполнять запрос путем последовательного «сканирования» таблицы TOVARY, строка за строкой, просматривая в каждой строке столбец DESCRIPTION. Для получения гарантии того, что она нашла все строки, удовлетворяющие условию отбора, СУБД должна просматривать каждую строку таблицы. Если таблица имеет тысячи и миллионы строк, то ее просмотр может занять минуты и даже часы. Если для столбца DESCRIPTION имеется индекс, СУБД находит требуемые данные с гораздо меньшими усилиями. Она просматривает индекс, чтобы найти требуемое значение (изделие 2А34), а затем с помощью указателя находит требуемую строку (строки) таблицы. Поиск в индексе осуществляется достаточно быстро, так как индекс отсортирован и его строки очень короткие. Для создания индекса необходимо написать следующий фрагмент кода: CREATE INDEX (название индекса) ON (название таблицы(название столбца)) (после ключевого слова ON мы прописываем для чего создаем индекс) Пример: CREATE INDEX superhero_name_idx ON superhero(name) При создании запроса с выборкой имени супергероя, созданный индекс может быть применен автоматически базой данных SELECT name, appearances, eye, heir FROM superhero WHERE name = ‘Iron Man (Anthony\”Tony\”Stark)’ При этом что до создания индекса, что после, сам SQL-запрос не изменяется. Помимо извлечения данных из таблиц (команда SELECT…WHERE), индексы также используются для объединения данных для столбцов, указанных в условии объединения, если существует индекс, а также для ускоренной сортировки данных для столбцов, указанных в ORDER BY и при этом чтобы существовал индекс в правильной последовательности сортировки. Указать последовательность сортировки данных в ORDER BY в индексе можно при его создании. Для этого необходимо написать после ключевого слова ON в названии столбца должно присутствовать ключевого слово сортировки DESC — убывание, или ASC (по умолчанию) — возрастание. Пример: CREATE INDEX superheroes_appearances_idx ON superheroes(appearances DESC), то есть создать индекс (его название) для столбца appearances таблицы superheroes по убыванию. Иногда эффективно создавать индексы по нескольким столбцам. Например: CREATE INDEX person_name_idx ON person(last_name, first_name) Если существует таблица person с информацией о людях, то такой индекс пригодиться для поиска людей с нужными именем и фамилией. Причем ограничения будут устанавливаться на два столбца: last_name и first_name таблицы person: SELECT * FROM person WHERE last_name = ‘Ivanov’ AND first_name = ‘ Ivan’ Также индекс может использоваться, если ограничение идет только по первому столбцу, то есть по фамилии: SELECT * FROM person WHERE last_name = ‘Ivanov’ Однако, использовать индекс по второму столбцу (по имени) нельзя, т.к. данные сначала отсортированы по первому столбцу, столбцу с фамилиями, а потом по второму столбцу (с именами). Для удаления индекса используется команда DROP INDEX с названием индекса: DROP INDEX person_name_idx Недостатками индексов является их сложная структура, т.к. при изменении исходной таблицы, появляется необходимость изменения всех связанных с ней индексов, и снижение производительности. Если индексов много, то при изменении данных в таблице требуется длительное время для обновления индексов. Представления в SQL |