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
Mar 312012
 

Jaký bude mít prováděcí plán následující select ?

SELECT count(*) FROM tabulka?

Myslíte si, že je třeba projít celou tabulku a spočítat všechny řádky? Tedy full table scan? Správná odpověď je ovšem trošku složitější, záleží jestli je nad tabulkou vhodný index, který má méně datových bloků (ve smyslu je možné ho rychleji přečíst – menší počet bloků ještě neznamená, že je jej možné rychleji přečíst, ačkoliv je to velice pravděpodobné), ze kterého lze count(*) zjistit. Velice vhodný index je bitmapový –  je malý a především ukládá hodnoty null do své struktury. Pokud neexistuje žádný bitmapový index nad touto tabulkou, pak je možné použít k výpočtu count(*) klasický  b*-tree index, ale to pouze v případě, že sloupec zaručeně neobsahuje hodnotu null, což jde zajistit NOT NULL constrainem, alternativně podle indexu svázaného z primárním klíčem tabulku (což je technicky UNIQUE+NOT NULL).

Ideálně tedy pojede tento select přes fast full index scan. Pokud však nemáte bitmapový index nebo žádný b*tree index v kombinaci s NOT NULL constrainem či ještě něco jiného (Query Rewrite například),  pak vás teprve čeká drahý full table scan. Alternativně pokud nemáte NOT NULL constraint, ale máte index můžete za Oracle převzít zodpovědnost a napsat a tento select :

SELECT count(*) FROM tabulka WHERE col_co_nema_null_a_ma_index IS NOT NULL;

Pak stačí když je na sloupci u kterého jsme dali v podmínce NOT NULL obyčejný b*tree index a jede se přes fast full index scan.

Oracle se podobně však chová nejen pro count(*), ale chová se tak také pokud máte v projection pouze sloupec či sloupce z indexu , takzvaný covering index. Textu již je tu dost, takže malinký příklad.

1) Založíme tabulku
CREATE TABLE TBL_SCAN_TEXT AS SELECT ROWNUM AS PK, TO_CHAR(chr(MOD(rownum,22)+50)) test_char,'POOR_INDEX' poor_index,'A'||ROWNUM unk FROM DUAL CONNECT BY LEVEL<10000;

2) Test exekučního plánu
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_SCAN_TEXT;
Dle očekávání trhu je v PLAN_TABLE full table scan. Není zde žádný vhodný index (či jiná struktura), která by optimalizátoru umožnila rychlejší přístup k výsledku tohoto selectu.

3) Fajn, založme tedy index, například nad sloupcem PK, který je odvozen od ROWNUM (číslo řádku) původního selectu
CREATE INDEX inx_tbl_scan_text_pk ON TBL_SCAN_TEXT(PK);

4) Test exeukčního plánu
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_SCAN_TEXT;
Smolík, do b*tree indexu se neukládají NULL hodnoty a Oracle nemá jistotu ani informaci, že ve fyzické struktuře indexu jsou všechny řádky, tedy full table scan, jinak to nejde.

5) Převezmem zodpovědnost za Oracle, že ve sloupci PK nejsou žádné hodnoty null a pomůžeme mu v selectu
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_SCAN_TEXT WHERE PK IS NOT NULL;
A je to tu, fast full index scan. Chceme počet všech řádků v tabulce a nepotřebujeme (alternativně přejímáme odpovědnost za to, že tam nejsou) řádky, kde PK má hodnotu null.

6) Takže přidejme constraint NOT NULL
ALTER TABLE TBL_SCAN_TEXT MODIFY PK CONSTRAINT con_nn_tbl_scan_text NOT NULL;

7) Test exeukčního plánu
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_SCAN_TEXT;
Ano, je to fast full index scan, krásná ukázka toho, proč je důležité používat constrainty i z pohledu výkonu, že?

8) Nad naší tabulkou máme NOT NULL constraint a index, co zkusit založit bitmapový index?
CREATE BITMAP INDEX inx_tbl_scan_text_test_char ON TBL_SCAN_TEXT(test_char);

9) Test exeukčního plánu
EXPLAIN PLAN FOR SELECT COUNT(*) FROM TBL_SCAN_TEXT;
Dle očekávání trhu, v bitmap indexu se ukládají i nully hodnoty a index je podstatně menší (u mne v tomto případě 21 vs 3 datové bloky) než b*-tree na sloupci PK, tedy optimalizér vybírá cestu fast full index scan, nyní však přes bitmapový index inx_tbl_scan_text_test_char.

10) A co exekuční plán tohoto selectu?
EXPLAIN PLAN FOR SELECT PK FROM TBL_SCAN_TEXT;
Všechnu infromaci má Oracle v indexu, tedy také fast full index scan, k čemuž se váže zmíněný název covering index, tedy index, ze kterého lze vytáhnout všechny informace, typicky je však covering index index nad více než jedním sloupcem.

Suma sumárum,  NOT NULL constraint je nezbytný pro zachování kvality dat, ale může být také velice důležitý z pohledu výkonu. Každá rozumná tabulka by měla obsahovat primární klíč a tedy by se mělo jet při count(*) na fast full index scanu téměř vždy. Ovšem odhalení chybějícího NOT NULL constriantu nad indexovaným sloupcem stále může urychlit tento select, pokud bude tento index menší než ten nad primárním klíčem.

 Posted by at 22:14

  2 Responses to “Constraint NOT NULL v covering indexu..”

  1. [...] tu článek prováděcích plánech countu(*) – http://www.plsql.cz/?p=520 a věc se má tak, že se prostě fyzicky datová struktura musí projít, aby se spočítaly [...]

  2. [...] tedy potřeba. Dá se na to udělat krásný test – viz starší příspěvek na blogu o covering indexech. Tedy ten [...]

 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>