Оптимизация 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

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

запрос A:

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

запрос B:

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?

Ответ 1

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

Query B явно уменьшает набор данных, которые будут обрабатываться только тем сотрудникам в Sales, которые Oracle может сделать до выполнения CONNECT BY.

Ответ 2

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

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.

Ответ 3

Это аналогичный запрос, длинный рассказ короче, он работал быстрее, используя вложенный 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 для фильтрации.

Ответ 4

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

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