TOAD je po meni najbolji alat za rad sa bazama podataka, a koristim ga za rad sa Oracle i MySQL bazama.
Pored komercijalnih verzija, možete skinuti i Freeware verzije, koje važe 60 dana. Nakon isteka probnog roka, samo deinstalirate taj Freeware i ponovo instalirate.
Freeware verzije možete naći na ovoj adresi: http://www.toadworld.com/Downloads/Freeware/tabid/80/Default.aspx
Aaaaaargh… $§&%/*#
Koliko jedna “sitnica” može koštati vremena i živaca…
Kolega je instalirao jednu Oracle bazu za testiranje, u koju smo kasnije importovali našu production bazu. Međutim, ne lezi vraže… Nakon nekoliko sati rada, određene aplikacije su počele da javljaju grešku, a pojedini PL/SQL paketi se nisu mogli kompilirati.
Problem je pojavio prilikom korištenja objekata (tabele i views) iz druge baze preko database linka. Ukoliko u svom kôdu imate nešto slično kao npr.:
SELECT id, value
INTO lvID, lvValue
FROM remote_table@mssqlDB;
onda taj upit može javiti grešku:
ORA-00942 Table or View does not exist
ali pravi uzrok greške leži u nedostupnosti database linka, odnosno u grešci:
ORA-02019: connection description for remote database not found
E sad je trebalo pronaći uzrok te greške – kolega je odustao nakon nekoliko sati, pa sam ja preuzeo i takođe izgubio skoro 3 sata dok nisam pronašao u čemu je bio problem.
Provjerio sam 10-ak puta tnsnames.ora, dotične PL/SQL pakete nekoliko puta liniju po liniju, ali bez uspjeha…
Googleao sam, tražio na MetaLinku, ali bez uspjeha…
Rješenje sam otkrio sasvim slučajno – usporedio sam init-parametre u toj novoj testnoj bazi i u production bazi, pa mi je za oko zapao parametar “open_links“. U testnoj bazi je vrijednost iznosila 4, a u production bazi 8.
U dokumentaciji piše:
“OPEN_LINKS specifies the maximum number of concurrent open connections to remote databases in one session.”
Standardno je vrijednost podešena na 4, a mi smo u nekoliko PL/SQL paketa koristili i po 7-8 database linkova, pa sam ja samo povećao tu vrijednost na 8, restartovao bazu i sve je funkcionisalo kako treba.
alter system set open_links=8 scope=spfile sid='*';
Nadam se da će ovo nekom pomoći i uštediti vrijeme i živce…
Napokon sam ugrabio vremena da napišem neku novu recenziju, a ovaj put sam odabrao još jednu odličnu knjigu o Oracle bazi – “Expert Oracle Database Architecture“, čiji autor je fenomenalni i svima poznat – Tom Kyte.
Knjiga je namijenjena svima – kako programerima, tako i administratorima, bez obzira da li se radi o početnicima ili iskusnim znalcima. Tom je u svom već poznatom stilu “proof-by-example” detaljno objasnio mnoge stvari potrebne za shvatanje funkcionalnosti i unutrašnje strukture Oracle baze, a mnogi savjeti spadaju u “best practice” primjere.
Početnici će moći upoznati internu strukturu i komponente Oracle baze, zatim koji procesi rade u pozadini i za šta je koji proces zadužen. Osim toga, mogu saznati kako funkcionišu transakcije, konkurentni višekorisnički pristup podacima, particionisanje i paralelizam. Pored poglavlja o tabelama i indeksima, tu je i poglavlje o svim tipovima podataka u Oracle bazi (npr. jeste li znali da je PLS_INTEGER efikasniji od BINARY_INTEGER?), pa koga zanima čemu služi npr. TIMESTAMP WITH TIMEZONE nek se lati knjige. 😀
Oni iskusniji će možda pronaći neku korisnu informaciju o poboljšavanju sistema (performance tuning) mijenjanjem određenih init-parametara, zatim informacije o razvijanju aplikacija u RAC okruženju i vjerovatno još mnogo korisnih stvari.
Za mene su veoma korisna bila poglavlja o tabelama i indeksima, u kojima sam saznao kako mogu koristiti IOT (Index Organized Table) i Cluster/Hash-Cluster/Hash-Cluster-Sorted tabele, a potom koji indeksi bi bili najefikasniji u određenim situacijama (npr. REVERSE index za datume ili BITMAP index za polja sa malim brojem različitih vrijednosti, npr. muško/žensko).
Knjigu najsrdačnije preporučujem i dajem joj zasluženu ocjenu 10.
“The Sun-MySQL deal stinks” – kaže John Dvorak i dodaje:”Uvjeren sam da Oracle želi kupiti MySQL sa ciljem da ga ubije, ali nije mogao sam povući okidač, pa je poslao marionetu da obavi posao.”
Ta tvrdnja je idealna koska za sve one, koji su gladni teorija zavjere. Neko će kosku zagristi, neko će ju ignorisati, a neko će samo usputno komentarisati – kao ja npr.
Ja lično ne vjerujem, da Oracle stoji iza te kupovine, ali vrijeme će pokazati kakvi konci su se vukli u vezi ove akvizicije.
S druge strane, kakvu korist ima Sun od toga? Sun ima širok spektar hardware i software proizvoda, a najpoznatiji je po OS Solarisu, SPARC serverima i programskom jeziku Javi.
Javi dakle. Hmmmm … Da pokušam analizirati to malkice…
MySQL se uglavnom asocira uz PHP. PHP je trenutno najpopularniji programski jezik za web. Web je IT-budućnost opstanka svih IT firmi. Takoreći svi sajtovi, koji su urađeni u PHP-u, koriste MySQL kao backend database engine. Sa sigurnošću se može reći da MySQL daje PHP-u ogroman boost u popularnosti i rasprostranjenosti na web tržištu. Što direktno znači manji dio kolača za Javu.
I sad tu uskače Sun kao sinonim za Java programski jezik, sa ciljem da razvoj i budućnost MySQL-a usmjeri po svojim željama, dakle ka boljoj i dubljoj simbiozi Jave i MySQL-a, a na štetu PHP-a. Na stranu to što PHP sve više liči na Javu.
Znači, Sun i Oracle bi iz ovoga trebali izaći kao dobitnici, a MySQL, PHP i naivna OpenSource zajednica kao pušioničari.
Ja se nadam da Sun neće “ubiti” MySQL i da MySQL neće nestati sa scene u idućih 2-3 godine, jer nestanak konkurencije ne donosi ništa pozitivno. Ionako imam već gorka iskustva sa Oracleom, a kako li bi tek bilo, da nedostatak konkurencije dozvoli Oracleu da se prema tržištu i svojim mušterijama odnosi monopolistički – što uključuje bahatost, paprene cijene, loš support i td.
Kao i uvijek do sada – vrijeme će pokazati šta se krije iza ove kupovine…
Kako bismo u MS SQL ispisali prvih 50 celih brojeva tako da svaki broj bude u posebnom redu?
1
2
3
…
50
Nešto ovako bi možda pomoglo:
DECLARE @i int SET @i = 0 WHILE @i<=50 BEGIN SET @I = @i + 1 SELECT @i AS i CONTINUE END
Nažalost, svaki SELECT se izvrsava za sebe:
i ----------- 1
(1 row(s) affected)
i ----------- 2
(1 row(s) affected)
i ----------- 3
Ovo je vec bolje:
DECLARE @i int SET @i = 0 WHILE @i<=50 BEGIN SET @I = @i + 1 -- SELECT @i AS i PRINT @i CONTINUE END
Međutim, s tim ne možemo ništa korisno da uradimo. Možda da rezultat cut/paste u Excel pa to da importujemo u SQL bazu, pa onda uradimo.. itd… itd…. Isuviše veliki posao za tako prostu stvar.
Na sreću, MS SQL 2005 ima Common Table Expressions. Probajte ovo:
WITH MyCTE AS ( SELECT 1 AS i UNION ALL SELECT i+1 FROM MyCte WHERE i<=50 ) SELECT * FROM MyCTE
Posto je ovo CTE, imamo posla u stvari sa temp tabelom i rezultat se moze upotrebiti u nekom SQL izrazu na način na koji smo pokazali u jednom od prethodnih napisa.
Pre nego objasnimo kako ovo radi i šta se dešava, evo još jednog prostog primera. Zadatak je ispisati sve dane u zadatom mesecu. Mogu da probam ovako:
WITH Mesec AS -- za mesec Mart 2008
(
SELECT CAST('2008/03/01' AS datetime) AS Dan
UNION ALL
SELECT Dateadd(day,1,Dan ) AS Dan
FROM Mesec
WHERE Dan <= CAST('2008/03/30' AS datetime)
)
SELECT * FROM Mesec
Možemo da uopštimo problem i da tražimo da se vrate svi datumi između dva zadata datuma (datumski opseg, ‘date range’) Ovako nekako bi moglo:
DECLARE @OdDatuma as datetime, @DoDatuma as datetime SET @OdDatuma = '2007/11/1' SET @DoDAtuma = '2007/11/30' ; WITH OpsegDatuma AS -- ako WITH nije prva linija u batchu, stavlja se tacka zarez ispred ( SELECT @OdDatuma AS Datum UNION ALL SELECT Dateadd(day,1,Datum) AS Datum FROM OpsegDatuma WHERE Dateadd(day,1,Datum) <= @DoDatuma ) SELECT * FROM OpsegDatuma
A možemo da napičemo i funkciju, OpesgDatuma, koja za zadata dva datuma vraća sve dane izmedju njih.
CREATE FUNCTION dbo.OpsegDatuma(@OdDatuma as datetime, @DoDatuma as datetime) RETURNS TABLE AS RETURN ( WITH OpsegDatuma AS ( SELECT @OdDatuma AS Datum UNION ALL SELECT Dateadd(day,1,Datum) AS Datum FROM OpsegDatuma WHERE Dateadd(day,1,Datum) <= @DoDatuma ) SELECT Datum FROM OpsegDatuma )
Sada može da se napiše i ovo:
SELECT * FROM dbo.OpsegDatuma('2007/11/1','2007/11/30')
ali i ovo:
SELECT
D.Datum
, COALESCE(COUNT(N.NarudzbaID),0) AS [Broj narudzbi na ovaj dan]
FROM dbo.OpsegDatuma('2007/11/1','2007/11/30') AS D
LEFT JOIN Narudzbe AS N
ON D.Datum = F.DatumNarudzbe
GROUP BY D.Datum
Poslednji izraz vraća SVE datume u zadatom opsegu, pa i one kad nije bilo narudžbi.
Bez funkcije i bez Common Table Expression ovakav izraz bi bio veoma zahtevan za pisanje.
Vratimo se na pocčtak, da pokušamo da objasnimo šta se dešava. Izraz
WITH MyCTE AS ( SELECT 1 AS i UNION ALL SELECT i+1 FROM MyCte WHERE i<=50 ) SELECT * FROM MyCTE
ima 3 dela, kao i svaki CTE.
WITH MyCTE AS
jeste deklaracija CTE izraza, gde je MyCTE ime koje smo dodelili.
Izraz u zagradi
( SELECT 1 AS i UNION ALL SELECT i+1 AS i FROM MyCte WHERE i<=50 )
je definicija naše temp tabele, šta će CTE da nam vrati.
Na kraju izrazom
SELECT * FROM MyCTE
pozivamo CTE. Ništa novo u odnosu na ono što smo već ranije znali.
Izraz u zagradi je ono što nas interesuje. I tu imamo 3 dela:
SELECT 1 AS i
zove se ‘anchor member’, u slobodnom prevodu ‘prvi red CTE tabele’
UNION ALL
je deo sintakse i jednostavno tako mora da se pise, tako trazi Microsoft
Treći deo je glavna stvar. Tu se dešavaju itaracije.
SELECT i+1 FROM MyCTE WHERE i<=50
Zapazite FROM MyCTE. Izraz u zagradi je dakle unija dva seta, prvi deo vraća jedan red, a drugi iskaz poziva samu CTE tabelu tabelu. Otud rekurzija. Kao u matematici, funkcija poziva samu sebe.
Šta se dešava u pozadini? Anchor member vraća tačno jedan red. U tom momentu CTE ima taj jedan red. To se unionizuje sa izrazom
SELECT i+1 AS i FROM MyCTE
što vracć još jedan red, sa uvećanim i za 1, koliko smo stavili u SELECT i+1
Sada već imamo dva reda u tabeli, vrednsti su 1,2. Ciklus se nastavlja dok važi WHERE uslov.
Posto imamo i=2, to je manje od 50, ciklus se nastavlja dok ne stignemo do 50. Ne pitajte me zašto ni kako, ali tako to radi.
Mnogo detaljnije i lepše objasnjenje imate ovde:
http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/
I zapamtite ime Nigel Rivett. Ot tog gospodina se ima mnogo sta naučiti.
Jos neke važne stvari da na pomenem. WHERE nije obavezno i u tom slucčju će, teorijski, ciklus biti beskonačan. Pošto beskonačnost postoji samo u teoriji, Microsoft je to ograničio na 100. Ako dakle odradite
WITH MyCTE AS ( SELECT 1 AS i UNION ALL SELECT i+1 FROM MyCte ) SELECT * FROM MyCTE
dobićete 101 red. Kakav bi to alat bio da se ne može primeniti na više od 100 ciklusa?
Probajmo
WITH MyCTE AS ( SELECT 1 AS i UNION ALL SELECT i+1 FROM MyCte WHERE i<=1000 ) SELECT * FROM MyCTE
i dobićemo grešku:
Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
Microsoft je mislio i na to. Postoji klauzula option (maxrecursion N) koja se dodaje na kraj svega, da bi se povecao broj iteracija, ako treba do vrednosti N.
WITH MyCTE AS ( SELECT 1 AS i UNION ALL SELECT i+1 FROM MyCte WHERE i<=1000 ) SELECT * FROM MyCTE option (maxrecursion 5000)
I ovde ima ograničenje. N u option (maxrecursion N) može da ide do 32767.
Svakako da bi i ovo u praksi bilo malo. Zasto samo 32767? Samo Bog zna, a posto Boga nema, onda ne zna niko (Ovu rečenicu izgovorio je Zoran Radmilovic kao Radovan III, ja nemam ništa stim i ne odgovaram na moguće komentare)
Postoji nedokumentovana opcija (ili bar slabo dokumentovana) (maxrecursion 0)
WITH MyCTE AS ( SELECT 1 AS i UNION ALL SELECT i+1 FROM MyCte WHERE i<=50000 ) SELECT * FROM MyCTE option (maxrecursion 0)
Ako ste probali da odradite poslednjih nekoliko primera, uočite kakvom brzinom se proizvede recimo 50,000 brojeva.
Postoje egzotični nacčni da se isti rezultat dobije jos brzž, ali meni je i manje od 1 sec za 50,000 redova dovoljno brzo.
Svi ostali načini kojih se obični smrtnici mogu setiti su za dva reda velicine sporiji od WITH sa rekurzijom.
Ako želite nešto brze od ovoga, pogledajte knjigu ‘Inside SQL Server 2005: T-SQL Querying’, autori Itzik Ben-Gan, Lubor Kollar i Dejan Sarka. Treći autor je očigledno naš covek, a i Lubor nije daleko, tek da se zna 🙂
Igrajte se neko vreme sa slicnim primerima, i svakako posetite sajt na kome piše Nigel Rivett. Kad svarimo ove ‘jednostavne’ upoterbe CTE izraza, pokušaću da se pozabavim hijerarhijama i mrežama.
Ono što smo videli do sada čini mi se definitivno ubija potrebu za kursorima.
Jeste li ikad vidjeli da delfini lete ka suncu?
U današnje vrijeme je sve moguće! Sun je kupio MySQL AB za milijardu dolara i najavljuje proboj na enterprise tržište.
Više informacija možete pročitati na blogu Jonathana Schwartza, CEO Sun-a.
Pre neki dan dobio sam zadatak da kreiram jednu prilično flat (nenormalizovanu) tabelu i da obezbedim integritet podataka upotrebom CHECK constraints. Pošto ja u stvari nisam DBA, je ovo u praksi retko radim, mislim, da zaista kreiram tabele i slično. Moja priča se završi obično sa ER dijagramom, posle normalizacije. Ali eto, zalomilo se. Na svoj užas i javnu sramotu, otkrio sam da ja to u stvari više ne ume da uradim. Odem u Help, tamo nema baš mnogo pomoći. Većina mojih knjiga je o logičkom modelitranju i normalizaciji i o specijalno zakukuljenim kverijima. Takoda sam morao da eksperimetišem. Mnogo mi se dopalo, pa sam poželeo da podelim iskustvo. Ako se nekom učini da je ovaj članak suviše trivijalan i jednostavan, unapred se izvinjavam.
Znam od ranije da se constraint može definisati pri definiciji kolone, a i posle. Zapitao sam se šta je bolje, definisati CHECK prilikom definicije kolone, ili nakon kreiranja tabele, komandom ALTER table.
Izabrao sam prvi način, jer sam pretpostavio da ima manje kucanja. I krenuo sam ovako:
CREATE TABLE Test(ProjectId int NOT NULL CHECK(ProjectId = 51))
Onda sam pokušao da unesem pogrešan podatak, da testiram CHECK. Evo šta se desilo:
INSERT INTO Test VALUES (52)
Server me je pozdravio porukom:
The INSERT statement conflicted with the CHECK constraint "CK__Test__ProjectId__4222D4EF". The conflict occurred in database "ScrapBook", table "dbo.Test", column 'ProjectId'. The statement has been terminated.
Lepo, CHECK radi. Jedino šta mi se ne dopada je što me je na prvi pogled ime CK__Test__ProjectId__4222D4EF bilo malo nečitljivo. Sad vidim da CK znaši CHECK Test je ime tabele a ProjectID je ime kolone. Meni je sve to promekolo i pažnju mi je odvuklo ono 4222D4EF . Interesantno akko najmanje važan podatak upada u oči. Onda sam probao drugi način, sa ALTER TABLE ADD CONSTRAINT i desilo se ovo:
DROP TABLE TestGO CREATE TABLE Test(ProjectId int NOT NULL ) ALTER TABLE Test ADD CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51)
Pokušaj da unesem 52 umesto zahtevanih 51 dao je ovakvu poruku:
Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "ProjectId_must_be_51". The conflict occurred in database "ScrapBook", table "dbo.Test", column 'ProjectId'.The statement has been terminated.
Interesantno, odmah sam video o čemu se radi – ProjectID mora da bude 51.
Onda sam “pronašao” i treći način da sagradim jednostavni CHECK – u definiciji kolone, sa zadavenjem imena.
DROP TABLE TestGO CREATE TABLE Test (ProjectId int NOT NULL CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51) , ProjectName varchar(50) NULL)
Pokušaj da unesem šta ne sme da se unese:
INSERT INTO Test (ProjectID) VALUES (52)
Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "ProjectId_must_be_51". The conflict occurred in database "ScrapBook", table "dbo.Test", column 'ProjectId'.The statement has been terminated.
I još jedan način. Sada se CHECK definiše na nivou tabele, posle svih unetih kolona. Ovako:
CREATE TABLE Test
(ProjectId int NOT NULL
, ProjectName varchar(50) NULL, CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51))
Uočite zarez (zapetu) ispred reči CONSTRAINT. Tim zarezom smo odvojili definiciju kolone ProjectName od definicije CHECK CONSTRAINT za kolonu ProjectID. Ovo će se kasnije pokazati kao jako korisan način.Eto dakle četiri načina da se postavi jednostavan CHECK za individualnu kolonu. Pa birajte koji vam se dopada. Za proste slučajeve verovatno je svejedno.
Moj zadatak je bio teži, treblo je postaviti ne baš jednostavne CHECK koji su nad više od jedne kolone. Naravno, u propisno normalizovanoj tabeli po pravilu ne bi trebalo da imamo kolone koje zavise od drugih kolona u istoj tabeli, ali kako rekoh na početku, radi se o nenormalizovanoj tabeli.
U mojoj tabeli bile su dve povezane kolone, PkgType, integer i PackageCode varchar(6), isto što i PkgType ali sa nulama ispred. Dakle, PkgTzpe = 1 postaje PackageCode ’01’. Pokušao sam ovako:
CREATE TABLE Test ( ProjectId int NOT NULL CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51), PackageCode varchar (2) NOT NULL , PkgType int NOT NULL CONSTRAINT PkgType_is_PackageCode CHECK(PkgType = CAST(PackageCode AS int)))Odgovor je bio:
Msg 8141, Level 16, State 0, Line 1 Column CHECK constraint for column 'PkgType' references another column, table 'Test'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.Pošto su u pitanju dve kolone, to ne može biti deo definicije kolone, to mora da je na nivou tabele. Ovo je prošlo bez problema:
CREATE TABLE Test ( ProjectId int NOT NULL CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51), PackageCode varchar (2) NOT NULL , PkgType int NOT NULL , CONSTRAINT PkgType_is_PackageCode CHECK(PkgType = CAST(PackageCode AS int)))Razlika je u jednom zarezu kojim smo CHECK odvojili od definicije kolona. Zbog čitljivosti, opredelio sam se za format
CREATE TABLE Test ( -- Kolone ProjectId int NOT NULL , PackageCode varchar (2) NOT NULL , PkgType int NOT NULL -- Uslovi , CONSTRAINT PkgType_is_PackageCode CHECK(PkgType = CAST(PackageCode AS int)), CONSTRAINT ProjectId_must_be_51 CHECK(ProjectId = 51))Na kraju smo dobili izraz koji radi šta nam treba, lepo formatiran i jasan.
U sledećem javljanju pokazaćemo kako iz CHECK constraints mogu da se pozivaju druge tabele. Da, tačno je, ima i u Helpu, CHECK može da poziva i druge tabele. Ovo naravno ne znači da treba da zaboravimo na FOREIGN KEY i da sve prebacimo na CHECK. Medjutim ima situacija kada nam baš odgovara da kroz CHECK vidimo druge tabele.
MS SQl 2005 doneo je nekoliko značajnih novih rešenja. Jedna od novosti je upotreba Common Table Expressions (CTE). CTE su u stvari virtualne temp tabele. Šta ovo znači?
Pokušaću da pokažem na primeru o čemu se radi. Ne jednom forumu neko je postavio sledeće pitanje:
Zadate su tabele:
Partner(partner_sif#, ime, prezime)
Faktura(faktura_br#, partner_sif, faktura_datum_fakt)
Stavke_fakture(faktura_br#,roba_sif#,faktura_kolicina, faktura_cijena)
Traži se SQL upit koji za rezultat daje partnera s kojim je ostvaren najveći promet.
Pretpostavimo da je promet = suma(kolicina*cena)
Pretpostavka je da postoji tačno jedan partner sa najvećim prometom.
Zadatak bi se logički mogao razbiti na tri dela, ovako:
1. utvrditi koliki je promet ostvario svaki partner
2. izabrati najveći od svih prometa
3. utvrditi ko je partner sa najvećim prometom
Da krenemo:
Utvrdimo koliki je promet ostvario svaki partner, is a;uvajmo SELECT kao view
CREATE VIEW v_Promet
AS
SELECT
partner_sif#
, Promet = SUM(faktura_kolicina * faktura_cijena)
FROM Stavke_fakture
Sada utvrdimo koji je to promet najveći. Evo još jedan view, koji poziva onaj što smo napravili u prvom koraku.
CREATE VIEW v_NajveciPromet
AS
SELECT
NajveciPromet = MAX(Promet)
FROM v_Promet
Sada ćemo utvrditi ko je partner sa najvećim prometom
SELECT
partner_sif#
, Promet
FROM v_Promet AS P
JOIN v_NajveciPromet AS M
ON M.NajveciPromet = P.Promet
Za sada sve izgleda relativno jasno. Nažalost, DBA ne dozvoljava da kreiramo views, pa sve mormo da strpamo nekako u jedan SQL iskaz. Tu se stvari komplikuju. Rezultujući kveri mogao bi da izgleda ovako:
SELECT
P.partner_sif#
, P.Promet
FROM
(SELECT
partner_sif#
, Promet = SUM(faktura_kolicina * faktura_cijena)
FROM Stavke_fakture
) AS P
JOIN
(SELECT
NajveciPromet = MAX(Promet)
FROM v_Promet
) AS M
ON M.NajveciPromet = P.Promet
Samo sam zamenio nazive view-a odgovarajucim SELECT iskazima. Ovo se zove upotreba in-line tabela.
Konacni SQL iskaz nije naročito komplikovan, ali nema baš previše ljudi koji mogu ovakav iskaz da napisu iz cuga. A dovoljno je nečitljiv, i pored truda uloženog u formatiranje.
SQL 2005 donosi rešeneje – CTE (Common Table Expressions). CTE su virtualne tabele, koje se definišu i žive u jednom baču. U našem slučaju, ono sto su bili views, postaće CTE.
Ovako to izgleda u našem zadatku:
WITH CTE_Promet AS
(SELECT
partner_sif#
, Promet = SUM(faktura_kolicina * faktura_cijena)
FROM Stavke_fakture
)
, CTE_NajveciPromet AS
(
SELECT
NajveciPromet = MAX(Promet)
FROM CTE_Promet
)
SELECT
partner_sif#
, Promet
FROM CTE_Promet AS P
JOIN CTE_NajveciPromet AS M
ON M.NajveciPromet = P.Promet
Mozete da zakljucite da sintaksa ide nekako ovako:
WITH
CTE_1 AS
(
SELECT koji definise CTE_1
)
, CTE_2 AS
(
SELECT koji definise CTE_2
ovde moze da se poziva prethodno definisani CTE
)
, CTE_X AS
(
SELECT koji definise CTE_X, koji
moze da poziva sve prethodno definisane CTE
)
SELECT f( CTE_1, CTE_2, f(CTE_X), završni izraz poziva bilo koji CTE)
Dakle, počinjemo službenom reči WITH. Onda definisemo jedan ili vise CTE virtualnih tabela. Svaki od prethodno definisanih može se upotrebit u definisanju sledećih. Na kraju pišemo izraz koji koristi bar jedan od definisanih CTE..
Na ovaj način se mnogi komplikovani izrazi sa subkverijima postaju mnogo jasniji. Ovakav jedan izraz, koji počinje sa WITH i definiše CTE može slobodno da se sačuva kao view, ili da se upotrebi u stored proceduri. Ako je u stored proceduri, svaki CTE može da koristi koje god hoćete parametre.
CTE je dakle neka vrsta temp tabele koja postoji u jednom baču.
Postoji i jedna druga upotreba CTE, kad CTE sam sebe poziva i rezultat je sličan onome što proizvode iterativne petlje DO WHILE i kursori. Na ovaj način potreba za kursorima se svodi na minimum. Ako tržite HELP za CTE, videćete primer gde se CTE koristi kao alat za iterativno procesiranje hijerarhijskog skupa. Primer nije preterano jasan pa ću u neko skorije vreme pokušati da nešto napišem o tome.
🙂
Pojavila se potreba da postojeću bazu klonirate, da biste mogli npr. izvršiti određene testove i provjeriti funkcionalnost novih programa. U ovom slučaju taj problem, odnosno zadatak, još kompleksnijim čini korištenje ASM (Automatic Storage Management), tako da postupak kloniranja baze nije jednostavan kao kod kloniranja baze sa datotekama na OS File-Systemu.
Sad se pitate:”Kako najlakše kopirati Oracle bazu, odnosno kako najefikasnije napraviti klon-bazu?“.
Klon-bazu možete kreirati na 3 načina:
1. koristeći RMAN i naredbu DUPLICATE
2. DBMS_FILE_TRANSFER paket
3. koristeći Enterprise Manager i opciju “Clone Database”
4. pomoću “Cold backup” metode (rijetko se koristi, pogotovo u 24/7 okruženju)
Kompanija za koju radim prikuplja podatke o učenicima srednjih i osnovnih škola u Ontariju. Podaci koje prikupljamo menjaju se od godine do godine. Da ne bi menjali bazu svaki čas, imamo dve tabele:
CREATE TABLE Osobe
(Ime varchar(25) NOT NULL PRIMARY KEY)
GOCREATE TABLE Predmeti
(Ime varchar(25) NOT NULL
, Predmet varchar(25) NOT NULL
PRIMARY KEY (Ime,Predmet)
)
GOU tabelama imamo ovo:
SELECT * FROM Osobe;Ime -------- Cica Goca Laza Maca Melanija Pera Verica Zika (8 row(s) affected)Za svaku osobu imamo podatke koje skupljamo, u ovom slučaju školske predmete:
SELECT Ime, Predmet
FROM Predmeti;
Ime Predmet
———- ——-
Cica Bio
Cica Fiz
Laza Bio
Laza Geo
Laza Math
Maca Bio
Maca Fiz
Maca Geo
Maca Math
Melanija Geo
Pera Bio
Pera Fiz
Pera Geo
Pera Math
Verica Bio
Verica Math
Zika Bio
Zika Fiz
Zika MathPostavljeno je pitanje Ko NEMA predmet ‘Fiz’?
Klasično rešenje problema ide ovako:
SELECT Ime
FROM Osobe
WHERE Ime NOT IN(SELECT Ime
FROM Predmeti
WHERE Predmet = ‘Fiz’);
i daje korektno rešenje:
Ime
———
Goca
Laza
Melanija
Verica(4 row(s) affected)
Neko je predložio da probamo da pokažemo sve osobe na levoj strani, a na desnoj njihove predmete, ali samo ‘Fiz’ i pogledamo gde je Predmet NULL. Probali smo prvo ovako:
SELECT A.Ime, B.Predmet
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
WHERE B.Predmet = ‘Fiz’
AND B.predmet IS NULL;što naravno nije vratilo ni jedan red. Ne može nešto da bude istovremeno ‘Fiz’ i NULL. Šteta, a delovalo je zaista obećavajuće. Onda se neko drugi setio da napiše upit ovako:
SELECT A.Ime, B.Predmet
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND B.Predmet = ‘Fiz’
WHERE B.predmet IS NULL;Deo WHERE prebacili smo u deo JOIN ON. I dobili tačan rezultat iz cuga. Ništa subquery, ništa IN. Upravo smo otkrili način da zadamo WHERE i na desnoj strani LEFT JOINa.
Isti efekat mogli smo postići bez upotrebe temp tabela ovako:
WITH A AS
(SELECT Ime, Predmet = ‘Fiz’
FROM Osobe
)
SELECT A.Ime, B.Predmet
FROM A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime AND A.Predmet = B.Predmet
WHERE B.Predmet IS NULLProblem je što ovo podrazumeva da znamo da upotrebimo CTE (common table expressions), pa da se setimo da ‘pomnožimo’ tabelu Osobe konstantom ‘Fiz’. Ostalo je lako. 🙂
Zato smo ostali kod našeg ‘proširenog JOINa’.
Onda smo proširili pitanje. Rekli smo ‘Ko NEMA ni jedan od predmeta (Fiz, Bio), dakle ni jedan od ova dva?
SELECT A.Ime, B.Predmet
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)
WHERE B.predmet IS NULLIme Predmet --------- ------- Goca NULL Melanija NULL (2 row(s) affected)Elegantno, zar ne?
Pitali smo dalje Ko ima bar jedan od ova dva predmeta, (Fiz,Bio)?
Pokušali smo i dalje istim putem i za malo promašili:
SELECT A.Ime, B.Predmet
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)
WHERE B.predmet IS NOT NULL;To nam je dalo zaista sve osobe koje imaju bar jedan od dva predmeta, ali su osobe koje imaju oba predmeta prikazane dva puta u izlaznom skupu:
Ime Predmet --------- ------- Cica Bio Cica Fiz Laza Bio Maca Bio Maca Fiz Pera Bio Pera Fiz Verica Bio Zika Bio Zika Fiz (10 row(s) affected)Resili smo se duplikata na jednostavan način (minimum rada), ovako:
SELECT DISTINCT A.Ime —-, B.Predmet
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)
WHERE B.predmet IS NOT NULL;Ubaciš DISTINCT, komentuješ Predmet i dobije se:
Ime --------- Cica Laza Maca Pera Verica Zika (6 row(s) affected)Postoji bolji način za eliminisanje duplikata. Zahteva malčice više rada u ovom momentu, ali donosi značajnu korist u sledećem koraku. Elem, ovako:
SELECT A.Ime , KolikoPredmeta = COUNT(*)
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)
WHERE B.predmet IS NOT NULL
GROUP BY A.Ime;Ime BrojPredmeta ------ ------------- Cica 2 Laza 1 Maca 2 Pera 2 Verica 1 Zika 2 (6 row(s) affected)Dobili smo i broj predmeta, ko ima koliko od traženih dva predmeta. To može lepo da se iskoristi, ako se postavi pitanje Ko ima oba tražena predmeta?
Pa svi oni iz poslednjeg kverija koji imaju broj predmeta jednak 2. Ovako:
SELECT A.Ime , KolikoPredmeta = COUNT(*)
FROM Osobe AS A
LEFT JOIN Predmeti AS B
ON A.Ime = B.Ime
AND (B.Predmet = ‘Fiz’ OR B.Predmet = ‘Bio’)
WHERE B.predmet IS NOT NULL
GROUP BY A.Ime
HAVING COUNT(*)=2;Ime KolikoPredmeta ----- -------------- Cica 2 Maca 2 Pera 2 Zika 2 (4 row(s) affected)Dopao mi se ovakav pristup, jer kad jednom u glavi svarite proširivanje JOIN filtera delom WHERE uslova, mnoga nezgodna pitanja postaju jednostavna. Međutim, najveća dobit jeste mogućnost da radite LEFT JOIN i da na desnoj strani ne dobijete baš sve redove, nego samo one koji vam trebaju. Ne sve predmete za svaku osobu, nego tačno onaj predmet koji vas u datom trenutku interesuje. Inače vam ne ginu temp tabele ili upotreba WITH.
Ako ovo nije bilo suviše suvoparno i zamorno, za koji dan pozabavićemo se upotrebom WITH, sa i bez rekurzije.