Наиболее распространенной причиной взрывов является неправильно сформированное предложение START WITH/CONNECT BY. Вот выдержка из статьи, которую я написал об этом. Предполагается, что мы начинаем со стандартных 14 строк в SCOTT.EMP.
Фильтрация против навигации
Определение того, как перемещаться по иерархии, сильно отличается от фильтрации, которая обеспечивается предикатами предложения WHERE в SQL. Чтобы продемонстрировать это, 14 строк в таблице EMP были продублированы, чтобы получить набор строк для нескольких географических регионов, как показано в листинге 7. Теперь в таблице 42 строки, стандартный набор из 14 строк, реплицированных дважды для EAST. СЕВЕРНЫЙ и ЮЖНЫЙ регионы.
Listing 7
SQL> alter table emp add region varchar2(10);
Table altered.
SQL> update emp set region = 'NORTH';
14 rows updated.
SQL>
SQL> insert into emp
2 select
3 empno, ename, job, mgr, hiredate, sal, comm, deptno,
4 decode(r,1,'SOUTH',2,'EAST') region
5 from emp,
6 ( select rownum r from dual connect by level <= 2 )
7 where region = 'NORTH';
28 rows created.
SQL>
SQL> select region, count(*)
2 from emp
3 group by region;
REGION COUNT(*)
---------- ----------
NORTH 14
SOUTH 14
EAST 14
Предположим, требуется получить иерархию для 14 строк только для СЕВЕРНОГО региона. Казалось бы интуитивно понятным использовать тот же запрос, что и в листинге 2, но с дополнительным предикатом, ограничивающим REGION значением NORTH. В листинге 8 показан результат.
Listing 8
SQL> select empno, rpad(' ',level*3)||ename ename, mgr
2 from emp
3 where region = 'NORTH'
4 connect by prior empno = mgr
5 start with mgr is null;
EMPNO ENAME MGR
---------- ---------------------------------------- ----------
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
...
...
7782 CLARK 7839
7934 MILLER 7782
7934 MILLER 7782
7934 MILLER 7782
136 rows selected.
Обратите внимание, что возвращенные выходные данные составляют не 14 строк, а 136 строк, даже несмотря на то, что был применен предикат для ограничения результатов только регионом СЕВЕР.
Чтобы понять причину этой проблемы, рассмотрим самую первую обрабатываемую строку региона NORTH. Предложение START WITH имеет значение MANAGER IS NULL, поэтому будут размещены три строки King, по одной для каждого региона, а затем начнется операция CONNECT BY. Предложение CONNECT BY равно PRIOR EMPNO = MANAGER, поэтому для каждой записи KING будут подключены три связанных сотрудника, по одному на каждый регион, что быстро увеличивает количество строк, возвращаемых по мере прохождения запроса по всей иерархии. Предикат предложения WHERE применяется только после установления отношения иерархии, поэтому даже несмотря на то, что набор результатов содержит только строки для REGION= NORTH, выполняется большой объем избыточной работы по ошибочным соединениям с другими регионами, поскольку синтаксис CONNECT BY и START WITH синтаксис не
• ограничиться началом только с СЕВЕРНОГО региона, и
• не удостоверился, что при подключении к следующей записи в иерархии,
что строка сопоставляется из того же региона с регионом следующей строки.
В листинге 9 показан исправленный синтаксис этого запроса. Он начинается с менеджеров, которые являются нулевыми, но также только в регионе СЕВЕР. Затем он гарантирует, что при подключении одного менеджера к следующему сотруднику определение подключения также включает общий регион. Это синоним того, как обрабатывается синтаксис соединения ANSI, а именно, существует четкое различие между условием иерархического соединения и предикатами предложения WHERE, которые будут применяться впоследствии в качестве фильтров.
Это порождает общую правильную критику иерархических запросов за то, что они выполняются слишком медленно или потребляют слишком много ресурсов. Обычно причина в том, что информация CONNECT BY неполная, что приводит к обработке гораздо большего количества строк, чем необходимо.
Listing 9
SQL> select empno, rpad(' ',level*3)||ename ename, mgr
2 from emp
3 connect by prior empno = mgr
4 and region = 'NORTH'
5 start with mgr is null
6 and region = 'NORTH';
EMPNO ENAME MGR
---------- ---------------------------------------- ----------
7839 KING
7566 JONES 7839
7788 SCOTT 7566
7876 ADAMS 7788
7902 FORD 7566
7369 SMITH 7902
7698 BLAKE 7839
7499 ALLEN 7698
7521 WARD 7698
7654 MARTIN 7698
7844 TURNER 7698
7900 JAMES 7698
7782 CLARK 7839
7934 MILLER 7782
14 rows selected.
Вы можете спросить: «Какая же тогда польза от предложения WHERE в иерархическом запросе?» Наличие предложения WHERE по-прежнему допустимо не для определения иерархии, а для определения фильтрации данных. Например, после создания экземпляра иерархии СЕВЕРНЫЙ регион требование может заключаться только в включенных сотрудниках выше определенной ЗАРПЛАТЫ.
person
Connor McDonald
schedule
02.02.2018
