Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 0
Jun 022013
 

Musím říct, že jsem několikrát ve svém životě potřeboval z DB vygenerovat nějaký report a pokaždé když došlo na potřebu grafické reprezentace, snažil jsem se tomu zuby nehty vyhnout. Jenže obrázky jsou sexy, ne?  Takže můj cíl je nyní generovat obrázky z databáze. Zatím pouze obrázky a pouze *.BMP. Mě by se totiž mnohem více hodila komprimovaná videa, nicméně je třebas dekomponovat. Ostatně generování  *.BMP bude mít jednu krásnou vlastnost – bude možné vidět případný sub-výsledek až bych třebas psal JPEG komprimaci v PL/SQL. Mnohem rychlejší by bylo napsat to v Javě a javu si provolat či jí přímo loadnout do Oracle – funkční, rychlé, efektivní – ale nudné.

Vím o jednom problému, který mi čeká a o kterém ještě nemám úplnou představu jak ho řešit – je třeba do obrázku mimo kreslení psát, což znamená mít představu jako takové písmeno (“A”) bude v bitmapě vypadat a zatímco v Javě to stačí drawStringnout na Canvas (či Canvas2d) tady to bude vyžadovat mnohem více. Což třebas povede na nějaké čtení fontů(?) a loadování do DB a o to to bude zajímavějšejší, inu, uvidíme.

Takže hurá do hébičkování !

Bitmapa (jako BMP) vypadá jako soubor pixelů a nic víc. Nicméně i tak má BMP soubor nějakou hlavičku, kterou je třebas zapsat: Při čtení článku na wiki jsem dokonce zjistil, že BMP umožňuje Huffamanovo kódování a tedy bezztrátovou komprimaci v jisté omezené podobě, což jsem do dneška netušil.

http://en.wikipedia.org/wiki/File:BMPfileFormat.png

Což znamená, že bílý jednopixelový (1×1) obrázek nejsou 3 bajty (24 bitů – 8×3 barvy), ale 58 bajtů:

Jeden bilý pixel v BMP

Pro pochopení jsem si vyzkoušel nějakou úpravu (modře) – konkrétně jsem z 01 přepsal pixely na 02 (2x 2x) a přidal 12 bajtů:

Čtyři pixely v BMP (uprava v hexaeditoru)

Fajn, to by zafungovalo, takže společně s obrázkem z wikipedie už je asi pomalu možné nějaké BMP začít tvořit.  Nejprve nastřelení nějakého minimálního kódu, že je to správný směr:

1) Založení adresáře, kam se budou cpát BMP soubory:
CREATE DIRECTORY output_bmp AS 'F:\bmp_output';

2) Úplně minimální kód na zkoušku:
declare
bmp_file utl_file.file_type;
un_used_1 RAW(4):=hextoraw('42')||hextoraw('4d');
begin
bmp_file:=utl_file.fopen('OUTPUT_BMP','test.bmp','wb');
utl_file.put_raw(bmp_file,un_used_1,true);
if utl_file.is_open(bmp_file) then
utl_file.fclose_all;
end if;
end;

Je to opravdu minimální kód, který do souboru test.bmp zapíše pouze 42 a 4D (hex), což odpovídá ascii textu ‘BM‘ a je to první část hlavičky bitmapového souboru. Což v hexaeditoru (použivám WinHex 17.0 a vřele doporučuji – umí přímý zápis na disk i do RAM), vypadá takto:

První část hlavičky BMP souboru

Fajn, to by bylo a nyní minimální kód, pro vygenerování nejjednoduššího BMP – hlavičku, jsem sprostě opráskl z wikipedie:

declare
/* uvodni BMP hlavička BMP */
static_magic_header RAW(2):=hextoraw('42')||hextoraw('4d');
/* velikost obrázku v B */
variable_image_size RAW(4):=hextoraw('46')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* aplication specific - takze se podepišu AZOR - AZ */
static_aplication_1 RAW(2):=hextoraw('41')||hextoraw('5A');
/* aplication specific - takze se podepišu AZOR - OR */
static_aplication_2 RAW(2):=hextoraw('4F')||hextoraw('52');
/* kde končí hlavička */
static_head_end RAW(4):=hextoraw('36')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* délka DIB hlavičky */
static_dib_size RAW(4):=hextoraw('28')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* sířka obrázku */
variable_image_width RAW(4):=hextoraw('02')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* výška obrázku */
variable_image_height RAW(4):=hextoraw('02')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* počet planes */
static_image_planes RAW(2):=hextoraw('01')||hextoraw('00');
/* počet barev - 24 bitová paleta */
static_image_bits RAW(2):=hextoraw('18')||hextoraw('00');
/* bi_rgb */
static_bi_rgb RAW(4):=hextoraw('00')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* velikost pixel array (dat) - a BEZ KOMPRSE*/
variable_raw_size RAW(4):=hextoraw('10')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* pixels/metters - horizontalne i vertikalne pouziju jednu promennou */
static_pix_metter RAW(4):=hextoraw('13')||hextoraw('0B')||hextoraw('00')||hextoraw('00');
/* nezbytné barvy - nulla */
static_colors RAW(4):=hextoraw('00')||hextoraw('00')||hextoraw('00')||hextoraw('00');
/* A KONEČNĚ JEDNOTLIVÉ PIXELY (prvně však separátor) */
static_pix_separator RAW(2):= hextoraw('00')||hextoraw('00');
data_pix_1_1 RAW(3):=hextoraw('00')||hextoraw('FF')||hextoraw('FF');
data_pix_1_2 RAW(3):=hextoraw('00')||hextoraw('00')||hextoraw('FF');
data_pix_2_1 RAW(3):=hextoraw('FF')||hextoraw('00')||hextoraw('FF');
data_pix_2_2 RAW(3):=hextoraw('FF')||hextoraw('00')||hextoraw('FF');
bmp_file utl_file.file_type;
begin
bmp_file:=utl_file.fopen('OUTPUT_BMP','test_2.bmp','wb');
/* postupné zapisování všech hlaviček */
utl_file.put_raw(bmp_file,static_magic_header,true);
utl_file.put_raw(bmp_file,variable_image_size,true);
utl_file.put_raw(bmp_file,static_aplication_1,true);
utl_file.put_raw(bmp_file,static_aplication_2,true);
utl_file.put_raw(bmp_file,static_head_end,true);
utl_file.put_raw(bmp_file,static_dib_size,true);
utl_file.put_raw(bmp_file,variable_image_width,true); -- šířka
utl_file.put_raw(bmp_file,variable_image_height,true); -- výška
utl_file.put_raw(bmp_file,static_image_planes,true);
utl_file.put_raw(bmp_file,static_image_bits,true);
utl_file.put_raw(bmp_file,static_bi_rgb,true);
utl_file.put_raw(bmp_file,variable_raw_size,true);
utl_file.put_raw(bmp_file,static_pix_metter,true);
utl_file.put_raw(bmp_file,static_pix_metter,true);
utl_file.put_raw(bmp_file,static_colors,true);
utl_file.put_raw(bmp_file,static_colors,true);
/* a konečně data */
utl_file.put_raw(bmp_file,data_pix_1_1,true);
utl_file.put_raw(bmp_file,data_pix_1_2,true);
/* separátor řádku */
utl_file.put_raw(bmp_file,static_pix_separator,true);
/* druhý řádek */
utl_file.put_raw(bmp_file,data_pix_2_1,true);
utl_file.put_raw(bmp_file,data_pix_2_2,true);
/* ukončení souboru */
utl_file.put_raw(bmp_file,static_pix_separator,true);
/* a zavřít soubor */
if utl_file.is_open(bmp_file) then
utl_file.fclose_all;
end if;
end;

A wohoho – 4pixelový výsledek uložený v souboru test_2.bmp vypadá po zvětšení takto :

Vygenerované BMP

Vygenerované BMP

Bohužel jsem změnil bezhlavě barvu a vypadla z toho růžová :/, zrovna taková hloupá barva. Kód rozhodně není hezký a v nějakém dalším díle ho upravím. Zatím jsem vytipoval části hlaviček, které nebudu měnit (prefix static_) a ty které se s velikostí obrázku budou muset měnit a přepočítávat (prefix variable_). Vygenerovaný obrázek vypadá pak v hexaeditoru:

První BMP v Oracle zobrazená v WinHex

První BMP v Oracle zobrazená v WinHex

Tak a to by bylo pro tento díl všechno ;) V druhém to navrhuji vykrášlit a udělat nějakou funkci, která mi z BLOBu vrátí přímo hlavičku. A taky funkci, která vygeneruje prázdný obrázek. Ještě s tím bude spousta legrace.

 Posted by at 21:36
Apr 242013
 

Potyčky s dlouhotravajícími selecty zná asi každý. Nejdůležitější je rychle a především přesně identifikovat  tu největší pain v daném selectu a začít u ní. Protože až po nalezení problémového místa může teprve přijít nějaká ta optimalizace -  přepis selectu, založení indexu, ohintování atd. Takže dnes pár tipů jak zjistit, co databáze dělá na  právě běžícím selectu a tedy jak identifikovat problémové místo či problémová místa.

Především je třeba správně  umět chápat “cost” v exekučním plánu, řádek s největším costem totiž rozhodně nemusí být problém a typicky ani není. Proč okamžitě neobvinit řádek exekučního plánu s největším costem, když nás trápí doba běhu selectu je doufám jasné a plyne to z  toho, co cost představuje. Spíš si myslím, že je třeba vysvětlit větu, proč si myslím, že tenhle to zrovna nebude. Záleží hodně na systému, v dávkových systémech, které jsem doposud potkával já tomu bylo tak, že estimátor spíše počet vrácených řádků do dalšího stupně exekučního plánu podhodnocuje než nadhodnocuje. Konkrétní  číslo, které se dá vyjet jednoduchým selectem je 2,58x. Tedy je 2,58x pravděpodobnější, že Oracle počet vrácených  řádků v exekučním plánu podhodnotí – pochopitelně v závislosti na systému, tohle je číslo jednoho konkrétního

Důvodů proč se asi člověk bude na batch systémech bude setkávát spíše s podhonocením  se asi najde několik. Obecně je tomu tak, že máme nějakou představu o datech a sloupce jsou nějakým způsobem téměř vždy korelovány,  což o proti náhodným datům a operátoru AND, kdy estimátor selectivity podmínek pronásobí opravdu spíše vede k podhodnocení. Podobně rozdělení do 4 threadu ORA_HASH(id,3) je funkce se selektivitou 25%, ale  defalutní selektivita pro funkce je 1%. Jinými slovy – záleží na systému a pokud tam programátor nesedí prvně, měl by mít nějakou představu -já by default očekávám, že další stupně exekučního plánu budou mít jako vstup počet řádků podhodnocen a tedy i cost bude menší než ve skutečnosti – a to nejspíše budou ty řádky, které budou trápit ;) Podhodnocení počtu očekávaných řádků vede logicky k použití indexu, tam kde to není úplně nejlepšejší..

A nyní tipy jak sledovat, co se tam děje:

1) Pohled v$session_longops

select * from v$session_longops where sid=&mysid order by start_time desc;

Velice známé view, která použije semtam nějaké IDE na zobrazení progressu či programátor. Zde je důležité správně hodnoty intepretovat. Především do view nejdou všechny operace, ale jen některé (dynamic sampling, spočet statistik, přístupy k objektům (Index/Table (Fast/Skip/)(Range/Full) Scany), Sorty, Hash join a pár dalších) Další věc – do view jdou operace trvající déle než 6 sekund – to znamená, že pokud Index Range Scan operace trvá v Nested loopě několik hodin, ovšem pro každou interaci se to vejde pod 6s tak v$session_longops nezobrazí nic ačkoliv dlouhotrvající Index Range Scan do tohoto view jde. Pozor na špatnou intepretaci těch nejzajímavějších sloupců sofar a totalwork (a jejich časové analogie). Protože výraz to_char(round((sofar/totalwork)*100)) ||’ %’ vypadá, že by mohl ukazovat kolik % již je hotovo. Ukazuje, ale ne však vždy – například pokud je tabulka partišnovaná a select je jen z jedné partition,  pak totalwork ukazuje počet datových bloků pro selectovanou partition v případě, že je známá v době optimalizace (static partition prunning, where x=’X’). Pokud je konkrétní partiton známá až za běhu selectu (dynamic partition pruning, where x=(select dummy from dual)) pak totalwork je průměrný počet datových bloků všech partition tabulky). Jinými slovy do totalwork mohou vstupovat statistická data, průměry i odhady, na což je třebas myslet, když je v v$session_longops řádek hotový na 178% a stále to pokračuje ;)

A těch příkladů by se našlo více, dalším jsou například dvě a více analytické funkce v projection ukazující se v exeukčním plánu stále jako jeden řádek. Sice to vypadá jako, že to trvá jeden krok (sort operace), ale v totalwork je vidět, že počet řazených bloku s každou analytickou funkcí, může růst (až dvojnásobek, podle sloupců order by) a tedy ne, neni to zadarmo ;)

Suma sumárum: jednoduché view ve kterém toho moc není a kde je třeba umět správně intepretovat  hodnoty. Co je fajn, je možnost vlastního záznamu v v$session_longops pomocí API v dbms_application_info.  Další fajn věcička je, že se tam propisují í některé dlouhotrvající tooly Oracle (backupy, přepočet statistik)

2) Pohled v$active_session_history

Tohle view je mým favoritem. View jako takové neslouží ke sledování vytížení ani běhu sql, ale obsahuje snpashoty v$session po 1 vteřině včetně toho, co zrovna daná session dělá. Stačí tedy řádky zgrupovat podle sql_plan_line_id a count(*) řádků ukazuje kolik sekund na daném řádku session trávila či ještě tráví:

select count(*),his.sql_plan_line_id, his.sql_plan_operation,his.sql_plan_options from v$active_session_history his where his.SQL_ID='sql id' and sample_time>sysdate-1 group by his.sql_plan_line_id,his.sql_plan_operation,his.sql_plan_options;

Suma sumárum: Jednoduché, krásné, efektivní. Ve většině případů dostačující. Ne moc vhodné pro sledování paralelních selectů. V$active_session_history rocks!

3) Pohled v$sql_plan_monitor

V případě, že v$active_session_history je nedostatečné, je tu ještě vyšší kalibr : v$sql_plan_monitor. View do kterého jdou SQL trvající déle než 5 sekund (neni to překlep, sem po 5s do longops 6s, dle Oracle), paralelní selecty a selecty s hintem /*+ MONITOR */. A nejdou všechny ostatní – neparalelní kratší než 5s a případně s inverzním hintem /*+ NO_MONITOR */.

select * from v$sql_plan_monitor where sql_id=‘sql_id’;

V tomhle view je vidět téměř vše co je potřeba - doba potřebná pro danou operaci, jak jdou operace za sebou, jak velké byly zprávy které si mezi sebou posílaly paralelní části, počet řádků vystupujících z daného kroku, potřebná paměť, velikost tempu pro danou operaci, počet iterací nested loopy atd.

Suma sumárum: V některých případech hůře čitelné než v$active_session_history, ale lépe se tam odhalují vnořené nested loopy a je to podstatně vhodnější pro sledování paralelních selectů. Velká nevýhoda je doba po kterou v tomto view selecty zůstávají (velice krátce – desítky minut). Nejlepšejší vychytávka: Možnost zjisti v jaké nested loop iteraci zrovna Oracle je.

4) Pohled v$sesstat

Úžasné view se spoustou statistik (pro všechny session). Dokonce tolik statistik, že pro sledování běžícího selectu je vhodné dělat pomocí CTAS:

create table actual_stats as select sid,s.statistic#,value,name,class from v$sesstat s join v$statname sp
on (s.statistic#=sp.statistic#)
where s.sid=143;

A po nějaké chvilce to odečíst, aby bylo vidět co se mění:

select sid,s.statistic#,value,name,class from v$sesstat s join v$statname sp
on (s.statistic#=sp.statistic#)
where s.sid=143;
minus select * from actual_stats;

Tohle je spíše doplňková informace pro odhalení případných podezření na operace, které nejsou na první pohled vidět a nebo na detailní rozpad.

Suma sumárum: O proti ostatním pohledum není samo o sobě vhodné pro identifikaci problému v selectu (neukazuje na kterém objektu, jaký exekuční plán apod.), nicméně pomůže v případech, které nejsou patrné v ostatních view. Typickým příkladem by mohl být chybějící index v cizím klíči – full table scan, který je nutný k dodžení referenční integrity v exekučním plánu vidět není,
ve statistikách se však objeví.

 

Tak to by bylo pro dnešek vše ;) Tipy se snad hodí.. alespoň pro některé systémy – pro OLTP se to moc nehodí ;)

 Posted by at 22:16
Mar 092013
 

Dnešní rychlotip s číslem 20 je věnován vcelku neznámému, ale o to více užitečnému, příkazu CREATE SCHEMA.  Možná to má lehce matoucí název – nemá to relaci na vytvoření schéma jako takového a klasicky schéma je vytvořeno pomocí CREATE USER. Příkaz CREATE SCHEMA slouží k vytvoření několika objektů (tabulek, view a grantů) v jedné transakci. Což se typcky hodí při nějakém updatu systému/nasazení RFC – kdy je třeba nasadit více tabulek – pomocí CREATE SCHEMA je od Oracle zaručeno, že to proběhne “jako transakce” – tedy všechno nebo nic. Normálně je CREATE TABLE příkaz typu DDL, takže se při releasu může povéct založit jen nějaké tabulky a ostatní vyhodí chybu – což bohužel vykazuje nějaký effort na srovnaní nebo to všechno vydropovat, vylézt zpět na strom a spustit to celé znovu.

Tedy jednou větou – CREATE SCHEMA umí v jedné transakci založit více objektu v jedné transakci, konkrétně:

  • CREATE TABLE
  • CREATE VIEW
  • GRANT

Podporovaných DDL je opravdu málo a navíc neexistuje inverzní operace DROP SCHEMA, ač v SQL-1999 standard tento příkaz podporuje.

Jediné omezení je na CRETE TABLE – parallel je povoleno, ale ve skutečnosti parallně tabulky v CREATE SCHEMA nejsou zakládány a běží to seriově.

CREATE SCHEMA AUTHORIZATION dot
CREATE TABLE TEST_A (A NUMBER)
CREATE TABLE TEST_B as select 1 FROM DUAL
CREATE VIEW TEST_C as select * from all_objects
GRANT SELECT ON TEST_A TO SCOTT;

Synaxe je jednoduchá CREATE SCHEMA AUTHORIZATION <user_schema> a pak seznam tabulek, view a grantu bez středníku, středník až na konci, který zavírá statement CREATE SCHEMA.

Takže po spuštění:

SQL Error: ORA-02425: nepodařilo se vytvořit tabulku
ORA-00998: tento výraz je třeba pojmenovat alternativním jménem sloupce
02425. 00000 - "create table failed"

Protože jsem jelito a hajdalák a v TEST_B selectuji jedničku z dualu a nedal jsem tam alias, nicméně alespoň důkaz, že opravdu CREATE SCHEMA funguje a nezaložila se tabulka TEST_A ani nic jiného:

Create schema - Fail

Create schema - Fail

Takže po opravě:

CREATE SCHEMA AUTHORIZATION dot
CREATE TABLE TEST_A (A NUMBER)
CREATE TABLE TEST_B as select 1 as haf FROM DUAL
CREATE VIEW TEST_C as select * from all_objects
GRANT SELECT ON TEST_A TO SCOTT;

Je výstup následující:

schema AUTHORIZATION created.

A nyní test, že se všechno založilo i nagrantovalo:

Create schema- Success

Create schema- Success

Pro nasazovací scripty šikovná věcička, co?

 Posted by at 23:08
Feb 162013
 

Minutý rychlotip byl o funkci DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL, která vytvoří z hodnot sloupců všechny možné množiny a spočítá jejich výskyt zkrt všechny řádky – funkce zajimavá, o praktickém použití v denním programování to však není. Balík DBMS_FREQUENT_ITEMSET obsahuje ještě jednu pipelined funkci - FI_TRANSACTIONAL.  Signatura funkce je stejná jako u FI_HORIZONTAL:



DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL (
 tranx_cursor IN SYSREFCURSOR,
 support_threshold IN NUMBER,
 itemset_length_min IN NUMBER,
 itemset_length_max IN NUMBER,
 including_items IN SYS_REFCURSOR DEFAULT NULL,
 excluding_items IN SYS_REFCURSOR DEFAULT NULL)
RETURN TABLE OF ROW (
 itemset [Nested Table of Item Type DERIVED FROM tranx_cursor],
 support NUMBER,
 length NUMBER,
total_tranx NUMBER);

Zatímco první funkce FI_HORIZONTAL tvoří množiny všech množin z hodnot ve sloupích a výskyt množin pak počítá skrz řádky, FI_TRANSACTIONAL vypadá, že by se mohla chovat opačně, respektive být otočená o 90% – tvořit množiny všech množin zkz sloupce a počítat je zkz řádky – není tomu tak! Narozdíl od FI_HORIZONTAL kam může vstupovat select, který má v projection libovolný počet sloupců (ale stejného typu) do FI_TRANSACTIONAL může vstupovat do parametru tranx_cursor pouze select jež má v projection pouze dva sloupce  – (number,<nějaký_jednoduchý_typ>), kde první sloupce typu number určuje skupinu zkz kterou se mají generovat všechny množiny a ve druhém jsou hodnoty.

Takže go, hébičky go, pojďme si to zkusit:
Tabulka je velice podobná jako zde, s tím rozdílem, že jsem se rozhodl přidat ještě jeden sloupec – name_6:

create table tbl_drks
(name_1 varchar2(20),
name_2 varchar2(20),
name_3 varchar2(20),
name_4 varchar2(20),
name_5 varchar2(20),
name_6 varchar2(20));

A nyní insert nějakých dat (stejné jako minule + sloupec name_6):

insert into tbl_drks values('RedBull','BigShock','RockStar',null,'Monster','A');
insert into tbl_drks values('BigShock','RedBull',null, null,'Monster','B');
insert into tbl_drks values('BigShock','RedBull','BigShock',null,'RedBull','C');
insert into tbl_drks values('BigShock','RedBull',null, null,'Monster','D');
insert into tbl_drks values('Monster','RedBull',null, null,'Monster','E');
insert into tbl_drks values('Monster','RedBull','RockStar','BigShock','Monster','F');
insert into tbl_drks values('RockStar','RedBull','BigShock','BigShock','Monster','G');
insert into tbl_drks values('RockStar','RedBull','RockStar','RockStar','Monster','H');
commit;

Což v tabulce pak vypadá nějak takto:

Vstupní data pro DBMS_FREQUENT_ITEMSET.fi_transactional

Funkce DBMS_FREQUENT_ITEMSET.fi_transactional povoluje tedy jako vstup select s projection se sloupcem typu number a druhým sloupcem s hodnotou, tedy nebude stačit jen select * from tbl_drinks, ale je třeba to rozdělit po skupinách – každý sloupec jako jiná skupina, nejjednodušeji tedy pomocí union all:

SELECT CAST(itemset as typ_name)itemset, support, length, total_tranx
FROM table(DBMS_FREQUENT_ITEMSET.FI_TRANSACTIONAL(
 CURSOR(SELECT 1 as skupina_1,name_1 FROM TBL_DRKS
  union all
  SELECT 2 as skupina_2,name_2 FROM TBL_DRKS
   union all
  SELECT 3 as skupina_3,name_3 FROM TBL_DRKS
   union all
  SELECT 4 as skupina_4,name_4 FROM TBL_DRKS
   union all
  SELECT 5 as skupina_5,name_5 FROM TBL_DRKS),
0,1,8));

Výsledky dbms_frequent_itemset.fi_transactional

Příklad intepratace výsledku - třeba jednoprvková množina (Monster), která se vyskytuje ve dvou sloupcích( name_1 a name_5) a proto má support 2, jedná se o jednoprvkovou množinu, proto je length 1 a konečně počet skupin (name_1..name_5) bylo 5 tedy total_tranx 5. Parametr 0 řiká, že neomezujeme výskyt na žádné minumum, parametr 1 říká, že minimální množina je jednoprvková a parametr 8, že maximální dovolená množina je 8 prvková.

Poslední sloupec – name_6 jsem jako další skupinu dal až v následujícím selectu:

Ukázka výsledku DBBMS_FRQUENT_ITEMSET.fi_transactional - count

Ukázka výsledku DBBMS_FRQUENT_ITEMSET.fi_transactional - count

Proč pouze count? Neb mimo 15 množin, které vznikly ze sloupců name_1..name_5 jsou tu ještě monžiny, které vzniknou z 8 řádků slupce name_6. A vzhledem k hodnotám A,B,C,D,E,F,G,H vznikne vcelku dost rozdílných množin: (A),(A,B) .. Konkrétně by to měly být všechny jednoprovkové, dvojprvkové, tříprvkové.. něco takového:

Počet všech možných množin z prvků A,B,C,D,E,F,G,H

Počet všech možných množin z prvků A,B,C,D,E,F,G,H

Na spočítání takovéhleho výrazu je třeba umět spočítat faktorial:

create or replace function factorial(a number) return number
 is
  ret number:=1;
 begin
 if a=0 then return 1; end if;
 for i in 1..a loop ret:=ret*i; end loop;
 return ret;
 end;

Ok, není to zrovna hezky napsaná funkce, nicméně umožní nám to spočítat výsledek:

select sum(factorial(8)/(factorial(rownum)*factorial(8-rownum))) from dual connect by level<=8;
255

Což v kombinaci s počtem množin vzniklých z name_1..name_2 – 15 dává dohromady 15+255=270. Tedy funguje dle očekávání trhu ;)
To by bylo asi vše k DMBS_FREQUENT_ITEMSET.fi_transactional – všechno ostatní funguje jako u fi_horizontal – opakující prvky se nepočítají (tzn, každá z 255 množin ze sloupce name_6 bude mít support=1) a je možné postrčit jako nepovinný parametr hodnoty, které být musí v množinách a naopak se zbavit množiny které mají nějaký námi určený prvek. A podobně jako předchozí rychlotip, exekuční plán:

Exekuční plán pro dbms_frequent_itemset.fi_transactional

Exekuční plán pro dbms_frequent_itemset.fi_transactional

 Posted by at 12:03
Feb 102013
 

Další šikovná featura Oracle Database a můj již 18 tip na snad využitelnout funkci. Ač tahle je hodně specifická. Dnes je můj tip na pipeline funkci z package DBMS_FREQUENT_ITEMSELF jménem FI_HORIZONTAL. Upřímně – je to funkce, která mi v Oracle nikdy nechyběla a nejspíše mi ani nikdy chybět nebude, nicméně o to více mi těší jí poznat ;)

Jak to celé funguje: Funkci se podstrčí select/kurzor s libovolným počtem sloupců a funkce vyhledává zkrz všechny sloupce všechny možné množiny dat splňující dané podmínky (mohutnost, % výskyt) a výskyt těchto množin pak agreguje zkrz všechny řádky podstrčeného selectu/kurzoru a jako návrat vrátí tabulku jejíž jeden řádek je definován tímto typem:

<návratová typ dbms_frequent_itemset> (
     itemset [Nested Table of Item Type DERIVED FROM tranx_cursor],
     support        NUMBER,
     length         NUMBER,
     total_tranx    NUMBER);

itemset – monžina, která se opakovala. Množina - tedy je to nested tabulka, maximální velikost je 20, neb Oracle podporuje maximálně 20 prvků, jejihž kombinaci počítá.
support – kolikrát se daná množina vyskytuje (v každém řádku může jen jednou)
length – mohutnost vrácené množiny
total_tranx – počet řádků

Mnohem lepší bude si to vyzkoušet, ne?  Vytvořme následující tabulku:

create table tbl_drks(
 name_1 varchar2(20),
 name_2 varchar2(20),
 name_3 varchar2(20),
 name_4 varchar2(20),
 name_5 varchar2(20)
);

A insert několika řádků pro test – poněkuď více než má Oracle v dokumentaci jako příklad neb tam bohužel nejsou postihnuty všechny případy, které mohou nastat:

insert into tbl_drks values('RedBull','BigShock','RockStar',null,'Monster');
insert into tbl_drks values('BigShock','RedBull',null, null,'Monster');
insert into tbl_drks values('BigShock','RedBull','BigShock',null,'RedBull');
insert into tbl_drks values('BigShock','RedBull',null, null,'Monster');
insert into tbl_drks values('Monster','RedBull',null, null,'Monster');
insert into tbl_drks values('Monster','RedBull','RockStar','BigShock','Monster');
insert into tbl_drks values('RockStar','RedBull','BigShock','BigShock','Monster');
insert into tbl_drks values('RockStar','RedBull','RockStar','RockStar','Monster');
commit;

A jak to vypadá v tabulce:

Tabulka pro test DBMS_FREQUENT_ITEMSET

Tabulka pro test DBMS_FREQUENT_ITEMSET

Vytvořme typ pro návrat (pro nested návratovou tabulku):

CREATE TYPE typ_name AS TABLE OF VARCHAR2(20);

A nyní konečně provolání funkce:

SELECT CAST(itemset as typ_name)itemset, support, length, total_tranx
FROM table(DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL(
CURSOR(SELECT name_1, name_2, name_3, name_4, name_5 FROM tbl_drks),
0,
1,
5));

Funkci DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL je podstrčen select vracející celou tabulku tbl_drks a parametry: support_threshold=0 – není omezeno na % počet výskytů, itemset_length_min=1 - minimální mohutnost množiny, itemset_length_max=5 – maximální mohutnost množiny (víc ani být nemůže, neb naše tabulka má 5 sloupců)

A výsledek:

Výsledek DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL

Výsledek DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL



Interpretace výsledku:
Množina (BigShock) se vyskytuje na 6ti řádcích naopak (RockStar) je pouze na 4 řádcích. Jedná se o množinu a tedy pokud se kombinace či hodnota na jednom řádku vyskytuje několikrát je to počitáno pouze jednou jako výskyt – tolik ke sloupci support.
(BighShock, Monster)  je množina mohutnosti 2 – tolik ke sloupci leng(th)
Sloupec total(_tranx) pak jen ukazuje počet řádků z kurzoru – v tabulce bylo 8 řádků.

Ve sloupci total je hodnota 8 jako celkový počet řádků – množiny (Monster),(RedBull) a (Monster,RedBull) se vyskytují 7 či 8. Což znamená jejich výskyt v procentech vůči total_tranx je 7/8*100=87,5%+, so zkusme takto omezit volanou funkci.

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 2

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 2

Fajn a ted k tomu přidáme ještě omezení mohutnosti množiny:

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 3

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 3

Omezeno na 0.62 proto, že 5/8 je 0.625 tzn. ve výsledku se objeví jen množiny se sloupcem support 5 a větším, čímž vypadává kombinace s nejlepšími energetickými drinky na trhu (Monster,Rockstar) s počtem výskytů 4. Dalším omezením je mohutnost množiny 2 až 3, což eliminuje všechny jednoprvkové množiny (BigShock),(Monster),(Redbull) a (RockStar). Jediná množina s mohutností větší než 3 je (BigShock,Monster,RedBull,RockStar) a ta padá nejen na omezením počtu prvků na 2-3 ale i na počet výskytů 5+ (0.62) neb se se vyskytuje 3x.

Nevyzkoušeli jsme ještě dva nepovinné parametry including_items  (SYS_REFCURSOR)  kde jsou hodnoty, které nezbytně nutně musí být v množinách, které chceme. A  excluding_items  (SYS_REFCURSOR) kde jsou hodnoty, které nesmí být v množinách, které chceme. Ukázka pouze pro including_items:

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 4

Výsledek funkce DBMS_FREQUENT_ITEMSET.FI_HORIZONTAL 4

Tedy všechny množiny, jejihž mohutnost je větší než 2 a menší než 3, počet výskytů je více než 0.5 (tzn 50% což při total_tranx=8 znamená počet výskytů (support)>=4) A každá z těchto množin musí obsahovat položku (RockStar).

Spíše než o šikovnou a užitečnou funkci se jedná o funkci zajímavou ;) Nevim, kdo z nás podobnou funkcionalitu kdy potřeboval. A však pokud je třeba nějaké podobné funkcionality určitě šáhněme po DBMS_FREQUENT_ITEMSET, protože napsat si podobnou funkci sám by bylo opravdu náročné, respektive napsat jí dobře  po výkonové stránce. Funkce z pohledu výkonu asi nebude napsána jako nějaké ořezávátko, protože možná nejzajímavější na tom je exekuční plán:

Exekuční plán - dbms_frequent_itemset

Exekuční plán - dbms_frequent_itemset

Napadá mi jedno vcelku pěkné použití pro tuto funkci – množiny nic moc – ale taková jednoprvková množina je pořád množina. Tedy funkce umí spočítat počet řádků kde se vyskytuje nějaká hodnota i když je rozhozená skrz několik sloupců. Což znamená jako parametr select z tabulky, itemset_length_min=itemset_length_max=1 a případně including_items na hodnotu, kterou hledáme. Což jsem třebas já několikrát potřeboval a dosud mi na to stačila kombinace CASE a DECODE, ale tohle je mocinky moc mocnější ;)

 Posted by at 01:19
Feb 072013
 


Dnešní rychlotip se týká šikovné funkce, která jako vstup dostane jména dvou objektů a vrátí takové statementy, které alterují objekt číslo jedna takovým  způsobem, aby se z něj stal strukturně stejný objekt číslo dva. Tato báječná  funkce se jmenuje COMPARE_ALTER a je z package DBMS_METADATA_DIFF.

Signatura funkce je následující:

   FUNCTION compare_alter (
                object_type     IN VARCHAR2,
                name1           IN VARCHAR2,
                name2           IN VARCHAR2,
                schema1         IN VARCHAR2 DEFAULT NULL,
                schema2         IN VARCHAR2 DEFAULT NULL,
                network_link1   IN VARCHAR2 DEFAULT NULL,
                network_link2   IN VARCHAR2 DEFAULT NULL)
        RETURN CLOB;

 Myslím, že názvy parametrů mluví za vše – typ objektu (TABLE, INDEX..) jejich jména a kde jsou umístěny (schéma a případně databázový link).

 1) Založme dvě malé, leč rozlišné tabulky:

   create table tbl_a(a number,
   b varchar2(10) not null);

 create table tbl_b(a number not null,
  b varchar2(10),
  c number primary key);

2) Zavolání funkce compare_alter
select dbms_metadata_diff.compare_alter('TABLE','TBL_A','TBL_B') from dual;

Výsledek:
ALTER TABLE "AZOR"."TBL_A" ADD ("C" NUMBER)
ALTER TABLE "AZOR"."TBL_A" MODIFY ("A" NOT NULL ENABLE)
ALTER TABLE "AZOR"."TBL_A" MODIFY ("B" NULL)
ALTER TABLE "AZOR"."TBL_A" ADD PRIMARY KEY ("C") USING INDEX PCTFREE 10 INITRANS 2 ENABLE
ALTER TABLE "AZOR"."TBL_A" RENAME TO "TBL_B"

3) Nyní aplikace (mimo přejmenování na stejný název) získaných ALTER statementů:

table TBL_A altered.
table TBL_A altered.
table TBL_A altered.
table TBL_A altered.

 4) A ukázka toho, že jsou objekty stejné:

Stejná struktura tabulek

Stejná struktura tabulek

A jeden tip na využití:
Pokud kopírujeme strukturu tabulky, tak typicky používáme CTAS + nesplnitelnou podmínku:

create table tbl_c as select * from tbl_a where 1=2;

Což bohužel nezachovává všechny constrainty, tohle ano.
Šikovná věcička, co? Bohužel – triggery to neumí, indexy to neumí a co hůře – občas to vyhodí nevalidní statement (prázdné závorky u storage, například).

 Posted by at 17:52
Jan 302013
 

Po další době nějaký ten rychlotip, protože poslední rychlotip číslo #15 byl transformace sloupců do řádků je logické následovat opačnou tranformací – tedy transformace ze řádků do sloupců.

Nechť je jako vstup výstup z předchozího rychlotipu:

Transformace řádků do sloupců

Pro transformaci slouží klauzule PIVOT:

Transformace řádků do sloupců
Syntaxe je následující:
SELECT * FROM TABLE
PIVOT ( <agregační funkce>(<pro který sloupec>)
FOR IN (hodnoty v řádcích, případně s aliasem)
)

Pokud řádek obsahuje uvedenou hodnotu, je transformována do sloupce, ostatní hodnoty v řádích, kde byly jiné hodnoty jsou však, null. Takže je vhodné výsledek nakonec nějak vykrášlit, například zgrupovat:

Transformace řádků do sloupců

 Posted by at 00:00