Функции oracle 8


8урок по oracle sql, Логика SELECT и аналитические функции

—Выборка данных — SELECT

—Возможные члены: SELECT, FROM, WHERE, GROUP BY, HAVING, PIVOT, UNPIVOT, CONNECT BY, ORDER BY.
—Члены SELECT и FROM в конструкции — обязательны.

—Проризводительность во многом завист от правильно спланированного запроса
—Т.е. отсечение ненужных данных на уровне WHERE даст большую производительность, чем на уровне HAVING например
—Т.к. далее в обработке уже не будут участвовать не нужные нам данные
—Логика обработки этапов SELECT:
—FROM
—PIVOT, UNPIVOT
—CONNECT BY
—WHERE
—GROUP BY
—HAVING
—ORDER BY
—SELECT

Аналитические функции на Oracle 8.1.7

CREATE OR REPLACE VIEW REPORT_RATES
(cur_cur_id, start_date, end_date, rate, int_rate, stock_rate)
AS
(SELECT cur_cur_id, date_rate,
LEAD (date_rate, 1, TO_DATE(‘31122999′,’DDMMYYYY’))
OVER (PARTITION BY cur_cur_id ORDER BY date_rate), rate,int_rate,stock_rate
FROM exchange_rates)

select * from REPORT_RATES возвращает

2 01.01.2000 24.01.2000 28,5 28,5 28,5
2 24.01.2000 30.11.2000 28,39 28,57 28,39
2 30.11.2000 03.01.2001 27,85 28,8 27,85

А вот этот
select rr.*
from report_rates rr
where rr.cur_cur_ > and to_date(‘25.01.2000′,’dd.mm.yyyy’) between rr.start_date and rr.end_date

Возвращает вот что .
1 2 01.01.2000 24.01.2000 28,5 28,5 28,5
2 2 24.01.2000 31.12.2999 28,39 28,57 28,39

Вместо одной записи две . ((((((((

Что делать? Как заставить Oracle сначала строить View а только потом накладывать ограничения .

Заранее благодарен за советы и предложения .

03.03.2009, 14:32

Аналитические функции
Добрый день. Подскажите пожалуйста, как переделать данный запрос, чтобы он был с аналитическими.

Аналитические функции
Доброго времени суток всем У меня есть таблица с некоторыми данными,например: Код Имя Сумма 1.

Исследовать аналитические свойства функции!
Здравствуйте, уважаемые математики! Помогите разобраться как решить такой пример: f(z)= (Im z)^2

Исследовать аналитические свойства функции и найти ее производную
исследовать аналитические свойства w=f(z) и найти ее производную f(z)=|z|Rez. Добавлено через 8.

05.03.2009, 10:38 2

Привет!
что значит сначала строить view а тока потом накладывать ограничения? :-))))

ты view уже построил, значит при каждом обращении к этой вьюхе, у тебя в динамический курсор вываливается выборка этой вьюхи а уж потом накладываются ограничения, которые ты там написал.

Функция даты в Oracle 8i медленная

Я пытаюсь запустить следующий PL/SQL на сервере Oracle 8i (старый, я знаю):

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

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

Если я изменяю следующее, где условие:

К статическому значению, например:

который начинает возвращать строки почти мгновенно.

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

Поскольку я относительно новичок в PL/SQL, я не могу понять причины существенных различий в планах выполнения.


Я также пытаюсь запустить запрос в качестве прохода из SAS, но для проверки скорости выполнения я использовал SQL * Plus.

Для пояснения я уже пробовал использовать переменные связывания следующим образом:

Со следующим предложением where:

который возвращает стоимость выполнения 1179377.

Я также хотел бы избежать связывания переменных, если это возможно, поскольку я не верю, что могу ссылаться на них из SAS-запроса (хотя я могу ошибаться).

Я сомневаюсь, что проблема здесь имеет много общего с временем выполнения функции ADD_MONTHS. Вы уже показали, что существует значительная разница в плане выполнения, когда вы используете фиксированную минимальную дату. Большие изменения в планах выполнения обычно оказывают гораздо большее влияние на время выполнения, чем накладные расходы на функционирование, вероятно, хотя потенциально различные планы выполнения могут означать, что функция вызывается еще много раз. В любом случае проблема с корнем, на которую нужно обратить внимание, заключается в том, почему вы не получаете желаемый план выполнения.

Хороший план выполнения начинается с сканирования диапазона на S_DOC_QUOTE_IDX1 . Учитывая характер изменения запроса, я предполагаю, что это индекс в столбце CREATED . Часто оптимизатор не будет использовать индекс в столбце даты, когда условие фильтра основано на SYSDATE . Поскольку он не оценивается до времени выполнения, после того, как план выполнения был определен, анализатор не может сделать хорошую оценку избирательности условия фильтра даты. Когда вы используете вместо этого дату с жестко запрограммированным кодом, анализатор может использовать эту информацию для определения избирательности и делает лучший выбор в отношении использования индекса.

Я бы также предложил переменные связывания, но я думаю, что, поскольку вы находитесь на 8i, оптимизатор не может заглядывать в значения привязки, поэтому это оставляет его так же темно, как и раньше. В более поздней версии Oracle я ожидал бы, что решение bind будет эффективным.

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

Итак, мое предложение — написать код для определения статического значения для даты начала и связать его непосредственно в строке запроса перед разбором и исполнением.

Прежде всего, причина, по которой вы получаете различное время выполнения, заключается не в том, что Oracle много выполняет функцию даты. Выполнение этой функции SQL, даже если это делается для каждой строки (это, вероятно, не кстати), занимает лишь незначительное количество времени по сравнению с временем, которое требуется для фактического извлечения строк с диска/памяти.

Вы получаете совершенно разные сроки выполнения, потому что, как вы заметили, Oracle выбирает другой путь доступа. Выбор одного пути доступа по другому может привести к порядку величин разницы во времени выполнения. Поэтому реальный вопрос заключается не в «почему add_months требует времени?» но:

Почему Oracle выбирает этот неэффективный путь, хотя есть более эффективный?

Как и все алгоритмы оценки, он делает предположения о ваших данных, таких как общее распределение, основанное на минимальном/максимальном значении столбцов, мощности и физическом распределении значений в сегменте (фактор кластеризации).

Как это относится к вашему конкретному запросу

В вашем случае оптимизатор должен сделать оценку относительно избирательности различных предложений фильтра. В первом запросе фильтр находится между двумя переменными ( add_months(trunc(sysdate,’MM’), -1) and sysdate ), а в другом случае фильтр находится между константой и переменной.

Они выглядят одинаково для вас, потому что вы заменили переменную на ее значение, но для оптимизатора случаи очень разные: оптимизатор (по крайней мере, в 8i) только один раз вычисляет план выполнения для конкретного запроса. Как только путь доступа будет определен, все последующие исполнения получат один и тот же план выполнения. Поэтому он не может заменить переменную на ее значение, потому что значение может измениться в будущем, а план доступа должен работать для всех возможных значений.

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

Что вы можете сделать, если оптимизатор не выбрал правильный план

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

  • Убедитесь, что ваша статистика обновлена ​​. Столбец last_analyzed на ALL_TABLES и ALL_INDEXES расскажет вам, когда последний раз статистика была собрана на этих объектах. Хорошие надежные данные приводят к более точным догадкам, что позволяет (надеюсь) улучшить план выполнения.
  • Узнайте о различных вариантах сбора статистики ( dbms_stats package)
  • Перепишите свой запрос, чтобы использовать константы, когда это имеет смысл, так что оптимизатор сделает более надежные догадки.
  • Иногда два логически идентичных запроса приводят к различным планам выполнения, поскольку оптимизатор не будет вычислять одни и те же пути доступа (всех возможных путей).
  • Есть некоторые трюки, которые вы можете использовать, чтобы заставить оптимизатор выполнить какое-либо соединение перед другими, например:
    • Используйте rownum для материализовать подзапрос (это может занять больше временного пространства, но позволит вам принудительно настроить оптимизатор с помощью определенного шага).
    • Используйте hints, хотя большую часть времени я обращался только к подсказкам, когда все остальное терпит неудачу. В частности, я иногда использую подсказку LEADING, чтобы заставить оптимизатор начать работу с определенной таблицей (или двумя таблицами).
  • В конце концов, вы, вероятно, обнаружите, что более свежие версии имеют более надежный оптимизатор. 8i 12+ лет, и возможно, настало время для обновления:)

Это действительно интересная тема. Оптимизатор оракула постоянно меняется (между релизами), который со временем улучшается, даже если новые причуды иногда вводятся с исправлением дефектов. Если вы хотите узнать больше, я бы предложил Джонатан Льюис Основанный на стоимости Oracle: Основы

ЛЕКЦИЯ 8. ВСТРОЕННЫЕ ФУНКЦИИ В ORACLE

    Николай Травин 2 лет назад Просмотров:

1 ЛЕКЦИЯ 8. ВСТРОЕННЫЕ ФУНКЦИИ В ORACLE

2 Встроенные функции: Однострочные (скалярные) Символьные функции Числовые функции Функции преобразования типа Функции для обработки дат Специальные функции Агрегирующие Sum Count Min Max AVG

3 Символьные функции Примеры INSTR(строка1, строка2) INSTR( Иванов, ан ) вернет результат 3 LENGTH(строка) LOWER(строка) UPPER(строка) INITCAP(строка) NVL(строка1, выражение) NVL2(строка1, выражение1, выражение2) LENGTH( Коротков ) Результат =8 LOWER( Дом ) Результат дом UPPER( Дом ) Результат ДОМ INITCAP ( дом ) или INITCAP ( ДОМ ) Результат — Дом NVL(fio, фамилия не введена ) Если fio — NULL, будет выдана фраза фамилия не введена, иначе будет выведена фамилия в текущей записи

4 SUBSTR(строка, n[, к]) REPLACE(строка1,строка2, Строка3) TRIM(строка) LTRIM(строка) RTRIM(строка) SUBSBR ( победа,3,4) Результат беда REPLACE *-*-*-*-*-, *, qqq ) Результат qqq-qqq-qqq-qqq-qqq- TRIM( www ) Результат www LPAD(строка, n[, ‘символ ]) RPAD(строка, n[, ‘символ ]) LPAD( www,7, * ) Результат — ****www RPAD( www,7, * ) Результат — www****

5 Пример 1. Вывести список наименований товаров так, чтобы первой буквой была прописная буква, а остальные строчные буквы, строчными буквами, прописными буквами, две буквы из названия товара, начиная с третьей буквы. SELECT INITCAP(n_tov), LOWER (n_tov), UPPER(n_tov), SUBSTR(n_tov,3,2) FROM price_list; INITCAP(n_tov) LOWER (n_tov) UPPER(n_tov) SUBSTR(n_tov,3,2) Телевизор телевизор ТЕЛЕВИЗОР ле Cd-RW cd_rw CD-RW -R

6 Пример 2. Ко всем фамилиям в таблице manager, которые оканчиваются символом «в» добавить окончание «ский». SQL> SELECT * FROM manager WHERE fio LIKE ‘%в’; KOD_MEN FIO OKLAD Иванов Сидоров Петров 9000 SQL> UPDATE manager SET fio=rtrim(fio) ‘ский’ WHERE fio LIKE ‘%в’; 3 строк обновлено. SQL> SELECT * FROM manager WHERE fio LIKE ‘%й’; KOD_MEN FIO OKLAD Ивановский Сидоровский Петровский 9000

7 Пример 3. Убрать окончание «ский» из фамилий в таблице manager SQL> UPDATE manager SET fio= SUBSTR (fio, 1,LENGTH(RTRIM(fio))-4) WHERE fio LIKE ‘%ский’; 3 строк обновлено. SQL> SELECT * FROM manager WHERE fio LIKE ‘%в’; KOD_MEN FIO OKLAD Иванов Сидоров Петров 9000

8 Числовые функции Функция ABS(n) CEIL(n) Назначение абсолютное значение числа наименьшее целое, превышающее или равное n Примеры ABS(-5) Результат 5 CEIL(6.78) Результат 7 FLOOR(n) TRUNC(n [,k]) наибольшее целое, меньшее или равное n Возвращает значение числа, усеченное до k знаков. Если аргумент k отсутствует, число усекается до целого FLOOR(6.78) Результат 6 TRUNC(6.78, 1) Результат 6.7

9 ROUND(n,k) MOD(n,k) Округляет число n до k знаков Возвращает остаток отделения числа n на число k ROUND(6.78,1) Результат 6.8 MOD(7,2) Результат 1 POWER(n,k) Возвращает n k POWER(2,3) Результат 8 SIGN(n) SQRT(n) Возвращает -1, если число отрицательное, 1, если оно больше нуля и 0, если число равно нулю Определяет квадратный корень числа n SIGN(-4) Результат -1 SQRT(25) Результат 5

10 Функции для обработки данных типа «дата и время» Функция SYSDATE EXTRACT (YEAR MONTH DA Y FROM дата) ADD_MONTHS (дата, Число_месяцев) MONTHS_BETWE EN (дата1, дата2) Назначение Возвращает текущую дату Извлекает год, месяц или день из данных типа дата Добавляет или вычитает заданное число месяцев из даты Определяет число месяцев между датами Примеры EXTRACT (YEAR FROM sysdate) Результат ADD_MONTHS ( 10/09/07, 3) Результат MONTHS_BETWE EN (sysdate, birthday)

11 Функции преобразования типа TO_CHAR(дата, формат) TO_DATE (строка, формат) TO_NUMBER(стро ка [,формат]) Преобразует данное типа «дата» к символьному типу Преобразует строку в дату, используя заданный формат Преобразует строку в число TO_CHAR(sysdate, dd/mm/yy ) TO_DATE ( 10/30/2007, mm/dd/yy ) TO_NUMBER ( 123 ) Результат число 123

12 Аргумент Элемент формата Назначение Столетие CC Двузначное представление века Квартал Q Выделить квартал из года Представление года Представление месяца YYYY YYY YY Y RR MONTH, Month, MON, Mon MM Год, представленный 4 цифрами 3 цифрами Двумя цифрами Одной цифрой Округление до ближайшего года Месяц представляется Полным наименованием месяца Сокращенным наименованием месяца Двузначным числом 20, 21 Пример 1,2,3 или JANUARY January JAN Jan 01

Илон Маск рекомендует:  Java обгоняет по производительности c

13 Неделя WW w Двузначное представление недели года Однозначное представление недели месяца Номер дня DDD, DD, D DAY Day DY Dy День года День месяца День недели Полное название дня недели Сокращенное название дня MONDAY Monday MON Mon Час HH24 HH Представляет часы в 24- часовом формате в 12-часовом формате 23 11

14 SQL> select to_date(’03/15/2010′,’mm/dd/yyyy’) from dual; TO_DATE( SQL> select to_date(‘март 15, 2010’, ‘Month dd,yyyy’) from dual; TO_DATE(

15 SQL> select to_date(‘понедельник Март 2010′,’Day Month yyyy’) from dual; TO_DATE( SQL> select TO_DATE(‘Февраль 20,2007′,’Month DD,YYYY’) from dual; TO_DATE( SQL> select TO_DATE(’20-Март-2010′) from dual; TO_DATE(

Oracle функция даты 8i медленно

December 2020

1.6k раз

Я пытаюсь выполнить следующий PL / SQL на сервере с 8i Oracle (старый, я знаю):

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

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

Если изменить следующий пункт где, однако:

Для статического значения, такие как:

план выполнения становится:

который начинает возвращать строки почти мгновенно.

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

Потому что я относительно новым для PL / SQL, я не в состоянии понять причины столь значительных различий в планах выполнения.

Я также пытаюсь выполнить запрос в качестве сквозного от SAS, но для целей тестирования скорости выполнения Я использую SQL * Plus.

Для уточнения, я уже пробовал использовать переменные связывания следующим образом:


При следующем пункте, где:

которая возвращает стоимость выполнения 1179377.

Я хотел бы также, чтобы избежать переменных связывания, если это возможно, как я не верю, что я могу ссылаться на них из сквозную запроса SAS (хотя я могу ошибаться).

4 ответы

Это SQL. Вы можете использовать PL / SQL и сохраните вычисления add_months (TRUNC (SYSDATE, «MM»), -1) в переменную, а затем связать это.

Кроме того, я видел Calcs SAS занять долгое время из-за вытягивать данные по сети и делать дополнительную работу по каждой строке он обрабатывает. В зависимости от среды, вы можете рассмотреть вопрос о создании временной таблицы для хранения результатов этих объединений, а затем ударять временную таблицу (попробуйте CTAS).

Это потому, что функция выполняется для каждого сравнения.

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

в противном случае, вы могли бы также присоединиться к дате, как это:

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

[Править] Я извиняюсь, я вижу, вы уже пробовали варианты, как эти. Тем не менее странно. Если значение константы работает, параметр привязки должен работать так же, до тех пор, пока вы сохраняете функцию add_months вне запроса.

Я сомневаюсь, что здесь проблема имеет много общего с временем выполнения функции ADD_MONTHS. Вы уже показали, что существует значительная разница в плане выполнения, когда вы используете жёстко прописанные минимальную дату. Большие изменения в планы выполнения, как правило, гораздо большее влияние на время выполнения, чем накладные расходы вызова функции, скорее всего, хотя потенциально различные планы выполнения может означать, что функция вызывается еще много раз. В любом случае корень проблемы, чтобы посмотреть на то, почему Вы не получаете план выполнения вы хотите.

Хороший план выполнения начинается со сканированием диапазона на S_DOC_QUOTE_IDX1 . Учитывая характер изменения в запрос, я полагаю , это индекс на CREATED столбце. Часто оптимизатор не будет хотеть использовать индекс на колонке даты , когда условие фильтра основано на SYSDATE . Потому что это не вычисляются до момента выполнения, после того , как план выполнения был определен, анализатор не может сделать хорошую оценку селективности состояния фильтра даты. При использовании жёстко прописанных дат начала вместо этого, анализатор может использовать эту информацию для определения селективности, и делает лучший выбор относительно использования индекса.

Я бы предложил переменные связывания, а также, но я думаю, потому что вы находитесь на 8i оптимизатор не может заглянуть при значениях связывания, так что это оставляет столько же, сколько в темноте, как и раньше. На более поздней версии Oracle я хотел бы ожидать, что решение привязывать будет эффективным.

Тем не менее, это хороший случай, когда с помощью буквальной замены, вероятно, более подходящий, чем при использовании переменного связывания, так как (а) значение даты начала не задаваемый пользователя, и (б) он будет оставаться постоянным в течение всего месяца, так что вы не будет разбор много несколько различных запросов.

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

Прежде всего, причина вы получаете различное время выполнения не потому, что Oracle выполняет функцию даты много. Выполнение этой функции SQL, даже если это делается для каждой строки (это, вероятно, не по пути), занимает незначительное количество времени по сравнению с временем, которое требуется, чтобы фактически извлекать строки из диска / памяти.

Вы получаете совершенно разные времена исполнения , потому что, как вы заметили, Oracle выбирает другой путь доступа. Выбор один путь доступа над другим может привести к порядкам величин разницы во время выполнения. Реальный вопрос таким образом, не является «почему add_months занимает много времени?» но:

Почему Oracle выбирают именно этот путь unefficient в то время как существует более эффективный один?

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

Как и все алгоритмы оценки, это делает предположение о данных, таких как общее распределение на основе минимального / максимальное значение столбцов, мощности и физическое распределение значений в сегменте (факторе кластеризации).

Как это относится к вашему конкретному запросу

В вашем случае, оптимизатор должен сделать оценку о селективности различных положений фильтра. В первом запросе фильтр находится между двумя переменными ( add_months(trunc(sysdate,’MM’), -1) and sysdate ) в то время как в другом случае фильтр находится между константой и переменной.

Они выглядят одинаково, потому что вы заменили переменную ее значение, но оптимизатор случаи очень разные: оптимизатор (по крайней мере, в 8i) только вычисляет план выполнения один раз для конкретного запроса. После того, как путь доступа был определен, все дальнейшее выполнение получит тот же план выполнения. Он не может, следовательно, заменить переменную ее значение, поскольку значение может измениться в будущем, и план доступа должен работать для всех возможных значений.

Так как второй запрос использует переменные, оптимизатор не может точно определить селективность первого запроса, поэтому оптимизатор делает предположение, что и приводит в вашем случае в плохом плане.

Что вы можете сделать, когда оптимизатор не выбирает правильный план

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

  • Убедитесь , что ваши статы уточненным . last_analyzed Столбец ALL_TABLES и ALL_INDEXES сообщит вам , когда был последний раз , когда статистика была собрана на этих объектах. Хорошая надежная статистика приводит к более точным догадкам, ведущий (надеюсь) лучше план выполнения.
  • Узнайте о различных вариантах для сбора статистических данных ( dbms_stats пакет)
  • Перепишите запрос, чтобы использовать константы, когда это имеет смысл, так что оптимизатор будет делать более надежные предположения.
  • Иногда два логически идентичные запросы приведут к различным планам исполнения, потому что оптимизатор не будет вычислять одни и те же пути доступа (из всех возможных путей).
  • Есть несколько трюков, которые вы можете использовать, чтобы заставить оптимизатор выполнять некоторые присоединиться перед другими, например:
    • Используйте ROWNUM , чтобы материализовать подзапрос (это может занять больше временного пространства, но позволит вам заставить оптимизатор через определенный шаг).
    • Используйте подсказки , хотя большую часть времени я бы только обратиться к намекам , когда все остальное терпит неудачу. В частности, я иногда использую Leading подсказку , чтобы заставить оптимизатор начать с определенной таблицей (или парой таблицы).

  • Последнее всего, вы, вероятно, обнаружите, что более поздние релизы имеют в целом более надежный оптимизатор. 8i составляет 12+ лет, и это может быть время для обновления :)

Это действительно интересная тема. Оракул оптимизатор постоянно меняется (между выпусками) улучшается с течением времени, даже если новые причуды иногда вводятся как дефекты получить исправлены. Если вы хотите узнать больше, я хотел бы предложить Джонатана Льюиса стоимости на основе Oracle: Основы

Функция даты в Oracle 8i медленная

Я пытаюсь запустить следующий PL/SQL на сервере Oracle 8i (старый, я знаю):

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

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

Если я изменяю следующее, где условие:

К статическому значению, например:

который начинает возвращать строки почти мгновенно.

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

Поскольку я относительно новичок в PL/SQL, я не могу понять причины существенных различий в планах выполнения.

Я также пытаюсь запустить запрос в качестве прохода из SAS, но для проверки скорости выполнения я использовал SQL * Plus.

Для пояснения я уже пробовал использовать переменные связывания следующим образом:

Со следующим предложением where:

который возвращает стоимость выполнения 1179377.

Я также хотел бы избежать связывания переменных, если это возможно, поскольку я не верю, что могу ссылаться на них из SAS-запроса (хотя я могу ошибаться).

Я сомневаюсь, что проблема здесь имеет много общего с временем выполнения функции ADD_MONTHS. Вы уже показали, что существует значительная разница в плане выполнения, когда вы используете фиксированную минимальную дату. Большие изменения в планах выполнения обычно оказывают гораздо большее влияние на время выполнения, чем накладные расходы на функционирование, вероятно, хотя потенциально различные планы выполнения могут означать, что функция вызывается еще много раз. В любом случае проблема с корнем, на которую нужно обратить внимание, заключается в том, почему вы не получаете желаемый план выполнения.

Хороший план выполнения начинается с сканирования диапазона на S_DOC_QUOTE_IDX1 . Учитывая характер изменения запроса, я предполагаю, что это индекс в столбце CREATED . Часто оптимизатор не будет использовать индекс в столбце даты, когда условие фильтра основано на SYSDATE . Поскольку он не оценивается до времени выполнения, после того, как план выполнения был определен, анализатор не может сделать хорошую оценку избирательности условия фильтра даты. Когда вы используете вместо этого дату с жестко запрограммированным кодом, анализатор может использовать эту информацию для определения избирательности и делает лучший выбор в отношении использования индекса.

Я бы также предложил переменные связывания, но я думаю, что, поскольку вы находитесь на 8i, оптимизатор не может заглядывать в значения привязки, поэтому это оставляет его так же темно, как и раньше. В более поздней версии Oracle я ожидал бы, что решение bind будет эффективным.

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

Итак, мое предложение — написать код для определения статического значения для даты начала и связать его непосредственно в строке запроса перед разбором и исполнением.

Прежде всего, причина, по которой вы получаете различное время выполнения, заключается не в том, что Oracle много выполняет функцию даты. Выполнение этой функции SQL, даже если это делается для каждой строки (это, вероятно, не кстати), занимает лишь незначительное количество времени по сравнению с временем, которое требуется для фактического извлечения строк с диска/памяти.

Вы получаете совершенно разные сроки выполнения, потому что, как вы заметили, Oracle выбирает другой путь доступа. Выбор одного пути доступа по другому может привести к порядку величин разницы во времени выполнения. Поэтому реальный вопрос заключается не в «почему add_months требует времени?» но:

Почему Oracle выбирает этот неэффективный путь, хотя есть более эффективный?

Как и все алгоритмы оценки, он делает предположения о ваших данных, таких как общее распределение, основанное на минимальном/максимальном значении столбцов, мощности и физическом распределении значений в сегменте (фактор кластеризации).

Как это относится к вашему конкретному запросу

В вашем случае оптимизатор должен сделать оценку относительно избирательности различных предложений фильтра. В первом запросе фильтр находится между двумя переменными ( add_months(trunc(sysdate,’MM’), -1) and sysdate ), а в другом случае фильтр находится между константой и переменной.

Илон Маск рекомендует:  border-top-color в CSS

Они выглядят одинаково для вас, потому что вы заменили переменную на ее значение, но для оптимизатора случаи очень разные: оптимизатор (по крайней мере, в 8i) только один раз вычисляет план выполнения для конкретного запроса. Как только путь доступа будет определен, все последующие исполнения получат один и тот же план выполнения. Поэтому он не может заменить переменную на ее значение, потому что значение может измениться в будущем, а план доступа должен работать для всех возможных значений.


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

Что вы можете сделать, если оптимизатор не выбрал правильный план

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

  • Убедитесь, что ваша статистика обновлена ​​. Столбец last_analyzed на ALL_TABLES и ALL_INDEXES расскажет вам, когда последний раз статистика была собрана на этих объектах. Хорошие надежные данные приводят к более точным догадкам, что позволяет (надеюсь) улучшить план выполнения.
  • Узнайте о различных вариантах сбора статистики ( dbms_stats package)
  • Перепишите свой запрос, чтобы использовать константы, когда это имеет смысл, так что оптимизатор сделает более надежные догадки.
  • Иногда два логически идентичных запроса приводят к различным планам выполнения, поскольку оптимизатор не будет вычислять одни и те же пути доступа (всех возможных путей).
  • Есть некоторые трюки, которые вы можете использовать, чтобы заставить оптимизатор выполнить какое-либо соединение перед другими, например:
    • Используйте rownum для материализовать подзапрос (это может занять больше временного пространства, но позволит вам принудительно настроить оптимизатор с помощью определенного шага).
    • Используйте hints, хотя большую часть времени я обращался только к подсказкам, когда все остальное терпит неудачу. В частности, я иногда использую подсказку LEADING, чтобы заставить оптимизатор начать работу с определенной таблицей (или двумя таблицами).
  • В конце концов, вы, вероятно, обнаружите, что более свежие версии имеют более надежный оптимизатор. 8i 12+ лет, и возможно, настало время для обновления:)

Это действительно интересная тема. Оптимизатор оракула постоянно меняется (между релизами), который со временем улучшается, даже если новые причуды иногда вводятся с исправлением дефектов. Если вы хотите узнать больше, я бы предложил Джонатан Льюис Основанный на стоимости Oracle: Основы

Функции oracle 8

Резюме. В Oracle8 i была введена функция CAST, которая позволяет обраатывать PL/SQL-коллекции (collection), как обычые таблицы. Когда CAST применяется в сочетании с с табличными функциями, это становится еще более мощным средством манипулирования данными. Эта статья содержит краткое технологическое толкование работы CAST и табличных функций на нескольких реальных примерах, практическое использование которых может представлять интерес.

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

  • служащий — это часть отдела (department), который в свою очередь является частью отделения (division);
  • каждый служащий может обратиться в от одной до пяти кредитных организаций;
  • каждый отдел имеет до пяти заданных по умолчанию кредитных организаций, в которых можно взять деньги;
  • каждое отделение имеет до пяти заданных по умолчанию кредитных организаций, в которых можно взять деньги;
  • коль скоро списки потенциально имеющих такое право служащих, заданных по умолчанию кредитных организаций для отделов и отделенияй уже определены, то только те кредитные организации, в которых может обращаться любой служащий, следует сохранить в списке;
  • из окончательного списка должны быть удалены дубли имен кредитных организаций.

Чтобы наиболее правильно поставить вопросы, хранимая процедура, которая реализует эти бизнес-правила, должна возвратить ответный набор в форме ссылочного курсора (REF CURSOR), как того требует приложение. К сожалению, приложение не может принять в качестве входного параметра ни одного типа Oracle-коллекций без существенного изменения устаревшего основанного на Powerbuilder кода.

Когда я открыл эту хранимую процедуру, я заметил, что она когда-то была конвертирована из Sybase-оригинала в нашу нынешнюю базу данных Oracle. База данных Sybase имеет несколько интересных возможностей по организации хранения временных данных – огромное, по существу, TEMP-пространство, которое постоянно доступно для использования любой хранимой процедурой. И все, кто конвертировали такую процедуру в Oracle, подражали этой методике, используя GTT (GLOBAL TEMPORARY TABLE — глобальную временную таблицу), чтобы сохранить данные.

GTT-таблицы, конечно, имеются в базе данных Oracle, но они обладают некоторыми недостатками. Во-первых, GTT – все-таки таблица, а, как я заметил, разработчики часто забывают выполнить COMMIT для фиксации изменений после записи в GTT. Кроме того, накладные издержки от создания и поддержания схемы для GTT в ситуациях, подобно описанной, часто являются слишком боьшими. В конце концов, наибольшее число записей, которые когда-либо ко мне возвращались в этом ответом наборе, было 15.

Я также столкнулся с проблемами при попытке открыть базу данных горячего резервирования в режиме READ ONLY, а затем пробовать выполнить хранимые процедуры, которые должны были использовать GTT-таблицы. Поскольку GTT-таблицы принадлежат табличному пространству SYSTEM, и это табличное пространство находится в режиме read-only (только_для_чтения). Когда же резервирная база открывается таким способом для составления отчетов, хранимые процедуры, использовавшиеся для этой цели, просто прекращали работать. Есть обходные пути, чтобы разрешить эту ситуацию, но они не очень элегантны.

К счастью, Oracle реализовал некоторые возможности, которые позволили мне преодолеть зависимость от GTT: функция CAST и способность создавать хранимые функции, которые возвращают типы PL/SQL-коллекций, известные также как табличные функции. Когда эти возможности используется в сочетании друг с другом, то формируется мощный комплект инструментальных средств, который подчиняет себе GTT-таблицы, использовавшиеся до этого способа. (Кроме того, они [CAST + табличные функции] работают настолько хорошо, даже если вам не нужно бороться с какими-то ни было GTT-таблицами, почему бы их не попробовать!)

Функция CAST

CAST часто называют pseudo-table function ( псевдотабличной функцией) , потому что она позволяет мне cast a variable ( приводить переменную ) – а именно, PL/SQL-коллекцию — в другой тип данных (datatype): в табличную структуру. К табличной структуре может быть сделан стандартный SQL-запрос точно так же, как к любой другой таблице Oracle на SQL.

Листинг 1.1 показывает пример, как может быть использована функция CAST в анонимном PL/SQL-блоке, чтобы прочитать PL/SQL-коллекцию, определенную декларируемым TYPE. CAST используется здесь для сортирки результирующих строк в обратном алфавитном порядке. Я мог бы создать TYPE как истинный объект и построить функцию сортировки для этого объекта, но CAST позволяет мне использовать добрый старый SQL, чтобы выполнить сортировку.

Листинг 1.2 показывает другой пример CAST. На сей раз я заполняю PL/SQL коллекцию набором случайных чисел. Затем я использую CAST, чтобы набрать данные из коллекции и применить различные групповые функции, как-то SUM(), MIN() и MAX() на результирующем наборе. И опять же я мог объявить объектный тип и написать некие специальные функции группировки. Но снова я использовал CAST, чтобы сделать работу при помощи обычных групповых SQL-функций.

Табличные функции

Табличная функция — это хранимая функция, которая возвращает PL/SQL коллекцию как результирующий набор, и функция CAST может затем этот набор читать и им манипулировать. В качестве примера табличной функции, sf_gather_cost_centers, которую я создал в Листинге 1.3 , используется PL/SQL коллекция, хранящая собранные названия кредитных организаций для указанного служащего и возвращающая список всех кредитных организаций, которыми служащий имеет право пользоваться, в зависимости от отношений его к отделу и отделению.

На Листинге 1.4 показаны три примера, которые собирают результаты табличной функции, которую я только что создал через псевдо-функциональную CAST-таблицу для более поздних манипуляций. Результирующий набор этих SQL-предложений может быть затем возвращен в ссылочном курсоре, сгенерированном с несколькими модификациями внутри существующей хранимой процедуры, как мне предписано в соответствии с первоначальными требованиями.

Конвейерные табличные функции

Табличные функции были доступны, начиная с Oracle 8i , они были расширены в Oracle 9i так, что результирующие наборы могут быть pipelined (конвейерными) . Кратко, конвейерная (pipelined) табличная функция не требует, чтобы псевдотабличная CAST- функция возвращала результирующий набор.

Листинг 1.5 показывает модифицированную версию той же самой функции, которую я создал в Листинге 1.3 , а на Листинге 1.6 показаны те же самые примеры, что и на Листинге 1.4 , но теперь не связанных с псевдофункцией CAST.

Заключение


CAST и табличные функции стали мощным набором инструментальных средств моей PL/SQL-среды разработки. И я рассчитаваю на ваш интерес в надежде, что вы также захотите поэкспериментировать с этими средствами. Если Вы пожелаете поработать с моими примерами, я на Листинге 2 поместил необходимый язык описания данных и DML-инструкции, чтобы изменить стандартную демонстрационную схему HR.

Ссылки и дополнительная лиература

A96595-01 Oracle 9i Data Cartridge Developer’s Guide, Chapter 12
A96624-01 Oracle 9i PL/SQL User’s Guide and Reference, Chapter 8

Хостинг в Европе для новичков (от 25 руб/мес) и VIP-хостинг для профессионалов (от 1000 руб/мес)

Скидка 25% на все тарифы хостинга по промокоду STDCITF

Developer notes

Oracle. Аналитические функции.

Общий синтаксис для использования аналитических функций следующий

Рассмотрим основные части данного синтаксиса.

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

partition by выражение [, выражение] [, выражение]

Данная конструкция логически разбивает результирующее множество на N групп
по критериям, задаваемым выражениями фрагментации. Аналитические функции применяются к каждой группе независимо, — для каждой новой группы они сбрасываются. Если не указать конструкцию фрагментации, все результирующее множество считается
одной группой.

Конструкция упорядочения имеет следующий синтаксис

order by выражение [asc | desc] [nulls first | nulls last]

Конструкция order by задает критерий сортировки данных в каждой группе (в каждом фрагменте). Это, несомненно, влияет на результат выполнения любой аналитической функции. При наличии (или отсутствии) конструкции order by аналитические функции вычисляются по-другому. Например.
— без конструкции order by

select ename, sal, avg(sal) over ()
from emp

— с конструкцией order by

select ename, sal, avg(sal) over (order by ename)
from emp

Здесь стоит отметить следующее, на самом деле наличие конструкции order by в вызове аналитической функции добавляет стандартную конструкцию окна — RANGE UNBOUNDED PRECEDING. Это означает, что для вычисления используется набор из всех предыдущих и текущей строки в текущем фрагменте. При отсутствии order by стандартным окном является весь фрагмент. То есть по-сути предыдущий запрос будет выглядеть следующим образом

select ename, sal, avg(sal) over (order by ename RANGE UNBOUNDED PRECEDING)
from emp

Конструкция окна позволяет задать перемещающееся или жестко привязанное окно (набор)
данных в пределах группы, с которым будет работать аналитическая функция.
Например, для создания отчета, показывающего сумму зарплат текущего и двух предыдущих сотрудников отдела, можно выполнить следующий запрос

select deptno, ename, sal,
sum(sal) over (partition by deptno order by ename rows 2 preceding) sliding_total
from emp
order by deptno, ename

Можно создавать окна по двум критериям: по диапазону (RANGE) значений данных или по
смещению (ROWS) относительно текущей строки
. Использование конструкции range как было сказано ранее в некоторых случаях используется неявно, RANGE UNBOUNDED PRECEDING например. Она требует брать все строки вплоть до текущей, в соответствии с порядком, задаваемым конструкцией order by. Следует помнить, что для использования окон
необходимо задавать конструкцию order by.

Окно определяется диапазоном строк, объединяемых в соответствии с заданным порядком.
Применять конструкцию range можно либо с числовыми выражениями (NUMBER), либо с выражениями, значением которого является дата (DATE). Еще одно ограничение для таких окон состоит в том, что в конструкции order by может быть только один столбец — диапазоны по природе своей одномерны. Нельзя задать диапазон в N-мерном пространстве. Пример.
Пусть необходимо выбрать зарплату каждого сотрудника и среднюю зарплату всех принятых на работу в течение 100 предыдущих дней, а также среднюю зарплату всех принятых на работу в течение 100 следующих дней. Соответствующий запрос будет выглядеть так:

select ename, hiredate, sal,
avg(sal) over (order by hiredate asc range 100 preceding) avg_sal_100_days_before,
avg(sal) over (order by hiredate desc range 100 preceding) avg_sal_100_days_after
from emp
order by hiredate desc

Помимо определения окна по диапазону (RANGE), также окна определяются и по количеству строк (ROWS). Для окон по строкам нет ограничений, присущих окнам по диапазону; данные могут быть любого типа и упорядочивать можно по любому количеству столбцов.
Например, пусть нужно вычислить среднюю зарплату для сотрудника и пяти принятых на работу до него и после него. Запрос можно записать следующим образом

select ename, hiredate, sal,
avg(sal) over ( order by hiredate asc rows 5 preceding ) avg_5_before,
avg(sal) over ( order by hiredate desc rows 5 preceding) avg_5_after
from emp
order by hiredate

Зная как определяются окна (по диапазону или по количеству строк), рассмотрим как окончательно задаются окна. В простейшем случае, окно задается с помощью одной из трех следующих взаимоисключающих конструкций.

— UNBOUNDED PRECEDING.
Окно начинается с первой строки текущей группы и заканчивается текущей обрабатываемой строкой.

— CURRENT ROW.
Окно начинается (и заканчивается) текущей строкой.


— Числовое_выражение PRECEDING.
Окно начинается со строки за числовое_выражение строк до текущей, если оно задается по строкам, или со строки, меньшей по значению столбца, упомянутого в конструкции order by, не более чем на числовое выражение, если оно задается по диапазону.

— Числовое_выражение FOLLOWING.
Окно заканчивается (или начинается) со строки, через числовое_выражение строк после текущей, если оно задается по строкам, или со строки, большей
по значению столбца, упомянутого в конструкции order by, не более чем на числовое_выражение, если оно задается по диапазону.

Стоит отметить, что окно CURRENT ROW в простейшем виде, вероятно, никогда не используется, поскольку ограничивает применение аналитической функции одной текущей строкой, а для этого аналитические функции не нужны. В более сложном случае для окна задается также конструкция BETWEEN. В ней CURRENT ROW можно указывать в качестве начальной или конечной строки окна. Начальную и конечную строку окна в конструкции BETWEEN можно задавать с использованием любой из перечисленных выше конструкций. Например, можно задать окно так, что обрабатываемая строка не будет
последней, а окажется где-то в середине окна

select ename, hiredate,
first_value(ename) over (order by hiredate asc range between 100 preceding and 100 following),
last_value(ename) over (order by hiredate asc range between 100 preceding and 100 following)
from emp
order by hiredate asc

В данном запросе дополнительно использованы функции first_value() и last_value(), которые возвращают первое значений текущего окна и последнее значение также текущего окна, соответственно, в то время как диапазон окна ограничен слева текущая скользящая дата — 100 дней и справа к текущей скользящей дате + 100 дней, в этом и состоит смысл выражения — between 100 preceding and 100 following .

select
level,
count(*) over (order by level asc rows 2 preceding) asc_count,
count(*) over (order by level desc rows 2 preceding) desc_count
from dual
connect by level

Окно rows 2 preceding, как видно из результата запроса, содержит от 1 до 3 строк (это определяется тем, как далеко текущая строка находится от начала группы). Для первой строки группы имеем значение 1 (предыдущих строк нет). Для следующей строки в группе таких строк 2. Наконец, для третьей и далее строк значение count(*) остается постоянным, поскольку мы считаем только текущую строку и две предыдущие.

select
n,
sum(n) over (order by days range 2 preceding) n_sum,
days
from (
select
level n,
( to_date(‘10.01.2014’, ‘dd.mm.yyyy’) + (level — 1) ) days
from dual
connect by level

В данном случае идет суммирование в пределах окна, диапазон которого составляет скользящий день и 2 предыдущих дня

select
ename,
sal,
rank() over (order by sal) rank,
dense_rank() over (order by sal) dens_rank,
row_number() over (order by sal) row_number
from emp
order by sal

Данный запрос демонстрирует работу ранжирующих функций rank(), dense_rank() и row_number() по окладам работников. Обратите внимание на поведение данных функций в строках с одинаковыми значениями окладов.

select
ename,
deptno,
sal,
rank() over (partition by deptno order by sal) rank,
dense_rank() over (partition by deptno order by sal) dens_rank,
row_number() over (partition by deptno order by sal) row_number
from emp
order by deptno

Можно предыдущий запрос фрагментировать по отделам, то есть разбить на группы по отделам, в пределах которых будут работать аналитические функции, которые при выходе за пределы групп будут сбрасывать результаты. Результат представлен ниже

Рассмотрим пример применения функций lag() и lead(). Для того, чтобы можно было обращаться в текущей строке к предыдущим строкам, необходимо использовать функцию — lag(). Синтаксис ее таков

lag(поле_для_обращения, смещение, значение_для_замещения_null)
over (partition by выражение order by выражение)

поле_для_обращения — поле, по которому нужно просматривать значения;
смещение — смещенная строка, с которой просматривается поле, по-умолчанию равно 1, если проставить 0, тогда будет просматриваться текущее поле;
значение_для_замещения_null — по-умолчанию равно null, в случае отсутствия значения в просматриваемом поле, возвращает данное значение. Здесь стоит отметить, что подставляемое значение должно быть того же типа, что и просматриваемое поле;
для данной функции обязательно использование order by

with
main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from emp
order by sal
),
numerated_main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from main
)
select
ename,
sal,
lag(sal) over (order by rownum) previous_sal
from numerated_main

with
main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from emp
order by sal
),
numerated_main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from main
)
select
ename,
sal,
lag(sal, 2, 0) over (order by rownum) previous_sal
from numerated_main

Логика и синтаксис работы функции lead() аналогичен предыдущей функции с одной лишь разницей: просмотр идет не назад, а вперед

with
main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from emp
order by sal
),
numerated_main as (
select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum
from main
)
select
ename,
sal,
lead(sal, 2, 0) over (order by rownum) previous_sal
from numerated_main

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

Аналитические функции в Oracle

Содержание


Общие положения


Общая информация

В версии СУБД Oracle 8.1.6 появился новый класс из 26 функций, названных аналитическими, и получившим дальнейшее развитие в версии 9. Их описания были созданы совместными усилиями фирм IBM, Informix, Oracle и Compaq путем разработки так называемых «улучшений» некоторых конструкций, имеющихся в стандарте SQL1999.

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

Цели введения аналитических функций в Oracle

Техническая цель введения аналитических функций — дать лаконичную формулировку и увеличить скорость выполнения «аналитических запросов» к БД, то есть запросов, имеющих смыслом выявление внутренних соотношений и зависимостей в данных. Более точно, пользование аналитическими функциями может дать следующие выгоды перед обычными SQL-операторами:


  • Лаконичную и простую формулировку . Многие аналитические запросы к БД традиционными средствами сложно формулируются, а потому с трудом осмысливаются и плохо отлаживаются.
  • Снижение нагрузки на сеть . То, что раньше могло формулироваться только серией запросов, сворачивается в один запрос. По сети только отправляется запрос и получается окончательный результат.
  • Перенос вычислений на сервер . С использованием аналитических функций нет нужды организовывать расчеты на клиенте; они полностью проводятся на сервере, ресурсы которого могут быть более подходящи для быстрой обработки больших объемов данных.
  • Лучшую эффективность обработки запросов . Аналитические функции имеют алгоритмы вычисления, неразрывно связанные со специальными планами обработки запросов, оптимизированными для большей скорости получения результата.

Стратегическая цель введения в Oracle аналитических функций — дать базовое средство для построения ИС типа «складов данных» (data warehouse, DW), ИС «аналитического характера» (business intelligence systems, BI) или OLAP-систем. По представлениям разработчиков, набор таких базовых средств помимо аналитических функций формируют еще и прочие средства Oracle, такие как

  • конструкции ROLLUP, CUBE и связанные с ними в предложениях с GROUP BY
  • материализованные выводимые таблицы (materialized views)

Классификация видов аналитических функций в Oracle

Согласно классификации из документации по Oracle, аналитические функции могут быть следующих видов:

(a) функции ранжирования
(b) статистические функции для плавающего интервала
(c) функции подсчета долей
(d) статистические функции LAG/LEAD с запаздывающим/опережающим аргументом
(e) статистические функции (линейная регрессия и т. д.)

Основные технические особенности


Место указания аналитических функций в SQL-предложении

Аналитические функции принимают в качестве аргумента столбец промежуточного результата вычисления SQL-предложения и возвращают тоже столбец. Поэтому местом их использования в SQL-предложении могут быть только фразы ORDER BY и SELECT, выполняющие завершающую обработку логического промежуточного результата.

Сравнение с обычными функциями агрегирования

Многие аналитические функции действуют подобно обычным скалярным функциям агрегирования SUM, MAX и прочим, примененным к группам строк, сформированным с помощью GROUP BY. Однако обычные функции агрегирования уменьшают степень детализации, а аналитические функции нет. Поясняющий сравнительный пример:

SELECT deptno, job, SUM(sal) sum_sal
FROM emp
GROUP BY deptno, job;

SELECT ename, deptno, job,
SUM(sal) OVER (PARTITION BY deptno, job) sum_sal
FROM emp;

ЛЕКЦИЯ 8. ВСТРОЕННЫЕ ФУНКЦИИ В ORACLE

    Николай Травин 2 лет назад Просмотров:

1 ЛЕКЦИЯ 8. ВСТРОЕННЫЕ ФУНКЦИИ В ORACLE

2 Встроенные функции: Однострочные (скалярные) Символьные функции Числовые функции Функции преобразования типа Функции для обработки дат Специальные функции Агрегирующие Sum Count Min Max AVG

3 Символьные функции Примеры INSTR(строка1, строка2) INSTR( Иванов, ан ) вернет результат 3 LENGTH(строка) LOWER(строка) UPPER(строка) INITCAP(строка) NVL(строка1, выражение) NVL2(строка1, выражение1, выражение2) LENGTH( Коротков ) Результат =8 LOWER( Дом ) Результат дом UPPER( Дом ) Результат ДОМ INITCAP ( дом ) или INITCAP ( ДОМ ) Результат — Дом NVL(fio, фамилия не введена ) Если fio — NULL, будет выдана фраза фамилия не введена, иначе будет выведена фамилия в текущей записи

4 SUBSTR(строка, n[, к]) REPLACE(строка1,строка2, Строка3) TRIM(строка) LTRIM(строка) RTRIM(строка) SUBSBR ( победа,3,4) Результат беда REPLACE *-*-*-*-*-, *, qqq ) Результат qqq-qqq-qqq-qqq-qqq- TRIM( www ) Результат www LPAD(строка, n[, ‘символ ]) RPAD(строка, n[, ‘символ ]) LPAD( www,7, * ) Результат — ****www RPAD( www,7, * ) Результат — www****

5 Пример 1. Вывести список наименований товаров так, чтобы первой буквой была прописная буква, а остальные строчные буквы, строчными буквами, прописными буквами, две буквы из названия товара, начиная с третьей буквы. SELECT INITCAP(n_tov), LOWER (n_tov), UPPER(n_tov), SUBSTR(n_tov,3,2) FROM price_list; INITCAP(n_tov) LOWER (n_tov) UPPER(n_tov) SUBSTR(n_tov,3,2) Телевизор телевизор ТЕЛЕВИЗОР ле Cd-RW cd_rw CD-RW -R

6 Пример 2. Ко всем фамилиям в таблице manager, которые оканчиваются символом «в» добавить окончание «ский». SQL> SELECT * FROM manager WHERE fio LIKE ‘%в’; KOD_MEN FIO OKLAD Иванов Сидоров Петров 9000 SQL> UPDATE manager SET fio=rtrim(fio) ‘ский’ WHERE fio LIKE ‘%в’; 3 строк обновлено. SQL> SELECT * FROM manager WHERE fio LIKE ‘%й’; KOD_MEN FIO OKLAD Ивановский Сидоровский Петровский 9000

7 Пример 3. Убрать окончание «ский» из фамилий в таблице manager SQL> UPDATE manager SET fio= SUBSTR (fio, 1,LENGTH(RTRIM(fio))-4) WHERE fio LIKE ‘%ский’; 3 строк обновлено. SQL> SELECT * FROM manager WHERE fio LIKE ‘%в’; KOD_MEN FIO OKLAD Иванов Сидоров Петров 9000

8 Числовые функции Функция ABS(n) CEIL(n) Назначение абсолютное значение числа наименьшее целое, превышающее или равное n Примеры ABS(-5) Результат 5 CEIL(6.78) Результат 7 FLOOR(n) TRUNC(n [,k]) наибольшее целое, меньшее или равное n Возвращает значение числа, усеченное до k знаков. Если аргумент k отсутствует, число усекается до целого FLOOR(6.78) Результат 6 TRUNC(6.78, 1) Результат 6.7

9 ROUND(n,k) MOD(n,k) Округляет число n до k знаков Возвращает остаток отделения числа n на число k ROUND(6.78,1) Результат 6.8 MOD(7,2) Результат 1 POWER(n,k) Возвращает n k POWER(2,3) Результат 8 SIGN(n) SQRT(n) Возвращает -1, если число отрицательное, 1, если оно больше нуля и 0, если число равно нулю Определяет квадратный корень числа n SIGN(-4) Результат -1 SQRT(25) Результат 5

10 Функции для обработки данных типа «дата и время» Функция SYSDATE EXTRACT (YEAR MONTH DA Y FROM дата) ADD_MONTHS (дата, Число_месяцев) MONTHS_BETWE EN (дата1, дата2) Назначение Возвращает текущую дату Извлекает год, месяц или день из данных типа дата Добавляет или вычитает заданное число месяцев из даты Определяет число месяцев между датами Примеры EXTRACT (YEAR FROM sysdate) Результат ADD_MONTHS ( 10/09/07, 3) Результат MONTHS_BETWE EN (sysdate, birthday)

11 Функции преобразования типа TO_CHAR(дата, формат) TO_DATE (строка, формат) TO_NUMBER(стро ка [,формат]) Преобразует данное типа «дата» к символьному типу Преобразует строку в дату, используя заданный формат Преобразует строку в число TO_CHAR(sysdate, dd/mm/yy ) TO_DATE ( 10/30/2007, mm/dd/yy ) TO_NUMBER ( 123 ) Результат число 123

12 Аргумент Элемент формата Назначение Столетие CC Двузначное представление века Квартал Q Выделить квартал из года Представление года Представление месяца YYYY YYY YY Y RR MONTH, Month, MON, Mon MM Год, представленный 4 цифрами 3 цифрами Двумя цифрами Одной цифрой Округление до ближайшего года Месяц представляется Полным наименованием месяца Сокращенным наименованием месяца Двузначным числом 20, 21 Пример 1,2,3 или JANUARY January JAN Jan 01

13 Неделя WW w Двузначное представление недели года Однозначное представление недели месяца Номер дня DDD, DD, D DAY Day DY Dy День года День месяца День недели Полное название дня недели Сокращенное название дня MONDAY Monday MON Mon Час HH24 HH Представляет часы в 24- часовом формате в 12-часовом формате 23 11

14 SQL> select to_date(’03/15/2010′,’mm/dd/yyyy’) from dual; TO_DATE( SQL> select to_date(‘март 15, 2010’, ‘Month dd,yyyy’) from dual; TO_DATE(

15 SQL> select to_date(‘понедельник Март 2010′,’Day Month yyyy’) from dual; TO_DATE( SQL> select TO_DATE(‘Февраль 20,2007′,’Month DD,YYYY’) from dual; TO_DATE( SQL> select TO_DATE(’20-Март-2010′) from dual; TO_DATE(

Илон Маск рекомендует:  PromptForFileName - Функция Delphi
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL