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;

(predikát 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.

(preditkát ora_hash(sloupec,5)=1 v and s tím samým predikátem)
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;

(s predikátama a hintem opravený odhadem estimátoru)
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;

(odhad s hintem na jiné tabulce se stejným predikátem)
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);

(oprava cardinality joinu hintem)
To by bylo vše k tomuto pozoruhodnému, leč nedokumentovanému, hintu.