Otázka na začátek – může databáze vracet jiné výsledky pro stejný select ale pro jeho jiné exekuční plány? Neměla by.., pokud je select správně napsaný, pokud se nemluví o bugu Oracle a pokud … a pokud? Pokud vím, tak v dokumentaci je jedna vyjímka, kde Oracle přímo píše, že dva stejné selecty s různým exekučním plánem mohou “legálně” vracet jiné hodnoty a to v případě poškozeného datového bloku v tabulce a nastaveném skipu “corrupted blocks” (DBMS_REAPIR) a dva selecty – jeden proti tabulce full scanem a druhý proti indexu (bez “corrupted blocks”).
Dneska jsem se rozhodl Oracle trošku potrápit – a přinutit ho k nehezkému chování, tím že mu poruším odkazy v indexu a Oracle bude vracet jiné řádky přes index a jiné přes full scan. Takže go hébičky go!
Upozornění: Updatovat systémové tabulky pod SYSem “není doporučeno” a může (a dělá to) poškozovat databázi.
1) Nejprve založení nějaké testovací tabulky
create table invalid_index_test_tbl tablespace USERS as select rownum id_key, rownum my_value from dual connect by level<20000;
2) Nyní založení indexu
create index inx_index_unusable_text on invalid_index_test_tbl(id_key);
3) Alterování indexu do UNUSABLE stavu
alter index inx_index_unusable_text unusable;
4) Odmazání řádek
Je třeba odmazat nějaké řádky, abychom získali prostor na shirnk space a rozhodili pozici řádků vůči rowid uloženým v indexu:
delete from invalid_index_test_tbl where my_value<8000 or (my_value>12000 and my_value<15000)
commit;
5) Enable row movement a Shrink space
Povolíme fyzický přesun řádků (enable row movement) a přesuneme řádky na promazané místo, tzn “setřesení tabulky” (shrink space)
alter table invalid_index_test_tbl enable row movement;
alter table invalid_index_test_tbl shrink space;
6) Hledání co vlastně updatovat
Nejzajímavější část je najít, co je třeba vlastně updatovat, nejjednodušší je podívat se na definici ALL_INDEXES, ve které je vidět:
decode(bitand(i.property, 2), 2,
decode(i.type#, 9, decode(bitand(i.flags, 8),
8, 'INPROGRS', 'VALID'), 'N/A'),
decode(bitand(i.flags, 1), 1, 'UNUSABLE',
decode(bitand(i.flags, 8), 8, 'INRPOGRS',
'VALID')))
Podle definice ALL_INDEXES je to update do tabulky sys.ind$ a bitově sloupec FALGS, potenciálně sloupec property. Co třeba je vědět je číslo objektu, například přes select * from sys.obj$ where name like upper(‘%inx_index_unusable_text%‘) . Moje číslo je 21988 a updatovat flags budu na 2050 (zjištěno z testovacího indexu,nemá cenu se počítat s bity)
7) Update sys.ind$
update sys.ind$ set flags=2050 where obj# in (21988);
commit;
8) Index je “validní” v systémových tabulkách Oracle, takže test:
select /*+ index(t) */ * from invalid_index_test_tbl t where id_key=2
ORA-01502: index ‘SYS.INX_INDEX_UNUSABLE_TEXT’ or partition of such index is in unusable state
01502. 00000 – “index ‘%s.%s’ or partition of such index is in unusable state”
*Cause: An attempt has been made to access an index or index partition
that has been marked unusable by a direct load or by a DDL
operation
*Action: DROP the specified index, or REBUILD the specified index, or
REBUILD the unusable index partition
Proč? Updatovat systémové tabulky pod SYSem by se nemělo, Oracle při tom dělá spoustu věcí (hlavně validacích, že..), které uživatel neudělá nebo udělat ani nemůže. Jednou z nich bude patrně invalidace stavu objektu v paměti, ale to naštětí lze řešit:
9) Čištění..
ALTER SYSTEM FLUSH SHARED_POOL;
Z mých zkušeností plyne, že je třeba flushnout shared_pool téměř vždy, když si hraji pod SYSem..
10) Výsledný pohled na selecty – jeden přes znásilněný index, druhý fullscannem:
Full vs Index
Super, ne? Stejné selecty – jiný hint a jiný výsledek.
Vada na kráse? Tím jak byla shrinkuta tabulka jsme posunuly řádky a tabulka byla zmenšena – tedy index pro vyšší hodnoty (za předpokladu, že Oracle insertnul data z selectu za sebou – neni zaručeno) odkazuje někam do pryč.. tedy:
11) Fail select:
select /*+ index(t) */ * from invalid_index_test_tbl t where id_key=15000;
S výsledkem (což je ta lepší varianta)
ORA-08103: object no longer exists
08103. 00000 – “object no longer exists”
*Cause: The object has been deleted by another user since the operation
began, or a prior incomplete recovery restored the database to
a point in time during the deletion of the object.
*Action: Delete the object if this is the result of an incomplete
recovery.
A
select /*+ index(t) */ * from invalid_index_test_tbl t where id_key=10800;
Což je ta horší varianta:
(ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
00600. 00000 – “internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]“
*Cause: This is the generic internal error number for Oracle program
exceptions. This indicates that a process has encountered an
exceptional condition.
*Action: Report as a bug – the first argument is the internal error number)
Vylepšení? Tabulku znovu roztáhnout – nainsertovat řádky a zase je smazat (???? pokud pujdou do stejné části datového souboru, jako byly předtím). Trefit to přesně na záznam tak tu máte úžasné chování 
Další možností je updatovat segmenty indexu a udělat si vlastní “polotrucate” indexu a zkrátit ho.
Vždycky lítaj třísky, když si člověk hraje, nicméně vypadá to, že pak stačí rebuildnout index:
alter index inx_index_unusable_text rebuild;
K čemu je to dobré? K ničemu. Na stranu druhou – to dokazuje, že shrink space funguje a dokonce i jak se tam sypou řádky – o kolik je posunut index vůči řádkům…¨
—————-
A neodpustím si jednu věc ze života mimo Oracle pro dnešek:
Zlato ve tmě modře nesvítí, což je dost nepříjemný fyzikální constrain :-/
