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
Jul 122013
 
Nedávno jsem byl svědkem diskuze dvou kolegů, kde řeč byla o tom jestli by šlo udělat to, že jednou by v selectu byla analytická klauzule projection, tzn select count(*) over (partition by..) a jednou by byl select spouštěn bez ní. Existovaly dva názory – jeden že to nejde bez toho aniž bychom měli dva podobné selecty v proceduře a druhý obecný, že to lze. Na meetingu jsem sprostě mlčel neb jsem na to neměl úplně přesný názor, neztotožňoval jsem se ani s větou, že to lze. Ale ani s větou, že bychom na to potřebovali dva selecty, když nebudeme uvažovat podmínečnou kompilaci. Nemyslel jsem si, že bychom potřebovali dva různé selecty – jeden s analytickou klauzulí a jeden bez. Spíše než dva selecty jsem si říkal, že budou nutné dva výstupy řádkového generování, což nezbytně nutně neznamená dva různé selecty. Mířeno na otázku jestli, nedokážeme napsat schizofrenní sql select, kde můžeme optimalizátor řídit a buď mu analytickou klauzuli povolit vykydlit nebo naopak mu tuhle optimalizaci nedovolit? Konkrétně jsem myslel na něco takového:

select
a,
b,
case when plsql_promena=1 then 0 else C end
from (select
a,
b,
count(*) over (partition by C) as C
from TBL_TEST)

Myšlenka byla taková, že pokud z PL/SQL enginu propasuji proměnnou (a tedy v sql enginu to bude už jako konstanta) plsql_promena s hodnotou 1, pak zcela jistě Oracle nemusí provádět sort, neb může vykydlit “count(*) over (partition by d) as C”, protože to k výsledku nepotřebuje. Naopak pro plsql_promena<>1 to prostě spočítat musí – ten výsledek chceme. Nicméně takhle to nefunguje, Oracle optimalizátor to takhle nevykydlí.
Napadlo mi to, protože Oracle má tuhle úžasnou optimalizaci:

select
a,
b
from (select
a,
b,
c from TBL_TEST)

Oracle nebude číst sloupec C – sice je požadován v subselectu, ale ve výsledku se na něj vůbec neodkazujeme, není tedy potřeba.
Dá se na to udělat krásný test – viz starší příspěvek na blogu o covering indexech. Tedy ten test.

Založení tabulky:
create table tbl_test
as
select rownum as a,
rownum as b,
rownum as c
from dual connect by level<=100

Constrainy jsou potřeba (nebo je nutné to vypsat v selectu IS NOT NULL):
ALTER TABLE TBL_TEST MODIFY (A NUMBER CONSTRAINT cons_a NOT NULL);
ALTER TABLE TBL_TEST MODIFY (B NUMBER CONSTRAINT cons_b NOT NULL);
ALTER TABLE TBL_TEST MODIFY (C NUMBER CONSTRAINT cons_c NOT NULL);

A nyní index, ale pouze na sloupcích A a B:
CREATE INDEX inx_tbl_test_ab ON TBL_TEST(a,b);

A nyní exekuční plán pro select všech tří sloupců:
Exekuční plán pro SELECT A,B,C

Exekuční plán pro SELECT A,B,C

Sloupec C má jen v tabulce, musí do ní. Exekuční plán pro select sloupců a,b:
select sloupců A a B

Exekuční plán pro SELECT A,B

V tomhle případě zafungovar covering index a Oracle čte pouze index ve kterém má oba sloupce – a i b. A constrainy zaručují, že tam není žádná hodnota NULL, jinak by to nebylo možné, protože v b-tree indexu NULLy nejsou. A ted exekuční plán pro select uvedený dříve – v subselectu chci sloupce A, B, C ale v hlavním selectu se odkazuji jen na sloupce A a B:
Exekuční plán pro select a,b from (select a,b,c from tbl_test)

Exekuční plán pro select a,b from (select a,b,c from tbl_test)

Hébičkozní, ne? Optimalizátor rozpoznal, že C pak vůbec nepotřebujeme a proto fyzicky jde jen do indexu ve kterém může najít pouze A a B sloupec, C tam vůbec není. Dokonce funguje i když se na to navěsí funkce:
Exekuční plán pro subselect s funkcí

Exekuční plán pro subselect s funkcí

Oracle dokonce vykydlí v optimalizaci i spouštění funkce, když její výsledek v hlavním selectu vůbec nepoužiji. Tím to však končí, jakmile se na něj začne odkazovat bude to reálně spuštěno – a to bohužel dokonce i když logicky ten výsledek nemůže být použit a je to odvozeno od konstant, tedy:
Exekuční plán pro select a,b,case..

Exekuční plán pro select a,b,case..

A nezafunguje na to bohužel ani hint DYNAMIC_SAMPLING, který by měl umožnit optimalizátoru vypočítat všechno, co v době optimalizace může. A překvapivě nefunguje ani tohle:
Exekuční plán pro "nesmyslný" join..

Exekuční plán pro "nesmyslný" join..

Úplně optimální by bylo přečíst index inx_tbl_test_ab a z něj hodnoty pro A,B a sloupec tbl_dva.C doplnit hodnotou null – jinou mít ani nemůže – join (1=2) sice nikdy nebude uspokojen, ale optimalizátor tohle bohužel neumí – join prostě fyzicky provede a tabulku TBL_TEST si olízne, ač má dostatek informací pro to, aby to dokázal vrátit z indexu.
Suma sumárum – neodkazované věci kydlí, odkazované nekydlí aní když je na ně odkazováno pouze v nesplnitelné podmínce, kterou zaručeně zná v době optimalizace.
 Posted by at 18:11

 Leave a Reply

(required)

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>