table access full oracle что значит
Описание операций плана выполнения в Oracle
Данная статья представляет собой описание основных операций, отображаемых в планах выполнения запросов СУБД Oracle RDBMS.
Index Unique Scan
Выполняет только обход B-дерева. Эта операция используется, если уникальное ограничение гарантирует, что критерии поиска будут соответствовать не более чем одной записи.
Index Range Scan
Выполняет обход B-дерева и просматривает цепочки конечных узлов, чтобы найти все подходящие записи.
Index Full Scan
Читает индекс целиком (все строки) в порядке, представленном индексом. В зависимости от различной системной статистики СУБД может выполнять эту операцию, если нужны все строки в порядке индекса, например, из-за соответствующего предложения ORDER BY. Вместо этого оптимизатор может также использовать операцию Index Fast Full Scan и выполнить дополнительную операцию сортировки.
Index Fast Full Scan
Читает индекс целиком (все строки) в порядке, хранящемся на диске. Эта операция обычно выполняется вместо полного сканирования таблицы, если в индексе доступны все необходимые столбцы. Подобно операции TABLE ACCESS FULL, INDEX FAST FULL SCAN может извлечь выгоду из многоблочных операций чтения.
Table Access By Index ROWID
Извлекает строку из таблицы, используя ROWID, полученный из предыдущего поиска по индексу.
Table Access Full
Полное сканирование таблицы. Читает всю таблицу (все строки и столбцы), в порядке, хранящемся на диске. Хотя многоблочные операции чтения значительно повышают скорость сканирования полной таблицы, это все еще одна из самых дорогих операций. Помимо высоких затрат времени ввода-вывода, полное сканирование таблицы должно проверять все строки таблицы, что также занимает значительное количество процессорного времени.
Merge Join
Соединение слиянием объединяет два отсортированных списка. Обе стороны объединения должны быть предварительно отсортированы.
Nested Loops
Соединение вложенными циклами объединяет две таблицы, выбирая результат из одной таблицы и запрашивая другую таблицу для каждой строки из первой. Встречается очень часто. Выполняет довольно эффективное соединение относительно небольших наборов данных. Соединение вложенными циклами не требует сортировки входных данных.
Hash Join
Хеш-соединение загружает записи-кандидаты с одной стороны соединения в хеш-таблицу, которая затем проверяется для каждой строки с другой стороны соединения. Операция используется всегда, когда невозможно применить другие виды соединения: если соединяемые наборы данных достаточно велики и/или наборы данных не упорядочены по столбцам соединения.
Sort Unique
Сортирует строки и устраняет дупликаты.
Hash Unique
Более эффективная реализация алгоритма сортировки и устранения дупликатов с использованием хэш-таблицы. Заменяет операцию Sort Unique в определенных обстоятельствах.
Sort Aggregate
Вычисляет суммарные итоги с использованием агрегатных функций SUM, COUNT, MIN, MAX, AVG и пр.
Sort Order By
Сортирует результат в соответствии с предложением ORDER BY. Эта операция требует больших объемов памяти для материализации промежуточного результата.
Sort Group By
Сортирует набор записей по столбцам GROUP BY и агрегирует отсортированный результат на втором этапе. Эта операция требует больших объемов памяти для материализации промежуточного результата.
Sort Group By Nosort
Агрегирует предварительно отсортированный набор записей в соответствии с предложением GROUP BY. Эта операция не буферизует промежуточный результат.
Hash Group By
Группирует результат, используя хеш-таблицу. Эта операция требует больших объемов памяти для материализации промежуточного набора записей. Вывод не упорядочен каким-либо значимым образом.
Filter
Применяет фильтр к набору строк.
Создает промежуточное представление данных.
Count Stopkey
Прерывает выполение операций, когда было выбрано нужное количество строк.
Sort Join
Сортирует набор записей в столбце соединения. Используется в сочетании с операцией Merge Join для выполнения сортировки соединением слияния.
Intersection
Выполняет операцию пересечения между двумя источниками.
Union-All
Выполняет операцию объединения всех записей между двумя таблицами. Дублирующиеся строки не удаляются.
Load As Select
Прямая загрузка с использованием оператора SELECT в качестве источника.
Temp Table Generation/Transformation
Создает/преобразует временную таблицу. Используется в специфичных для Oracle преобразованиях типа Star.
Методы доступа к данным в Oracle
Не найдя на хабре статьи, объединяющей в удобном для чтения виде информацию о методах доступа к данным, используемых СУБД Oracle, я решил совершить «пробу пера» и написать эту статью.
Общая информация
Не углубляясь в детали, можно утверждать что Oracle хранит данные в таблицах, вместе с которыми могут существовать особые структуры данных – индексы, призванные ускорить запросы к таблицам. При выполнении запросов Oracle по-разному обращается к таблицам и индексам – способы доступа к данным в различных ситуациях и являются предметом этой статьи.
Для примеров мы будем использовать следующую таблицу и данные в ней:
Для анализа плана выполнения запроса будем пользоваться следующими средствами:
После создания индекса и использования его в примерах и перед созданием следующего индекса, он должен быть удален. Это можно сделать с помощью следующего запроса:
TABLE FULL SCAN
Данный метод доступа, как следует из названия, подразумевает перебор всех строк таблицы с исключением тех, которые не удовлетворяют предикату where (если таковой есть). Применяется он либо в случае, когда условия предиката отсутствуют в индексе, либо когда индекса нет в принципе. Примеры:
TABLE ACCESS BY ROWID, он же ROWID
INDEX FULL SCAN
Данный метод доступа просматривает все листовые блоки индекса для поиска соответствий условиям предиката. Для того чтобы Oracle мог применить этот метод доступа, хотя бы одно из полей ключа должно иметь ограничение NOT NULL, т.к. только в этом случае соответствующая строка таблицы попадет в индекс. Этот метод обычно быстрее чем TABLE FULL SCAN, но медленнее, чем INDEX RANGE SCAN (см. ниже).
INDEX FAST FULL SCAN
Этот метод доступа применяется, когда выполнены все требования для INDEX FULL SCAN, а также все данные, выбираемые запросом, содержатся в индексе и таким образом доступ к самой таблице не требуется. В отличие от INDEX FULL SCAN этот метод может читать блоки индекса в несколько параллельных потоков и таким образом порядок возвращаемых значений не регламентирован. Oracle также не может использовать этот метод для bitmap-индексов.
Oracle mechanics
Операции CBO
Описание некоторых методов доступа к данным (access path), соединения наборов данных (join method) и преобразований запросов (query transformation) используемых оптимизатором / в планах выполнения запросов Oracle
Методы доступа и соединения наборов данных
Index Full Scan
«… full index scan исключает сортировку, поскольку по одному блоку считывает упорядоченные по индексному ключу данные…»
Используется для эффективной замены табличного доступа при выполнении сортировок (ORDER BY), группировок (GROUP BY) и операции sort merge join (вместо традиционных full table scan с последующей сортировкой) при выполнении определённых условий
Index Fast Full Scan
INDEX SKIP SCAN
«Index Skip Scan использует разделение составного индекса на логические части (subindexes). Такое сканирование используется в случаях, когда первый столбец составного индекса не входит в условия запроса. Другими словами, первый столбец пропускается (skipped) [при выполнении этой операции] … [Oracle] определяет количество таких логических частей (subindexes) по числу неповторяющихся значений (distinct values) первого столбца составного индекса. Такое сканирование является предпочтительным в случаях, когда первый столбец составного индекса имеет малое количество неповторяющихся значений, а следующий стобец — большое … [CBO] может предпочесть операцию index skip scan если первый столбец составного индекса не входит в условия запроса (query predicate)…»
Практический пример использования оптимизатором index skip scan при наличии составного индекса IX_AA_AFLID_DATE_INS по столбцам AA(AFFILIATE_ID, DATE_INSERT)
FILTER
Фильтрация по условию набора строк (row set), полученного в рез-тате предыдущей операции доступа к данным. Отражается в секции predicate info:
, или отдельной операцией плана выполнения, например, в случаях:
filter(NULL IS NOT NULL)
специальный случай операции FILTER, используемый для исключения из универсального плана выполнения избыточных операций с данными при наличии невыполнимых условий
Например, если для ненулевого по определению поля EMP.EMPNO указать в в запросе невыполнимое условие empno is null:
При этом в трейсе оптимизатора финальное уточнение стоимости выглядит след.образом:
CONCATENATION
Операция выбора путей доступа к данным / объединения результатов.
В первом случае применяется в случаях, когда план выполнения запроса выбирается динамически, например, в зависимости от значения связанной переменной:
при этом, несмотря на то, что запрос будет выполняться либо с использованием полного сканирования всех блоков таблицы (TABLE ACCESS FULL), либо — по уникальному индексу (TABLE ACCESS BY INDEX ROWID), общая стоимость плана (и ожидаемое кол-во строк Rows/Bytes) просто складываются из альтернативных стоимостей вариантов доступа:
8 = 7+1
В секции Predicate Information можно видеть малодокументированную функцию SYS_OP_MAP_NONNULL, используемую внутри DECODE для успешного сравнения (с результатом TRUE) нулевых значений
функция эта встречается, например, также в описании старого бага Oracle В другом случае в результате применения OR-expansion transformation операция CONCATENATION используется в плане для обозначения объединения результатов вместо UNION ALL:
CARTESIAN JOIN
«… используется в случаях, когда одна или несколько таблиц не имеют никаких условий (join conditions) для соединения с другими таблицами запроса. Оптимизатор соединяет каждую строку первого источника данных с каждой строкой другого, создавая картезианское произведение (Cartesian product) двух наборов данных»
В последнем примере благодаря подсказке ORDERED оптимизатор вынужден первым делом соедиить таблицы DEPT и BONUS, не имеющие по условиям запроса никаких условий для соединения (join keys) и, следовательно, единственной возможной операцией для такого безусловного соединения оказывается картезианское произведение (Join Cartesian) В случае, когда две небольшие таблицы (DEPT и BONUS) соединяются через условия d.deptno = e.deptno and b.ename = e.ename к «большой» таблице EMP и имеются дополнительные условия на столбцы небольших таблиц (фильтры dept.loc = ‘CHICAGO’ and bonus.comm > 30), оптимизатор по соображениям улучшения избирательности (selectivity) и уменьшения стоимисти без всяких подсказок может выбрать Merge Join Cartesian небольших таблиц с последующим соединением (Hash join) с большой таблицей:
Начиная с Oracle 10g использование картезианского произведения на уровне системы, сессии, запроса может быть запрещено параметрами с разной степенью эффективности:
HASH JOIN
«… используется для соединения больших наборов данных (data sets). Оптимизатор использует меньшую из двух таблиц — источников данных для посторения хэш-таблицы ключа соединения (join key) в памяти. Затем сканирует бОльшую таблицу, сравнивая по ключу с хэш-таблицой для получения результирующего набора строк»
Управление использованием hash join с помощью oracle events на уровне сессии/системы:
или скрытым, начиная с 10g, параметром:
Right Join – The secret of swapping join input — про hash join swapping и испольхование хинта SWAP_JOIN_INPUTS
HASH UNIQUE / SORT UNIQUE
From10Gr2, HASH UNIQUE Operation Returns Results in UNSORTED ORDER by Default [ID 341838.1]: «Начиная с 10gR2 при выполнении SELECT DISTINCT при настройках по умолчанию [оптимизатор] отдаёт предпочтение операция HASH UNIQUE вместо SORT UNIQUE, в результате которой [в отличие от операции SORT UNIQUE] данные возвращаются в неотсортированном виде»
или же для исключения использования операции HASH UNIQUE и возвращения к «старому поведению» (использованию SORT UNIQUE) можно установить параметр optimizer_features_enableв значение 9.2.0 и ниже:
Sort join: Оба источника входных данных / таблицы сортируются по ключу соединения (join key)
Merge join: Совместная обработка / объдинение (merging) отсортированных списков
В случае, если источник данных уже отсортирован по столбцу[ам] условия соединения (join column), операция sort join не производится для этого источника данных» Например, при использовании оператора сравнения «>» несвязанных столцов видим по одной операции SORT JOIN для каждой таблицы и объединённую операцию MERGE JOIN:
В случае с отсортироваными значениями одна из операций SORT JOIN исключается за ненадобностью:
SORT UNIQUE NOSORT
операция последовательной сортировки результатов одноблочного чтения для получения набора неповторяющихся значений, применяемая при выполнении SELECT DISTINCT
LOAD AS SELECT
отражает операции параллельной или непараллельной прямой загрузки данных (direct-path DML|DDL) при операциях: CREATE TABLE AS SELECT, INSERT …SELECT,…
Julian Dyke отмечает, что в ранних версиях операция наблюдалась только при выполнении EXPLAIN PLAN, и не наблюдается при реальном выполнении (AUTOTRACE или V$SQL_PLAN), начиная с Oracle 10.2 операцию можно наблюдать в выполняемых планах оптимизатора
INDEX MAINTENANCE
«… производится перестроение индекса по окончании операций direct-path INSERT [в т.ч. в составе parallel MERGE]… Перестроение индекса выполняется PX процессами при parallel direct-path INSERT, либо клиентским процессом при непараллельном / serial direct-path INSERT. Для уменьшения влияния этой операции на производительность DML можно сделать индекс(ы) unusable перед загрузкой данных в таблицу (INSERT) с последующим перестроением индекс(ов)»
PX COORDINATOR FORCED SERIAL
Появление этой операции означает, что план или его часть, первоначально рассчитанные для параллельного выполнения, переключёны в последовательный режим (serial execution) по причине использования в запросе функций, не допускающих параллельного выполнения или других видов зависимостей от «непараллельных» функций (например, пользовательских типов в таблицах) — см. тестскейс http://oracle-randolf.blogspot.com/2011/03/px-coordinator-forced-serial-operation.html
Трейс оптимизатора при этом рапортует:
В случаях, когда Oracle не в состоянии самостоятельно определить безопасность PL/SQL кода для параллельного выполнения, рекомендуются следующие способы пометить функции, как пригодные для параллельного выполнения: How To Enable Parallel Query For A Function? [ID 1093773.1]:
1) предпочтительный метод — использовать PARALLEL_ENABLE в определении функции:
2) допустимый / устаревающий метод, пригодный только для определения функции в составе пакета:
TEMP TABLE TRANSFORMATION
Преобразование промежуточного набора данных во временную таблицу во время выполнения запроса, может использоваться:
В случаях subquery factoring (WITH clause) временная таблица формируется при использовании подсказки /*+ MATERIALIZE*/ или без подсказки при выполнении определённых условий (например, количество использований таблицы-подзапроса в запросе):
Временная таблица SYS_TEMP_% строится на следующих шагах плана:
И затем, на шагах 6 и 8 сканируется только подготовленная временная таблица SYS_TEMP_% (исключая таким образом избыточный доступ к исходной таблице T)
MAT_VIEW CUBE ACCESS
Операция доступа к агрегированным данным MOLAP куба (Oracle OLAP Option), созданного с возможностями материализованного представления — cube materialized view.
Используется при выполнении query rewrite, доступа с версии 11.1
На примере тестовой схемы OLAPTRAIN без query rewrite запрос выполняется дорого с полным доступом к таблицам:
При использовании query rewrite данные получаются напрямую из куба CB$SALES_CUBE, значительно уменьшая стоимость и воемя выполнения:
Те же данные можно получить прямым запросом к кубу, например через mview с вышеуказанными в Predicate Information условиями:
PARTITION LIST EMPTY
Операция не предполагает выполнения последующих / дочерних операций ни с одной из партиций
Наблюдалась в 11.2.0.3 при ошибочном применении преобразования Table Expansion в виде:
доступ к партициям при этой операции определялся следующими противоречивыми предикатами по ключу партицирования:
Методы преобразования запросов (query transformation)
Join Elimination (JE)
При наличии ограничений целостности на столбцах, по которым выполняется соединение таблиц в запросе [например, PK->FK], трансформация типа join elimination может исключать из запроса таблицу в случае, когда сам факт соединения таблиц не влияет на результат запроса [т.е. правильное выполнение запроса не требует обращения к исключаемой таблице]
First K Rows Optimization
Применяется в процессе построения плана запроса, использующего функцию ROWNUM в условиях (rownum predicate)
Query Slow With Rownum Predicate [ID 833286.1] — на уровне сессии/системы управляется параметром:
— на уровне запроса с использованием подсказки OPT_PARAM параметр (в 11.2.0.3) не меняется
Star Transformation (ST)
преобразование запроса с целью исключения обработки большого количества строк таблиц с фактическими данными (fact table) за счёт комбинированного индексного доступа по подготовленному набору внешних ключей, полученных из сканирования/фильтрации небольших справочных таблиц (dimension table)
Для выполнения этой трансформации поля таблицы фактов, по которым выполняется соединение должны быть проиндексированы — по этим индексаи будет выполняться доступ к большой таблице (fact) с использованием ключей, полученных в результате bitmap операций (BITMAP AND, BITMAP MERGE, BITMAP KEY ITERATION, …) над битовыми картами ключей, полученными из bitmap-индексов или регулярных B-tree индексов справочных таблиц (dimension)
Set to Join Conversion (SJC)
Filter Push-Down (FPD)
преобразование, позволяющее не только «опускать» условия внешнего запроса в используемые Inline View (аналогично Complex View Merging), но и логически генерировать дополнительные предикаты, которые могут быть использованы, например, для Partition Pruning:
Методы преобразования запросов на основе стоимости (Cost-Based QueryTtransformation | CBQT)
Complex View Merging
Group by Placement
— обратное по отношению к Complex View Merging преобразование
Distinct Aggregate Transformation
Table Expansion
Преобразование, позволяющее при выполнении запросов к партиционированным таблицам использовать отличные пути доступа в зависимости от состояния (USABLE/UNUSABLE) партиций локальных индексов
Отображается в плане в виде VIEW VW_TE_2, цифра отражает номер итерации:
Понравилось это:
2 комментария »
Хороший FAQ, почерпнул много нового.
комментарий от blacksaifer — 28.08.2012 @ 08:06 | Ответить
Спасибо за статью. Все по методоам доступа в одном месте. За вторую книжку в списке литературы — отдельный респект!
5
Optimizer Hints
Optimizer hints can be used with SQL statements to alter execution plans. This chapter explains how to use hints to force various approaches.
The chapter contains the following sections:
Understanding Optimizer Hints
Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know.
For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.
You can use hints to specify the following:
The use of hints involves extra code that must be managed, checked, and controlled.
Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the following criteria:
Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used.
Specifying Hints
Hints apply only to the optimization of the statement block in which they appear. A statement block is any one of the following statements or parts of statements:
For example, a compound query consisting of two component queries combined by the UNION operator has two statement blocks, one for each component query. For this reason, hints in the first component query apply only to its optimization, not to the optimization of the second component query.
You can send hints for a SQL statement to the optimizer by enclosing them in a comment within the statement.
Oracle9i SQL Reference for more information on comments
The APPEND hint always follows the INSERT keyword, and the PARALLEL hint can follow the INSERT keyword.
The following syntax shows hints contained in both styles of comments that Oracle supports within a statement block.
If you specify hints incorrectly, then Oracle ignores them but does not return an error:
The optimizer recognizes hints only when using the cost-based approach. If you include a hint (except the RULE hint) in a statement block, then the optimizer automatically uses the cost-based approach.
Specifying a Full Set of Hints
When using hints, in some cases, you might need to specify a full set of hints in order to ensure the optimal execution plan. For example, if you have a very complex query, which consists of many table joins, and if you specify only the INDEX hint for a given table, then the optimizer needs to determine the remaining access paths to be used, as well as the corresponding join methods. Therefore, even though you gave the INDEX hint, the optimizer might not necessarily use that hint, because the optimizer might have determined that the requested index cannot be used due to the join methods and access paths selected by the optimizer.
In Example 5-1, the ORDERED hint specifies the exact join order to be used; the join methods to be used on the different tables are also specified.
Example 5-1 Specifying a Full Set of Hints
Note that the hints could have been also been in this format:
Using Hints Against Views
By default, hints do not propagate inside a complex view. For example, if you specify a hint in a query that selects against a complex view, then that hint is not honored, because it is not pushed inside the view.
If the view is a single-table, then the hint is not propagated.
Unless the hints are inside the base view, they might not be honored from a query against the view.
Local Hints Compared with Global Hints
The SQL Analyze tool that is available with the Oracle Tuning Pack, provides a graphical user interface for working with optimizer hints. The Hint Wizard, a feature of SQL Analyze, helps you easily add or modify hints in SQL statements.
Using Optimizer Hints
Optimizer hints can be categorized as follows:
Hints for Optimization Approaches and Goals
The hints described in this section let you choose between the cost-based and the rule-based optimization approaches. With the cost-based approach, this also includes the goal of best throughput or best response time.
If a SQL statement has a hint specifying an optimization approach and goal, then the optimizer uses the specified approach regardless of the presence or absence of statistics, the value of the OPTIMIZER_MODE initialization parameter, and the OPTIMIZER_MODE parameter of the ALTER SESSION statement.
ALL_ROWS
The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
For example, the optimizer uses the cost-based approach to optimize this statement for best throughput:
FIRST_ROWS( n)
The hints FIRST_ROWS ( n ) (where n is any positive integer) or FIRST_ROWS instruct Oracle to optimize an individual SQL statement for fast response. FIRST_ROWS (n) affords greater precision, because it instructs Oracle to choose the plan that returns the first n rows most efficiently. The FIRST_ROWS hint, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability.
For example, the optimizer uses the cost-based approach to optimize this statement for best response time:
In this example each department contains many employees. The user wants the first 10 employees of department #20 to be displayed as quickly as possible.
The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax:
These statements cannot be optimized for best response time, because Oracle must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any of these statements, then the optimizer uses the cost-based approach and optimizes for best throughput.
If you specify either the ALL_ROWS or the FIRST_ROWS hint in a SQL statement, and if the data dictionary does not have statistics about tables accessed by the statement, then the optimizer uses default statistical values (such as allocated storage for such tables) to estimate the missing statistics and, subsequently, to choose an execution plan.
These estimates might not be as accurate as those gathered by the DBMS_STATS package. Therefore, use the DBMS_STATS package to gather statistics. If you specify hints for access paths or join operations along with either the ALL_ROWS or FIRST_ROWS hint, then the optimizer gives precedence to the access paths and join operations specified by the hints.
«How the CBO Optimizes SQL Statements for Fast Response» for an explanation of the difference between FIRST_ROWS( n ) and FIRST_ROWS
CHOOSE
The CHOOSE hint causes the optimizer to choose between the rule-based and cost-based approaches for a SQL statement. The optimizer bases its selection on the presence of statistics for the tables accessed by the statement. If the data dictionary has statistics for at least one of these tables, then the optimizer uses the cost-based approach and optimizes with the goal of best throughput. If the data dictionary does not have statistics for these tables, then it uses the rule-based approach.
Oracle Corporation strongly advises the use of cost-based optimization. Rule-based optimization will be deprecated in a future release.
Hints for Access Paths
Each hint described in this section suggests an access path for a table.
Specifying one of these hints causes the optimizer to choose the specified access path only if the access path is available based on the existence of an index or cluster and on the syntactic constructs of the SQL statement. If a hint specifies an unavailable access path, then the optimizer ignores it.
You must specify the table to be accessed exactly as it appears in the statement. If the statement uses an alias for the table, then use the alias rather than the table name in the hint. The table name within the hint should not include the schema name if the schema name is present in the statement.
For access path hints, Oracle ignores the hint if you specify the SAMPLE option in the FROM clause of a SELECT statement.
Oracle9i SQL Reference for more information on the SAMPLE option
The FULL hint explicitly chooses a full table scan for the specified table.
where table specifies the name or alias of the table on which the full table scan is to be performed. If the statement does not use aliases, then the table name is the default alias.
Oracle performs a full table scan on the employees table to execute this statement, even if there is an index on the last_name column that is made available by the condition in the WHERE clause.
Because the employees table has alias e the hint must refer to the table by its alias rather than by its name. Also, do not specify schema names in the hint even if they are specified in the FROM clause.
ROWID
The ROWID hint explicitly chooses a table scan by rowid for the specified table.
where table specifies the name or alias of the table on which the table access by rowid is to be performed.
CLUSTER
The CLUSTER hint explicitly chooses a cluster scan to access the specified table. It applies only to clustered objects.
where table specifies the name or alias of the table to be accessed by a cluster scan.
The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster.
where table specifies the name or alias of the table to be accessed by a hash scan.
INDEX
The INDEX hint explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B-tree, bitmap, and bitmap join indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes, because it is a more versatile hint.
This hint can optionally specify one or more indexes:
For example, consider this query that selects the name, height, and weight of all male patients in a hospital:
Barring the use of frequency histograms, the number of occurrences of each distinct column value is not available to the optimizer. The cost-based approach assumes that each value has an equal probability of appearing in each row. For a column having only two distinct values, the optimizer assumes each value appears in 50% of the rows, so the cost-based approach is likely to choose a full table scan rather than an index scan.
INDEX_ASC
The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in ascending order of their indexed values.
Each parameter serves the same purpose as in the INDEX hint.
Because Oracle’s default behavior for a range scan is to scan index entries in ascending order of their indexed values, this hint does not specify anything more than the INDEX hint. However, you might want to use the INDEX_ASC hint to specify ascending range scans explicitly should the default behavior change.
INDEX_COMBINE
The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes.
INDEX_JOIN
The INDEX_JOIN hint explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.
For example, the following query uses an index join to access the employee_id and department_id columns, both of which are indexed in the employees table.
INDEX_DESC
The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.
Each parameter serves the same purpose as in the INDEX hint. For example:
INDEX_FFS
The INDEX_FFS hint causes a fast full index scan to be performed rather than a full table scan.
NO_INDEX
The NO_INDEX hint explicitly disallows a set of indexes for the specified table.
AND_EQUAL
The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.
Hints for Query Transformations
Each hint described in this section suggests a SQL query transformation.
USE_CONCAT
The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.
NO_EXPAND
REWRITE
The REWRITE hint forces the cost-based optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost.
Oracle does not consider views outside of the list. If you do not specify a view list, then Oracle searches for an eligible materialized view and always uses it regardless of its cost.
EXPAND_GSET_TO_UNION
The EXPAND_GSET_TO_UNION hint is used for queries containing grouping sets (such as queries with GROUP BY GROUPING SET or GROUP BY ROLLUP ). The hint forces a query to be transformed into a corresponding query with UNION ALL of individual groupings.
is first transformed to
NOREWRITE
The NOREWRITE hint disables the use of function-based indexes.
MERGE
The MERGE hint lets you merge a view for each query.
If a view’s query contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view’s query into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an IN subquery into the accessing statement if the subquery is uncorrelated.
Complex merging is not cost-based; that is, the accessing query block must include the MERGE hint. Without this hint, the optimizer uses another approach.
This example requires that complex view merging be enabled.
NO_MERGE
The NO_MERGE hint causes Oracle not to merge mergeable views.
This hint lets the user have more influence over the way in which the view is accessed.
This causes view dallas_dept not to be merged.
When the NO_MERGE hint is used without an argument, it should be placed in the view query block. When NO_MERGE is used with the view name as an argument, it should be placed in the surrounding query.
STAR_TRANSFORMATION
The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a cost-based decision to use the best plan generated without the transformation, instead of the best plan for the transformed query.
Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer only generates the subqueries if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.
The FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.
NO_FACT
The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table.
Hints for Join Orders
The hints in this section suggest join orders:
ORDERED
The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.
If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.
The following query is an example of the use of the ORDERED hint:
The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table’s concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
Usually, if you analyze the tables, then the optimizer selects an efficient star plan. You can also use hints to improve the plan. The most precise method is to order the tables in the FROM clause in the order of the keys in the index, with the large table last. Then use the following hints:
where facts is the table and fact_concat is the index. A more general method is to use the STAR hint.
Hints for Join Operations
Each hint described in this section suggests a join operation for a table.
In the hint you must specify a table exactly the same way it appears in the statement. If the statement uses an alias for the table, then you must use the alias rather than the table name in the hint. However, the table name within the hint should not include the schema name, if the schema name is present in the statement.
Use of the USE_NL and USE_MERGE hints is recommended with the ORDERED hint. Oracle uses these hints when the referenced table is forced to be the inner table of a join; the hints are ignored if the referenced table is the outer table.
USE_NL
The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join, using the specified table as the inner table.
where table is the name or alias of a table to be used as the inner table of a nested loops join.
For example, consider this statement, which joins the accounts and customers tables. Assume that these tables are not stored together in a cluster:
Because the default goal of the cost-based approach is best throughput, the optimizer chooses either a nested loops operation, a sort-merge operation, or a hash operation to join these tables, depending on which is likely to return all the rows selected by the query more quickly.
However, you might want to optimize the statement for best response time or the minimal elapsed time necessary to return the first row selected by the query, rather than best throughput. If so, then you can force the optimizer to choose a nested loops join by using the USE_NL hint. In this statement, the USE_NL hint explicitly chooses a nested loops join with the customers table as the inner table:
In many cases, a nested loops join returns the first row faster than a sort merge join. A nested loops join can return the first row after reading the first selected row from one table and the first matching row from the other and combining them, while a sort merge join cannot return the first row until after reading and sorting all selected rows of both tables and then combining the first rows of each sorted row source.
USE_MERGE
The USE_MERGE hint causes Oracle to join each specified table with another row source, using a sort-merge join.
where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort merge join.
The following query shows an inventory usage report in which the optimizer avoids a sort for the GROUP BY operation by using the sort merge operation specified by the USE_MERGE hint.
The following is a query applying the USE_MERGE hint with the FULL hint.
USE_HASH
The USE_HASH hint causes Oracle to join each specified table with another row source, using a hash join.
where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.
DRIVING_SITE
The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization.
where table is the name or alias for the table at which site the execution should take place.
If this query is executed without the hint, then rows from departments are sent to the local site, and the join is executed there. With the hint, the rows from employees are sent to the remote site, and the query is executed there, returning the result to the local site.
This hint is useful if you are using distributed query optimization.
LEADING
The LEADING hint causes Oracle to use the specified table as the first table in the join order.
If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.
where table is the name or alias of a table to be used as the first table in the join order.
HASH_AJ, MERGE_AJ, and NL_AJ
Figure 5-1 Parallel Hash Anti-join
HASH_SJ, MERGE_SJ, and NL_SJ
A subquery is evaluated as a semi-join only with these limitations:
Oracle9i SQL Reference for more information about joins
Hints for Parallel Execution
The hints described in this section determine how statements are parallelized or not parallelized when using parallel execution.
Oracle9i Data Warehousing Guide for more information on parallel execution
PARALLEL
The number of servers that can be used is twice the value in the PARALLEL hint, if sorting or grouping operations also take place.
If any parallel restrictions are violated, then the hint is ignored.
The PARALLEL hint must use the table alias, if an alias is specified in the query. The hint can then take two values, separated by commas after the table name. The first value specifies the degree of parallelism for the given table, and the second value specifies how the table is to be split among the Oracle Real Application Clusters instances. Specifying DEFAULT or no value signifies that the query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism. In the following example, the PARALLEL hint overrides the degree of parallelism specified in the employees table definition:
In the next example, the PARALLEL hint overrides the degree of parallelism specified in the employees table definition and tells the optimizer to use the default degree of parallelism determined by the initialization parameters. This hint also specifies that the table should be split among all of the available instances, with the of parallelism on each instance.
NOPARALLEL
The NOPARALLEL hint overrides a PARALLEL specification in the table clause. In general, hints take precedence over table clauses.
The following example illustrates the NOPARALLEL hint:
PQ_DISTRIBUTE
The PQ_DISTRIBUTE hint improves the performance of parallel join operations. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make.
Use the EXPLAIN PLAN statement to identify the distribution chosen by the optimizer. The optimizer ignores the distribution hint, if both tables are serial.
Oracle9i Database Concepts for more information on how Oracle parallelizes join operations
There are six combinations for table distribution. Only a subset of distribution method combinations for the joined tables is valid, as explained in Table 5-1.
Table 5-1 Distribution Hint Combinations
Maps the rows of each table to consumer query servers, using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This hint is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort merge join.
All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This hint is recommended when the outer table is very small compared to the inner table. As a general rule, use the Broadcast/None hint when inner table size * number of query servers > outer table size.
All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This hint is recommended when the inner table is very small compared to the outer table. As a general rule, use the None/Broadcast hint when inner table size * number of query servers Note: The optimizer ignores this hint if the inner table is not partitioned or not equijoined on the partitioning key.
Maps the rows of the inner table using the partitioning of the outer table. The outer table must be partitioned on the join keys. This hint is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers.
Note: The optimizer ignores this hint if the outer table is not partitioned or not equijoined on the partitioning key.
Each query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equipartitioned on the join keys.
PARALLEL_INDEX
The PARALLEL_INDEX hint specifies the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.
The hint can take two values, separated by commas after the table name. The first value specifies the degree of parallelism for the given table. The second value specifies how the table is to be split among the Oracle Real Application Clusters instances. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters to determine the default degree of parallelism.
In this example, there are three parallel execution processes to be used on each of two instances.
NOPARALLEL_INDEX
The NOPARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation.
Additional Hints
Several additional hints are included in this section:
APPEND
The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode.
NOAPPEND
The NOAPPEND hint enables conventional INSERT by disabling parallel mode for the duration of the INSERT statement. (Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).
CACHE
The CACHE hint specifies that the blocks retrieved for the table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.
In the following example, the CACHE hint overrides the table’s default caching specification:
NOCACHE
The NOCACHE hint specifies that the blocks retrieved for the table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache.
The CACHE and NOCACHE hints affect system statistics «table scans(long tables)» and «table scans(short tables)», as shown in the V$SYSSTAT view.
Automatic Caching of Small Tables
Starting with Oracle9 i, Release 2 (9.2), small tables are automatically cached, according to the criteria in Table 5-2.