Oracle nie używa unikalnego indeksu dla długiego klucza

Mam tabelę z 250 000 wierszy w mojej testowej bazie danych. (W produkcji jest kilkaset milionów, możemy zaobserwować ten sam problem.) Tabela ma identyfikator ciągu nvarchar2 (50), a nie null, z unikalnym indeksem (to nie jest PK).

Identyfikatory składają się z pierwszej części, która ma 8 różnych wartości w mojej testowej bazie danych (i około tysiąca w produkcji), następnie znaku @, a na końcu liczby o długości od 1 do 6 cyfr. Na przykład może być 50 tysięcy wierszy zaczynających się od „ABCD_BGX1741F_2006_13_20110808.xml @”, po których następuje 50 tysięcy różnych liczb.

Kiedy pytam o pojedynczy wiersz na podstawie jego identyfikatora, liczność jest szacowana na 1, koszt jest bardzo niski, działa dobrze. Gdy pytam o więcej niż jeden wiersz z kilkoma identyfikatorami w wyrażeniu IN lub wyrażeniu OR, oszacowania dla indeksu są całkowicie niepoprawne, więc używany jest pełny skan tabeli. Jeśli wymuszę indeks za pomocą podpowiedzi, jest to bardzo szybkie, skanowanie pełnego stołu jest faktycznie wykonywane o rząd wielkości wolniej (i znacznie wolniej w produkcji). Jest to więc problem z optymalizatorem.

W ramach testu zduplikowałem tabelę (w tym samym schemacie + przestrzeni tabel) z dokładnie tym samym DDL i dokładnie taką samą zawartością. Dla lepszej miary odtworzyłem unikalny indeks na pierwszej tabeli i utworzyłem dokładnie ten sam indeks na tabeli klonowania. Zrobiłem DBMS_STATS.GATHER_SCHEMA_STATS('schemaname',estimate_percent=>100,cascade=>true);. Możesz nawet zobaczyć, że nazwy indeksów są następujące po sobie. Tak więc teraz jedyną różnicą między dwiema tabelami jest to, że pierwsza została załadowana w losowej kolejności przez długi czas, z blokami rozrzuconymi na dysku (w przestrzeni tabel wraz z kilkoma innymi dużymi tabelami), druga została załadowana jako jedna partia WYBIERZ WSTAW. Poza tym nie wyobrażam sobie żadnej różnicy. (Oryginalna tabela została zmniejszona od ostatniego dużego usunięcia, a potem nie było ani jednego usunięcia).

Oto plany zapytań dla chorych i tabeli klonów (ciągi pod czarnym pędzlem są takie same na całym obrazie, a także pod szarym pędzlem.):

(W tym przykładzie jest 1867 wierszy, które zaczynają się od czarnego, szczotkowanego identyfikatora. Zapytanie 2-wierszowe daje liczność 1867 * 2, zapytanie 3-wierszowe daje liczność 1867 * 3 itd. Nie można przypadek, Oracle wydaje się nie przejmować końcem identyfikatorów).

Co może powodować takie zachowanie? Oczywiście odtworzenie stołu w produkcji byłoby dość drogie.

USER_TABLES: http://i.stack.imgur.com/nDWze.jpg USER_INDEXES: http://i.stack.imgur.com/DG9um.jpg Zmieniłem tylko nazwę schematu i obszaru tabel. Widać, że nazwy tabel i indeksów są takie same jak na zrzucie ekranu planu zapytań.

(To jest odpowiedź na inne pytanie dlaczego histogramy są różne).

Histogramy są tworzone domyślnie na podstawie pochylenia kolumny i tego, czy kolumna została użyta w odpowiednim predykacie. Kopiowanie DDL i danych to za mało, ważne są również informacje o obciążeniu.

Zgodnie z Przewodnikiem dostrajania wydajności :

Po upuszczeniu tabeli informacje o obciążeniu używane przez funkcję automatycznego gromadzenia histogramów i zapisana historia statystyk używana przez procedury PRZYWRACANIA _ * _ STATS są tracone. Bez tych danych funkcje te nie działają poprawnie.

Na przykład, oto tabela z wypaczonymi danymi, ale bez histogramu:

drop table test1;
create table test1(a date);
insert into test1 select date '2000-01-01'+level from dual connect by level <= 10;
insert into test1 select date '2000-01-01' from dual connect by level <= 1000;
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/
select histogram from user_tab_columns where table_name = 'TEST1';

HISTOGRAM
---------
NONE

Uruchomienie tego samego, ale z zapytaniem przed zebraniem statystyk, wygeneruje histogram.

drop table test1;
create table test1(a date);
insert into test1 select date '2000-01-01'+level from dual connect by level <= 10;
insert into test1 select date '2000-01-01' from dual connect by level <= 1000;
select count(*) from test1 where a = sysdate; --Only new line
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/
select histogram from user_tab_columns where table_name = 'TEST1';

HISTOGRAM
---------
FREQUENCY

Znalazłem rozwiązanie! Jest taki piękny i naprawdę dużo się nauczyłem o Oracle.

Jednym słowem: histogramy.

Zacząłem dużo czytać o tym, jak działa CBO Oracle i natknąłem się na histogramy. Nie do końca zrozumiałem, więc spojrzałem na tabelę USER_HISTOGRAMS i voilá. Na stole chorych było kilka rzędów, a na stole sklonowanym praktycznie nic. W przypadku chorego tabeli był jeden wiersz dla każdej z 8 różnych części początkowych identyfikatora. I to jest klucz: zostały odcięte przy 32 znakach przed znakiem @. Jak powiedziałem, pierwsza część kluczy jest bardzo powtarzalna, stają się one różne po znaku @.

Wydaje się, że histogramy mogą być silniejsze niż prosty fakt, że unikalny indeks zawsze ma liczność 0 lub 1 dla danej wartości. Kiedy pytałem o ponad 2 wiersze, Oracle spojrzało na histogram, pomyślało, że dla tej części początkowej identyfikatora mogą istnieć dziesiątki tysięcy wartości, i zepsuło to CBO.

Usunąłem histogramy dla tej kolumny w starej tabeli i problem zniknął!

Więcej lektur: https://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating

Wysłałem e-mail do Jonathana Lewisa na ten temat i otrzymałem bardzo pomocną odpowiedź:

Dziwność w obliczeniach jest konsekwencją ograniczeń histogramów opartych na znakach, patrz w szczególności:

http://jonathanlewis.wordpress.com/2010/10/13/frequency-histogram-5/ http://jonathanlewis.wordpress.com/2010/10/19/frequency-histograms-6/

Patrząc na przykład, zapytanie dotyczy listy IN, a nie pojedynczego wiersza, więc początkowo zgaduję, że optymalizator zastosował ogólną strategię do obliczania selektywności w wielu wierszach zamiast specjalnego fragmentu kodu przypadku Lista IN na kluczu podstawowym. Wydaje mi się, że rozpoznanie tej sprawy nie byłoby dla nich zbyt trudne, ale programiści prawdopodobnie nie uznali tego za warty wysiłku.

Gorąco polecam przeczytanie postów na blogu, do których prowadzi, szczegółowo opisują ograniczenia histogramów, na które biegasz, np .:

Wniosek : jeśli masz dość długie i podobne ciągi w kolumnie, która jest dobrym kandydatem do histogramu częstotliwości (np. Bardzo opisowa kolumna stanu), masz problem, jeśli bardzo rzadka wartość wygląda identycznie jak bardzo popularny wartość do pierwszych 32 znaków. Może się okazać, że jedynym rozwiązaniem jest zmiana listy wartości prawnych (chociaż różne strategie obejmujące wirtualne kolumny lub indeksy oparte na funkcjach mogą ominąć problem).