Datab

1
DatabázovéAplikace

• Slidy k prednášce NDBI026
• KSI MFF UK
• http//www.ms.mff.cuni.cz/kopecky/vyuka/dbapl/
• Verze 13.10.02.12.00

2
Literatura

• Manuálové stránky Oracle 11g
• Dostupné odkudkoli
• http//www.oracle.com/pls/db112/homepage
• Dostupné z domény .mff.cuni.cz
• http//tirpitz.ms.mff.../intra/oracle/doc/ora1120d
  oc/
• Dostupné odkudkoli
• http//www.orafaq.org/faq.htm
• http//www.orafaq.org/faq2.htm

DBI026 -DB Aplikace - MFF UK
3
Literatura, pokrac.

• Manuálové stránky MS SQL
• Dostupné odkudkoli
• http//technet.microsoft.com/en-us/library/bb5454
  50.aspx

DBI026 -DB Aplikace - MFF UK
4
Literatura, pokrac.

• D. Quass, J. Widom. R. Goldman, K. Haas, Q. Luo,
  J. McHugh, S. Nestorov, A. Rajaraman, H. Rivero,
  S. Abiteboul, J. Ullman, and J. Wiener LORE A
  Lightweight Object REpository for Semistructured
  Data, http//www-db.stanford.edu/lore/pubs/lore-de
  mo.pdf, http//www-db.stanford.edu/lore
• Roy Goldman, Jennifer Widom DataGuides Enabling
  Query Formulation and Optimalization in
  Semistructured Databases, http//www-db.stanford.e
  du/lore/pubs/dataguide.pdf

DBI026 -DB Aplikace - MFF UK
5
O cem to bude?

• Predpoklady
• Databázové systémy
• Základy SQL
• SELECT príkazy
• Návrh schémat
• Normální formy
• Vlastnosti transakcí, serializovatelnost,

• Co se dozvím
• Praktický návrh aplikací
• Na co si dát pozor
• Pri vytvárení relacních schémat
• Pri psaní SQL príkazu
• Optimalizace odezvy
• Indexy
• Plány provádení
• Ve víceuživatelském provozu
• Zamykání
• Transakcní zpracování
• Zabezpecení dat

DBI026 -DB Aplikace - MFF UK
6
Co zde bude

• Relacní model
• dosud nejrozšírenejší a nejpoužívanejší
• Optimalizace dotazu
• volba indexu a vhodné formulace dotazu muže
  ovlivnit výsledný cas provádení dotazu i o
  nekolik rádu
• Víceuživatelský provoz
• nevhodne navržená aplikace muže zpusobovat
  nekorektní prubeh datových manipulací a
  neocekávané výsledky

DBI026 -DB Aplikace - MFF UK
7
Co zde bude, pokrac.

• Procedurální rozšírení
• Triggery pro hlídání korektnosti aktualizací
• Uložené procedury a funkce
• Objektové rozšírení
• Uživatelské datové typy
• Hnízdené tabulky

DBI026 -DB Aplikace - MFF UK
8
Na cem se cvicí?

• DB systém Oracle 11g
• Objektove relacní databáze
• Podpora provádení kódu v jazycích
• PL/SQL
• Java
• C/C (obecne jakákoli .dll/.so knihovna)
• Podpora XML, multimédií a dalších rysu

• DB MS SQL 2008 R2
• Objektove relacní databáze
• Podpora provádení kódu v jazycích
• T-SQL
• C
• Podpora XML, textu a dalších rysu

DBI026 -DB Aplikace - MFF UK
9
Problémy ANSI/ISO norem SQLs jejich
(ne)dodržováním

• SQL-78
• SQL-92 ANSI/SQL2ISO/IEC 90751992
• Entry,
• Intermediate,
• Full
• SQL-99ANSI/ISO/IEC 90751999
• SQL-2003ISO/IEC 90752003

DBI026 -DB Aplikace - MFF UK
10
Problémy ANSI/ISO norem SQLs jejich
(ne)dodržováním

• Jednotlivé databázové servery ne vždy dodržují
  ANSI normu
• Obvykle pouze SQL-92 Entry
• Rada neprenositelných rozšírení navíc
• Ne všechny rysy implementovány dle ANSI
• S novými verzemi se kompatibilita zlepšuje
• Casto nyní existují obe syntaxe zároven

Bežný SQL-92 kompatibilní server
78
92
99
DBI026 -DB Aplikace - MFF UK
11
Problémy ANSI/ISO norem SQLs jejich
(ne)dodržováním

• Cím více se pri vývoji aplikace využijí rysy
  vyšší než SQL-92 Entry
• Tím menší je šance, že aplikace bude
  provozuschopná i na jiné databázi
• Cásti vyšších verzí jsou casto rešeny pouze
  proprietárne a neprenositelne
• Pred zacátkem vývoje je proto obvykle nutné
  zvolit cílovou platformu
• Její zmena v prostredku vývoje nebo po nasazení
  aplikace je velmi drahá

Bežný SQL-92 kompatibilní server
78
92
99
DBI026 -DB Aplikace - MFF UK
12
Problémy ANSI/ISO norem SQLs jejich
(ne)dodržováním

• Co v prípade, že server nechce SQL príkaz
  prijmout?
• Je príkaz nekorektní, nebo server normu
  nepodporuje?
• Validátory SQL
• http//developer.mimer.se/validator/

DBI026 -DB Aplikace - MFF UK
13
Úvod do SQL

• SELECT príkazVestavené funkce a operátory

14
SELECT príkaz

• SELECT DISTINCT výraz1 AS c_alias1 ,
  FROM zdroj1 AS t_alias1 , WHERE
  podmínka_rGROUP BY výraz_g1 , HAVING
  podmínka_sORDER BY výraz_o1 ,

DBI026 -DB Aplikace - MFF UK
15
SELECT príkaz - logické vykonání (bez uvažování
optimalizace dotazu)

• SELECT DISTINCT výraz1 AS c_alias1 ,
  FROM zdroj1 AS t_alias1 , WHERE
  podmínka_rGROUP BY výraz_g1 , HAVING
  podmínka_sORDER BY výraz_o1 ,

• Nejprve se zkombinují data ze všech zdroju
  (tabulek, pohledu, poddotazu)
• Pokud jsou oddeleny cárkami, provede se kartézský
  soucin
• ANSI SQL-92zavádí spojení JOIN ON, NATURAL JOIN,
  OUTER JOIN,

DBI026 -DB Aplikace - MFF UK
16
SELECT príkaz - logické vykonání (bez uvažování
optimalizace dotazu)

• SELECT DISTINCT výraz1 AS c_alias1 ,
  FROM zdroj1 AS t_alias1 , WHERE
  podmínka_rGROUP BY výraz_g1 , HAVING
  podmínka_sORDER BY výraz_o1 ,

• Vyradí se vzniklé rádky, které nevyhovují podmínce

DBI026 -DB Aplikace - MFF UK
17
SELECT príkaz - logické vykonání (bez uvažování
optimalizace dotazu)

• SELECT DISTINCT výraz1 AS c_alias1 ,
  FROM zdroj1 AS t_alias1 , WHERE
  podmínka_rGROUP BY výraz_g1 , HAVING
  podmínka_sORDER BY výraz_o1 ,

• Zbylé rádky se seskupí do skupin se stejnými
  hodnotami uvedených výrazu(SORT/HASH)
• Každá skupina obsahuje atomické sloupce s
  hodnotami uvedených výrazu a množinové sloupce se
  skupinami ostatních hodnot sloupcu

DBI026 -DB Aplikace - MFF UK
18
SELECT príkaz - logické vykonání (bez uvažování
optimalizace dotazu)

• SELECT DISTINCT výraz1 AS c_alias1 ,
  FROM zdroj1 AS t_alias1 , WHERE
  podmínka_rGROUP BY výraz_g1 , HAVING
  podmínka_sORDER BY výraz_o1 ,

• Vyradí se skupiny, nevyhovující podmínce

DBI026 -DB Aplikace - MFF UK
19
SELECT príkaz - logické vykonání (bez uvažování
optimalizace dotazu)

• SELECT DISTINCT výraz1 AS c_alias1 ,
  FROM zdroj1 AS t_alias1 , WHERE
  podmínka_rGROUP BY výraz_g1 , HAVING
  podmínka_sORDER BY výraz_o1 ,

• Výsledky se setrídí podle požadavku

DBI026 -DB Aplikace - MFF UK
20
SELECT príkaz - logické vykonání (bez uvažování
optimalizace dotazu)

• SELECT DISTINCT výraz AS alias ,
  FROM zdroj1 t_alias1 , WHERE
  podmínka_rGROUP BY výraz_g1 , HAVING
  podmínka_sORDER BY výraz_o1 ,

• Vygeneruje se výstup s požadovanými hodnotami
• V prípade DISTINCT se vyradí duplicity(SORT/HASH)

DBI026 -DB Aplikace - MFF UK
21
Co hlídat pri psaní SELECT príkazu

• Klauzule GROUP BY setrídí pred vytvorením skupin
  všechny rádky dle výrazu v klauzuli, v lepším
  prípade nejdríve rádky podle hash hodnoty výrazu
  rozdelí do menších prihrádek
• Seskupovat by se mel co nejmenší možný pocet
  rádek
• Pokud je možné rádky odfiltrovat pomocí WHERE, je
  výsledek efektivnejší, než následné odstranování
  celých skupin

DBI026 -DB Aplikace - MFF UK
22
Co hlídat pri psaní SELECT príkazu

• SELECT Ulice, COUNT()FROM ObyvatelWHERE
  Mesto'Brno'GROUP BY Mesto, Ulice
• Setrídí se jen 370 tis. záznamu obyvatel Brna

• SELECT Ulice, COUNT() FROM Obyvatel GROUP
  BY Mesto, UliceHAVING Mesto'Brno'
• Všech 10mil. záznamu se setrídí (zahešuje),
  ponechají se jen skupiny z Brna

DBI026 -DB Aplikace - MFF UK
23
Co hlídat pri psaní SELECT príkazu

• Klauzule DISTINCT trídí (hešuje) výsledné záznamy
  (ješte pred operací ORDER BY),aby našla
  duplicitní záznamy
• Pokud to jde,je vhodné se bez DISTINCT obejít
• Klauzule ORDER BY by mela být použita, jen v
  nutných prípadech
• Není príliš vhodné ji používat v definicích
  pohledu, nad kterými se dále delají další dotazy

DBI026 -DB Aplikace - MFF UK
24
Prehled software pro cvicení
25
Oracle _at_ MFF
tirpitz.ms.mff

• Linux Servertirpitz.ms.mff.cuni.cz
• Databáze jedenact (11.x)
• Klienti Linux
• Laborator UW1, UW2
• SQL Developer
• Laborator US1, US2(u-pl.ms.mff)
• SQL Developer

TCP/IP
UW1,2
u-pl.ms.mff
DBI026 -DB Aplikace - MFF UK
26
MS SQL _at_ MFF

• Laborator UW1, UW2
• Klienti Win32
• MS SQL Server 2008

UW1,2
DBI026 -DB Aplikace - MFF UK
27
Relacní model SQL

• Vytvárení tabulek a pohleduIntegritní
  omezeníAktualizace dat

28
Vytvárení tabulek

• CREATE TABLE jm_tab (jm_sloupce typ (velikost)
  omezení,,omezení_rádky,)
• CREATE TABLE Osoba (rc character(11)
  CONSTRAINT Osoba_PK PRIMARY KEY,jmeno
  character(50) NOT NULL)

DBI026 -DB Aplikace - MFF UK
29
Reprezentace textových dat

• SQL-92 rozlišuje dvoje kódování znaku
• Duvodem je podpora UTF-8 (UTF-16)
• Možnost ukládání znaku libovolného jazyka
• Neefektivní vícebajtová reprezentace znaku pro
  znaky národních abeced
• Globální znaková sada,
• UTF (ne nutne)
• Národní znaková sada pro texty v jednom
  konkrétním jazyce - ceském, slovenském,
• CP-1250, ISO-8859-2,

DBI026 -DB Aplikace - MFF UK
30
Reprezentace textových dat

• SQL-92 dále rozlišuje dvojí reprezentaci retezcu
• Pevná délka, zprava doplneno mezerami
• Snadnejší aktualizace dat
• Méne efektivní reprezentace
• Promenná délka, ukládají se použité znaky plus
  délka retezce
• Efektivnejší reprezentace
• Komplikovanejší aktualizace, nová hodnota muže
  zabrat jiný pocet bajtu

DBI026 -DB Aplikace - MFF UK
31
Datové typy dle ANSI SQL-921) Textové

• CHARACTER(n) text v pevné délce n
  znakuCHARACTER VARYING(n)CHAR VARYING(n) text
  v promenné délce max. n znaku
• NATIONAL CHARACTER(n) text v pevné délce n znaku
  v národní abecedeNATIONAL CHARACTER
  VARYING(n)NATIONAL CHAR VARYING(n)NCHAR
  VARYING(n) text v promenné délce max. n znaku v
  národní abecede

DBI026 -DB Aplikace - MFF UK
32
Datové typy dle ANSI SQL-921) Textové

• Konstanty se uzavírají do apostrofu
• Apostrof se v konstantách zdvojuje

DBI026 -DB Aplikace - MFF UK
33
Datové typy dle ANSI SQL-922) Císelné

• NUMERIC(p,s)obecný císelný typ na p platných
  míst, s cifer za desetinnou cárkouINTEGER, INT,
  SMALLINT celé císloFLOAT(b)reálné císlo v
  b-bitové presnosti reprezentace REALreálné
  císlo DOUBLE PRECISIONreálné císlo ve
  dvojnásobné presnosti

DBI026 -DB Aplikace - MFF UK
34
Datové typy dle ANSI SQL-923) Datumové

• DATEdatum (YYYY-MM-DD ) s presností na dnyTIME
  cas (HHMM.SS.MMMM) s presností minimálne na
  sekundyTIMESTAMPdatum plus cas (YYYY-MM-DD
  HHMM.SS.MMMM)TIMESTAMP(p) WITH TIMEZONE p
  udává presnost sekund, cásová zóna se udává na
  konci ve tvaru HHMM, resp. HHMM

DBI026 -DB Aplikace - MFF UK
35
Datové typy dle ANSI SQL-923) Datumové

• Konstanty se uzavírají do apostrofuv uvedeném
  formátu

DBI026 -DB Aplikace - MFF UK
36
Datové typy dle ANSI SQL-92vs. reálná DB

• Databázové servery
• Ne vždy podporují všechny uvedené typy
• Nemusí tyto typy podporovat nativne,pouze si
  preloží název typu na podobný nativne
  podporovaný typ

DBI026 -DB Aplikace - MFF UK
37
Datové typy dle ANSI SQL-92vs. Oracle SQL

• CHARACTER(n)
• CHARACTER VARYING(n)CHAR VARYING(n)
• NATIONAL CHARACTER(n)
• NATIONAL CHARACTER VARYING(n)NATIONAL CHAR
  VARYING(n)NCHAR VARYING(n)
• NUMERIC(p,s)
• INTEGER, INT, SMALLINT
• FLOAT(b)DOUBLE PRECISIONREAL

• CHAR(n)
• VARCHAR2(n) VARCHAR2(n)
• NCHAR(n)
• NVARCHAR2(n) NVARCHAR2(n) NVARCHAR2(n)
• NUMBER(p,s)
• NUMBER(38)
• NUMBERNUMBERNUMBER

DBI026 -DB Aplikace - MFF UK
38
Datové typy dle ANSI SQL-92vs. Oracle SQL

• DATE
• Presnost ukládání na sekundy, tj. splnuje
  požadavky SQL-92 na TIMESTAMP
• Základní (americký) formát DD-MON-YYnapr.
  01-JAN-2006
• VARCHAR2(velikost), //doporucenoVARCHAR(velikost)
  
• Retezec s promenlivou délkou
• velikost max. 4000 znaku (dop. max. 2000 znaku)

DBI026 -DB Aplikace - MFF UK
39
Integritní omezení

• CONSTRAINT jméno definice_omezeníINITIALLY
  DEFERREDIMMEDIATENOT DEFERRABLE
• Pokud omezení není pojmenované, dostane obvykle
  nic neríkající jméno(V Oracle napr.
  SYS_Cnnnnnn).
• Doporucuje se proto omezení pojmenovávat
• Jednotlivá sloupcová omezení se od sebe oddelují
  mezerou

DBI026 -DB Aplikace - MFF UK
40
Integritní omezení

• NULL, resp. NOT NULL
• Sloupec muže, resp. nemuže mít nedefinovanou
  hodnotu NULL.
• UNIQUE
• Sloupec musí mít všechny hodnoty ruzné.
• Obvykle se používá volnejší interpretaceSloupec
  musí mít všechny definované hodnoty ruzné.
• PRIMARY KEY
• Sloupec tvorí primární klíc tabulky, je
  automaticky chápán jako NOT NULL a UNIQUE.

DBI026 -DB Aplikace - MFF UK
41
Integritní omezení

• CHECK (podmínka)
• Hodnota sloupce musí splnovat podmínku.
• REFERENCES tabulka(sloupec)ON DELETE
  CASCADESET NULL
• Hodnota odkazuje na primární klíc, nebo
  kandidátní klíc (UNIQUE sloupec) uvedené tabulky
• S klauzulí ON DELETE je povoleno mazání nadrízené
  rádky. Pokud je smazána, podrízená rádka je
  smazána rovnež, nebo je je hodnota ve sloupci
  nastavena na NULL

DBI026 -DB Aplikace - MFF UK
42
Integritní omezení

• DEFAULT hodnota
• Defaultní hodnota, pokud není uvedeno jinak
• Standardne je DEFAULT NULL

DBI026 -DB Aplikace - MFF UK
43
Integritní omezení

• Príklad
• CREATE TABLE Osoba( RC CHARACTER(11)
  CONSTRAINT Osoba_PK PRIMARY KEY, JMENO CHAR
  VARYING(30) CONSTRAINT Osoba_U_Jmeno UNIQUE
  NOT NULL, EMAIL CHAR VARYING(30) CONSTRAINT
  Osoba_C_Email CHECK (EMAIL LIKE '__at__._'
  )

DBI026 -DB Aplikace - MFF UK
44
Datový slovník v Oracle / MS SQL

• Informace o tabulkách jsou v Oracle uloženy v
  pohledech
• USER_TABLES
• USER_TAB_COLUMNS
• USER_CONSTRAINTS

• Informace o tabulkách jsou v MS SQL uloženy v
  pohledech
• INFORMATION_SCHEMA.TABLES
• INFORMATION_SCHEMA.COLUMNS
• INFORMATION_SCHEMA.TABLE_CONSTRAINTS

DBI026 -DB Aplikace - MFF UK
45
Rádková integritní omezení

• Mohou kontrolovat vzájemný vztah více sloupcu
  jednoho rádku
• CHECK (zacatek lt konec)
• Mohou definovat vícesloupcové primární a
  kandidátní a cizí klíce
• PRIMARY KEY (zacatek, konec)
• FOREIGN KEY (zacatek, konec)REFERENCES Rodic (x,
  y)

DBI026 -DB Aplikace - MFF UK
46
Atributy integritních omezení

• ENABLED / DISABLED
• Omezení je aktivní a platnost se overuje
• ALTER TABLE tabulka
• ENABLEDISABLE CONSTRAINT omezení
• DEFERRED / IMMEDIATE
• Overení se odloží na konec transakce, jinak se
  overuje okamžite
• DEFERRABLE / NOT DEFERRABLE
• Overení je / není možné odkládat na konec
  transakce

DBI026 -DB Aplikace - MFF UK
47
Na co si dát pri návrhu pozor

• Hlídat na úrovni databáze všechny manipulace s
  daty, které ohlídat jdou
• Cokoli jde zadat uživatelem špatne, bude zadáno
  špatne
• Integritní omezení, triggery
• Pozdeji je cištení nekonzistentních dat namáhavé
  a opravy casto nemožné
• Lépe ohlídat vše centrálne, než v každé aplikaci
  zvlášt

DBI026 -DB Aplikace - MFF UK
48
Na co si dát pri návrhu pozor

• Hlídat unikátnost vkládaných dat
• Každá tabulka by mela mít primární klíc
• I v prípade, kdy je primární klíc umele
  vytvorený, jednotlivé instance (rádky) obvykle
  mívají svuj prirozený jedno- ci vícesloupcový
  identifikátor, který by mel být definován jako
  alternativní klíc tabulky (UNIQUE)
• Nezrídka existuje více alternativních klícu

DBI026 -DB Aplikace - MFF UK
49
INCLUDE dbacv_01.ppt

• INSERT/UPDATE/DELETE
• Vestavené funkce

50
Zmena schématu aplikace

• Pridávání a odebíránísloupcu a omezení
• Zmena definicesloupcu a omezení

51
Proc menit schéma aplikace

• Chyba v návrhu
• Špatná normální forma aplikace
• Nelze uložit všechna požadovaná data
• Chybne definovaná kontrola
• Zmena požadavku ze strany zákazníka
• Podpora nových vlastností evidovaných entit
• Zmena v omezeních

DBI026 -DB Aplikace - MFF UK
52
Na co pri zmenách schématu nezapomenout

• Zmeny schématu a aplikace predstavují obvykle
  výraznou vetšinu životního cyklu aplikace
• Data evidovaná ve schématu mají vetší cenu, než
  použitý software a hardware
• Schopnost zmeny schématu aplikace a modifikace
  dat bez jejich ztráty je duležitejší, než
  vytvárení schématu zcela nového

DBI026 -DB Aplikace - MFF UK
53
Zmena definic tabulek

• Pridání sloupce do již existujících tabulek
• ALTER TABLE tabulka ADDdefinice sloupce
• Pr.ALTER TABLE Osoba ADD Poznamka CHARACTER
  VARYING(1000)ALTER TABLE Vyrobek ADD EAN
  NUMERIC(13) CONSTRAINT Vyrobek_U_EAN UNIQUE

DBI026 -DB Aplikace - MFF UK
54
Zmena definic tabulek

• Zrušení sloupce v již existující tabulce
• Pred zrušením je obvykle nutné data presunout na
  nové místo!
• ALTER TABLE tabulkaDROP COLUMN sloupec
• Pr.ALTER TABLE OsobaDROP COLUMN Psc

DBI026 -DB Aplikace - MFF UK
55
Zmena definic tabulek

• Pridání omezení do již existujících tabulek
• ALTER TABLE tabulka ADDdefinice omezení
• Pr.ALTER TABLE Osoba ADD CONSTRAINT
  Osoba_FK_Matka FOREIGN KEY(matka)
  REFERENCES Osoba(ID) ON DELETE SET NULL

DBI026 -DB Aplikace - MFF UK
56
Zmena definic

• Zrušení nadbytecného omezení v tabulce
• ALTER TABLE tabulkaDROP CONSTRAINT omezení
• Pr.ALTER TABLE OsobaDROP CONSTRAINT
  Osoba_U_Jmeno

DBI026 -DB Aplikace - MFF UK
57
Zmena definic tabulek

• Sloupce a omezení lze pridávat najednou príkazem
• ALTER TABLE tabulka ADD (definice sloupce
  omezení, )
• Pr.ALTER TABLE Osoba ADD ( Poznamka CHARACTER
  VARYING(1000), CONSTRAINT Osoba_Chk_Vek
  CHECK (Vekgt0))

DBI026 -DB Aplikace - MFF UK
58
Zmena definic tabulek

• Sloupce lze redefinovat príkazem
• ALTER TABLE tabulka MODIFY (nová definice
  sloupce, )
• Pr.ALTER TABLE Osoba MODIFY ( Poznamka
  CHARACTER VARYING(2000))
• Lze menit
• NULL na NOT NULL a naopak
• Šírku sloupce
• Zvetšení
• Zmenšení (pokud je sloupec prázdný)

DBI026 -DB Aplikace - MFF UK
59
Spojování tabulek v SELECT príkazu
60
Spojování tabulek

• Norma SQL-92 zavedla více typu spojování tabulek
  v klauzuli WHERE vcetne podmínek
• Kartézský soucin
• Spojení
• Prirozené spojení
• Vnejší spojení
• Predchozí normy dovolovaly pouze
• cárkami oddelený seznam zdroju (tabulek a
  pohledu)
• u každého zdroje bylo možné uvést alias, oddelený
  mezerou
• podmínky spojení výhradne v klauzuli WHERE

DBI026 -DB Aplikace - MFF UK
61
Spojování tabulek

• ANSI SQL-92 syntaxe
• dovoluje použití klícového slova ASpro oddelení
  definice aliasu od zdroje dat
• FROM Emp AS E, Dept AS D
• Rozlišuje jednotlivé zpusoby spojení pomocí
  dalších klícových slov
• Uvádí podmínky spojení v klauzuli FROM
• Klauzule WHERE slouží pro uvedení dodatecných
  podmínek (selekci)

DBI026 -DB Aplikace - MFF UK
62
Spojování tabulek

• X CROSS JOIN Y
• kartézský soucin
• ekvivalent predchozího stylu zápisuX, Y
• SELECT EmpNo, LocFROM Emp CROSS JOIN Dept

DBI026 -DB Aplikace - MFF UK
63
Spojování tabulek

• X NATURAL INNER JOIN Y
• prirozené spojení pres všechny spolecné sloupce
  tabulek
• SELECT EmpNo, LocFROM Emp NATURAL JOIN Dept

DBI026 -DB Aplikace - MFF UK
64
Spojování tabulekOracle vs. ANSI SQL-92

• X INNER JOIN Y ON (podmínka)
• standardní spojení tabulek, ekvivalent
  zápisuFROM X, Y WHERE podmínka
• X INNER JOIN Y USING (sloupce)
• standardní spojení pres rovnost uvedených sloupcu
  (v obou tabulkách se musí jmenovat shodne)

DBI026 -DB Aplikace - MFF UK
65
Vnejší spojování tabulek

• Místo INNER je možné uvést jednu z možností
• LEFT OUTER, RIGHT OUTER, FULL OUTER
• Pokud je uvedeno X LEFT JOIN Y ON (podmínka)
  ...Jsou ve výsledku všechny rádky levé tabulky
  (X), i když k nim neexistuje žádná odpovídající
  rádka z pravé tabulky (Y)

DBI026 -DB Aplikace - MFF UK
66
Vnejší spojování tabulek

• Místo INNER je možné uvést jednu z možností
• LEFT OUTER, RIGHT OUTER, FULL OUTER
• SELECT FROM Emp NATURAL LEFT JOIN Dept
• Ve výsledku jsou uvedeni i zamestnanci, kterí
  nejsou v žádném oddelení
• Neexistující položky z oddelení jsou vyplneny
  NULL hodnotami

DBI026 -DB Aplikace - MFF UK
67
Vnejší spojování tabulek

• Místo INNER je možné uvést jednu z možností
• LEFT OUTER, RIGHT OUTER, FULL OUTER
• SELECT FROM Emp NATURAL RIGHT JOIN Dept
• Ve výsledku jsou uvedena i oddelení, ve kterých
  nejsou žádní zamestnanci
• Neexistující položky z oddelení jsou vyplneny
  NULL hodnotami

DBI026 -DB Aplikace - MFF UK
68
Vnejší spojování tabulek

• Místo INNER je možné uvést jednu z možností
• LEFT OUTER, RIGHT OUTER, FULL OUTER
• SELECT FROM Emp NATURAL FULL JOIN Dept
• Kombinace NATURAL LEFT OUTER JOIN a NATURAL
  RIGHT OUTER JOIN zároven

DBI026 -DB Aplikace - MFF UK
69
Vnejší spojování tabulekv Oracle

• Oracle má i svoji nativní syntaxi pro vnejší
  spojení
• Starší, pouze pro rovnost hodnot sloupcu
• Použití ANSI SQL je silnejší a prenositelné
• Levé vnejší spojení
• SELECT FROM Dept, EmpWHERE Dept.Deptno
  Emp.Deptno()
• Pravé vnejší spojení
• SELECT FROM Dept, EmpWHERE Dept.Deptno()
  Emp.Deptno
• Oboustranné neexistuje

DBI026 -DB Aplikace - MFF UK
70
Optimalizace SQL dotazu

• Indexy
• Plány provedení dotazu
• Ovlivnení optimalizátoru

71
Indexy

• Slouží pro zrychlení prístupu k datum na základe
  podmínky ve WHERE klauzuli
• Nemení syntaxi ani sémantiku príkazu pro
  manipulaci s daty
• Unikátní vs. neunikátní
• Jednosloupcové vs. vícesloupcové
• B-stromy vs. bitmapy
• Nad sloupci vs. nad výrazy
• Doménové indexy (fulltextové, prostorové, )

DBI026 -DB Aplikace - MFF UK
72
Indexy

• Tvorba indexu není v SQL-92 standardizována
• Jednotlivé databázové systémy reší tvorbu indexu
  svými prostredky, které jsou navzájem více ci
  méne podobné
• Muže se lišit
• syntaxe
• podpora ruzných typu indexu
• jejich (ne)použití pro daný dotaz a obsah tabulky

DBI026 -DB Aplikace - MFF UK
73
B-tree indexy

• Obvykle redundantní B stromy
• Hodnoty v listech
• Listy oboustranne linkované pro snadný sekvencní
  pruchod.
• Vhodné pro sloupce s vysokou selektivitou
  (poctem ruzných hodnot ve sloupci).
• Vícesloupcové (složené) indexy mohou zvýšit
  selektivitu.
• Použitelné, pokud dotaz omezuje hodnoty prvních k
  sloupcu indexu,pricemž prvních k-1 sloupcu musí
  být omezeno na rovnost
• Nepoužitelné, pokud v dotazu není omezení na
  první sloupec indexu
• Nad jednou tabulkou v jednom dotazu nelze obvykle
  kombinovatvíce B-tree indexu. Dotaz se
  vyhodnocuje s použitím jednoho z indexu a ostatní
  podmínky se dopocítávají

DBI026 -DB Aplikace - MFF UK
74
Bitmapové indexy

• Pro každou hodnotu sloupce / výrazu vytvoren
  binární retezec obsahující 1 práve pro rádky s
  danou hodnotou
• Vhodné pro sloupce s nízkou selektivitou
• Lze kombinovat více bitmapových indexu nad
  jednou tabulkou pro zvýšení selektivity
• Kombinací více bitmap se zvyšuje selektivita
  indexu
• SELECT FROM ObyvatelWHERE PohlavíM AND
  (KrajPha OR KrajSC)
• Kombinace trí bitmapových retezcu

DBI026 -DB Aplikace - MFF UK
75
Kdy indexy (ne)pomohou

• Nepomohou
• Pokud je procento vyhovujících záznamu velké
• zvýšená režie s prístupem k rádkum v
  nesekvencním poradí, daném indexem
• Pri dotazech na hodnotu NULL
• v indexech se bežne neukládají
• Pomohou
• V dotazech na rovnost sloupce s konstantou
• V dotazech na náležení hodnoty do intervalu

DBI026 -DB Aplikace - MFF UK
76
Kdy (ne)vytváret indexy

• Jak Oracle, tak MS SQL vytvárí automaticky
  unikátní indexy pro
• primární klíce
• jméno bývá shodné se jménem omezení
• kandidátní klíce (UNIQUE sloupce)
• jméno bývá shodné se jménem omezení

DBI026 -DB Aplikace - MFF UK
77
Kdy (ne)vytváret indexy

• Duležité je vytváret indexy pro cizí klíce !!!
• Zrychlení odezvy pri manipulaci s nadrízenou
  tabulkou
• Pri rušení nadrízené rádky je bez indexu nutné
  projít celou podrízenou tabulku, zda neobsahuje
  závislé rádky
• Pokud je aktivované kaskádové mazání a odkazy
  jsou víceúrovnové, pro každou nalezenou
  podrízenou rádku je nutné projít celou její
  porízenou tabulku atd. atd.
• Pruchod tabulkou cte i bloky,obsahující zrušené
  záznamy v tabulce
• Pruchod pres index najde efektivne všechny
  existující závislé rádky bez nutnosti ctení
  samotné tabulky
• Napr. Oracle reší nemožnost efektivne najít a
  zamknout podrízené rádky zamknutím celé podrízené
  tabulky, címž velmi omezuje možnost behu aplikace
  s více uživateli.

DBI026 -DB Aplikace - MFF UK
78
Kdy (ne)vytváret indexy

• Indexy by se jinak mely vytváret jen v prípade,
  pokud výrazne pomohou casto kladeným dotazum
• V opacném prípade spíše zdržují aktualizacní
  operace

DBI026 -DB Aplikace - MFF UK
79
Indexy

• Indexy nad sloupci
• CREATE UNIQUE INDEX jméno_indexuON
  jméno_tabulky(sloupec1, sloupec2, )
• Pr.
• CREATE INDEX Osoba_Pr_Jm_InxON
  Osoba(Prijmeni,Jmeno)
• Index lze použít v príkazu, omezujícím první
  použitý sloupec
• SELECT From OsobaWHERE PrijmeniSvoboda

DBI026 -DB Aplikace - MFF UK
80
Indexy

• Indexy nad sloupci
• CREATE UNIQUE INDEX jméno_indexuON
  jméno_tabulky(sloupec1, sloupec2, )
• Pr.
• CREATE INDEX Osoba_Pr_Jm_InxON
  Osoba(Prijmeni,Jmeno)
• Index nelze použít v príkazu, omezujícím jen
  druhý použitý sloupec
• SELECT From OsobaWHERE JmenoZdenek

DBI026 -DB Aplikace - MFF UK
81
Unikátní indexy

• Vhodnejší je definovat unikátnost pomocí
  integritních omezení PRIMARY KEY a UNIQUE
• Krome unikátních indexu jsou tak definována i
  samotná omezení

DBI026 -DB Aplikace - MFF UK
82
Indexy

• Indexy s definicí trídení
• CREATE UNIQUE INDEX jméno_indexuON
  jméno_tabulky(sloupec1 ASCDESC, )
• Definují smer trídení v jednotlivých sloupcích
• Mohou pomoci urcit výsledné poradí záznamu bez
  provádení trídících operací behem SELECT príkazu
• Pr.
• CREATE INDEX Zamestnanec_Zarazeni_Plat_InxON
  Zamestnanec(Zarazeni, Plat DESC)

DBI026 -DB Aplikace - MFF UK
83
Bitmapové indexy

• Bitmapové indexy (jsou vždy neunikátní)
• CREATE BITMAP INDEX jméno_indexuON
  jméno_tabulky(sloupec1výraz1, )
• Pr.
• CREATE BITMAP INDEX Vyuka_Den_Inx ON
  Vyuka(DenVTydnu)

DBI026 -DB Aplikace - MFF UK
84
CLUSTERED vs. NONCLUSTERED indexy

• CLUSTERED
• Nejvýše jeden defaultne primární klíc
• Pokud existuje
• Data v tabulce trídena dle sloupcu indexu (ISF)
• Ostatní indexy odkazují na hodnoty techto sloupcu
• Pokud neexistuje
• Data v tabulce netrídená (HEAP)
• Ostatní indexy odkazují na rádky
• NONCLUSTERED

DBI026 -DB Aplikace - MFF UK
85
CLUSTERED vs. NONCLUSTERED indexy

• create table nahalde(id numeric(5) identity
  (100,10) constraint nahalde_pk primary key
  NONCLUSTERED,nazev character varying(10)
  constraint nahalde_u_nazev unique)
• select object_id, name, index_id iid, type typ,
  type_descfrom sys.indexes
• object_id name iidtyp
  type_desc1357247890 kategorie_pk 1 1
  CLUSTERED1357247890 kategorie_u_naz 2 2
  NONCLUSTERED1417772108 NULL 0 0
  HEAP1417772108 nahalde_u_nazev 2 2
  NONCLUSTERED1417772108 nahalde_pk 3 2
  NONCLUSTERED

DBI026 -DB Aplikace - MFF UK
86
Index Oranized Tables

• Obdoba CLUSTERED u MS SQL
• Tabulka setrídená dle primárního klíce,celé
  rádky tvorí listovou úroven indexu
• Ostatní indexy obsahují tzv. logické
  ROWIDPrimární klíc predpokládanou adresu
• CREATE TABLE Osoba(RC VARCHAR2(11) CONSTRAINT
  Osoba_PK PRIMARY KEY,) ORGANIZATION INDEX

DBI026 -DB Aplikace - MFF UK
87
Indexy

• Zrušení indexu
• ORACLE DROP INDEX jméno_indexu
• MSSQL DROP INDEX tabulka.jméno_indexu

DBI026 -DB Aplikace - MFF UK
88
Datový slovník

• Informace o indexech jsou v Oracle uloženy v
  pohledech
• USER_INDEXES
• USER_IND_COLUMNS

• Informace o indexech jsou v MS SQL uloženy v
  pohledech
• INFORMATION_SCHEMA.INDEXES

DBI026 -DB Aplikace - MFF UK
89
Na co si dát pri návrhu pozor

• Používat správné typy indexu
• Negenerovat bez uvážení indexy pro všechny
  sloupce tabulky a jejich kombinace
• Zdržují se aktualizace dat
• Zvyšuje se nárok na diskový prostor

DBI026 -DB Aplikace - MFF UK
90
Na co si dát pri návrhu pozor

• Pri návrhu dotazu využívat prostredky daného
  serveru pro
• zjištení té nejlepší varianty dotazu
• ovlivnení zpusobu vyhodnocení dotazu
• Optimalizátory databází mají své meze
• pro nalezení optimálního plánu se používají
  heuristické postupy
• Neuvažují se zdaleka všechny kombinace poradí
  tabulek a indexu

DBI026 -DB Aplikace - MFF UK
91
Optimalizace dotazu

• Jeden dotaz lze napsat více zpusoby
• Shodná sémantika
• Rozdílný zpusob výpoctu výsledku
• Doba výpoctu se muže lišit i rádove !!
• O zpusobu výpoctu rozhoduje optimalizátor uvnitr
  databázového serveru
• Potrebné je
• Umet zjistit, jakým zpusobem výpocet probehne
• Zvolit nejlépe optimalizovatelný zápis
  dotazunebo optimalizátoru pomoci s výberem

DBI026 -DB Aplikace - MFF UK
92
Co obsahuje plán provedení

• Strom elementárních operací
• Vyhodnocuje se post-order metodou, operace v
  koreni získá celý požadovaný výsledek
• Nástin obsahu plánu
• V listech prístupy ke zdrojum
• Index UNIQUE SCAN
• Index RANGE SCAN
• Table FULL SCAN
• Ve vnitrních uzlech
• Prístupy k rádku tabulky podle indexu
• Spojení tabulek (vnorené cykly, MERGE JOIN, HASH
  JOIN)
• Trídící operace
• Filtry dle dodatecných podmínek

DBI026 -DB Aplikace - MFF UK
93
Typy optimalizace

• V Oracle
• Starší RULE BASED optimalizace (RBO)
• Odvozuje plán ze syntaxe príkazu a existence
  indexu
• Novejší COST BASED optimalizace (CBO)
• Oracle 8, doporucována pro lepší vlastnosti
• Založena na statistikách, pocítá cenu zdroju
  provedení operace (cas, prostor, trídící operace,
  )
• Dokáže rozlišit plány i pro ruzné hodnoty
  konstant v dotazu

DBI026 -DB Aplikace - MFF UK
94
Rule-Based Optimalizace dotazu

• Cena prístupu k podmnožine rádek v tabulce v
  klesajícím poradí
• Full-scan
• Prochází se celá tabulka, u každé rádky se overí
  podmínka
• Muže být vhodné, pokud procento vyhovujících
  rádek je dost velké
• Index-Range-Scan
• Vyhledání intervalu v indexu, overení ostatních
  podmínek v odkazovaných rádcích
• Unique-Index-Scan
• Vyhledání jediné možné vyhovující rádky podle
  unikátního indexu
• ROWID-Scan
• Vyhledání rádky na základe známé hodnoty jejího
  fyzického identifikátoru v databázi

DBI026 -DB Aplikace - MFF UK
95
Optimalizace dotazu

• Cena operace JOIN dvou tabulek
• Databáze se snaží zvolit tabulku s dražším
  prístupem jako hlavní tabulku
• Ke každé nalezené vyhovující rádce dohledává
  odpovídající rádky ve druhé tabulce
• Pokud obe tabulky nabízí pouze Full-Scan,data
  obou se setrídí a provede se Merge-Join

DBI026 -DB Aplikace - MFF UK
96
Optimalizace dotazu

• Jak zjistit zpusob provedení príkazu?
• Nejprve je nutné mít tabulku PLAN_TABLE s
  odpovídající strukturou, do které plánovac ukládá
  informace o plánu provedení príkazu_at_?\rdbms\admin
  \utlxplan.sql
• SQLPlus nabízí prepínacSET AUTOTRACE
  OFFONTRACEONLY
• Oracle obsahuje príkaz EXPLAIN PLAN

DBI026 -DB Aplikace - MFF UK
97
Tabulka PLAN_TABLE

• Zajímavé sloupceSTATEMENT_ID VARCHAR2(30)OPERATI
  ON VARCHAR2(30)OPTIONS VARCHAR2(30)OBJECT_OWNER
  VARCHAR2(30) OBJECT_NAME VARCHAR2(30)OBJECT_TYPE
  VARCHAR2(30)ID NUMBER(38)PARENT_ID NUMBER(38)C
  OST NUMBER(38)

• Identifikátor príkazu, shodný pro všechny rádky
  plánuOperace (SCAN, SORT, )Upresnení operace
  (FULL, RANGE, UNIQUE, )Vlastník zdrojeJméno
  zdrojeTyp zroje (TABLE, INDEX, CLUSTER,
  )Identifikace kroku v plánu s daným
  STATEMENT_IDIdentifikace rodicovského kroku ve
  stromu provedeníOdhadovaná cena

• Strom plánu se cte od listu ke koreni
• Rodicovská operace se provádí po dokoncení
  operací v potomcích
• Korenová operace má ID0

DBI026 -DB Aplikace - MFF UK
98
Príkaz EXPLAIN PLAN

• EXPLAIN PLANSET STATEMENT_ID jméno INTO
  tabulkaFOR príkaz
• EXPLAIN PLANSET STATEMENT_ID emp_dept
  FORSELECT Emp., Dept.LocFROM Dept, EmpWHERE
  Dept.DeptNo Emp.Deptno

DBI026 -DB Aplikace - MFF UK
99
Príkaz EXPLAIN PLAN

• Získání plánu provedení a) vlastním príkazem
• SELECT LPad(' ',2Level-1)operation '
  'options ' 'object_name AS text FROM
  Plan_TableSTART WITH Statement_IDpríkaz AND
  ID 0 CONNECT BY Parent_ID PRIOR ID AND
  Statement_ID PRIOR Statement_ID

DBI026 -DB Aplikace - MFF UK
100
Príkaz EXPLAIN PLAN

• Získání plánu provedení b) príkazem (verze 10)
• select plan_table_outputfrom table(
  dbms_xplan.display( 'PLAN_TABLE',statement_id
  null, 'ALL''TYPICAL''BASIC''SERIAL'
  ) )
• PLAN_TABLE_OUTPUT
• --------------------------------------------------
  --------------------------------------------------
  
• Id Operation Name
  Rows Bytes Cost
  (CPU)
• --------------------------------------------------
  --------------------------------------------------
  
• 0 SELECT STATEMENT
  96961 1893K 270
  (2)
• 1 NESTED LOOPS
  96961 1893K 270
  (2)
• 2 INDEX RANGE SCAN
  MF_CISPOLATR_SK_ATR_DO_PBCP 12 216
  3 (0)
• 3 COLLECTION ITERATOR PICKLER FETCH
  XMLSEQUENCEFROMXMLTYPE
  
• --------------------------------------------------
  --------------------------------------------------

DBI026 -DB Aplikace - MFF UK
101
Optimalizace dotazu

• Jak zjistit zpusob provedení príkazu?
• Napr. konzola ISQL nabízí možnost zobrazit
  textove plán provedení príkazu sekvencí príkazu
• set showplan_text on goltpríkazgtgo

DBI026 -DB Aplikace - MFF UK
102
Query Analyzer

• Grafická reprezentace plánu provedení
• Vložení príkazu
• Stisk CTRLL

DBI026 -DB Aplikace - MFF UK
103
Jak psát optimalizovatelné dotazy

• Obvyklá rada v aplikacích používat místo
  konstant tzv. placeholdery a aplikacní promenné
• Dva ruzné dotazy mají dva samostatné, i když
  shodné, plány provedení. Jejich vytvorení zabírá
  cas a jiné zdroje databáze
• SELECT FROM Emp WHERE DeptNo10SELECT FROM
  Emp WHERE DeptNo20
• SELECT FROM Emp WHERE DeptNod
• Nekdy ovšem mohou dva plány, získané s využitím
  CBO pomoci, (pokud se princip provedení
  oduvodnene liší).
• SELECT FROM Vojaci WHERE PohlaviM
• SELECT FROM Vojaci WHERE PohlaviŽ

90 dat (full s.)
10 dat (range s.)
DBI026 -DB Aplikace - MFF UK
104
Jak psát optimalizovatelné dotazy

• Jeden dotaz psát všude presne stejne
• Ruzné zápisy databáze vždy znovu analyzuje a
  vymýšlí plán provedení
• SELECT FROM Emp WHERE Ename LIKE A AND
  DeptNo10
• SELECT FROM EmpWHERE DeptNo10 AND Ename LIKE
  A

DBI026 -DB Aplikace - MFF UK
105
Nevýhody rule-based optimalizace

• Pokud existuje více neunikátních indexuna jedné
  tabulce,nemusí (RBO) optimalizátor vybrat ten
  nejlepší
• SELECT FROM OsobaWHERE JmenoJan AND
  MestoPraha
• Budto se pres index hledají Janové, a overuje se
  bydlište, nebo se hledají Pražáci a overuje se
  jméno

DBI026 -DB Aplikace - MFF UK
106
Nevýhody rule-based optimalizace

• Použití urcitého indexu je možné optimalizátoru
  znemožnit použitím (i neutrálního) výrazu v
  dotazu
• SELECT FROM OsobaWHERE CONCAT(Jmeno,)Jan
  AND MestoPraha
• Index pres jméno nelze použít, použije se tedy
  index pres mesto
• Pozn. sofistikovanejší optimalizátor by mohl
  takovéto úpravy odhalit a dotaz prepsat

DBI026 -DB Aplikace - MFF UK
107
Cost-based optimalizace

• Pro jednotlivé plány se pocítá cena provedení v
  rade hledisek
• Množství I/O operací, rádek, Byte,
• Cena provádených trídících operací
• Cena za HASH operace
• Vybírá se plán s nejnižší váženou cenou

DBI026 -DB Aplikace - MFF UK
108
Cost-based optimalizace

• Využívá statistických informací o datech
• Pocet ruzných hodnot ve sloupci,Histogramy
  rozložení hodnot ve sloupci,Pocet rádek v
  tabulce,Prumerná délka jedné rádky
• Pro konkrétní hodnotu nebo interval hodnotlze
  odhadnout
• procento vyhovujících rádek v tabulce
• jejich datový objem

DBI026 -DB Aplikace - MFF UK
109
Cost-based optimalizace

• V Oracle CBO dovoluje používat indexy pres
  výrazy (RBO je nepodporuje)
• CREATE INDEX Emp_Income_INXON Emp(SalCOALESCE(Co
  mm,0))
• Dotaz se shodným zápisem výrazu muže index použít
• SELECT EName FROM EmpWHERE SalCOALESCE(Comm,0)
  gt 25000
• Dotaz s upraveným zápisem index použít nedokáže
• SELECT EName FROM EmpWHERE COALESCE(Comm,0)Sal
  gt 25000

DBI026 -DB Aplikace - MFF UK
110
Výber typu optimalizace

• Výber optimalizace
• ALTER SESSION SET OPTIMIZER_MODE)
• CHOOSE výber podle (ne)prítomnosti
  statistiknejsou-li k dispozici, potom RBO, jinak
  CBO
• ALL_ROWS vždy CBO, minimalizuje se cenaza
  získání všech rádek odpovedi
• Vhodné pro dávkové zpracování.
• FIRST_ROWS vždy CBO, minimalizuje se cenaza
  získání prvních rádek odpovedi.
• Vhodné pro interaktivní zpracování.
• RULE vždy RBO
• ) Pozn. Starší syntaxe OPTIMIZER_GOAL

DBI026 -DB Aplikace - MFF UK
111
Tvorba statistik

• ANALYZE TABLE jm_tabulkyCOMPUTE ESTIMATE
  DELETE STATISTICSFOR TABLE ALL INDEXED
  COLUMNS
• DBMS_UTILITY.ANALYZE_SCHEMA( jm_schematu,com
  pute delete estimate )
• DBMS_STATS.GATHER_SCHEMA_STATS(jm_sch)
• Pohledy v datovém slovníku
• INDEX_STATS,USER_TAB_COL_STATISTICSUSER_USTATS

DBI026 -DB Aplikace - MFF UK
112
Tvorba statistik

• Standardne povolen prepínac AUTO_CREATE_STATISTICS
  
• Statistiky se generují automaticky
• ALTER DATABASE dbname SET AUTO_CREATE_STATISTICS
  ONOFF
• Rucne pomocí
• sp_createstats
• Pr. vytvorení dodatecné statistiky pro
  dvousloupcovou hodnotu na základe vzorku dat
• CREATE STATISTICS FirstLast ON Person.Contact(Firs
  tName,LastName) WITH SAMPLE 50 PERCENT

DBI026 -DB Aplikace - MFF UK
113
Co statistiky obsahují

• Tabulky
• Pocet rádek
• Pocet datových bloku na rádku
• Sloupce
• Pocet ruzných hodnot
• Pocet NULL hodnot
• Histogramy hodnot (kolik hodnot pripadne do
  daného intervalu)

DBI026 -DB Aplikace - MFF UK
114
Nápoveda optimalizátoru (hinty)

• Pomocí plusových komentáru bezprostredne za
  prvním klícovým slovem príkazu SELECT/UPDATE/INSE
  RT/DELETE
• SELECT -- seznam hintu
• SELECT / seznam hintu /
• Možné použít pro volbu typu optimalizace
• SELECT / RULE / FROM EMP
• SELECT / FIRST_ROWS / FROM EMP
• Použití hintu (krome RULE) vždy použije CBO na
  základe statistik. Pokud nejsou statistiky
  spocteny, výsledek optimalizace je
  kontraproduktivní.

DBI026 -DB Aplikace - MFF UK
115
Nápoveda optimalizátoru (hinty)

• Základní nastavení optimalizátoru
• CHOOSE
• Optimalizátor vybere metodu dle
  prítomnosti/neprítomnosti statistik
• RULE
• Optimalizátor použije optimalizaci založenou na
  pravidlech i v prípade, že má k dispozici
  statistiky
• ALL_ROWS
• Optimalizátor se bude snažit minimalizovat cenu
  za zpracování všech rádek v dotazu ci príkazu
• FIRST_ROWS, FIRST_ROWS(n)
• Optimalizátor bude minimalizovat cenu za získání
  první, resp. prvních n rádek

DBI026 -DB Aplikace - MFF UK
116
Nápoveda optimalizátoru (hinty)

• Další možnosti (výber dat z tabulek)
• FULL(jm_tabulky)
• Full-scan pro tabulku
• INDEX (jm_tabulky jm_indexu)
• Pro pruchod tabulkou se použije požadovaný index
• NO_INDEX (jm_tabulky jm_indexu)
• Pro pruchod tabulkou se nepoužije požadovaný
  index
• ORDERED
• Pri spojování tabulek se použije poradí uvedené
  ve FROM
• USE_NL, USE_MERGE, USE_HASH
• Spojení pomocí vnorených cyklu, merge-join,
  hash-join

DBI026 -DB Aplikace - MFF UK
117
Nápoveda optimalizátoru (hinty)

• FULL(jm_tabulky)
• SELECT / FULL(Emp) / EmpNo, EnameFROM
  EmpWHERE ENamegtX
• Použít FULL SCANi pres prípadne malé procento
  vracených rádek
• Pokud má tabulka alias, je v hintu tento alias,
  to dovoluje mít v dotazu jednu tabulku vícekrát a
  nápovedu cílit na konkrétní výskyt

DBI026 -DB Aplikace - MFF UK
118
Nápoveda optimalizátoru (hinty)

• INDEX(jm_tabulky index index )
• SELECT -- INDEX(Emp ENameInx EDeptInx) EmpNo,
  EnameFROM EmpWHERE EName LIKESC AND
  DeptNogt50
• Použít ten z uvedených indexu, který nabízí
  nejnižší cenu provedení operace a ostatní
  prípadné indexy nad tabulkou neuvažovat

DBI026 -DB Aplikace - MFF UK
119
Nápoveda optimalizátoru (hinty)

• NO_INDEX(jm_tabulky index index )
• SELECT -- NO_INDEX(Emp ENameInx) EmpNo,
  EnameFROM EmpWHERE EName LIKESC AND
  DeptNogt50
• Index pres EName se nebude pro vytvorení plánu
  uvažovat

DBI026 -DB Aplikace - MFF UK
120
Nápoveda optimalizátoru (hinty)

• ORDERED
• SELECT -- ORDERED EmpNo, EnameFROM Emp,
  DeptWHERE
• Tabulky se budou spojovat v poradí, v jakém jsou
  uvedené ve FROM klauzuli
• Pri plánování ušetrí cas, jinak nutný pro další
  možná poradí spojování

DBI026 -DB Aplikace - MFF UK
121
Nápoveda optimalizátoru (hinty)

• SELECT OPTION (hint )
• Hintem muže být  HASH ORDER GROUP
  CONCAT HASH MERGE UNION    LOOP MERGE
  HASH JOIN FAST number_rows FORCE ORDER
  MAXDOP number_of_processors OPTIMIZE FOR (
  _at_variable_name UNKNOWN literal_constant
  , ...n )

DBI026 -DB Aplikace - MFF UK
122
Nápoveda optimalizátoru (hinty)

•   HASH ORDER GROUP
• Zpusob realizace GROUP BY operace
• CONCAT HASH MERGE UNION
• Zpusob realizace spojení výsledku dvou SELECT
  príkazu s vynecháním duplicit
• LOOP MERGE HASH JOIN
• Zpusob realizace spojování tabulek
• FAST number_rows
• Dotaz optimalizován na rychlé získání prvních
  number_rows rádek

DBI026 -DB Aplikace - MFF UK
123
Nápoveda optimalizátoru (hinty)

• FORCE ORDER
• Zachování poradí spojování tabulek tak, jak jsou
  v SELECTu
• MAXDOP number_of_processors
• Omezení maximálního stupne paralelismu v dotazu
• OPTIMIZE FOR ( _at_variable_name UNKNOWN
  literal_constant , ...n )
• Pokud príkaz obsahuje promennou, predpokládá se
  nejaká konkrétní hodnota, nebo naopak neznámá
  hodnota

DBI026 -DB Aplikace - MFF UK
124
Nápoveda optimalizátoru (hinty)

• FORCE ORDER
• Zachování poradí spojování tabulek tak, jak jsou
  v SELECTu
• MAXDOP number_of_processors
• Omezení maximálního stupne paralelismu v dotazu
• OPTIMIZE FOR ( _at_variable_name UNKNOWN
  literal_constant , ...n )
• Pokud príkaz obsahuje promennou, predpokládá se
  nejaká konkrétní hodnota, nebo naopak neznámá
  hodnota

DBI026 -DB Aplikace - MFF UK
125
INCLUDE dbacv_02.ppt INCLUDE dbacv_03.ppt

• Optimalizace
• Príklady

126
Pohledy
127
Pohledy nad relacními tabulkami

• Pohled pojmenovaný SELECT príkaz
• Reprezentuje virtuální tabulku
• Pri dotazu nad pohledem se pohled vždy znovu
  rozvíjí v rámci položeného dotazu

DBI026 -DB Aplikace - MFF UK
128
Vytvárení pohledu

• PríkazemCREATE VIEW jmeno_pohledujmeno_sloupce
  , ASSELECT WITH CASCADE LOCAL
  CHECK OPTION

DBI026 -DB Aplikace - MFF UK
129
Vytvárení pohledu

• PríkladCREATE VIEW Obyvatel_BrnaASSELECT
  FROM ObyvatelWHERE MestoBrno

DBI026 -DB Aplikace - MFF UK
130
Vytvárení pohledu

• Sloupce mohou být explicitne pojmenované
• Pokud nejsou, zdedí jména sloupcu ze SELECT
  príkazu
• WITH CHECK OPTION zajistí, aby vkládané ci
  aktualizované rádky po provedení odpovídaly
  podmínce v pohledu
• Pokud by rádka nebyla v pohledu
  viditelná,aktualizace je odmítnuta

DBI026 -DB Aplikace - MFF UK
131
Vytvárení pohledu

• PríkladCREATE VIEW Obyvatel_BrnaASSELECT
  FROM ObyvatelWHERE MestoBrnoWITH CHECK
  OPTION-- chybná aktualizaceUPDATE
  Obyvatel_Brna SET MestoOstravaWHERE
  RC751015/1234

DBI026 -DB Aplikace - MFF UK
132
Vytvárení pohledu

• Použití pohledu
• Odstínení nutnosti spojení více (normalizovaných)
  tabulek
• Individualizace zobrazených dat pro uživatele
• Ruzní uživatelé mohou ve stejném pohledu videt
  ruzné rádky
• Ruzní uživatelé mohou mít definován stejný pohled
  ruzne a zobrazovat jiné sloupce

DBI026 -DB Aplikace - MFF UK
133
Vytvárení pohledu

• PríkladCREATE VIEW Zam_Moje_OddASSELECT
  Z.Cislo, Z.Prijmeni, Z.Jmeno, Z.ZarazeniFROM
  Zamestnanec AS Z, Zamestnanec AS XWHERE
  X.LoginCURRENT_USER AND Z.OddeleniX.Oddeleni

DBI026 -DB Aplikace - MFF UK
134
Vytvárení pohledu

• Nekterým nevhodne napsaným aplikacím muže posun v
  poradí ve sloupcích pohledu (nebo i samotné
  zvýšení poctu sloupcu) prinést problémy
• Pri vytvárení pohledu je obvykle vhodné zajistit,
  aby se spolu s pridáváním sloupcu do tabulek
  neposunovaly sloupce stejného významu na jiné
  pozice v seznamu sloupcu
• Explicitne vyjmenovat sloupce v SELECT príkazu
• Navíc vhodné všechny sloupce kvalifikovat, budto
  jménem tabulky, nebo jejím aliasem
• Explicitne pojmenovat sloupce pohledu

DBI026 -DB Aplikace - MFF UK
135
Vytvárení pohledu

• Jak zabránit posunu sloupcu v pohledech, které
  používají symbol pro zobrazení všech rádek?
• V pohledu nad více pospojovanými tabulkami je
  vhodné použít maximálne jednou, pro sloupce
  konkrétní tabulky (použitím kvalifikátoru jmena
  tabulky jm_tabulky.).
• Za ní už by nemely být žádné sloupce vyjmenované.

DBI026 -DB Aplikace - MFF UK
136
Vytvárení pohledu

• PríkladCREATE VIEW Zam_OddASSELECT Z.RC,
  Z.Jmeno, O.FROM Zamestnanec AS Z NATUAL
  RIGHT OUTER JOIN Oddeleni AS O

DBI026 -DB Aplikace - MFF UK
137
Vytvárení pohledu

• Není vhodné do pohledu dávat ORDER BY klauzuli
• Ne vždy je dotaz nad pohledem nutné trídit
• Nekdy je potreba zcela jiné trídení
• V rade prípadu se více pohledu spojuje v dotazu
  dohromady a trídení každého z nich muže zdržovat

DBI026 -DB Aplikace - MFF UK
138
Vytvárení pohledu

• Pri konstrukci dot