Удмуртский Государственный Университет реферат
| Вид материала | Реферат |
- «Удмуртский государственный университет», 302.5kb.
- Удмуртский Государственный Университет реферат, 136.44kb.
- Удмуртский Государственный Университет (наименование учебного заведения) Кафедра Информационной, 225.78kb.
- Удмуртский Государственный Университет Институт экономики и управления Кафедра финансов, 1976.72kb.
- Фгбоу впо «Удмуртский государственный университет», 68.29kb.
- Контрольная работа экз вопросы фгбоу впо «удмуртский государственный университет», 1136.39kb.
- Удмуртский государственный университет, 70.06kb.
- 57. Шквырина Анжелика Вячеславовна заявка на участие в I международном симпозиуме, 30.69kb.
- Субъективные основания социальной реальности: пространство смысла 09. 00. 11. социальная, 219.67kb.
- Представления об обществе в картине мира населения древней руси XI xiii вв. 07. 00., 653.67kb.
Министерство образования РФ
Удмуртский Государственный Университет
Реферат:
Оптимизация SQL запросов в СУБД Oracle.
Настройка приложений.
Выполнил: студент гр.38-41
Дюгуров Д. В.
Проверил: Вотинцев А.А.
Ижевск - 2004
Введение.
Понятие настройки производительности приложения (application tuning) относится к настройке используемых в приложении SQL-команд. Если приложение обладает графическим интерфейсом пользователя, то настройка приложения заключается также в настройке аналогов SQL-команд. Иначе говоря, все действия, выполняемые на уровне графического интерфейса пользователя, могут быть отображены на соответствующие SQL-команды (SELECT, INSERT, UPDATE и DELETE) различной сложности. В любом случае рекомендуется сначала настроить используемое приложение, а затем приступить к настройке остальных компонентов СУБД.
По разным субъективным оценкам общая производительность системы в среднем на 80% зависит от приложения, а остальные 20% приходятся на собственно базу данных. Однако следует отметить, что такое распределение ответственности за производительность системы соблюдается не всегда. Иногда, все может быть как раз наоборот — основное влияние на производительность СУБД могут оказывать не приложения, а базы данных.
Для администратора баз данных это означает, что производительность СУБД зависит от конкретной ситуации и является величиной относительной. Несмотря на эти замечания, все же нужно еще раз подчеркнуть основное правило, которого следует придерживаться при выполнении настройки:
Прежде всего, необходимо настроить приложение. Необходимость выполнения этого условия определяется двумя причинами. Во-первых, производительность большинства современных СУБД во многом определяется производительностью используемых приложений. Во-вторых, даже если это не так, администратор базы данных может классифицировать тип приложения, используемого для конкретной СУБД. Это значит, что администратор должен просмотреть приложение и выделить в нем все, что касается транзакций: код доступа и SQL-код. Даже если код приложения является достаточно эффективным с точки зрения доступа к базам данных, этот анализ поможет глубже разобраться в используемых методах взаимодействия программы с базами данных.
Необходимо сосредоточить основное внимание на тех 20% текста программы, которые ответственны за 80% производительности всего приложения.
С другой стороны, существует расхожее мнение о том, что оптимизация запросов, с точки зрения быстродействия, очень сильно затрудняет чтение и анализ программного кода. Тем более, что СУБД Oracle и так является самой быстродействующей из всех аналогов на рынке. Однако есть примеры запросов, время выполнения которых сократилось с 17 часов до 6 минут благодаря использованию правил оптимизации запросов и использованию встроенного оптимизатора Oracle.
Поэтому при разработке приложения рекомендуется использовать правила оптимизации запросов, даже если это вызовет трудности с сопровождением системы. При этом нужно четко понимать, что в оптимизацию реляционных запросов входят два различных аспекта. Во-первых, это внутренняя задача СУБД, которая заключается в определении наиболее оптимального (эффективного) способа выполнения реляционных запросов. Во-вторых, это задача программиста (или квалифицированного пользователя): она заключается в написании таких реляционных запросов, для которых СУБД могла бы использовать более эффективные способы нахождения данных.
Обработка запроса в реляционных СУБД. Оптимизатор.
Каждая команда языка манипулирования данными может быть выполнена разными способами. Определение наиболее оптимального плана выполнения запроса называется оптимизацией. Выбором этого плана занимается оптимизатор, являющийся частью СУБД.
Для оптимизатора следует выбрать такую последовательность доступа, которая обеспечит самый эффективный в рамках СУБД Oracle путь доступа к данным и формирование плана выполнения, основанного на найденных методах. Под методом доступа (access path) в нашем случае подразумевается вариант алгоритма доступа к затребованным из базы данным, а под планом выполнения (execution plan) — последовательность выполняемых действий, которые сопровождают выбранные методы доступа. Работа оптимизатора состоит из следующих 5 стадий.
На первой фазе запрос, представленный на языке запросов, подвергается лексическому и синтаксическому анализу. При этом вырабатывается его внутреннее представление, отражающее структуру запроса и содержащее информацию, которая характеризует объекты базы данных, упомянутые в запросе (отношения, поля и константы). Информация о хранимых в базе данных объектах выбирается из каталогов базы данных (словаря-справочника данных). Внутреннее представление запроса используется и преобразуется на следующих стадиях обработки запроса.
На второй фазе запрос в своем внутреннем представлении подвергается логической оптимизации. При этом могут применяться различные преобразования, "улучшающие" начальное представление запроса. Среди этих преобразований могут быть эквивалентные преобразования, после проведения которых получается внутреннее представление, семантически эквивалентное начальному (например, приведение запроса к некоторой канонической форме). Преобразования могут быть и семантическими, когда получаемое представление не является семантически эквивалентным начальному, но гарантируется, что результат выполнения преобразованного запроса совпадает с результатом запроса в начальной форме при соблюдении ограничений целостности, существующих в базе данных. В любом случае после выполнения второй фазы обработки запроса его внутреннее представление остается непроцедурным, хотя и является в некотором смысле более эффективным, чем начальное.
Третий этап обработки запроса состоит в выборе на основе информации, которой располагает оптимизатор, набора альтернативных процедурных планов выполнения данного запроса в соответствии с его внутреннем представлением, полученным на второй фазе. Основой является информация о существующих путях доступа к данным. Единственный путь доступа, который возможен в любом случае, – это последовательное чтение. Возможность использования других путей доступа зависит от способов размещения данных в памяти (например, кластеризация данных), наличия индексов и формулировки самого запроса. На этом же этапе для каждого плана оценивается предполагаемая стоимость выполнения запроса по этому плану. При оценках используется либо доступная оптимизатору статистическая информация о состоянии базы данных, либо информация о механизмах реализации различных путей доступа. Из полученных альтернативных планов выбирается наиболее оптимальный с точки зрения некоторого (заранее выбранного или заданного) критерия. Внутреннее представление этого плана теперь соответствует обрабатываемому запросу.
На четвертом этапе по внутреннему представлению наиболее оптимального плана выполнения запроса формируется процедурное представление плана.
Наконец, на последнем, пятом этапе обработки запроса происходит его реальное выполнение в соответствии с выполняемым планом запроса.
Технология оптимизатора Oracle, а также технологии, используемые для оптимизаторов другими разработчиками программного обеспечения, значительно развились за несколько последних лет. При работе с ранними версиями Oracle программисту приходилось быть очень внимательным при разработке SQL-кода, поскольку упорядочение операторов внутри SQL-выражения могло в значительной степени повлиять на его производительность. Аналогичную работу по оптимизации приходилось выполнять и администратору базы данных. Однако, чем лучше используемый оптимизатор, тем меньше оптимизирующих действий приходится выполнять пользователям, программистам и администратору базы данных; соответственно необходимая работа по оптимизации в Oracle8i по сравнению с предыдущими версиями существенно уменьшилась.
Существует два основных вида оптимизаторов.
• Оптимизатор, основанный на анализе заданных правил (rule-based optimizer). Этот оптимизатор выбирает методы доступа на основе предположения о статичности СУБД и в соответствии с заданной разработчиком системой правил выбора методов доступа к данным. Хотя приоритеты разных правил зависят от самого разработчика, их можно довольно легко сравнить и найти соответствие между результатами, полученными с помощью разных оптимизаторов. Такой оптимизатор учитывает иерархическое старшинство операций. Если для какой-либо операции существует более одного пути ее выполнения, то выбирается тот путь, чей ранг выше, т.к. в большинстве случаев он выполняется быстрее, чем путь с более низким рангом. План выполнения запроса формируется из выбранных путей доступа с максимальными рангами.
• Оптимизатор, основанный на анализе затрат (cost-based optimizer).
Для этого оптимизатора выбор метода доступа основан на хранимой внутренней статистике, которая обычно используется совместно со словарем данных СУБД. При использовании этого метода оптимизатор сначала строит несколько возможных планов выполнения запроса. При этом он применяет некоторые эвристики, т.е. правила, полученные опытным путем. Эти правила позволяют сузить пространство поиска оптимального плана благодаря тому, что неэффективные планы отбрасываются в самом начале и не рассматриваются. Примером эвристики может послужить такое правило: хорошим является такой план, в котором селекция производится на раннем этапе выполнения запроса. Для каждого из построенных планов рассчитывается его стоимость. Она определяется на основании статистики данных в таблицах, к которым обращается команда, и связанными с ними кластерами и индексами. Статистика касается распределения значений данных в таблице и может включать в себя количество записей в таблице, минимальные и максимальные значения данных по столбцам и другие параметры. Стоимость (затраты)– это оценка ожидаемого времени выполнения запроса с использованием конкретного плана выполнения. Оптимизатор может учитывать количество необходимых ресурсов памяти, стоимость операций ввода-вывода, времени процессора и оперативной памяти, необходимой для выполнения плана.
Оптимизация выполнения запроса осуществляется в следующем порядке:
1.Вычисление выражений и условий, содержащих константы.
2.Преобразование сложной команды в эквивалентную ей с использованием соединения (проводится не всегда).
3.Если команда выполняется над представлением, то оптимизатор обычно объединяет запрос на создание представления и запрос к этому представлению в одну команду.
4.Выбор метода оптимизации.
5.Выбор путей доступа к таблицам, к которым обращается запрос.
6.Выбор порядка соединения (если в запросе соединяются несколько таблиц, то оптимизатор определяет, какие две таблицы будут соединяться первыми, какая таблица следующей будет подключаться в результату и т.д.).
7.Выбор операции соединения для каждой команды соединения.
В Oracle версий 7.х и выше оптимизаторы могут работать по принципу анализа, как правил, так и затрат одновременно. В Oracle 6.х работа оптимизатора основана на анализе правил, и в полной мере реализовать технологию анализа затрат он не может. Во всех этих версиях для управления оптимизатором, программисту предоставляется возможность использовать специализированные указания (hint), которые размещаются непосредственно в строках кода SQL-выражения. Они принимаются во внимание оптимизатором, хотя и не всегда выполняются, поскольку носят рекомендательный характер. При отсутствии синтаксических ошибок оптимизатор обычно следует полученному указанию. Приведем пример выражения SELECT, содержащего указание оптимизатору об использовании индекса для таблицы, из которой извлекаются данные.
SQL> SELECT /*+ INDEX ПРОД_ПК*/ ИД FROM ПРОДАВЦЫ WHERE ИД =110; (1)
Заметим то, что указание следует сразу же после SQL-команды (SELECT, INSERT, UPDATE, DELETE). Указание для оптимизатора, как и любой другой комментарий SQL*Plus, начинается с символов /*, за которыми следует знак +, и заканчивается символами */. В данном примере предполагается, что для указанной таблицы существует только один индекс (первичный ключ); для задания конкретного индекса из нескольких имеющихся в указании следует использовать имя таблицы и имя нужного индекса.(SQL> SELECT /*+ INDEX ОФИС*/ ОФИС_КОД FROM ПРОДАВЦЫ WHERE ОФИС_КОД =И01;)
Оптимизатор воспринимает только одно указание для данного выражения или блока выражений; все остальные будут проигнорированы. Кроме того, все указания с неверным синтаксисом также игнорируются без вывода какого-либо сообщения об ошибке. Чтобы выявить факт игнорирования указания оптимизатором, необходимо воспользоваться планом выполнения, который более подробно описывается ниже
Ранжирование методов доступа
Оптимизатор Oracle при работе использует приведенные в таблице 1 ранги методов доступа – от самого быстрого в верхней части до самого медленного в нижней части этого перечня.
Таблица 1
Ранги | Метод доступа |
| 1 | Одна строка по ее идентификатору |
| 2 | Одна строка по объединению кластеров |
| 3 | Одна строка по хэш-ключу кластера с уникальным или первичным ключом |
| 4 | Одна строка по уникальному или первичному ключу |
| 5 | Объединение кластеров |
| 6 | Кэш-ключ кластера |
| 7 | Индекс кластера |
| 8 | Составной индекс |
| 9 | Индекс на основе одного столбца |
| 10 | Ограниченный диапазон поиска по индексированным столбцам |
| 11 | Неограниченный диапазон поиска по индексированным столбцам |
| 12 | Объединение с сортировкой и слиянием |
| 13 | Поиск максимального или минимального значения по индексированным столбцам |
| 14 | Упорядочение по индексированным столбцам |
| 15 | Полное сканирование таблицы |
Таким образом, если оптимизатор основан на анализе правил, то он будет выбирать из всех доступных для данного запроса методов самый быстрый. Покажем тот же запрос, который был приведен ранее, но без указания для оптимизатора и с изменением оператора WHERE. (Запрос с неограниченным диапазоном поиска)
SQL> SELECT ИД FROM ПРОДАВЦЫ WHERE ИД >=110; (2)
При использовании в Oracle стратегии, основанной на анализе правил, и наличии первичного ключа поля ИД таблицы ПРОДАВЦЫ, для оптимизации кода в (1) будет применен метод доступа 11 (неограниченный диапазон поиска по индексированным столбцам). Неограниченный диапазон поиска проявляется в том случае, если в операторе WHERE использован один из операторов сравнения, за исключением проверки равенства (неравенства) — "больше" (>), "больше или равно" (>=), "меньше" (<) и "меньше или равно" (<=). В результате диапазон поиска ограничивается только с одной стороны— сверху или снизу. Вторая граница диапазона поиска до начала выполнения запроса неизвестна.
Если оператор WHERE содержит ограниченный диапазон поиска (т.е. содержит два условных оператора — по одному с каждой границы диапазона), то обе границы диапазона будут известны еще во время синтаксического анализа выражения. Превратить неограниченное WHERE в ограниченное можно и с помощью литерала максимального значения (если оно известно) или с помощью предполагаемого (теоретического) максимального значения для столбца, исходя из априорных знаний о данных в столбце. Однако использование оператора МАХ (оператора определения максимального значения) наряду с исходным неограниченным диапазоном поиска крайне нежелательно, так как в табл. 1 ранг этого метода поиска (13) ниже ранга неограниченного (11) и ограниченного (10) диапазонов поиска. Использование оператора МАХ для поиска максимального значения приведет только к замедлению выполнения запроса.
SQL> SELECT ИД FROM ПРОДАВЦЫ WHERE ИД >=103 and ИД <=110; (3)
5QL> SELECT ИД FROM ПРОДАВЦЫ WHERE ИД >103 AND ИД <=MAX(ИД);
Задание режима оптимизации.
Одним из важнейших вопросов при работе с оптимизатором является способ выбора режима его работы. Дело в том, что описанные выше виды оптимизации могут быть заданы на уровне экземпляра, сеанса или выражения. Для указания режима оптимизации на уровне экземпляра в файле параметров init.ora следует использовать приведенные ниже значения параметра OPTIMIZER_MODE.
• CHOOSE. При установке этого значения будет выбрана оптимизация, основанная на анализе затрат, при наличии у администратора базы данных соответствующих статистических данных. В противном случае будет использована оптимизация, основанная на анализе правил.
• RULE. При установке этого значения будет использована оптимизация, основанная на анализе правил.
• FIRST ROWS. Это значение используется для минимизации времени отклика, т.е. для сведения к минимуму временного интервала между вводом запроса в СУБД и появлением результатов на экране. При этом будет выбран вариант оптимизации, основанный на анализе затрат (при наличии соответствующих статистических данных). Это значение следует использовать только в интерактивном приложении с множеством экранных форм вывода информации. Типичный пример— OLTP-системы и некоторые DSS-системы.
• ALL ROWS. При установке этого значения будет использована оптимизация, основанная на анализе затрат (опять же, при наличии соответствующих статистических данных) для минимизации общего количества строк, проходящих через систему за единицу времени (в транзакциях за секунду). Это значение следует использовать при работе с системами пакетной обработки или большими DSS-системами.
Для указания режима оптимизации на уровне сеанса следует применить показанное ниже DDL-выражение, в котором для параметра режим задается одно из приведенных выше значений (CHOOSE, RULE, FIRST_ROWS, ALL_ROWS):
SQL> ALTER SESSION SET OPTIMIZER_GOAL=<;>
Однако заданный таким образом режим оптимизации будет использоваться только при работе в данном сеансе, а при работе в другом сеансе его следует указать вновь.
Для задания режима оптимизации на уровне выражения следует использовать перечисленные в табл. 2 ключевые слова методов доступа либо использовать одно из приведенных выше значений (CHOOSE0 RULE, FIRST ROWS,ALL ROWS).
Таблица 2
| Ключевое слово | Метод извлечения строк метода доступа |
| ROWID | Для извлечения строк используется их идентификатор |
| CLUSTER | Сканирование ключа кластера |
| HASH | Сканирование хэш-индекса |
| INDEX | Сканирование индекса |
| INDEX_ASC | Сканирование индекса в порядке возрастания |
NDEX_DESC | Сканирование индекса в порядке убывания |
| AND_EQUAL | Использование нескольких индексов со слиянием результатов |
| ORDERED | Использование порядка таблиц, указанного в предложении FROM, в качестве порядка их объединения |
| USE_NL | Использование вложенных циклов для объединения таблиц USE_MERGE Использование сортировки со слиянием для объединения таблиц FULL Полное сканирование таблицы |
В Oracle есть средства позволяющие отслеживать метод выборки данных из таблицы, и режим работы оптимизатора (например Explain Plan в PL/SQL).
Анализ запросов с целью повышения скорости их выполнения.
Правила построения запросов. Настройка приложений.
Вся работа, связанная с попытками изменения формулировки запросов для повышения эффективности их выполнения, называется коррекцией запросов (query rewriting). Приведенные примеры являются достаточно стандартными, и с их помощью можно понять основные принципы оптимизации. Например, для извлечения максимального значения из столбца потребуется время, сравнимое со временем полного сканирования таблицы — независимо от того, индексирован ли этот столбец. Это становится ясным при ознакомлении с таблицей рангов методов доступа. Однако существуют менее очевидные и более экзотические способы коррекции запросов. Теперь, после знакомства с работой оптимизатора на основе анализа правил, можно приступить к рассмотрению работы оптимизатора, основанного на анализе затрат, на примерах, показанных далее:
SQL> SELECT ИД FROM ПРОДАВЦЫ WHERE ДОЛЖНОСТЬ='МЕНЕДЖЕР'', (4)
SQL> SELECT ИД FROM ПРОДАВЦЫ WHERE ДОЛЖНОСТЬ=' ПРОДАВЕЦ '', (5)
Допустим, что приведенные запросы созданы для достаточно большой торговой организации с 10 менеджерами, 1000 продавцов и общим числом сотрудников — около 6000. Анализируя эти запросы, можно заключить, что из двух типов оптимизации лучше выбрать оптимизацию, основанную на анализе затрат, а не оптимизацию, основанную на анализе правил. Действительно, если применяется оптимизация, основанная на анализе правил, то при наличии неуникального индекса по столбцу ДОЛЖНОСТЬ для обоих запросов будет выбран метод доступа 9 (индекс на основе одного столбца). С другой стороны, при использовании оптимизации, основанной на анализе затрат, знание некоторых характеристик распределения данных (например, того, что строки с данными о продавцах составляют 1/6 всех строк, а строки с данными о менеджерах составляют 1/600 часть всех строк) позволяет применять неуникальный индекс для запроса в (4). Однако для выполнения запроса в (5) будет уместно и эффективно полное сканирование таблицы (т.е. использование метода доступа 15). Таким образом, выбор вариантов методов доступа базируется на априорной информации о конкретной структуре данных.
При необходимости доступа к значительной части строк какой-либо таблицы полное сканирование является более эффективным, чем индексное. Дело в том, что для сканирования индекса и извлечения строки требуются, по крайней мере, две операции чтения для каждой строки, а в некоторых случаях и больше — в зависимости от количества уникальных данных в индексе. А при полном сканировании таблицы для извлечения строки требуется только одна операция чтения. При доступе к большому количеству строк — как, например, в запросе (5) — становится очевидной неэффективность использования индекса по сравнению с полным сканированием таблицы, при котором строки считываются непосредственно из таблицы. Помимо уменьшения общего количества операций чтения записей таблицы, полное сканирование при определенных обстоятельствах обладает еще одним преимуществом по сравнению с индексным сканированием. Дело в том, что большое количество строк таблицы часто требуется извлекать последовательно или в порядке, близком к последовательному. Очевидно, что если считывается индекс, потом таблица, потом опять индекс, а потом опять таблица, последовательный порядок извлечения данных соблюдаться не будет. Таким образом, для запроса, показанного в (5), использование индекса только замедлит выполнение.
Использование индекса гораздо предпочтительнее полного сканирования таблицы при работе с запросом, показанным в (4). В этом случае потребуется извлечь незначительное количество строк (10), а общее количество операций чтения будет равняться 20 ( т.е. по 2 операции чтения для извлечения каждой из 10 строк). При использовании полного сканирования таблицы придется выполнить
6000 операций чтения, поскольку при этом неизвестно точное расположение искомых строк и для их поиска придется просмотреть всю таблицу. Запрос, приведенный в (4), является классическим примером случая, когда необходимо использовать индекс, особенно если такой запрос выполняется часто. Из этого сравнительного анализа можно сделать следующий вывод. Пользователь и программист, которые привыкли применять оптимизацию только на основе анализа правил, могут даже не догадываться о существовании более эффективного способа выполнения запроса (5), выбор которого основан на оптимизации согласно анализу затрат.
Настройка команд SQL, которые используются в приложении, – это один из основных способов повышения производительности системы. Эта настройка должна производиться каждым разработчиком. Для оптимизации приложений необходимо иметь представление о порядке и механизмах реализации запросов в СУБД.
В ОП хранятся все результаты ранее выполненных запросов до тех пор, пока эта память не потребуется для записи результатов последующих запросов. Подготовленные к исполнению SQL-операторы обычно помещаются в разделяемую SQL-область. Перед началом выполнения запроса система проверяет, есть ли в этой области аналогичный запрос: если есть, то он отправляется на выполнение минуя стадию предварительной обработки (компиляции). Составляя запросы таким образом, чтобы они совпадали в уже имеющимися в SQL-области, можно исключить предобработку запроса, что является важным моментом оптимизации приложений.
Рекомендации по написанию запросов.
1.Если запрос содержит несколько условий, то они должны располагаться в порядке уменьшения селективности. Например, при условии, что во втором отделе сотрудников около 5% от общего числа сотрудников предприятия, а женщин – примерно половина, запрос должен выглядеть так:
select * from emp
where deptNo=2 and sex='ж';
2.В запросе, который реализует соединение двух и более таблиц, эти таблицы должны стоять в списке в порядке уменьшения количества записей в них. Например, список пациентов по отделениям:
select * from patients p, depart d
where p.deptNo=d.id;
3.Если запрос содержит условие для проиндексированного поля маленькой таблицы, которая может быть считана за одно обращение к памяти, то запрос нужно сформулировать так, чтобы система игнорировала индекс. Например, запрос на выборку названия отделения №3:
select name from depart
where id*1=3;
id – это первичный ключ, по нему есть индекс. Но при доступе через индекс потребуется минимум два обращения к диску. Включение индексированного поля в выражение (id*1 вместо id) подавляет использование индекса.
4.Используйте UNION ALL вместо UNION, если уверены в том, что в объединяемых отношениях отсутствуют одинаковые записи (или наличие одинаковых записей некритично). Дело в том, что UNION вычисляется путем сортировки, которая может занять много времени.
5.Варьировать использование UNION или OR в зависимости от наличия индекса. Например, список пациентов палат №№3 и 8 при наличии индекса должен быть таким:
select * from patients
where room=3
union all
select * from patients
where room=8;
а если индекса нет, то таким:
select * from patients
where room=3 or room=8;
6.Условие '<>' подавляет использование индекса, поэтому, если значения индексированного столбца распределены неравномерно, следует заменять его на комбинацию '<' or '>' и, с учетом правила (4), реализовывать это с помощью UNION. Например, список сотрудников всех филиалов (10%), кроме центрального офиса:
select * from emp
where deptNo<3
union all
select * from emp
where or deptNo>3;
7.При соединении двух больших таблиц лучше, чтобы по каждой таблице был произведен отбор записей. Такой отбор можно ввести искусственно:
Вместо такого запроса
select * from a, b
where a.f1 = b.f1 and a.f1 = 2;
можно использовать запрос:
select * from a, b
where a.f1 = b.f1
and a.f1 = 2
and b.f1 in (select f1 from a
where f1 = 2);
Во многих практических случаях (но не всегда) второй запрос выполняется быстрее.
8.Если после слияния таблиц отбираются поля только из одной таблицы, то вместо операции join надо использовать операцию in. Это может значительно ускорить выполнение запроса.
Исходный запрос:
select emp.name
from emp, empjob
where emp.no = empjob.emp
and empjob.salary > 900;
Оптимизированный запрос:
select name
from emp
where no in
(select emp
from empjob
where salary > 900);
9.Если после группировки надо отсортировать результат, то желательно, чтобы поля сортировки и поля группировки перечислялись в одном порядке. Тогда после выполнения группировки результат будет уже отсортирован как надо (Суммарное время выполнения такого запроса уменьшается).
Исходный запрос:
select emp, job, max(salary)
from empjob
group by job, emp
order by emp, job desc;
Оптимизированный запрос:
select emp, job, max(salary)
from empjob
group by emp, job
order by emp, job desc;
10.Выражения, заданные в конструкции having, выполняются после группировки записей, а выражения, указанные в where, выполняются до группировки. Если возможно перенести выражения из having в where, то можно будет сократить время выполнения группировки.
Исходный запрос:
select emp, max(salary)
from empjob
group by emp
having job = (select no from job where name = 'Клерк');
Оптимизированный запрос
select emp, max(salary)
from empjob
group by emp
where job = (select no from job where name = 'Клерк');
Нельзя забывать, что помимо использования правил написания запросов в настройку приложений входит:
· создание индексов;
· определение потребности в кластеризации и хешировании данных;
· выбор метода оптимизации SQL-запросов;
· использование средств сбора статистики.
При настройке команд SQL важно помнить, что настраивая одну из них можно оказать влияние (и не всегда позитивное) на другие команды. Например, при создании дополнительного индекса могут поменяться планы выполнения всех команд, работающих с этой таблицей.
Заключение.
Таким образом, переходя к настройке приложения необходимо оценить объем информации о самой базе данных, обеспечить сбор статистики, проанализировать процесс сопровождения системы и исходя из этих данных комбинировать анализ правил и анализ затрат при применении оптимизатора и следовать тем или иным рекомендациям по написанию запросов.
Однако, быстродействие приложения далеко не на 100% зависит от того, на сколько грамотно составлены SQL-запросы. Необходимо помнить, что быстродействие зависит и от того, как размещены файлы БД на дисках сервера, количества клиентских мест, как организован клиентский кеш начитываемых данных, как распределена на сервере процессорная нагрузка и т.д.
Список литературы
- Фейейрштейн С., Прибыл Б. «Oracle PL/SQL для профессионалов.» СПб.: Питер 2003
- Урман С. «Oracle 8: программирование на языке PL/SQL» М.: Лори 1999
- Хотка Д. «Oracle 8i на примерах». М., СПб., К.: Вильямс 2001 г.
- Карпова И.П. «Оптимизация реляционных запросов. Материалы к лекциям по курсу "Базы данных" МГУ.» 1999 г.
- Материалы различных сайтов Интернета.