Оптимизация Oracle CONNECT BY при использовании с предложением WHERE

Предложение Oracle START WITH ... CONNECT BY применяется перед применением условия WHERE в том же запросе. Таким образом, ограничения WHERE не помогут оптимизировать CONNECT BY.

Например, следующий запрос, скорее всего, выполнит полное сканирование таблицы (игнорируя селективность по dept_id):

SELECT * FROM employees 
WHERE dept_id = 'SALE'
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id

Я пытался улучшить производительность двумя способами:

запрос А:

SELECT * FROM employees 
START WITH manager_id is null AND dept_id = 'SALE'
CONNECT BY PRIOR employee_id = manager_id

запрос Б:

SELECT * FROM (
               SELECT * FROM employees 
                WHERE dept_id = 'SALE'
              )
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id

Хотя оба запроса работали намного лучше исходных, в Oracle 10g Release 2 запрос B работал намного лучше, чем A.

Была ли у вас аналогичная оптимизация производительности в отношении предложений CONNECT BY и WHERE? Как бы вы объяснили, что запрос B работает намного лучше, чем запрос A?


sql performance oracle query-optimization select
person topchef    schedule 04.07.2009    source источник
comment
Покажите планы объяснения, может быть, даже файлы трассировки и какие индексы у вас есть? Во всяком случае, я думаю, вы сами уже объяснили, почему B быстрее, B быстрее, потому что где оценивается в первую очередь.   -  person tuinstoel    schedule 04.07.2009
comment
Значения null не помещаются в индексы, поэтому, возможно, вы можете заменить значение null на 0 или -1 или что-то подобное, чтобы индекс работал.   -  person Martlark    schedule 04.07.2009
comment
@tuinstoel - да, похоже, что уменьшение набора данных, с которым работает CONNECT BY, является лучшей оптимизацией, чем уменьшение исходного набора START WITH. Я больше думал о различных подходах к оптимизации: я пытался играть с ограничениями внутри CONNECT BY, но это не увенчалось успехом.   -  person topchef    schedule 04.07.2009
comment
вы обновили статистику? использовать гистограмму статистики   -  person J-16 SDiZ    schedule 18.10.2010

Ответы (4)


arrow_upward
16
arrow_downward

Запрос A говорит: начните с менеджеров отдела продаж, а затем выберите всех их сотрудников. Oracle не «знает», что все сотрудники, возвращенные запросом, будут в отделе продаж, поэтому он не может использовать эту информацию для сокращения набора данных для работы перед выполнением CONNECT. К.

Запрос B явно сокращает набор обрабатываемых данных только до тех сотрудников отдела продаж, что Oracle затем может сделать перед выполнением CONNECT BY.

person Tony Andrews    schedule 04.07.2009
comment
обратите внимание, что этот ответ только объясняет, почему B лучше, чем A, но решение C, предоставленное @jluu, помогло мне намного лучше - person Christophe Blin; 05.01.2017
comment
Ответ для A быстрее, чем оригинал, заключается в том, что Oracle оценивает предложения в следующем порядке: FROM (включая JOIN), CONNECT BY (включая START WITH), WHERE, GROUP BY, HAVING, SELECT, ORDER BY. - person Jean-Philippe Martin; 21.06.2017

arrow_upward
7
arrow_downward

Это должно дать максимальную производительность:

CREATE INDEX i_employees_employee_manager_dept ON employees (employee_id,manager_id,dept_id);
CREATE INDEX i_employees_manager_employee_dept ON employees (manager_id,employee_id,dept_id);

SELECT * FROM employees  
START WITH manager_id is null AND dept_id = 'SALE' 
CONNECT BY PRIOR employee_id = manager_id AND dept_id = 'SALE'

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

person jluu    schedule 27.12.2011
comment
Можем ли мы переписать его как from employees join dual on dept_id = 'SALE' , чтобы не повторять этот фильтр дважды? - person Johan Boulé; 14.07.2021

arrow_upward
2
arrow_downward

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

'SELECT level, XMLElement("elemento", XMLAttributes(codigo_funcion as "Codigo",
                                                    nombre_funcion as "Nombre",
                                                    objetivos as "Objetivos",
                                                     descripcion as "Descripción",
                                                    ''rightHanging'' as "layout"))
   FROM (
           SELECT * FROM dithe_codigo_funcion 
           WHERE nodo_raiz = ''PEP''
    )      
   START WITH codigo_funcion = ''PEP'' 
   CONNECT BY PRIOR codigo_funcion = nivel_anterior';

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

person Hernando    schedule 03.12.2013

arrow_upward
1
arrow_downward

Какие индексы у сотрудников? Вам лучше иметь индекс на employeeid. И у вас, вероятно, есть один из-за объявления employeeid в качестве первичного ключа.

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

person Walter Mitty    schedule 04.07.2009
comment
Уолтер, все индексы есть. Спасибо. - person topchef; 04.07.2009