all-servs 4 янв 2018 в 08:36

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

3 мин

54K

Oracle * SQL * Программирование *

Из песочницы

Комментарии 27

mad_nazgul 5 янв 2018 в 03:07

Лучше бы «with» реализовали, как в PostgreSQL, тогда табличные функции особо не нужны были бы. :-)

all-servs 5 янв 2018 в 05:28

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

mad_nazgul 5 янв 2018 в 06:03

Не истины ради, а холивара для…
По мне использование ХП это плохо, тем более для «сложной логики», для этого есть другие ЯП, где это делать по моему удобнее.
Конечно PL/SQL это мощный ЯП, но он застрял в 80 годах прошлого века.
Плюс если этой «логики» много и она размазана по ХП, пакетам, функциям в куче мест, то сопровождать этого «фрянкинпуха» то еще удовольствие.
«with» как в PostgreSQL по мне это разумный компромисс для создания сложных запросов, без тотального привлечения ХП.

all-servs 5 янв 2018 в 06:25

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

mad_nazgul 5 янв 2018 в 06:31

Понятно, что некоторые задачи на императивном ЯП решаются проще, чем на декларативном SQL.
Я «холиварю» немного про другое.
Если есть «сложная логика», то ее лучше оформить не на ХП (и иже с ними), а на другом ЯП не зависимым от БД.
:-)

all-servs 5 янв 2018 в 06:40

если в субд не встроен мощный процедурный язык то да, но в oracle pl/sql очень силен и заточен именно под работу с данными, поэтому мне кажется на нем решать такие задачи удобнее, так сказать не отходя от кассы.

mad_nazgul 5 янв 2018 в 07:56

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

all-servs 5 янв 2018 в 08:12

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

mad_nazgul 8 янв 2018 в 02:53

Согласен с вами.
Просто мое мнение, что если запрос сложный, что его приходится выносить в ХП, значит «что-то здесь не так».
И стоит присмотреться как структуре данных, так и к бизнес-логике

xtender 7 янв 2018 в 23:15

«WITH» есть в Oracle, причем он функционально гораздо шире, чем в PostgreSQL. Там сейчас можно даже процедуры и функции размещать.

ЗЫ. Не истины ради, а холивара для в Oracle исторически многое очень хорошо оптимизировано, включая PL/SQL, причем настолько много, что мало кто знает о таких оптимизациях и как они работают, что потом удивляются почему «то же самое» на других СУБД выполняется очень медленно.

mad_nazgul 8 янв 2018 в 02:52

Есть, то он есть, но вот работать с ним нельзя, т.к. убожество полное.
Я не могу использовать уже определенные представления в других представлениях.
Только в результирующем запросе.
И нафига там размещать процедуры и функции?!

Насчет PL/SQL — это типичный vendor lock.
Для Oracle это хорошо, а вот для остальных не очень.

xtender 8 янв 2018 в 03:32

И нафига там размещать процедуры и функции?!

Ни разу не встречал когда нужны функции и сам придумать не можешь?

Я не могу использовать уже определенные представления в других представлениях.

Не понял? Почему не можешь?

SQL> with
  2   a(a) as (select 1 from dual)
  3  ,b(b) as (select * from a)
  4  ,c(c) as (select * from b)
  5  select *
  6* from a,b,c;

         A          B          C
---------- ---------- ----------
         1          1          1

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

Забавно слышать, т.к. в PG возможностей еще меньше…

all-servs 5 янв 2018 в 05:20

вы с mssql работаете?

all-servs 5 янв 2018 в 05:27

dr_Irbisov 5 янв 2018 в 05:20

Подскажите, в данном случае есть какое-то приниципиальное отличие от использования хранимых процедур? Ну кроме простоты переиспользования)

all-servs 5 янв 2018 в 05:21

вы с mssql работаете?

dr_Irbisov 5 янв 2018 в 09:26

Регулярно не работаю. А при чем тут mssql?

all-servs 5 янв 2018 в 09:44

Просто в mssql хранимая процедура может вернуть выборку, в oracle такого нет. Статья про то как получить выборку средствами pl/sql кода обратиться к ней как к обычной таблице и не просесть в плане производительности. Как это можно сделать с помощью процедуры?

xtender 7 янв 2018 в 23:19

С 12.1 может

viktprog 5 янв 2018 в 05:56

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

all-servs 5 янв 2018 в 06:33

Описанный пример да, так как тут отсутствует сложность в логике получения, но я начал с того что иногда просто необходимо задействовать процедурный язык, если запрос очень сложный не всегда все можно получить за счет sql, а если и получиться то запрос будет ужасным и начнет тормозить, именно для этого и нужны такие конструкции как я описал. Там где запрос не очень сложный конечно нужно использовать sql без использования pl/sql кода

Посчитать среднюю зп пожалуйста я не написал вызов этой функции
select * from table (hr.test.get_employees_dep(v_department_id)) emps
основной смысл в том что к результату можно обратиться как к реляционной таблице, табличные функции для этого и созданы.

viktprog 5 янв 2018 в 07:44

Если у вас 1000 подразделений вы будете 1000 запросов выполнять? Думаю, будет что-то такое все-таки:

select dep.department_id, avg(emp.salary_recom) as avg_salary from hr.departments dep cross join table (hr.test.get_employees_dep(dep.department_id)) emp group by dep.department_id

И как вы считаете, производительность тут не упадет?

Oracle может очень сложные запросы обрабатывать, если их правильно написать. Хотя, тут от случая к случаю, конечно.

all-servs 5 янв 2018 в 08:01

Эта функция рассчитана на получение списка по одному отделу, джонить ее 1000 раз конечно не нужно. Если вам нужно получать информацию по всем отделам, стоит просто доработать функцию, например условиях выборки поменять:

where
      dep.department_id = p_department_id

на

where
      dep.department_id = p_department_id  or p_department_id   is null

а если нужно получать информацию не по всем отделам а по нескольким, то строку со списком ид отделов через запятую передавать и брать через like:

where
      '%p_str%' like ',' || dep.department_id || ','

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

viktprog 5 янв 2018 в 08:31

Согласен, что не всегда получается все написать на чистом SQL. Хотя, в общем случае, SQL всегда должен быть быстрее динамики.

По поводу доработки функции — так вы ее еще бесконечно будете дорабатывать, а со вью такого не было бы. И не используйте like как вы предлагаете, вы так потеряете индекс на department_id и все станет медленным (а еще само условие там некорректное). Лучше разбить строку и делать непосредственный join уже по pk.

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

all-servs 5 янв 2018 в 09:03

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

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

А дописывать всегда все приходиться, требования растут. У нас была сначала небольшая view, аналитик все дописывал требования, в итоге все это в 1000 строк когда уложилось, когда в функцию перенесли, на view стало дорабатывать ее просто не возможно и огромное кол-во оператора or в запросе делало свое дело в плане торможения.

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

xtender 7 янв 2018 в 23:43

У автора просто пример надуманный… Конечно, практически всегда лучше следовать мантре от Тома Кайта:

I have a pretty simple mantra when it comes to developing database software, and I have written this many times over the years:

You should do it in a single SQL statement if at all possible.

If you cannot do it in a single SQL statement, do it in PL/SQL.

If you cannot do it in PL/SQL, try a Java stored procedure.

If you cannot do it in Java, do it in a C external procedure.

If you cannot do it in a C external procedure, you might want to seriously think about why it is you need to do it.

Но иногда, в крайне редких случаях, по производительности лучше использовать PL/SQL, я писал пару примеров тут: orasql.org/2014/02/28/straight-sql-vs-sql-and-plsql

И кстати, насчет обычных табличных неконвеерных функций: чуть менее месяца назад, я как раз разбирался с тем, что неконвеерные функции ужасно медленно возвращают результаты в SQL, причем настолько, что даже если у вас уже есть такая функция, то будет быстрее, если просто напишите конвеерную функцию-обертку, в которой будете получать весь результат неконвеерной и возвращать через пайплайн: orasql.org/2017/12/13/collection-iterator-pickler-fetch-pipelined-vs-simple-table-functions

tom_ozi 5 янв 2018 в 08:34

Шикарно! Очень познавательная статья.
Мы использовали подход посложнее:

CREATE OR REPLACE TYPE "TYPE_ROW" AS OBJECT (COL1 VARCHAR2(10),COL2 VARCHAR2(256));
CREATE OR REPLACE TYPE "TYPE_TAB"  IS TABLE OF TYPE_ROW;
--Заполняем TYPE_ROW в SAME_PKG.SAME_FN;
SELECT * FROM TABLE(CAST(SAME_PKG.SAME_FN(:PARAM) AS N_TABLE)) ;

Кстати в Oracle 9.2i всё же нужно писать RETURN;

Зарегистрируйтесь на Хабре, чтобы оставить комментарий