лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
Oracle не поддерживает INFORMATION_SCHEMA. Справедливости ради следует сказать, что Oracle предоставляет возможность использовать системные представления вместо непосредственного обращения к системным таблицам, что также позволяет безопасно изменять структуру системных таблиц. Приведем ряд типичных запросов в стандартном варианте и для Oracle. 1. Список таблиц базы данных (текущей) Стандартное 1. SELECT table_name FROM information_schema. TABLES ; Oracle 1. SELECT table_name FROM all_tables WHERE owner= 'ИМЯ СХЕМЫ' ; 2. Список столбцов заданной таблицы (например, PC) Стандартное 1. SELECT column_name FROM information_schema. COLUMNS WHERE table_name= 'PC' ; Oracle 1. SELECT column_name FROM all_tab_columns WHERE table_name= 'PC' ; 3. Тип данных и размер заданного символьного столбца таблицы (столбец model таблицы PC) Стандартное 1. SELECT column_name, data_type, CHARACTER_MAXIMUM_LENGTH 2. FROM information_schema. COLUMNS WHERE table_name= 'PC' AND column_name = 'model' ; Oracle 1. SELECT column_name,data_type,data_length FROM all_tab_columns 2. WHERE table_name= 'PC' AND column_name= 'MODEL' ; 4. Имя ограничения первичного ключа таблицы PC Стандартное 1. SELECT table_name,constraint_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 2. WHERE table_name= 'PC' AND CONSTRAINT_TYPE= 'Primary key' ; Oracle 1. SELECT table_name,constraint_name FROM all_constraints 2. WHERE table_name= 'PC' AND constraint_type= 'P' ; 5. Список столбцов, составляющих первичный ключ таблицы Utb Стандартное 1. SELECT table_name, column_name 2. FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE table_name= 'utb' 3. AND constraint_name= ( SELECT constraint_name FROM INFORMATION_SCHEMA.table_constraints 4. WHERE table_name= 'utb' AND CONSTRAINT_TYPE= 'Primary key' ) ; Здесь мы в основном запросе получаем столбцы, участвующие в ограничениях, и выбираем те из них, у которых имя ограничения совпадает с именем ограничения первичного ключа для той же таблицы. В принципе, условие table_name='utb' основного запроса является избыточным с точки зрения получения правильного ответа, поскольку имя ограничения является уникальным для всей базы. Однако ранняя фильтрация существенно ускоряет выполнение запроса. Оракл 1. SELECT table_name, column_name FROM all_ind_columns WHERE table_name= 'UTB' 2. AND index_name= ( SELECT constraint_name FROM all_constraints 3. WHERE table_name= 'UTB' AND constraint_type= 'P' ) ; Вопросы оптимизации Настройка производительности СУБД - многоуровневый процесс, и оптимизация запросов является лишь одним из его аспектов. Под оптимизацией запроса понимаются действия, приводящие к тому, что оптимизатор запросов выбирает наилучший процедурный план его выполнения. Зачастую это сводится к созданию или перестройке соответствующих индексов, обновлению статистики и т.д., т.е. действиям, не приводящим к переписыванию самого запроса. Чтобы выполнять такого рода оптимизацию, необходимо умение читать план выполнения запроса и понимать, как выполняются физические операторы, фигурирующие в плане. Этим вопросам посвящены статьи и переводы, которые публикуются в блогах на sql-ex.ru Здесь же мы будем рассматривать несколько иные вопросы, которые более соответствуют тематике настоящего учебника. Эти вопросы связаны с тем, что одну и ту же практическую задачу можно решить по разному, т.е. использовать различные алгоритмы, реализованные затем в SQL. Грубо говоря, можно так написать запрос, что никакая оптимизация не повысит его производительности до требуемого уровня. Многочисленные примеры сказанного вы можете найти на форумах sql-ex.ru, где публикуются разнообразные решения одной и той же задачи. А имеющийся на сайте оптимизационный этап непосредственно связан с написанием производительных запросов. Основные операции плана выполнения SQL Server Данная статья представляет собой описание основных операций, отображаемых в планах выполнения запросов СУБД MS SQL Server. Index Seek Поиск по некластеризованному индексу. В большинстве случаев является хорошим для производительности, так как представляет собой прямой доступ SQL Server к требуемым строкам данных. Однако это вовсе не означает, что он всегда работает быстро, например, если он возвращает большое число строк, то по производительности он будет практически равен Index Scan. Подробнее... Index Scan Сканирование некластеризованного индекса. Обычно наличие этой операции плохо отражается на производительности, поскольку она предполагает последовательное чтение индекса для извлечения большого числа строк, приводя к более медленной обработке. Но бывают исключения, например, применение директивы TOP, ограничивающей число возвращаемых записей; если возвращать всего несколько строк, то операция сканирования будет выполняться достаточно быстро, и вы не сможете получить лучшую производительность, чем ту, которую уже имеете, даже если вы попытаетесь перестроить запрос/индексы, чтобы добиться операции Index Seek. Подробнее... RID Lookup Поиск идентификатора записи, является узким местом производительности запроса. Но это легко исправить: если вы видите этот оператор, это означает, что у вас отсутствует кластеризованный индекс на таблице. По крайней мере, вы должны добавить кластеризованный индекс, и тут же получите некоторый рост производительности для большинства ваших запросов. Подробнее... Key Lookup Поиск ключей. Возникает, когда SQL Server предполагает, что он с большей эффективностью может использовать некластеризованный индекс, а затем перейти к кластерзованному индексу для поиска оставшихся значения строк, которые отсутствуют в некластеризованном индексе. Это не всегда плохо: обращение SQL Server к кластеризованному индексу для извлечения недостающих значений довольно эффективный метод по сравнению с необходимостью создавать и поддерживать совершенно новые индексы. Однако, если все, что нужно SQL Server от операции Key Lookup, это единственный столбец данных, гораздо проще добавить этот столбец в ваш существующий некластеризованный индекс. Размер индекса увеличится на один столбец, но SQL Server сможет избежать необходимости обращаться к двум индексам для извлечения всех необходимых данных и это в целом окажется более эффективным решением. Подробнее... Sort Сортировка является одной из наиболее дорогих операций, которые могут быть в плане выполнения, поэтому лучше избегать ее, насколько это возможно. Простой способ избежать оператора сортировки – иметь данные, хранящиеся в предварительно упорядоченном виде. Это может быть выполнено созданием индекса с ключевыми столбцами, перечисленными в том же самом порядке, который использует оператор сортировки. Если SQL Server должен выполнить сортировку одних и тех же данных в одном и том же порядке несколько раз в плане выполнения, то еще одним выходом является разбиение запроса на несколько этапов при использовании временных индексированных таблиц для сохранения данных между этапами. В таком случае, если вы будете повторно использовать временную таблицу в плане выполнения вашего запроса, то вы получите чистую экономию. Подробнее... Spool Спулы бывают разных типов, но большинство из них можно сформулировать как операторы, которые сохраняют промежуточную таблицу результатов в tempdb. SQL Server часто использует спул для обработки сложных запросов, преобразуя данные во временную таблицу в базе tempdb для использования её данных в последующих операциях. Побочным эффектом здесь является необходимость записи данных на диск. Для ускорения выполнения запроса можно попытаться найти способ его перезаписи таким образом, чтобы избежать спула. Если это не получается, использую метод "разделяй и властвуй" для временных таблиц, который может также заменить спул, обеспечивая больший контроль по сравнению с тем, как SQL Server записывает и индексирует данные в tempdb. Подробнее... Merge Join Соединение слиянием. Редко встречаются в реальных запросах, как правило, являются наиболее эффективными из операторов логического соединения. Оптимизатор выбирает использование соединение слиянием, когда входные данные уже отсортированы или SQL Server может выполнить сортировку данных с относительно небольшой стоимостью. Операция неприменима, если входные данные не отсортированы. Подробнее... Nested Loops Join Соединение вложенными циклами. Встречаются очень часто. Выполняют довольно эффективное соединение относительно небольших наборов данных. Соединение вложенными циклами не требует сортировки входных данных. Однако производительность можно улучшить при помощи сортировки источника входных данных; SQL Server сможет выбрать более эффективный оператор, если оба входа отсортированы. Операция неприменима, если данные слишком велики для хранения в памяти. Подробнее... Hash Match Join Операция используется всегда, когда невозможно применить другие виды соединения. Она выбираются оптимизатором запросов по одной из двух причин: 1. Соединяемые наборы данных настолько велики, что они могут быть обработаны только с помощью Hash Match Join. 2. Наборы данных не упорядочены по столбцам соединения, и SQL Server думает, что вычисление хэшей и цикл по ним будет быстрей, чем сортировка данных. При первом сценарии трудно оптимизировать выполнение запроса, если только не найти способа соединять меньшие объемы данных. При втором же сценарии, если есть некоторый способ получить данные в упорядоченном виде до соединения, типа предопределенного порядка сортировки в индексе, то возможно, что SQL Server выберет вместо этой операции более быстрый алгоритм соединения. Операторы Hash Match Join достаточно эффективны тогда, когда не сбрасывают данные в tempdb. Подробнее... Parallelism Операторы параллелизма обычно считаются хорошими вещами: SQL Server дробит ваши данные на множество частей для асинхронной обработки на множестве процессоров, сокращая общее время работы, требуемое для выполнения вашего запроса. Однако параллелизм может стать плохим, если большинство запросов используют его. При параллелизме процессоры по-прежнему выполняют тот же самый объем работы, что и без него, тем самым отнимая ресурсы у других запросов, которые могут быть запущены, плюс накладывается дополнительная нагрузка на SQL Server по дроблению и последующему объединению всех данных из множества нитей выполнения. Если параллелизм является узким местом производительности, можно рассмотреть вопрос об изменении порогового значения стоимости для настройки параллелизма, если оно установлено слишком низким. Подробнее... Stream Aggregate Статистическое выражение потока. Группирует строки в один или несколько столбцов и вычисляет одно или несколько агрегатных выражений (пример: COUNT, MIN, MAX, SUM и AVG), возвращенных запросом. Выход этого оператора может быть использован последующими операторами запроса, возвращен клиенту или то и другое. Оператору Stream Aggregate необходимы входные данные, упорядоченные по группируемым столбцам. Оптимизатор использует перед этим оператором оператор Sort, если данные не были ранее отсортированы оператором Sort или используется упорядоченный поиск или просмотр в индексе. Compute Scalar Оператор Compute Scalar вычисляет выражение и выдает вычисляемую скалярную величину. Затем эту величину можно вернуть пользователю или сослаться на нее в каком-либо запросе, а также выполнить эти действия одновременно. Примерами одновременного использования этих возможностей являются предикаты фильтра или соединения. Всегда возвращает одну строку. Часто применяется для того, чтобы конвертировать результат Stream Aggregate в ожидаемый на выходе тип int (когда Stream Aggregate возвращает bigint в случае с COUNT, AVG при типах столбцов int). Concatenation Оператор просматривает несколько входов, возвращая каждую просмотренную строку. Используется в запросах с UNION ALL. Копирует строки из первого входного потока в выходной поток и повторяет эту операцию для каждого дополнительного входного потока. Filter Оператор просматривает входные данные и возвращает только те строки, которые удовлетворяют критерию фильтрации (предикату). Описание операций плана выполнения в Postgresql Данная статья представляет собой описание основных операций, отображаемых в планах выполнения запросов СУБД PostgreSQL. Seq Scan Операция сканирует всю таблицу в порядке, в котором она хранится на диске. Обычно наличие этой операции плохо отражается на производительности, поскольку она предполагает последовательное чтение для извлечения большого числа строк, приводя к более медленной обработке. Но бывают исключения, например, применение директивы Limit, ограничивающей число возвращаемых записей. Index Scan Сканирование индекса выполняет обход индекса, просматривает конечные узлы, чтобы найти все соответствующие записи, и извлекает соответствующие данные из таблицы. В большинстве случаев является хорошей для производительности, так как представляет собой прямой доступ к требуемым строкам данных. Bitmap Index Scan В то время как Index Scan извлекает один указатель кортежа за раз из индекса и немедленно переходит к этому кортежу в таблице, Bitmap Index Scan извлекает все указатели кортежей из индекса за один раз, сортирует их, используя структуру данных "битовая карта (bitmap)" в оперативной памяти, а затем просматривает кортежи таблиц в порядке физического расположения кортежей. Merge Join Соединение слиянием объединяет два отсортированных списка. Обе стороны объединения должны быть предварительно отсортированы. Nested Loops Соединение вложенными циклами объединяет две таблицы, выбирая результат из одной таблицы и запрашивая другую таблицу для каждой строки из первой. Встречается очень часто. Выполняет довольно эффективное соединение относительно небольших наборов данных. Соединение вложенными циклами не требует сортировки входных данных. Hash Join Хеш-соединение загружает записи-кандидаты с одной стороны соединения в хеш-таблицу, которая затем проверяется для каждой строки с другой стороны соединения. Операция используется всегда, когда невозможно применить другие виды соединения: если соединяемые наборы данных достаточно велики и/или наборы данных не упорядочены по столбцам соединения. Sort Сортирует набор по столбцам, указанным в Sort Key. Операция сортировки требует больших объемов памяти для материализации промежуточного результата. Aggregate Появляется в плане, если в запросе есть агрегатная функция, используемая для вычисления отдельных результатов из нескольких входных строк: COUNT, SUM, AVG, MAX, MIN и прочие. GroupAggregate Группирует предварительно отсортированный набор в соответствии с предложением GROUP BY. Эта операция не буферизует промежуточный результат. HashAggregate Использует временную хэш-таблицу для группировки записей. Операция HashAggregate не требует предварительно отсортированного набора данных, вместо этого она использует большие объемы памяти для материализации промежуточного результата. Вывод не упорядочен каким-либо значимым образом. Unique Удаляет дублирующиеся данные. Практически не требует памяти: сравнивает значение в предыдущей строке с текущим и, если они одинаковые, отбрасывает его. Данные должны быть предварительно отсортированны. Filter Применяет фильтр к набору строк. Limit Прерывает выполение операций, когда было выбрано нужное количество строк. Append Запускает множество субопераций и возвращает все возвращенные ими строки в виде общего результата. Используется в запросах, содержащих UNION или UNION ALL. HashSetOp Операция используется операциями INTERSECT и EXCEPT (с опциональным модификатором ALL). Она работает следующим образом: запускает субоперации Append для пары подзапросов, а затем, на основании результата и опционального модификатора ALL, решает, какие строки нужно вернуть. Materialize Операция получает данные из нижележащей операции и размещает их в памяти (или частично в памяти), чтобы ими можно было быстрее воспользоваться, или добавляет им дополнительные свойства, которые предыдущая операция не предоставляет. CTE Scan Схожа с Materialize. Операция запускает часть запроса и сохраняет ее вывод, чтобы он мог быть использован другой частью (или частями) запроса. SubPlan Операция означает подзапрос, в котором есть ссылки на основной запрос. Вызывается, чтобы посчитать данные из подзапроса, которые зависят от текущей строки. InitPlan Операция означает подзапрос, у которого нет ссылок на основной запрос. Операция появляется в плане, когда есть часть запроса, которую можно (или нужно) вычислить прежде всего, и она не зависит ни от чего в остальной части запроса. Subquery Scan Операция означает подзапрос, входящий в UNION. Описание операций плана выполнения в Oracle Данная статья представляет собой описание основных операций, отображаемых в планах выполнения запросов СУБД Oracle RDBMS. Index Unique Scan Выполняет только обход B-дерева. Эта операция используется, если уникальное ограничение гарантирует, что критерии поиска будут соответствовать не более чем одной записи. |