Динамическая привязка для динамического запроса на собственном динамическом SQL

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

Вот пример с тремя запросами (я знаю, что это можно решить с помощью одного запроса, я стараюсь не усложнять):

sql1 varchar2(500) := 'select empno from emp where deptno = :1';
sql2 varchar2(500) := 'select empno from emp where sal > :2 and hiredate >=:3'; 
sql3 varchar2(500) := 'select empno from emp where sal <= :2 and hiredate =:3'
realStatement varchar(1500) := sql1 || ' insersect ' || sql2 || ' union ' sql3;

Теперь, учитывая, что количество подоператоров неизвестно во время выполнения, но известны значения всех переменных привязки (т. е. deptno, sal и Hiredate всегда будут: 1, : 2 и : 3 соответственно). Я не могу использовать форму 'EXECUTE IMMEDIATE realStatement USING', потому что ее привязки являются позиционными, и для этого примера я должен передать параметры sal и Hiredate дважды, что приведет к утверждению:

EXECUTE IMMEDIATE realStatement USING l_deptno,l_sal,l_hiredate,l_sal,l_hiredate;

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

Я знаю, что я мог бы использовать пакет DBMS_SQL с функцией bind(), но производительность в 1,5-3 раза хуже, чем с собственной динамикой (из документации оракула), и в этом случае производительность актуальна.

Итак, что я на самом деле делаю, так это заменяю все вхождения ':1' на l_deptno, все вхождения ':2' на l_sal и все вхождения ':3' на 'to_date(''' || l_hiredate || '' ',''ДД/ММ/ГГГГ'')' в строке realStatement перед выполнением следующим образом:

realStatement := replace(realStatement,':1',l_deptno);
realStatement := replace(realStatement,':2',l_sal);
realStatement := replace(realStatement,':3','to_date(''' || l_hiredate || ''',''DD/MM/YYYY'')');
EXECUTE IMMEDIATE realStatement;

Но я не уверен, что это лучшее решение, вопросы:

  1. Есть ли способ повысить производительность или динамически передавать привязки с помощью собственного динамического SQL?

  2. приведет ли использование пакета DBSM_SQL к повышению производительности по сравнению с выбранным решением?


performance oracle plsql dynamic-binding
person Typo    schedule 24.04.2015    source источник
comment
Удовлетворит ли sql1 varchar2(500) := 'select empno from emp where deptno = ' || :1 вашу потребность?   -  person Politank-Z    schedule 24.04.2015
comment
@Politank-Z Я забыл упомянуть, что строка запроса восстанавливается из таблицы, поэтому ссылка уже есть, плюс она не обязательно всегда будет в конце запроса.   -  person Typo    schedule 24.04.2015
comment
Откуда вы узнали о снижении производительности функции bind()?   -  person Wernfried Domscheit    schedule 24.04.2015
comment
@Wernfried, вот версия документа 9i, я не смог найти обновленную docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg09dyn.htm   -  person Typo    schedule 25.04.2015

Ответы (3)


arrow_upward
1
arrow_downward

перепишите свои утверждения:

sql1 varchar2(500) := 'select empno from emp where deptno = :1';
sql2 varchar2(500) := 'select empno from emp where sal > :2 and hiredate >=:3'; 
sql3 varchar2(500) := 'select empno from emp where sal <= :2 and hiredate =:3'
realStatement varchar(1500) := sql1 || ' insersect ' || sql2 || ' union ' sql3;

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

sql0 varchar2(500) := 'WITH par AS (SELECT :1 AS P1, :2 AS P2, :3 AS P3 FROM dual)';
sql1 varchar2(500) := '(select empno from emp join par where deptno = par.p1)';
sql2 varchar2(500) := '(select empno from emp join par where sal > par.p2 and hiredate >=par.p3)'; 
sql3 varchar2(500) := '(select empno from emp join par where sal <= par.p2 and hiredate = par.p3)';
realStatement varchar(2000) := sql0 || ', sql1 as ' || sql1 || ', sql2 as ' || sql2 || ', sql3 as ' || sql3 || ' select * from sql1 intersect select * from sql2 union select * from sql3';

или (при отсутствии повторного использования подзапросов):

sql0 varchar2(500) := 'WITH par AS (SELECT :1 AS P1, :2 AS P2, :3 AS P3 FROM dual)';
sql1 varchar2(500) := 'select empno from emp join par where deptno = par.p1';
sql2 varchar2(500) := 'select empno from emp join par where sal > par.p2 and hiredate >=par.p3'; 
sql3 varchar2(500) := 'select empno from emp join par where sal <= par.p2 and hiredate = par.p3';
realStatement varchar(2000) := sql0 || ' ' || sql1 || ' intersect ' || sql2 || ' union ' || sql3;

затем выполните его, используя 3 переменные связывания: EXECUTE IMMEDIATE realStatement USING l_deptno,l_sal,l_hiredate

person Pavel Gatnar    schedule 28.04.2015
comment
Здравствуйте Павел, спасибо что ответили. Я попробую и вернусь к вам, хотя выглядит многообещающе - person Typo; 28.04.2015
comment
Привет, Павел, я попробовал ваше предложение и частично сработал, но мне пришлось изменить то, как значения из предложения with ссылаются в подзапросах. Ссылка par.p1 не будет разрешена, на нее нужно ссылаться как на select p1 из пар. Что (из того, что я читал) так или иначе работает с предложением with, если вы исправите это в своем ответе, я приму это как правильное. - person Typo; 29.04.2015
comment
@ Опечатка, у меня не было времени попробовать, я добавил недостающие join par в каждый подзапрос (вы также можете заменить join par where на join par on, я не хотел слишком сильно менять операторы) - person Pavel Gatnar; 29.04.2015

arrow_upward
0
arrow_downward

Как вы уже упоминали, документация Oracle (http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm#BJEBACEH) говорит:

Программы, использующие собственный динамический SQL, работают намного быстрее, чем программы, использующие пакет DBMS_SQL. Как правило, собственные операторы динамического SQL работают в 1,5–3 раза лучше, чем эквивалентные вызовы DBMS_SQL.

Но с другой стороны - вы можете увидеть некоторое сравнение здесь: http://www.toadworld.com/products/toad-for-oracle/w/toad_for_oracle_wiki/231.dbms-sql-vs-execute-immediate.aspx:

Есть много преимуществ использования DBMS_SQL по сравнению с EXECUTE IMMEDIATE:

  • Легче писать и печатать (во всяком случае, для меня - я не могу написать НЕМЕДЛЕННО НЕМЕДЛЕННО НЕМЕДЛЕННО)

  • Меньше блокировок с DBMS_SQL.

  • Меньше синтаксических анализов с DBMS_SQL.

  • Лучшее масштабирование вашего приложения с помощью DBMS_SQL из-за вышеизложенного.

Недостатки:

  • Больше печатать в целом с DBMS_SQL.

Я хотел бы сказать - если у вас много таких вызовов, то привязка переменных и уменьшение количества синтаксических анализов дадут значительный эффект. Но если у вас не так много вызовов и один запрос возвращает много строк, тогда EXECUTE IMMEDIATE без переменных связывания (ваше решение с заменой) может быть быстрее.

В любом случае - если важна производительность - сделайте оба решения (с EXECUTE IMMEDIATE и DBMS_SQL) и сравните результаты.

person Janis Baiza    schedule 24.04.2015

arrow_upward
0
arrow_downward

Когда я был на тренинге несколько лет назад, разница в производительности была темой (это был Oracle 9i). Нам удалось воспроизвести разницу. Однако, когда я выполняю те же тесты сегодня (с Oracle 11), я больше не получаю никакой разницы в производительности. Используйте более удобный для вас способ.

person Wernfried Domscheit    schedule 25.04.2015
comment
какие тесты вы проводили? - person Typo; 25.04.2015
comment
Запускаю тот же запрос, что и с EXECUTE IMMEDIATE и с DBMS_SQL несколько раз -› никакой разницы. 10 лет назад с Oracle 9i разница была значительной. - person Wernfried Domscheit; 26.04.2015