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 39
Dec 012012
 

Minulé tipy byly o SQL Developeru, takže nyní přímo něco pro Oracle. Ostatně to asi byly poslední tipy pro SQL Developer neb již ho v práci nepouživám a druhak mi zabanovali na Facebooku ve skupině SQL Developer, když jsem pod link patche na SQL Developer napsal, že nejlepší patch na SQL Developer je Toad.. ;)

Dnešní rychlotip si zajisté spousta lidí oblíbí, je to častý dotaz na forech a existuje spousta workaroundů a spousta různých implementací – od Oracle 10g je však k dispozici klauzule v selectu, která to zvládá sama o sobě. Tedy go, hébičky, go!

Nejprve založíme nějakou testovací tabulku – CTAS z dualu, klasika:

create table sloupce_do_radku as select rownum ID, 'Monter Energy' as Monster, 'Red Bull' as Red,'RockStar' as Rock,'Shock Bitter' as Shock,'Suicide Silence' as Suicide,
'Beneath The Sky' as Beneath from dual;

Transformace sloupcu do řádků

Transformace sloupcu do řádků

A nyní jak na transformaci – je na to klauzule UNPIVOT (a alternativně PIVOT jako inverzní operace)

select id,sloupce_v_radcich,odkud_transformovano from sloupce_do_radku unpivot
(sloupce_v_radcich for odkud_transformovano in
(Monster,Red,Shock,Suicide,Beneath,Rock));

UNPIVOT klauzule obsahuje název sloupce ve kterém budou uloženy názvy sloupců ze kterých se hodnoty transformovaly – odkud_transformovano (- není nutné ho vytahovat v selectu ven, ale je to povinná součást UNPIVOT klauzule) a název sloupce ve kterém jsou ztransformované hodnoty – sloupce_v_radcich (- není nuté ho vytahovat v selectu ven, ačkoliv je to typicky žádoucí). A nyní výsledek výše uvedeného selectu:

Transformace sloupců do řádků a klauzule UNPIVOT

Transformace sloupců do řádků a klauzule UNPIVOT

V poslední části UNPIVOT je v IN seznam sloupců, které se mají ztransformovat. A nějaké poznámky k tomu:

  • Co je v in listu v UNPIVOTU nelze vytáhnout v selectu do projection (logicky by to nedávalo smysl)
  • Pokud jsou v hodnotách NULL je na výběr INCLUDE nebo EXCLUDE NULLS (výchozí)
  • V exeukučním plánu je to označeno přímo jako UNPIVOT, náklady čekejme  počet_transformovanejch_sloupcu*acces_path_cost_bez_unpivota.


A neodpustim si jednu větu z osobního života: zlatý řez je pověra, nelze to implementovat – lidské samičky prostě nemají hlavu ve formátu 1:1,618..

 Posted by at 21:11
Nov 042012
 

Jedna z příjemných novinek neustále zlepšujícícho se SQL Developeru je i grafického zobrazení hodnoty ve sloupci přímo v GUI. Synaxe je následující:

'SQLDEV:GAUGE:<min>:<max>:<low>:<high>:'||sloupec
select 'SQLDEV:GAUGE:0:100000:20000:50000:'|| sloupec alias_sloupce from..

min – minimum odkud vykreslovat hodnoty (hodnoty pod min graf vubec není kreslen)
max – jako maximální hodnota v grafu (hodnoty nad max jsou kresleny jako 100%)
low/high – viz obrázek níže a  žlutá barva na grafu

Obrovská nevýhoda této featury je, že to chce podstrčit přímo hodnoty ve stringu a tedy nelze přímo v použít výsledek nějakého skalárního selectu, který by si min/max vytáhl z tabulky. V Reportech již však tato funkcionalita takto omezená není.
Př:

create table tbl_gaugae as select rownum as id, rownum*5 as val from dual connect by level<21;

A nějaké ukázky grafů:

Grafy v sql developeru

Grafy v sql developeru

 Posted by at 00:15
Oct 312012
 

SQL Developer sice již není mým hlavním nástrojem, občas ale mrknu co je nového a odtud je i muj dnešní tip. V SQL Developeru lze použít “pseudohint” CVS, který vynutí od SQL Developeru výstup ve formátu CSV.  Šikovná věcička, co?

Je to háklivé na mezery i velikost písmen, tzn. přesně /*csv*/

select /*csv*/ object_name,created,status from all_objects;

CSV hint v SQL Developeru

CSV hint v SQL Developeru

Co se mi na tom hrozně líbí je rychlost – žádné klikání, žádné dialogy. Nápad vzkutku dobrý, takhle to lze přidat i do snippets. Dobře ty!

 Posted by at 21:38
Apr 032012
 

Dnešní tip je na SAMPLE, které z tabulky vybere jen dané procento řádků, použití je jednoduché :

SELECT * FROM TBL_TABLE SAMPLE (10);

Tento select vybere z tabulky TBL_TABLE pouze část (v tomto případě 10% a nádhodně) řádků. Parametr pro SAMPLE může být v rozmezí (0.000001;100> a udává procentí část z tabulky.

Pro SAMPLE existují ještě další dva parametry, jedním je klíčové slovo BLOCK (Block random sampling, jinak row random sampling) a druhým parametrem je SEED, inicializační parametr náhodného generátoru k deterministickým výsledkům:

SELECT count(*) FROM TBL_TABLE SAMPLE BLOCK (15) SEED (21);

Tento select vybere z tabulky TBL_TABLE 15% záznamů, je založen na náhodném výběru bloků a inicializační parametr náhodného generátoru je 21.

SAMPLE je úžasná věcička, má však nějaké restrikce, které je třeba mít na paměti:

  • Bez SEED není deterministická a tedy pro každé spuštění vrací jiný počet a jiné řádky
  • Odhad je založen na statistikách, špatné statistiky mohou ovlivnit výsledek
  • Nelze uvéci SAMPLE pro DML
  • Nelze aplikovat na některá VIEW (s join například)
  • A především nespoléhat na to, že dostaneme 15% z tabulky, když zadáme SAMPLE (15) dostaneme něco “co by tak nějak mělo odpovídat 15%”
 Posted by at 23:26
Mar 302012
 

Kde najdem hinty? Ty známé a dokumentované hinty najdeme v Oracle® Database SQL Language Reference v sekci “Comments”, pokud se nepletu, tak 69 hintů (Oracle 11R2). Mimo těchto hintů existují další nedokumentované hinty, které lze splašit na různě po internetu.

Můj dnešní tip je na hint, který je sice dokumentovaný, ale v SQL Reference o něm není bohužel ani zmíňka. Hint, který mi dnes zaujal se jmenuje NO_STMT_QUEUING (existuje i v negaci STMT_QUEUING).

K čemu je NO_STMT_QUEUING dobrý? V případě paralelního spouštění selectu (alternativně DML), je při paralelním zpracování příkaz vložen do fronty, pokud nejsou dostatečné zdroje na spuštění, a čeká. Hint dovolí bypassovat FIFO frontu a přeskočit vlastní zařazení na konec.

Příklad:

SELECT /*+ parallel(t,32) NO_STMT_QUEUING */ SUM(CNT) FROM VERY_BIG_TABLE t;

Mimochodem, pokud u parallel hintu není uvedeno DOP (degree of parallelism), pak Oracle jako výchozí hodnotu považuje CPU*THREADS, alternativně na RAC CPU*TRHEADS*INSTANCE – tedy plný kotel.

 Posted by at 08:45
Dec 312011
 

Pátek, předposlední den v roce – kdo by nechtěl být brzo doma? Už jsem se pomalu zvedal, když mi do emailu vypadla chyba z logu ORA-01555 (prilis stary snimek: segment rollback cislo 3). Jeden SELECT v reportingu nám běží déle než nám udrží undo snapshot. Tohle má několik způsobů řešení – typicky je to přepsáním selectu, použit HINT, přepočítat statistiky, pořešit histogramy..

Select rozhodně nebyl optimánlní z pohledu aplikační logiky, ale co horší – exekuční plán mi vyhodil cost 13 a selectivitu 4 (po vynucení hintama jak bych to chtěl mi ukázal cost 1 250 548).  Postupem času jsem došel k závěru, že problém je v určení selectivity u jednoho ze sloupců ve WHERE podmínce. Selectivita (4) naprosto neodpovídala skutečnosti, data měla rovnoměrné rozdělení, žádná kulišárna – na základě této podmínky to mělo vybrat cca 1,8mil záznamů.  Otázka zněla jakou představu o hodnotách ve sloupci MDS_LST_UPD  má CBO:

SELECT COLUMN_NAME, LOW_VALUE,HIGH_VALUE FROM ALL_TAB_COLS WHERE TABLE_NAME='MDS_T_ORDER_DATA' AND COLUMN_NAME='MDS_LST_UPD';

COLUMN_NAME LOW_VALUE HIGH_VALUE
MDS_LST_UPD 786F0202042215 786F0A07170D22

 

Oracle bohužel v ALL_TAB_COLS nemá LOW_VALUE  a HIGH_VALUE čitelnou formu pro datový typ DATE (ono ani pro jiné viz. varchar2). Stačil jediný dotaz do Googlu a zde je můj dnešní tip je na úžasný select od Gary Myers, který vrátí LOW_VALUE a HIGH_VALUE pro DATE v human-readable formě, krása:

SELECT owner
,table_name
,column_name
,data_type
,decode (nullable,'N','Y','N') M
,num_distinct num_vals
,num_nulls
,density dnsty
,decode(data_type
,'NUMBER' ,to_char(utl_raw.cast_to_number(low_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(low_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(low_value))
,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(low_value))
,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(low_value))
,'DATE',to_char(1780+to_number(substr(low_value,1,2),'XX')
+to_number(substr(low_value,3,2),'XX'))||'-'
||to_number(substr(low_value,5,2),'XX')||'-'
||to_number(substr(low_value,7,2),'XX')||' '
||(to_number(substr(low_value,9,2),'XX')-1)||':'
||(to_number(substr(low_value,11,2),'XX')-1)||':'
||(to_number(substr(low_value,13,2),'XX')-1)
, low_value
) low_v
,decode(data_type
,'NUMBER' ,to_char(utl_raw.cast_to_number(high_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(high_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(high_value))
,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(high_value))
,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(high_value))
,'DATE',to_char(1780+to_number(substr(high_value,1,2),'XX')
+to_number(substr(high_value,3,2),'XX'))||'-'
||to_number(substr(high_value,5,2),'XX')||'-'
||to_number(substr(high_value,7,2),'XX')||' '
||(to_number(substr(high_value,9,2),'XX')-1)||':'
||(to_number(substr(high_value,11,2),'XX')-1)||':'
||(to_number(substr(high_value,13,2),'XX')-1)
, high_value
) hi_v
FROM all_tab_cols
WHERE owner LIKE upper('MON_DSL')
AND table_name='MDS_T_ORDER_DATA' AND column_name='MDS_LST_UPD'
ORDER BY owner,table_name,COLUMN_ID

Select naštěstí potvrdil mé očekávání – hodnoty LOW_VALUE a HIGH_VALUE neodpovádají skutečnosti.  Ve skutečnosti je můj datum (SYSDATE) velice vzdálený tomu v ALL_TAB_COLS..

 Posted by at 11:13
Nov 102011
 

Jednou z vychytávek Oracle, která mi nedávno zaujala je databázový parametr jménem FIXED_DATE.  Tímto parametrem se dá nastavit pevné datum pro sysdate.  Parametr je určený především pro testování,  kdy se může hodit mít jedno pevné datum po celou dobu testu, ale dá se to také použít pro znovuopakování konkrétního testu.  So,  pojďme to zkusit :

1) Testovací kód – nejprve přiřazení sysdate do proměnné typu date, pak čekání 4 sekundy a po té přiřazení sysdate do druhé proměnné typu date a následně jejich srovnání.
declare
d_first date;
d_second date;
begin
d_first:=sysdate;
dbms_lock.sleep(4);
d_second:=sysdate;
if (d_first=d_second) then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end;
Výsledek je false.

2) Nyní nastavení parametru FIXED_DATE :
ALTER SYSTEM SET FIXED_DATE='2011-11-10-22:23:11';

3) A nyní opět testovací kód, nyní však s výsledkem true:
declare
d_first date;
d_second date;
begin
d_first:=sysdate;
dbms_lock.sleep(4);
d_second:=sysdate;
if (d_first=d_second) then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end;
Výsledek je true.

4) A nyní ještě vrátit změny zpět:
ALTER SYSTEM SET FIXED_DATE=NONE;

Tedy pro potřeby fixního sysdate je tu databázový parametr Oracle FIXED_DATE. Pozor ale, na systimestamp daný parametr nefunguje a systimestamp dále vrací aktuální čas, nikoliv konstantu.

 Posted by at 22:17