Najlepszy sposób na usunięcie bardzo dużego zestawu rekordów w Oracle

Zarządzam aplikacją, która ma bardzo duży (prawie 1 TB danych z ponad 500 milionami wierszy w jednej tabeli) zaplecze bazy danych Oracle. Baza danych tak naprawdę nic nie robi (bez SProc, żadnych wyzwalaczy itp.), To tylko magazyn danych.

Co miesiąc jesteśmy zobowiązani do usuwania danych z dwóch głównych tabel. Kryteria oczyszczania są różne i stanowią kombinację wieku wiersza i kilku pól statusu. Zwykle oczyszczamy od 10 do 50 milionów wierszy miesięcznie (dodajemy około 3-5 milionów wierszy tygodniowo poprzez import).

Obecnie musimy to usunąć w partiach po około 50 000 wierszy (tj. Usuń 50000, zatwierdzaj, usuwaj 50000, zatwierdzaj, powtarzaj). Próba usunięcia całej partii naraz powoduje, że baza danych nie odpowiada przez około godzinę (w zależności od liczby wierszy). Usuwanie wierszy w takich partiach jest bardzo trudne dla systemu i zwykle musimy to robić „jak pozwala na to czas” w ciągu tygodnia; zezwolenie na ciągłe działanie skryptu może spowodować obniżenie wydajności, które jest nie do przyjęcia dla użytkownika.

Uważam, że tego rodzaju usuwanie wsadowe zmniejsza również wydajność indeksu i ma inne skutki, które ostatecznie powodują pogorszenie wydajności bazy danych. W jednej tabeli znajdują się 34 indeksy, a rozmiar danych indeksu jest w rzeczywistości większy niż same dane.

Oto skrypt, z którego korzysta jeden z naszych informatyków, aby wykonać tę czystkę:

BEGIN
LOOP

delete FROM tbl_raw 
  where dist_event_date < to_date('[date]','mm/dd/yyyy') and rownum < 50000;

  exit when SQL%rowcount < 49999;

  commit;

END LOOP;

commit;

END;

Ta baza danych musi mieć wzrost o 99,99999%, a my mamy 2-dniowy okres konserwacji raz w roku.

Szukam lepszej metody usuwania tych rekordów, ale jeszcze jej nie znalazłem. Jakieś sugestie?

Logika z literami „A” i „B” może być „ukryta” za wirtualną kolumną, na której można wykonać partycjonowanie:

alter session set nls_date_format = 'yyyy-mm-dd';
drop   table tq84_partitioned_table;

create table tq84_partitioned_table (
  status varchar2(1)          not null check (status in ('A', 'B')),
  date_a          date        not null,
  date_b          date        not null,
  date_too_old    date as
                       (  case status
                                 when 'A' then add_months(date_a, -7*12)
                                 when 'B' then            date_b
                                 end
                        ) virtual,
  data            varchar2(100) 
)
partition   by range  (date_too_old) 
( 
  partition p_before_2000_10 values less than (date '2000-10-01'),
  partition p_before_2000_11 values less than (date '2000-11-01'),
  partition p_before_2000_12 values less than (date '2000-12-01'),
  --
  partition p_before_2001_01 values less than (date '2001-01-01'),
  partition p_before_2001_02 values less than (date '2001-02-01'),
  partition p_before_2001_03 values less than (date '2001-03-01'),
  partition p_before_2001_04 values less than (date '2001-04-01'),
  partition p_before_2001_05 values less than (date '2001-05-01'),
  partition p_before_2001_06 values less than (date '2001-06-01'),
  -- and so on and so forth..
  partition p_ values less than (maxvalue)
);

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '2008-04-14', date '2000-05-17', 
 'B and 2000-05-17 is older than 10 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '1999-09-19', date '2004-02-12', 
 'B and 2004-02-12 is younger than 10 yrs, must be kept');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2000-06-16', date '2010-01-01', 
 'A and 2000-06-16 is older than 3 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2009-06-09', date '1999-08-28', 
 'A and 2009-06-09 is younger than 3 yrs, must be kept');

select * from tq84_partitioned_table order by date_too_old;

-- drop partitions older than 10 or 3 years, respectively:

alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;

select * from tq84_partitioned_table order by date_too_old;

Klasyczne rozwiązanie polega na obciążeniu, ponieważ to jest to, co naprawdę robisz - obcinanie lub upuszczanie jednej z tych niewidocznych tabel podrzędnych). To będzie znaczna ilość przetwarzania do podziału „po fakcie”, ale nie ma sensu płakać nad rozlanym mlekiem - korzyści płynące z tego jak dotąd przewyższają koszty. Każdego miesiąca dzielisz najwyższą partycję, aby utworzyć nową partycję dla danych na następny miesiąc (możesz łatwo zautomatyzować te za pomocą podział tabel, np. miesiąca lub tygodnia. Jeśli jeszcze ich nie spotkałeś, tabela podzielona na partycje przypomina kilka identycznie ustrukturyzowanych tabel z niejawnym UNIONwyborem, a Oracle automatycznie zapisze wiersz na odpowiedniej partycji podczas wstawiania go na podstawie kryteriów partycjonowania. Wspominasz o indeksach - cóż, każda partycja również ma swoje własne indeksy podzielone na partycje. Upuszczanie partycji w Oracle jest bardzo tanią operacją (jest analogiczne doTRUNCATEDBMS_JOB ).

A dzięki partycjom możesz również wykorzystać równoległe zapytania i eliminację partycji , co powinno sprawić, że Twoi użytkownicy będą bardzo zadowoleni ...

Jednym aspektem do rozważenia jest to, ile wydajności usuwania wynika z indeksów, a ile z tabeli surowej. Każdy rekord usunięty z tabeli wymaga takiego samego usunięcia wiersza z każdego indeksu btree. Jeśli masz ponad 30 indeksów Btree, podejrzewam, że większość czasu spędzasz na utrzymywaniu indeksu.

Ma to wpływ na użyteczność partycjonowania. Załóżmy, że masz indeks nazwisk. Standardowy indeks Btree, wszystko w jednym segmencie, może wymagać wykonania czterech skoków, aby przejść z bloku głównego do bloku liścia, i piątego odczytu, aby uzyskać wiersz. Jeśli ten indeks jest podzielony na 50 segmentów i nie masz klucza partycji jako części zapytania, wówczas każdy z tych 50 segmentów będzie musiał zostać sprawdzony. Każdy segment będzie mniejszy, więc być może będziesz musiał wykonać tylko 2 skoki, ale nadal możesz zrobić 100 odczytów zamiast poprzednich 5.

Jeśli są to indeksy bitmapowe, równania są różne. Prawdopodobnie nie używasz indeksów do identyfikacji poszczególnych wierszy, ale raczej ich zestawy. Zamiast zapytania wykorzystującego 5 IO do zwrócenia pojedynczego rekordu, użyło 10 000 IO. W związku z tym dodatkowe obciążenie w dodatkowych partycjach dla indeksu nie będzie miało znaczenia.

usunięcie 50 milionów rekordów miesięcznie w partiach po 50 000 to tylko 1000 iteracji. jeśli usuniesz 1 co 30 minut, powinno to spełniać Twoje wymagania. zaplanowane zadanie uruchomienia wysłanego zapytania, ale usunięcie pętli, aby wykonało się tylko raz, nie powinno spowodować zauważalnej degradacji użytkowników. W naszym zakładzie produkcyjnym wykonujemy prawie taką samą liczbę rekordów, która działa prawie 24 godziny na dobę, 7 dni w tygodniu i spełnia nasze potrzeby. Rozpowszechniamy go nieco ponad 10 000 rekordów co 10 minut, co wykonuje się w około 1 lub 2 sekundy na naszych serwerach Oracle unix.

Jeśli miejsce na dysku nie jest na wagę złota, możesz utworzyć „roboczą” kopię tabeli, powiedzmy my_table_new, przy użyciu CTAS (Utwórz tabelę jako wybraną) z kryteriami, które pominą rekordy, które należy usunąć. Możesz wykonać instrukcję create równolegle i za pomocą podpowiedzi dołączającej, aby przyspieszyć, a następnie zbudować wszystkie swoje indeksy. Następnie, po zakończeniu (i przetestowaniu) zmień nazwę istniejącej tabeli na my_table_oldi zmień nazwę tabeli „roboczej” na my_table. Kiedy już wszystko ci drop my_table_old purgeodpowiada, możesz pozbyć się starego stołu. Jeśli istnieje kilka ograniczeń klucza obcego, spójrz na dbms_redefinition pakiet PL / SQL . Sklonuje twoje indeksy, przeciwności itp. Przy użyciu odpowiednich opcji. To jest podsumowanie sugestii Toma Kyte z AskTomsława. Po pierwszym uruchomieniu możesz zautomatyzować wszystko, a tworzenie tabeli powinno przebiegać znacznie szybciej i można to zrobić, gdy system jest uruchomiony, a czas przestoju aplikacji byłby ograniczony do mniej niż minuty na zmianę nazw tabel. Korzystanie z CTAS będzie znacznie szybsze niż wykonanie kilku operacji usuwania partii. To podejście może być szczególnie przydatne, jeśli nie masz licencji na partycjonowanie.

Próbka CTAS, zachowując wiersze z danymi z ostatnich 365 dni i flag_inactive = 'N':

create /*+ append */ table my_table_new 
   tablespace data as
   select /*+ parallel */ * from my_table 
       where some_date >= sysdate -365 
       and flag_inactive = 'N';

-- test out my_table_new. then if all is well:

alter table my_table rename to my_table_old;
alter table my_table_new rename to my_table;
-- test some more
drop table my_table_old purge;

po upuszczeniu partycji pozostawiasz indeksy globalne bezużyteczne, które trzeba odbudować, przebudowa indeksów globalnych byłaby dużym problemem, ponieważ jeśli zrobisz to online, będzie to dość powolne, w przeciwnym razie potrzebujesz przestoju. w obu przypadkach nie można spełnić wymagań.

„Zwykle usuwamy od 10 do 50 milionów wierszy miesięcznie”

zaleciłbym użycie PL / SQL usuwania wsadowego, myślę, że kilka godzin jest w porządku.