Оптимизиране на Oracle CONNECT BY, когато се използва с клауза WHERE

Клаузата START WITH ... CONNECT BY на Oracle се прилага преди прилагането на 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

Опитах се да подобря производителността по 2 начина:

запитване А:

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?


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

Заявка А казва, че започнете с мениджърите в отдела по продажбите и след това вземете всичките им служители. 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 (които включват JOINs), 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