Выражение не выбрано oracle

Обновлено: 04.07.2024

CBO: Оптимизация на основе затрат. Оптимизатор на основе затрат.

CBO оптимизаторСоздайте набор планов выполнения, которые можно использовать в соответствии с оператором SQL, оцените стоимость каждого плана выполнения и вызовите генератор планов (Генератор планов), чтобы сгенерировать план выполнения, сравните стоимость плана выполнения и, наконец, выберите наиболее дешевый план выполнения. ,
CBO состоит из следующих компонентов: Query Transformer, Estimator, Plan Generator

В Oracle 10g есть 2 необязательных режима работы для CBO:
（1） FIRST_ROWS(n)
(2) ALL_ROWS-значение по умолчанию в 10g

Просмотр режима CBO:

Три способа изменить модель CBO:
(1) Уровень сессий

(2) Системный уровень

Oracle предоставляет возможность использовать подсказку для установки типа оптимизатора в SQL на CBO или RBO.

(3) Уровень выписки

использование Hint（/*+ . */） Установить

Оптимизатор на основе затрат (CBO) очень умный, в большинстве случаев он выберет правильный оптимизатор, уменьшая нагрузку на администраторов баз данных. Но иногда он также является умным и ошибочным и выбирает очень плохой план выполнения, что делает выполнение определенного оператора чрезвычайно медленным.

На этом этапе администратору БД необходимо вмешательство человека, чтобы сказать оптимизатору использовать указанный путь доступа или тип соединения для генерации плана выполнения, чтобы оператор выполнялся эффективно. Например, если мы считаем, что более полное сканирование таблицы более эффективно, чем сканирование индекса для определенного оператора, то мы можем поручить оптимизатору использовать полное сканирование таблицы.

В Oracle цель оптимизации оптимизатора вмешательства достигается путем добавления подсказки (подсказки) к выражению.

Oracle Hint - это механизм, который позволяет оптимизатору сгенерировать план выполнения так, как мы его сообщаем.

Мы можем использовать Oracle Hint для достижения:

  • 1) Тип используемого оптимизатора
  • 2) Целью оптимизации оптимизатора на основе затрат является all_rows или first_rows.
  • 3) Независимо от того, является ли путь доступа к таблице полным сканированием таблицы, индексным сканированием или rowid напрямую.
  • 4) Тип соединения между таблицами
  • 5) Порядок соединения между таблицами
  • 6) Степень параллелизма высказываний

При использовании подсказки следует отметить следующее: Не все моменты подсказка работает.

Причины сбоя HINT следующие:

Если CBO считает, что использование подсказки приведет к неверным результатам, подсказка будет проигнорирована.

Если запись в индексе несовместима с записью таблицы из-за нулевого значения, результат неверен, а подсказка игнорируется.

Если в таблице указан псевдоним, псевдоним также должен использоваться в подсказке, иначе подсказка также будет игнорироваться.

  • 1
  • 2
  • 3
  • 4
  • 5
  • 1) DELETE, INSERT, SELECT и UPDATE - это ключевые слова, которые идентифицируют начало блока оператора, и комментарии, содержащие подсказки, могут появляться только после этих ключевых слов, в противном случае подсказка недействительна.
  • 2) Знак «+» означает, что примечание является подсказкой, а знак плюс должен следовать сразу за «/ *» без пробелов.
  • 3) Подсказка - это одна из конкретных подсказок, описанных ниже. Если имеется несколько подсказок, каждая подсказка должна быть разделена одним или несколькими пробелами.
  • 4) текст другой пояснительный текст, который объясняет подсказку

Если вы не укажете Hint правильно, Oracle проигнорирует Hint и не выдаст никаких ошибок.

Другое: только один / после каждой команды SELECT / INSERT / UPDATE / DELETE + /, но может быть несколько приглашений, разделенных запятыми или пробелами.

Такие как:/ *+ ordered index() use_nl() */

Оптимизатор режима Подсказка


Путь доступа Подсказка




Таблица подключения порядок подсказка


Метод ассоциации таблиц Подсказка


Параллельное исполнение подсказки


Подсказка преобразования запроса


Другое Подсказка


В процессе оптимизации операторов SQL мы часто используем подсказки, а теперь обобщаем общее использование Oracle HINT в процессе оптимизации SQL:

1. /*+ALL_ROWS*/

Он показывает, что для блока операторов выбран метод оптимизации на основе затрат, получена наилучшая пропускная способность и минимизировано потребление ресурсов.

Когда режим CBO установлен на ALL_ROWS, Oracle выполнит SQL с максимальной скоростью и вернет все наборы результатов. Разница между ним и FIRST_ROWS (n) заключается в том, что ALL_ROWS подчеркивает, что SQL будет выполняться с самой быстрой скоростью, и будет Все результирующие наборы возвращаются, и FIRST_ROWS (n) фокусируется на времени выполнения возврата первых n записей.

2. /*+FIRST_ROWS*/

Он показывает, что для блока операторов выбран метод оптимизации на основе затрат, и наилучшее время отклика достигается для минимизации потребления ресурсов.

Если для режима оптимизации CBO задано значение FIRST_ROWS (n), при выполнении SQL Oracle будет отдавать приоритет первым n записям в наборе результатов и отсылать самые быстрые, тогда как другие результаты не нужно возвращать одновременно.

В примере подкачки каждый раз, когда из набора результатов берутся 10 записей, записи сортируются в соответствии с полем x.

Примечание: поле x, используемое для сортировки, должно быть проиндексировано, иначе CBO будет игнорировать FIRST_ROWS (n) и использовать ALL_ROWS.

3. /*+CHOOSE*/

Указывает, что если в словаре данных есть статистическая информация о таблице доступа, она будет основана на методе оптимизации служебных данных и обеспечит наилучшую пропускную способность.
Покажите, что если в словаре данных отсутствует статистическая информация о таблице доступа, она будет основана на методе оптимизации издержек правила;

4. /*+RULE*/

Это указывает на то, что метод оптимизации на основе правил выбран для блока предложения.
Например:

5. /*+FULL(TABLE)*/

Указывает, что для таблицы выбран метод глобального сканирования.

Подсказка сообщает оптимизатору доступ к данным через полное сканирование указанной таблицы.
Например:

Следует отметить, что если таблица имеет псевдоним, псевдоним также следует использовать в подсказке

6. /*+ROWID(TABLE)*/

Подсказка ясно указывает, что указанная таблица доступна в соответствии с ROWID.
Например:

7. /*+CLUSTER(TABLE)*/

Подсказка ясно указывает, что метод доступа для выбора сканирования кластера для указанной таблицы действителен только для объекта кластера.
Например:

8. /*+INDEX(TABLE INDEX_NAME)*/

Указывает метод сканирования для выбора индексов в таблице.
Индексная подсказка сообщает оптимизатору доступ к данным путем индексации указанной таблицы. При доступе к индексу будет получен неполный набор результатов, оптимизатор проигнорирует этот совет.

Индекс используется только тогда, когда в предикате есть поле индекса.

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/

Указывает, что метод сканирования для выбора индекса в порядке возрастания на таблице.
Например:

10. /*+INDEX_COMBINE*/

Выберите путь доступа к битовой карте для указанной таблицы. Если индекс в качестве параметра не указан в INDEX_COMBINE, будет выбран метод логической комбинации индекса битовой карты.
Например:

11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/

В приглашении четко указано, что оптимизатор использует индекс в качестве пути доступа.
Например:

12. /*+INDEX_DESC(TABLE INDEX_NAME)*/

Показывает метод сканирования в порядке убывания выбора индекса в таблице.
Например:

13. /*+INDEX_FFS(TABLE INDEX_NAME)*/

Выполните быстрое полное сканирование индекса по указанной таблице вместо полного сканирования таблицы.
Например:

14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2. */

Предложите четко выбрать план выполнения и объединить несколько сканирований индекса по одной колонке.
Например:

15. /*+USE_CONCAT*/

Преобразуйте условие OR после WHERE в запросе в объединенный запрос UNION ALL.
Например:

16. /*+NO_EXPAND*/

Для операторов запроса OR или IN-LIST после WHERE NO_EXPAND предотвратит его расширение на основе оптимизатора.
Например:

17. /*+NOWRITE*/

Операции перезаписи запросов в блоках запросов запрещены.

18. /*+REWRITE*/

Вы можете принять вид в качестве параметра.

19. /*+MERGE(TABLE)*/

Возможность объединить различные запросы представления соответственно.
Например:

20. /*+NO_MERGE(TABLE)*/

Представления, которые можно объединить, больше не объединяются.
Например:

21. /*+ORDERED*/

В соответствии с порядком, в котором таблицы отображаются в FROM, ORDERED заставляет ORACLE соединять их в этом порядке.

Подсказка указывает Oracle выбирать таблицу дисков в порядке, следующем за таблицей, следующей за From. Oracle рекомендует использовать Leading при выборе таблицы дисков, что является более гибким.

22. /*+USE_NL(TABLE)*/

Подключите указанную таблицу к вложенному связанному источнику строк и используйте указанную таблицу в качестве внутренней таблицы.

В запросе на сопоставление с несколькими таблицами укажите использование гнездовых циклов для сопоставления с несколькими таблицами.
Например:

23. /*+USE_MERGE(TABLE)*/

Соедините указанную таблицу с другими источниками строк через соединение сортировки слиянием.

В запросе ассоциации с несколькими таблицами укажите использование объединения слиянием для ассоциации с несколькими таблицами.
Например:

24. /*+USE_HASH(TABLE)*/

Соедините указанную таблицу с другими источниками строк с помощью хеш-соединения.

В запросе на сопоставление с несколькими таблицами укажите использование хеш-соединения для выполнения сопоставления с несколькими таблицами.
Например:

25. / +DRIVING_SITE(TABLE) /

Принудительно задать таблицу с местоположением, отличным от того, которое выбрано ORACLE для выполнения запроса.
Например:

26. / +LEADING(TABLE) /

Используйте указанную таблицу в качестве первой таблицы в порядке подключения.

В запросе, включающем несколько таблиц, подсказка указывает, какая таблица используется в качестве таблицы управления, и сообщает оптимизатору, какой таблице следует сначала получить доступ к данным.

27. / +CACHE(TABLE) /

В операции полного сканирования таблицы, если вы используете это приглашение, Oracle поместит отсканированные блоки данных в LRU (наименее недавно использованный: недавно использованный список, это алгоритм для Oracle для определения активности блоков данных в памяти) Наиболее используемый конец (самый активный конец блока данных), так что блок данных может находиться в памяти в течение более длительного времени.

Если имеется небольшая таблица, к которой часто обращаются, этот параметр повысит производительность запроса. В то же время CACHE также является атрибутом таблицы. Если установлен атрибут кэширования таблицы, он имеет тот же эффект, что и подсказка. После полного сканирования таблицы блок данных Оставайтесь в самом активном конце списка LRU.

28. /*+NOCACHE(TABLE)*/

29. /*+APPEND*/

Вставка непосредственно в конец таблицы может увеличить скорость.

Предложите базе данных загрузить данные в базу данных путем прямой загрузки.

Этот намек используется очень часто. особенно вВставьте большое количество данных, Как правило, используйте этот совет.

30. /*+NOAPPEND*/

Запустите обычные операции вставки, остановив параллельный режим в течение срока действия оператора вставки.

31. NO_INDEX: указать, какие индексы не используются

/ + NO_INDEX ( table [index [index]…] ) /

Параллельное выполнение связанной подсказки

parallel

Укажите параллелизм выполнения в SQL, это значение переопределит его собственный параллелизм

Что касается параллельности таблицы, мы можем указать ее при создании таблицы, например:

Я не смог найти примеров, которые имели бы предложения GROUP BY и ORDER BY в одном запросе. Я попытался удалить каждое поле из группы по одному за раз, но все равно получаю ту же ошибку.

вы должны поместить все столбцы SELECT на GROUP BY или используйте на них функции, которые сжимают результаты до одного значения (например, MIN , MAX или SUM ).

простой пример, чтобы понять, почему это происходит: представьте, что у вас есть база данных, как это:

а ты беги SELECT * FROM table GROUP BY foo . Это означает, что база данных должна возвращать одну строку в результате с первым столбцом 0 выполнить GROUP BY но теперь есть два значения bar выбрать от. Какой результат вы ожидаете - A или B ? Или база данных должна возвращать более одной строки, нарушая контракт GROUP BY ?

входит в GROUP BY пункт все SELECT выражения, которые не являются аргументами групповой функции.

слишком плохо Oracle имеет такие ограничения. Конечно, результат для столбца не в GROUP BY будет случайным, но иногда вы этого хотите. Глупый Oracle, вы можете сделать это в MySQL / MSSQL.

но есть работа вокруг для Oracle:

пока следующая строка не работает

Вы можете обмануть Oracle с некоторыми 0, как показано ниже, чтобы сохранить ваш столбец в области, но не группировать его (предполагая, что это числа, в противном случае используйте Функция concat)

вы должны сделать следующее:

если вы делаете ощупью в силу включения GROUP BY статья, любое выражение в SELECT , который не является функцией группы (или агрегатной функцией или агрегированным столбцом), например COUNT , AVG , MIN , MAX , SUM и так далее (список агрегатных функций) должен присутствовать в GROUP BY предложения.

пример (правильный способ) (здесь employee_id не является групповой функцией (неагрегированный столбец), поэтому это должны явиться в GROUP BY . От напротив, sum (зарплата) - это групповая функция (агрегированный столбец), поэтому она не должна отображаться в GROUP BY предложения.

пример (неправильный путь) (здесь employee_id не является групповой функцией и не отображается в GROUP BY п., что приведет к на Ora-00979 ошибки .

  • включить все неагрегированные выражения, перечисленные в SELECT статья в GROUP BY п.
  • удалить функцию group (aggregate) из SELECT предложения.

group by используется для агрегирования некоторых данных, в зависимости от агрегатной функции, и кроме этого вам нужно поместить столбец или столбцы, к которым вам нужна группировка.

в результате в отделы максимальную зарплату.

теперь, если мы опустим d.deptno из предложения group by он даст ту же ошибку.

такая же ошибка также приходит, когда верхний или ниже ключевое слово не используется в обоих местах в select expression и group BY expression .

CURSOR_ALREADY_OPEN ORA-06511 -6511
DUP_VAL_ON_INDEX ORA-00001 -1
INVALID_CURSOR ORA-01001 -1001
INVALID_NUMBER ORA-01722 -1722
LOGIN_DENIED ORA-01017 -1017
NO_DATA-FOUND ORA-01403 +100
NOT_LOGGED_ON ORA-01012 -1012
PROGRAM_ERROR ORA-06501 -6501
ROWTYPE_MISMATCH ORA-06504 -6504
STORAGE_ERROR ORA-06500 -6500
TIMEOUT_ON_RESOURCE ORA-00051 -51
TOO_MANY_ROWS ORA-01422 -1422
VALUE_ERROR ORA-06502 -6502
ZERO_DIVIDE ORA-01476 -1476
Чтобы ознакомится с более подробным списком исключений, смотрите Oracle7 Server Messages.

Описание исключений :
CURSOR_ALREADY_OPEN вызывается, если вы раннее уже открыли данный курсор. Вы должны закрыть курсор, перед тем как снова открыть его.
Курсор для цикла FOR открывается автоматически , поэтому вы не можете выполнить куpсорный цикл по уже открытому курсору.
DUP_VAL_ON_INDEX вызывается при попытке сохранить несколько одинаковых значений в колонку таблицы, когда на данную колонку установлен уникальный индекс.
INVALID_CURSOR вызывается ,если вы пытаетесь выполнить некорректную операцию с курсором. Например, INVALID_CURSOR вызывается, если вы пытаетесь закрыть еще не открытый курсор.
INVALID_NUMBER вызывается в SQL выражениях, когда не получается корректно конвертировать строку в число , потому что строка не преобразуется корректно в число. Например, следующее выражение INSERT вызывает INVALID_NUMBER когда Oracle пытается преобразовать строку 'HALL' в число:

INSERT INTO emp (empno, ename, deptno) VALUES ('HALL', 7888, 20);
Следует помнить, что процедурных выражениях вызывается исключение VALUE_ERROR .
LOGIN_DENIED вызывается если вы пытаетесь соединится с Oracle с неправильным именем пользователя или паролем.
NO_DATA_FOUND вызывается если в выражение SELECT INTO не возвращает ни одной строки или если вы обращаетесь к неустановленной строке в PL/SQL таблице. Выражение FETCH в случае когда не выбрано строк, выполняется успешно, не вызывая исключения.


Групповые выражения SQL ,такие как AVG и SUM всегда возвращают значение или null. Так, выражение SELECT INTO statement с групповой функцией никогда не вызовет исключение NO_DATA_FOUND.


NOT_LOGGED_ON вызывается если вы в PL/SQL приложении обращаетесь к базе данных без предварительного соединения с Oracle.
PROGRAM_ERROR вызывается если в PL/SQL при внутренниих структурных ошибках.
ROWTYPE_MISMATCH вызывается , когда курсор или выражение PL/SQL вы пытаетесь преобразовать к перемнной несовместимого типа. Например, когда вы открыли курсор в хранимой процедуре, если возвращаемый тип имеет несовметимый формат параметров, PL/SQL вызывает ROWTYPE_MISMATCH.
STORAGE_ERROR вызывается если PL/SQL не хватет оперативной памяти или в опративной памяти есть поврежденные блоки.
TIMEOUT_ON_RESOURCE вызвается когда превышен интервал ожидания Oracle необходимого ресурса.
TOO_MANY_ROWS вызывается если в выражение SELECT INTO возвращается более одной строки.
VALUE_ERROR возникает в операциях преобразования , математических операциях , или когда не совпадает размерность типов. Например, когда вы выбираете значение колонки строку, и если длина переменно меньше размерности данной строки, PL/SQL прерывает выполнение программы исключением VALUE_ERROR.
В процедурных выражениях, VALUE_ERROR вызывается если преобразование строки в число ошибочно. Например, следующее выражение вызывает VALUE_ERROR когда PL/SQL пытается преобразовать строку 'HALL' в число:
в выражениях SQL вызывается исключение INVALID_NUMBER
ZERO_DIVIDE вызывается при попытке деления на ноль.

Даже лучшее написано PL/SQL Программа также столкнется с ошибками или неожиданными событиями. Хорошая программа должна быть в состоянии правильно обрабатывать все виды ошибок и максимально исправлять ошибки. Любые ошибки ORACLE (сообщается как ORA-xxxxx Номер ошибки Oracle в форме), ошибка операции PL / SQL или определенные пользователем условия (не записано является ошибкой), могут быть. Конечно, ошибки компиляции PL / SQL не могут быть обработаны обработкой исключений PL / SQL, потому что эти ошибки происходят до выполнения программы PL / SQL.

ORACLE предоставляет исключения ( EXCEPTION ) И обработка исключений ( EXCEPTION HANDLER ) Реализовать обработку ошибок.

5.1 Концепции обработки исключений

Обработка исключений ( EXCEPTION ) Используется для обработки непредвиденных событий во время обычного выполнения. PL/SQL Как только программный блок генерирует исключение и не указывает, как его обработать, программа автоматически завершит всю программу.

Существует три типа ошибок исключений:

1. Предопределено ( Predefined )ошибка

ORACLE Есть около 24 предопределенных аномалий. Обработка этой ненормальной ситуации не должна быть определена в программе, ORACLE Это срабатывает автоматически.

2. Не определено ( Predefined )ошибка
- это другой стандарт ORACLE ошибка. Обработка этой ненормальной ситуации требует, чтобы пользователь определил ее в программе, а затем ORACLE Это срабатывает автоматически.

3. Определяемые пользователем ( User_define ) Ошибка

Часть обработки исключений обычно помещается в PL/SQL Вторая половина тела программы имеет структуру:

Обработка исключений может быть организована в любом порядке, но OTHERS Должно быть последним.

5.1.1 Предопределенная обработка исключений

Предопределенный раздел описания ORACLE ошибка

Пример 1. Обновите зарплату назначенного сотрудника, если зарплата меньше 1500, добавьте 100;

5.1.2 Неопределенная обработка исключений

Для обработки таких нештатных ситуаций вы должны сначала определить неопределенную ошибку ORACLE. Действуйте следующим образом:

  1. в PL/SQL Раздел определения блока определяет исключение:

<Ненормальная ситуация> ИСКЛЮЧЕНИЕ;

  1. Определите ненормальную ситуацию и стандарт ORACLE Ошибки ссылок, используйте EXCEPTION_INIT Утверждение:
  1. в PL/SQL Часть обработки нештатной ситуации блока соотносит обработку с ненормальной ситуацией.

Пример 2. Удалите информацию о записи назначенного отдела, чтобы убедиться, что в отделе нет сотрудников.

5.1.3 Определяемая пользователем обработка исключений

Когда возникает ошибка, связанная с ошибкой исключения, ошибка исключения неявно запускается. Определяемые пользователем ошибки исключения используются явно RAISE Заявление для запуска. При возникновении ненормальной ошибки управление передается EXCEPTION Блокировать исключительную часть ошибки, выполнить код обработки ошибок.

Для обработки таких нештатных ситуаций необходимо выполнить следующие шаги:

1. Определите исключение в разделе определения блока PL / SQL:

<Ненормальная ситуация> ИСКЛЮЧЕНИЕ;

2. Поднять <ненормальная ситуация>;

3. В части обработки исключений блока PL / SQL выполните соответствующую обработку для исключения.

Пример 3: Обновление зарплаты назначенных сотрудников, увеличение на 100;

5.1.4 Определяемая пользователем обработка исключений

RAISE_APPLICATION_ERROR Синтаксис выглядит следующим образом:

Пример 4: создание функции get_salary , Эта функция извлекает сумму заработной платы указанного отдела, которая определяет ошибки -20991 и -20992, и обрабатывает два вида ошибок: параметр пуст и недопустимый код отдела:

Пример 5: Определите триггер, используйте RAISE_APPLICATION_ERROR Чтобы предотвратить вставку новых записей сотрудников без имен сотрудников:

5.2 Распространение аномальной ошибки

Поскольку ошибки исключения могут появляться в части объявления, части выполнения и части ошибки исключения, ошибки исключения, возникающие в разных частях, также различны.

5.2.1 В исполнительной части возникает ошибка исключения

Когда в исполнительной части возникает ошибка исключения, возможны следующие ситуации:

l Если текущий блок настроен на обработку ошибки исключения, выполните ее и успешно завершите выполнение блока, а затем управление передается в содержащий блок.

l Если для настройки ошибки исключения текущего блока не определен процессор, то ошибка исключения распространяется путем увеличения его в содержащем блоке. Затем выполните шаг 1) для содержащего блока.

5.2.2 Ошибка исключения возникает в разделе объявлений

Если в части объявления возникает ненормальное условие, то есть в части объявления возникает ошибка, то эта ошибка может повлиять на другие блоки. Например, есть следующие программы на PL / SQL:

В примере, потому что Abc number(3)=’abc’; Ошибка, хотя в EXCEPTION Объяснил в WHEN OTHERS THEN Заявление, но WHEN OTHERS THEN Это не будет выполнено. Но если есть ненормальная ошибка за пределами блока оператора ошибки, ошибка может быть обнаружена, например:

5.3 Программирование обработки ненормальных ошибок

При общей обработке приложения рекомендуется, чтобы персонал программы использовал обработку исключений, потому что, если в программе не объявлена ​​обработка исключений, программа будет прервана, если программа работает неправильно, и никакая информация не будет запрошена. Ниже приведен пример программирования с использованием исключения, предоставленного системой.

5.4 Использование в PL / SQL SQLCODE, SQLERRM Функция обработки исключений

Пример 6. Сохраните код ошибки ORACLE и его информацию в таблице кодов ошибок.

Пример 7. Запрос кода ошибки ORACLE;

Пример 8. Использование кодов ошибок ORACLE для написания кода обработки ошибок исключений;

Читайте также: