Oracle hijerarhije i CONNECT BY

pozdrav svima,

zanima me misljenje nekog iskusnijeg.

Trebalo je da napisem proceduru koja prolazi kroz hijerarhiju organizacija (standardna flat hijerarhija) i vraca sve podorganizacije za dati ID parenta. Ja sam to uradio koristeci CONNECT BY. Naravno to radi kako treba, medjutim DBA & Co. ne dozvoljavaju CONNECT BY (kazu da ima lose performanse). I sada traze od mene da napisem istu proceduru bez CONNECT BY.

Zanima me nekoliko stvari:

1. da li stvarno CONNECT BY ima lose performanse (npr. losije od implementacije koristenjem rekurzivne procedure)?
2. da li postoji neko bolje rjesenje od rekurzivnih procedura (izmjena modela baze ne dolazi u obzir)? Ja moram da napisem alternativno rjesenje.. problem je sto rekurzivna procedura moze da udje u beskonacnu rekurziju ako su podaci losi u tabeli. Trenutno trazim sta bi to bilo najbolje da iskoristim umjesto CONNECT BY. Opet napominjem u pitanju je standardna flat hijerarhija (kao u EMP tabeli : SCOTT schema)

hvala unaprijed

Reci shefu da otpusti tog DBA, ako tvrdi tako.

CONNECT BY i postoji zbog tih hijerarhijskih upita i nijedna rekurzivna procedura ne moze imati bolje performanse od ovog internog operatora...

Potpis

Blog - baze podataka
---------------------
Oracle OCP DBA (9i & 10g)
Oracle Database: SQL Certified Expert
Oracle OCP Developer
Certified MySQL DBA

I ja sam mislio da je logicnije da CONNECT BY ima bolje performanse od bilo kog rekurzivnog algoritma ali sam morao da se raspitam malo cisto da budem siguran. Pretpostavio sam da taj DBA zna sta radi i sta prica...


hvala ti Dejane na brzom odgovoru




jos nesto me zanima:

za prolazak kroz hijerarhiju proizvoljne dubine postoje sledeci nacini:

1. CONNECT BY
2. rekurzija
3. nesto da se petlja sa pomocnim tabelama pa onda iterativno da se pretrazuje (naravno ovo kod mene ne dolazi u obzir jer baza ne moze da se mijenja)


da li sam nesto izostavio???



pozdrav

Pozdrav!

Khm... pitanja su u najmanju ruku diskutabilna.
1.
Da utice na performanse, utice... i ne utice... kako se posmatra...
Pazi, ako pogledas explain plan, videces sta koliko kosta. Oracle sa CONNECT BY koristi rekurzije-nekoliko prolaza- da bi "razmotao" ko je kome sta.
Prevedeno na "naski", ukoliko firma ima gomilu ljudi, i pri tom, taj upit se "cima" svakih par sekundi (npr, generises tree zaposlenih) to ce itekako uticati na performanse, a ako se to korsiti jednom dnevno za poneki izvestaj ... i nije neko veliko opterecenje.
A ako su rekli da nemozes to da koristis, onda verovatno postoji dobar razlog za to a ti bas i neces mnogo dobiti (ako vec imate DBA kome je samo to posao) ako i dokazes da to nece bitnije uticati na performanse...

2. Pa.... ne postoji... ali mozda i postoji!
Video sam resenje problema "HANOJSKE KULE" uradjeno bez koriscenja rekurzije!!! Sto svakako ne znaci da svaki problem moze da se resi bez rekurzije!
Iskren da budem, mislim da ne moze (pogotovu ako je hijerarhija duboka) ali nisam siguran jer nisam nikada razmisljao da radim takav sport bez rekurzije...
Jedino sto mi pada na pamet je da, ako mozes, unesrecis programera, tj. ako je u pitanju aplikacija a ne PLSQL paket/procedura, posalji REFCursor sacinjen od obicnog selecta iz tabele, pa neka onaj ko razvija aplikaciju uradi rekurziju, ili sta vec hoce...

Ako podaci nisu konzistentni to nece raditi ni sa rekurzijom ni bez nje... Milim, ako postoji radnik koji ima shefa koji mu je podredjen, niti jedan "normalan" metod to ne moze da detektuje vec moras da se dovijas na razne nacine...

A ako si ti srecnik koji mora da uradi aplikaciju... pa sta da ti kazem... ono sto mi pada na pamet je Function OverRide i nezaobilazno pitanje dragom DBA koji je to zabranio: Kako da uradim bez CONNECT?
Mislim, njegov posao je da obezbedi neke mehanizme...

Ili jos bolje, zahtevaj od DBa (Data manager-a) konzistentnost podataka iz te tabele i koristi rekurziju!

PS> Svako drugo resenje bih i ja voleo da cujem ... pa ako nadjes nesto... javni ovde

Potpis

http://noctua4u.blogspot.com
http://moourl.com/shfb0
..:: Aquila non captat muscas ::..

hvala na opsirnom odgovoru. Vec sam poslao zahtjev DBA da mi kaze kako mogu da uradim bez CONNECT BY. Njegov prvobitni odgovor je bio



Pretpostavljam da momak zna sta radi jer sumnjam da su ovi zaposlili bilo koga na tu poziciju!

Znam da CONNECT BY jeste zahtjevan.. samo je pitanje bilo da li je manje ili vise zahtjevan od analognog rjesenja uz pomoc rekurzije. Nasao sam rjesenje za hijerarhije unaprijed odredjene dubine. Problem je sto ja ne znam dubinu do koje moram da odem tako da mi rekurzija ne gine

Sto se tice integriteta podataka. Pa ovdje sam mislio da koristim NOCYCLE opciju ali nazalost ne koristimo 10g vec 9i (a 9i nema NOCYCLE). CONNECT BY (kao sto vec vjerovatno znate) puca ako naidje na petlju a nije zadata i NOCYCLE opcija. Analogno tom pucanju bi bila i beskonacna rekurzija .. u svakom slucaju upit ne bi vratio ono sta treba da vrati! Posto nemam punu kontrolu nad bazom (a ko pa ima taj luksuz) ne mogu da se uvijek pouzdam da su podaci konzistentni!

ako saznam nesto pametno javicu

pozdrav

Reci tom DBA nek procita malo noviju literaturu, npr. od Toma Kytea, pa nece vise lupati takvo nesto...

Susretao sam se ja sa slicnim konzervativnim DB Administratorima, koji su odbijali prihvatiti neke nove opcije, jer su se plasili zbog sigurnosti, slabijih performansi i sl. Kad im polozis argumente na stol, onda ce te drugacije gledati. :)

Sto se tice CONNECT BY u verziji 9i, ukoliko dodje do beskonacne rekurzije, dobices gresku:

ORA-01436: CONNECT BY loop in user data

Tu gresku mozes uhvatiti u EXCEPTION dijelu i raditi onda sta zelis s njom... Ako dobijes takvu gresku, onda provjeri da li su podaci konzistentni i mogu li se ikako popraviti...

Jos jednom - CONNECT BY postoji bas zbog hijerarhijskih upita u Oracle bazi i NIJE potrebno izmisljati toplu vodu nekim "user defined" metodama...

Potpis

Blog - baze podataka
---------------------
Oracle OCP DBA (9i & 10g)
Oracle Database: SQL Certified Expert
Oracle OCP Developer
Certified MySQL DBA

Pozdrav!

Opet khm...
Po mom nekom iskustvu, rekurzija (pa cak i vise medjusobnih rekurzija) moze zauzimati mnogo manje systemskog (cpu, mem...) vremena nego neke stvari koje se genericki koriste. ALI..... kazem ALI...

Sve to zavisi od iskustva onog ko pise te rekurzije (tebe)!

Takva procedura(e), ako su uopste moguce, bi morale biti perfektno optimizovane (indexi, upiti, cursori... sta god) + jos ponesto u vezi izvrsenja + ... sto opet nije posao (a ni mogucnost) onog ko to "pise" - tebe!

Po mom nekom misljenju takvu proceduru bi morao da napise (posalje ti kod, naknadno prepravi, savetuje...) sam DBA jer on je taj ko optimizuje bazu i brine o performansama i on je taj ko moze da menja execution plan...
Mislim, glupo je ocekivati od programera da uradi "the best" kod kada nema mogucnosti da ispobava ili menja neke parametre sistema... Drugo, programer (ti) nije u stanju da "globalno" nadgleda performanse (ono sto ce se tebi ciniti najbrze i nabolje uopste na globalnom nivou ne mora da bude tako)

Razmisljao sam nesto na temu tabela...
Ideja nije losa... toliko...
Sama DB ima temp tabele koje cuvaju podatke dokle traje sesija... Pa ako si u mogucnosti mozda se moze i izmozgati nesto sa njima ... Sto opet ne zavisi od tebe vec od DBA da li ce ti dozvoliti da ih kreiras/koristis...

Da skratimo pricu: dobar potez ti je sto trazis od njega pomoc!

PS.
Da se razumemo: ne branim doticnog DBA, nego, jednostavno, uzimam u obzir sve mogucnosti. Moguce da cak ni programer (ti) ne znas sve razloge zasto ti je to receno... moguce da DBA nema pojma a moguce da ima stvarno neki jak razlog sto je to napisao (dobar razlog: javlja se ORA0600 pri svakom connect by; los: opravdava svoje postojanje tako sto odaje utisak da non-stop vrsi monitoring...)

Potpis

http://noctua4u.blogspot.com
http://moourl.com/shfb0
..:: Aquila non captat muscas ::..

Pridruzujem se misljenju da je zabrana korisnjenja connect by totalna glupost.
Mislim da ne postoji nikakva, ni teorijska sansa, da uz pomoc sopstvenog
algoritma, i sto je najgore , algoritma koji koristi rekurziju, trosis
manje resursa nego sa connect by.

Naravno, ako je upit jednostavan, sve je ok. Slozenije upite
koji koriste connect by je jako tesko optimizovati, ali to ne bi bio tvoj posao
nego od tog DBA.

Ako je u pitanju trosenje resursa, to je argument, dakle SIGURNO ces trositi vise resursa.

Nemoguce je menjati model podataka, to je mozda ok, mada.....
Da li menjanje modela modela podrazumeva dodavanje nove tabele?
Da li radis nad nekom 3rd party aplikacijom, pa imate problem sa podrskom ako se doda nova tabela?
Ako je to u pitanju, trebao bi DBA da vam napravi novu semu, za ovakve slucajeve.
Jedna tabela u kojoj bi bila denoramlizovana hijerarhija bi te spasila...

Pozdrav,
Dusan

Zdravo,



Ovo interesuje sve na ovom forumu, pa te molim da postujes odgovor kada ga dobijes.



Necu nikoga da vredjam i potcenjujem, ali mislim da taj momak ne zna bas tacno sta radi. CONNECT BY moze da bude katastrofalan sto se performansi tice ako se ne koristi kako treba, ali ako se koristi pametno i jos ako se koriste indeksi po kolonama (ovde ima mala fora za upotrebu indeksa) performanse ce biti odlicne.

Pitaj tvog DBA da li on misli da kada bilo ko napise neki algoritam za ovako nesto, da li on misli da ce to biti kvalitetnije napisano od algoritma onih koji su napravili Oracle i koji ga poznaju do najmanjih detalja...



Vec sam rekao, ako se koristi kako treba sigurno ce da radi brze od bilo kog alternativnog resenja.

Sada sam malo u guzvi, ali ako ti je potrebno, mogu da ti dam primer upotrebe CONNECT BY sa indeksima, to radi perfektno i ultrabrzo. Ja nisam video neko bolje (i brze) resenje....

Pozdrav