Jak uzyskać statystyki związane z indeksami?

Cześć,
zrobiłem index na kolumnie i teraz chciałbym porównać czas wykonania zapytania wykonanego z nowym indexem oraz jak go nie bylo. Jak to zrobic?

create table test (kol1 number constraint test_pk primary key
,kol2 number
,kol3 number
,kol4 number
);
insert into test
select rownum, dbms_random.random, dbms_random.random, dbms_random.random
from dual
connect by level <= 2000000;
commit;
-- i jeszcze statystyki
begin
dbms_stats.gather_table_stats(ownname => 'c##wojtek_admin', tabname => 'TEST');
end;

create index test_kol2_idx on test (kol2);
-- statystyki
begin
dbms_stats.gather_index_stats(ownname => 'c##wojtek_admin', indname => 'test_kol2_idx');
end;

select * from test order by kol1;

Próbowałem coś takiego, ale w zakładce explain Plan nic się nie zmieniło. Używam SQL Developera.
Z góry dziękuję za odpowiedź.

Czego byś się tu spodziewał?
Wyciągasz wszystko z tabeli i robisz ORDER BY kol1 (kol1 to klucz główny).

Oracle dochodzi do wniosku, że:

• chcesz wszystkie dane z tabeli TEST
• chcesz je posortowane względem klucza głównego
• korzysta, z tego, że z kluczem głównym jest związany indeks i w nim dane już są posortowane (odpada więc robota związana z sortowaniem)

Wyciąga dane z tabeli po ROWIDZIE.

Szybciej się w tym przypadku nie da. Chyba, że o czymś nie wiem :D

Gdyby tabela była fizycznie zrealizowana inaczej (index organized), to nawet indeksy nie trzeba byłoby czytać dla tego zapytania.

Jak chcesz zobaczyć inne plany zapytania, to spróbuj np.

select * from test where kol1=345; 
select * from test where kol2=123;
select * from test where kol1>=5 and kol2<=10; 
select * from test order by kol1 desc; 
select /*+ INDEX_DESC(a) */ a.* from test a order by kol1 desc; 

Powinieneś zobaczyć INDEX UNIQUE SCAN, RANGE SCAN, FULL SCAN DESCENDING etc.

@elo_elo_elo: Aby dobrze doradzić trzeba by zobaczyć prawdziwe zastosowanie zamiast testowego. No i jest tylko jeden explain plan, więc nie wiadomo co się nie zmieniło. Jaki był poprzedni indeks ? I jak duża tablea ? Najlepiej w bajtach.

Oracle pamięta inne plany wykonania. Możesz do nich dotrzeć pakietem DBMS_XPLAN i wyszukać wg SQL_ID. A na bieżąco możesz wspomóc się hintem wyłączającym indeks: NO_INDEX w komendzie SQL lub uniewidocznić indeks (alter index ... INVISIBLE) - od 11g - lub zrobić go unusable (alter index ... UNUSABLE). Zaznaczam, że zrobienie indeksu ponownie USABLE skutkuje przebudową indeksu.

W każdym razie, jeśli wyciągasz wszystkie dane z tabeli - select * - to Oracle musi je wszystkie odczytać. Wg mnie używanie do tego indeksu spowolni operację bo Oracle zamiast odczytu wieloblokowego będzie czytał po jednym bloku z dysku. Oczywiście zależy od wielkości tabeli i częstości odczytu. Mała tabela i częsty odczyt wg indeksu zostanie utrzymana w cache bazy - będzie, więc najszybciej jak się da. Sugeruję testy na realnym wolumenie danych czy zejście po posortowanym indeksie i po ROWID do danych jest wydajniejsze niż FULL SCAN tabeli. Zejście po indeksie to 3 IO na rekord. FULL SCAN pójdzie szybciej bo wieloblokowo. Dodatkowo przyspieszy jeśli użyjesz result cache (od 11g dostępny).