PL/SQL programátor už NEbloguje – vývoj aplikací pod databází Oracle

Dnešní tip na hint, který dokáže trošku podpořit odhad cardinality estimátorem. Nevýhodou tohoto hintu je, že je nedokumentovaný, takže na produkci ne-e(ačkoliv vypadá bezpečně..). Což je velká škoda, protože jinak je to neuvěřitelně sexy hint.

Použití: Hint slouží k narovnání odhadu estimátoru (podobně jako nedokumentovaný /*+ cardinality */). Můžeme určit nejen cardinalitu u tabulky, ale dokonce i u joinu. A co více, existuje dokonce možnost odkazovat se násobky na to, co spočitá estimátor. A snad to nejlepší nakonec – lze zadat MINum a MAXimum. Prostě vymazlenej hint.

Syntaxe (s rezervou – z netu, není to dokumentované):

/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */

Možné hodnoty :

operation_type = table | index_fliter | index_scan | index_skip_scan | join..

identfier = tab_alias@qrblk | tab_alias@qrblk index_name | JOIN(tab1_alias@qrblk tab2_alias@qrblk) s tím, že, query blok je nepovinný.

ajdusment  = rows -počet řádků, které očekáme | scale_rows - multiplikátor pro estimátor | min - minimální počet řádků, které očekáváme | max  maxmální počet řádků, které očekáváme

A nyní nějaké ty příklady, nejprve založme tabulku:

CREATE TABLE tbl_test AS SELECT ROWNUM AS sloupec FROM dual CONNECT BY LEVEL<784561;

A spočtěme statistiky (na 12C nemusím – ale pro lidi, co mají ještě 11.2 či hůře..)

BEGIN dbms_stats.gather_table_stats(null, 'tbl_test', method_opt => 'FOR ALL COLUMNS SIZE 1'); END;

Tabulka má přesně 784561 řádků. Takže odhad pro select count(*) from tbl_test je přesně 784561. Číslo, takhle hnusné, jsem vybral schválně ;), takže zkusme odhad pro select count(*) from tbl_test where ora_hash(id,5)=1. Zde se Oracle pekelně splete – defalutní selectivita na “=” pro filtrování pomocí funkce, tzn. where f(n)=x|const je 1%, tedy jeho odhad je 7846. Ve skutečnosti při náhodných datech ora_hash(id,5)=1 selectuje 1/6 tabulky (16,6%), protože máme rovnoměrně rozhozené hodnoty <0,5> a filtrujeme na =1.

SELECT count(*) FROM tbl_test WHERE ora_hash(sloupec,5)=1;

Celkový vrácený počet řádků: 130521 (16,6%)
Odhad estimátoru: 7846 (1%)

A udělejme to ještě jednou, ještě jednou přidejme filtrování funkcí ať je Oracle hodně vedle:

SELECT count(*) FROM tbl_test WHERE ora_hash(sloupec,5)=1 AND ora_hash(sloupec,5)+1=2;

Přidání predikátu ora_hash(id,5)+1=2 (nemohu znovu ora_hash(id,5)=1, doublování predikátů Oracle pozná, tak jednoduše se ošálit nenechá), pro Oracle znamená znovu selectivitu 1%. Takže celkový odhad estimátoru je 0.01*0.01* num_rows=78 +/- zaokrouhlení. Z logického hlediska je to po odečtení 1 na obou stranách naprosto stejný predikát jako ora_hash(id,5)=1. Tedy stále selectujeme stále 1/6 tabulky (16,6%), podmínku jsme pouze zdvojili.

Celkový vrácený počet řádků: 130521 (16,6%)
Odhad estimátoru: 78 (0.0001%)

Takže spočítejme kolikrát více řádků opravdu vracíme o proti estimátoru – select ((1/6)/0.0001) from dual – 1666.6 násobek. A nyní k našemu vytouženému hintu:

SELECT /*+ OPT_ESTIMATE(TABLE a SCALE_ROWS=1667) */ count(*) FROM tbl_test2 a WHERE ora_hash(id,5)=1 AND ora_hash(id,5)+1=2;

Nyní je odhad 130786 proti skutečnému 130521 (jsme o 0,2% vedle, not bad). Výhoda tohoto hintu o proti /*+ cardinality */ je, že je to datově citlivé – přes odhad estimátoru.

Takže po té, co vím, že pro predikát “ora_hash(id,5)=1 and ora_hash(id,5)+1=2″ je potřeba magické číslo 1667 můžu to udělat s jinou tabulkou znovu:

CREATE TABLE tbl_test2 AS SELECT 'ABCD_'||ROWNUM as sloupec FROM dual CONNECT BY LEVEL<=100000;
BEGIN dbms_stats.gather_table_stats(null, 'tbl_test2', method_opt => 'FOR ALL COLUMNS SIZE 1'); END;

SELECT /*+ OPT_ESTIMATE(TABLE a SCALE_ROWS=1667) */ count(*) FROM tbl_test2 a WHERE ora_hash(id,5)=1 AND ora_hash(id,5)+1=2;

Celkový vrácený počet řádků: 16716 (cca (1/6)*100000)
Odhad estimátoru s hintem:   16670 (0.01*0.01*100000*1667)
Odhad estimátoru bez hintu: 10 (0.01*0.01*100000)

Myslím, hezký příklad na úpravu odhadu estimátoru. Hint má navíc i takové možnosti jako MIN a MAX. Speciálně to MIN je zajímavé, vzhledem k tomu, že většinou dochází k podhodnceni počtu řádků, které Oracle odhadne o proti skutečnosti. Myslím, že se asi každému z nás stává, že Oracle odhadne 1 (ve skutečnosti spočítá 0, ale až na menší vyjímky vždy raději počitá s tím, že se vrátí alepsoň jeden řádek – a to i za nesmyslných podmínek, aby to mohl propasovat do dalšího stupně exekučního plánu) a nakonec je tam řádků asi tak přesně bžlilion.. takže timto hintem se mu dá vnutit, že prostě musí počítat s tím, že tam minimálně xxx řádků mít může Prostě sexy hint.

A ještě nějaký krátký příklad na join:

SELECT /*+ OPT_ESTIMATE(JOIN, (a, b), ROWS=15) */ * FROM dual a join dual b on (a.dummy=b.dummy);

To by bylo vše k tomuto pozoruhodnému, leč nedokumentovanému, hintu.

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:

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));

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

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:

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:

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:

Pro transformaci slouží klauzule PIVOT:

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: