Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Witam,
muszę usunąć ok 3,7 mln rekordów z tabeli.
Dla bezpieczeństwa chcę je usuwać po 1000 sztuk z każdorazowym zatwierdzeniem transakcji.
Dla przyśpieszenia operacji chciałem również założyć indeks na pola.
Poniżej jest skrypt, który moim zdaniem powinien to "ogarnąć".
Prośba do osób, które mają doświadczenie w tej materii o rzucenie okiem i ewentualne zasugerowanie zmian jeśli wydałyby się Wam konieczne.

prompt
prompt 'create temp index'
declare
begin
execute immediate 'create index tmp_idx_all on cct_contacts(con_id, con_channel, con_direction, con_audit_cu, con_other_result, trunc(con_contact_date))';

end;
/

prompt
prompt 'delete row'
BEGIN
FOR i IN 1..3730 LOOP
DELETE FROM ib_crm_cc.cct_contacts
WHERE con_channel in ('SMS','EML') and con_direction ='OUT' and con_audit_cu='ftp'
and con_other_result not in ('Zapytanie','informacja','informacja o karcie')
and con_id NOT in ('3519769','1987482','1653456')
and trunc(CON_CONTACT_DATE) <= to_date('2012-07-26', 'yyyy-mm-dd') AND rownum <= 1000;
COMMIT;

END LOOP;
END;

prompt
prompt 'delete tem index'
declare
begin
execute immediate 'drop index tmp_idx_all';
end;
/

Najwięcej obaw mam do indeksów.

con_channel (mogą wystąpić tylko 5 wartości EML, SMS, PHO, LST i FAX 80% rekordów tabeli ma znacznik SMS i EML różne od null)
con_direction (mogą wsytapić tylko dwie wartosci 'IN' i 'OUT' różne od null)
con_audit_cu (najczęsciej 'ftp' ok 10% rekordów ma jeden z kilkudziesięciu loginów)
con_other_result (ok kilkadziesiąt różnych nazw)
con_id - niepowtarzalne, numeryczne
CON_CONTACT_DATE (typu data - data w przypadku 80% rekordów jest jedną z kilkudziesięciu powtarzających się wartości, pozostałe 20% ok 900 tys. rekordów ma losowe/zróżnicowane daty)

Dlaczego piszę o tym co jest w polach?

Zastanawiam się tylko czy z uwagi na niewielką liczbę unikalnych wartości w polach np. CON_DIRECTION nie użyć indeksu BITMAP oraz standardowo B-Tree dla pola CON_ID i trunc(con_contact_date)

execute immediate 'create index tmp_idx_others on cct_contacts(con_id, trunc(con_contact_date))';

execute immediate 'create BITMAP index tmp_idx_bit on cct_contacts(con_channel,con_direction,con_audit_cu,con_other_result)';

Które rozwiązanie będzie szybsze?
A może inaczej to zrobić?Michał Kowlaski edytował(a) ten post dnia 02.11.12 o godzinie 15:43

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Michał Kowlaski:
Witam,
muszę usunąć ok 3,7 mln rekordów z tabeli.
Dla bezpieczeństwa chcę je usuwać po 1000 sztuk z każdorazowym zatwierdzeniem transakcji.

To mnie niezmiernie ciekawi… czemu zatwierdzanie po 1 tys. dla bezpieczeństwa? Dla bezpieczeństwa to bym zrobił backup, a potem skasował dane (chociaż jak ostatnio robiłem to w Postgresie, to dało radę zrobić obie te rzeczy w jednym zapytaniu).

Czemu bezpieczeństwo oznacza robienie tego po 1 tys.?Szymon G. edytował(a) ten post dnia 02.11.12 o godzinie 15:58

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Zgadzam się z Szymonem, dziwne zabezpieczenie.
Poza tym robić indeks tylko po to żeby usunać dane to też słaby pomysł, bo usuwanie danych spowoduje że indeks trzeba będzie przebudowywany.
No i jeszcze ten plsql, też nie pomaga wydajnościowo, bo o ile dobrze pamiętam oracle będzie przełączał kontekst z silnika plsql na sql żeby wykonać zapytania sql'a.
Może
1) załóż ten indeks
2) stwórz nową tabelę tymczasową na podstawie tabeli cct_contacts bez rekordów które są do usunięcia , powiedzmy niech ta tabela się nazywa cct_contacts_tmp
3) usuń oryginalną tabelę
4) zmień nazwę nowej tabeli na cct_contacts

powinno być szybciej niż ten delete, tylko trochę miejsca na dysku będziesz potrzebował na stworzenie tej tabli tymczasowej

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

raczej chodzi koledze o bezpieczeństwo działania systemu.

walniecie dużego delete'a wymaga 2x rozmiar danych raz na strony w tabeli dwa na redo, a jeszcze do tego locki dochodzą.

Pomysł roberta nie jest zły ale ma wady ...
- samo stworzenie takiej tabeli docelowej może położyć baze ... locki ...
- zakładać index na tabeli z dużą ilością danych też jest bolesne
- alter rename wymaga exclusive locka
- nie w każdym wypadku da się, może być bardziej popierdzielona logika wewnątrz, lub np loby a tego łatwym selectem nie przeniesiesz

ja bym podszedł do tego inaczej.
1) tabelka pośrednia tak ale w niej tylko jedna kolumna najlepiej unikalna a jeszcze lepiej pkey
2) zapytanie bez indexu sie wykonywać będzie długo no ale jest jednorazowe, wynikiem nie delete a insert z wartością pkey lub inne unikalne
3) skoro mamy juz idiki to większego problemu z backupem ani deletem nie będzie a można sobie bedzie szybko sprawdzić czy o to nam chodziło

kroki 2 i 3 można zrobić w pętli nie ma większego problemu, jedyna wada że musi coś być unikalnegoŁukasz Dudek edytował(a) ten post dnia 02.11.12 o godzinie 20:39

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Łukasz Dudek:
raczej chodzi koledze o bezpieczeństwo działania systemu.

walniecie dużego delete'a wymaga 2x rozmiar danych raz na strony w tabeli dwa na redo, a jeszcze do tego locki dochodzą.

Hm… nie wiedziałem, że to tak beznadziejnie działa… 2x rozmiar danych i do tego te dane trzeba skopiować… to rzeczywiście tragedia.

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

podejscie postgresowe jest w tym przypadku bardziej oszczędne.
Niestety cały czar pryska przy updateach ...

klasyczny problem co lepsze: MVC vs dziennik tranzakcji

PS. oczywiscie undo nie redo

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Łukasz Dudek:
podejscie postgresowe jest w tym przypadku bardziej oszczędne.
Niestety cały czar pryska przy updateach ...

klasyczny problem co lepsze: MVC vs dziennik tranzakcji

PS. oczywiscie undo nie redo

Podejście postgresowe jest lepsze z innego powodu, nie dostajesz tym mitycznym ora-1555 snapshot too old.

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Łukasz Dudek:
podejscie postgresowe jest w tym przypadku bardziej oszczędne.
Niestety cały czar pryska przy updateach ...

klasyczny problem co lepsze: MVC vs dziennik tranzakcji

PS. oczywiscie undo nie redo

Podejście postgresowe jest lepsze z innego powodu, nie dostajesz tym mitycznym ora-1555 snapshot too old.

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Szymon G.:

Michał Kowlaski:
Witam,
muszę usunąć ok 3,7 mln rekordów z tabeli.
Dla bezpieczeństwa chcę je usuwać po 1000 sztuk z każdorazowym zatwierdzeniem transakcji.

To mnie niezmiernie ciekawi… czemu zatwierdzanie po 1 tys. dla bezpieczeństwa? Dla bezpieczeństwa to bym zrobił backup, a potem skasował dane (chociaż jak ostatnio robiłem to w Postgresie, to dało radę zrobić obie te rzeczy w jednym zapytaniu).

Czemu bezpieczeństwo oznacza robienie tego po 1 tys.?

A więc po kolei:
Dostawcą naszego systemu jest firma zewnętrzna. O ile SELECT'y możemy robić do woli, to w przypadku update'ów i delete'ów musimy postępować zgodnie z wytycznymi dostawcy - taką mamy umowę serwisową.
Zasugerowali usuwanie rekordów w krokach po 1000 sztuk - myślę, że dla większej wartości też będzie akcept. Proponują także utworzenie indeksu, co ich zdaniem ma przyśpieszyć proces.
Faktycznie w paczkach z upgrade'ami i hotfix'ami, które podejrzałem najczęściej robią w krokach po 1000, może jakaś specyficzna polityka w firmie lub prywatne upodobania autora skryptów.

Na koniec zaleceń z ich strony napisali:
"Proszę przygotować instrukcję, przebieg wraz z krokami jak będzie wyglądał proces usuwania rekordów wraz z blokiem instrukcji usuwającym rekordy np.

declare
...
begin
...
end;
/

lub skrypt usuwający rekordy .

My go zweryfikujemy i na jego podstawie potwierdzimy. "

Nie wiem skąd tak mała wartość rekordów na cykl.
Robimy inserty po 50-100 tys rekordów i nie ma z tym większych problemów.
Wyciągnąłem te 3,7 mln kontaktów celem archiwizacji jednym zapytaniem do 750 MB pliku csv też bez większych problemów (trwało to ok 70 minut).

Oczywiście będzie robiony backup całości przed usuwaniem.

Ale już na robienie kolejnej kopii tabeli z kontaktami miejsca nie znajdziemy. Pozostaje DELETE.
Mimo iż usuwamy ponad 80% rekordów tabeli, zajmują one jedynie 20% jej rozmiaru.
Pozostałe kontakty powiązane z innymi tabelami, mają CLOB'y ze skryptami rozmów telefonicznych - dość pokaźne html'e.

@Łukasz Dudek: O locki bać się nie musimy, DELETE ma być wykonany na"zatrzymanej" bazie.

Mam parę pytań:
Czy faktycznie nie ma żadnego sensu zakładać indeksu?
Jaką Waszym zdaniem porcję rekordów jednorazowo mogę usuwać nie narażając na nadmierne przeciążenia?Michał Kowlaski edytował(a) ten post dnia 02.11.12 o godzinie 23:59

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Szymon G.:

Łukasz Dudek:
podejscie postgresowe jest w tym przypadku bardziej oszczędne.
Niestety cały czar pryska przy updateach ...

klasyczny problem co lepsze: MVC vs dziennik tranzakcji

PS. oczywiscie undo nie redo

Podejście postgresowe jest lepsze z innego powodu, nie dostajesz tym mitycznym ora-1555 snapshot too old.

na twoim miejscu nie znając technologii nie ferowałbym wyroków co jest lepsze.

Zwiększenie retencji undo to nie problem, za to dzieki podejsciu oracle mozesz postawic kilka instancji (nazwijmy je roboczo servery) korzystajace z tego samego dysku z bazą. W postgresie musisz bawić się w replikacje dla takiej skalowalności... Oracle ma flashback query , w sumie postgresie tez można by to zaimplementować w miarę prosto ale podejrzewam że chodzi bardziej o patenty.

W MVC największą wadą jest to że update to tak naprawdę delete/insert tabela (oraz indexy) puchną i trzeba ja vacummować, a na tabeli gdzie masz jakieś 5k updatów na sekundę to nie jest przyjemne. A już najmniej przyjemny jest vaccum ogromnej tabeli z uwagi na to że się xid przekręcił...

@michał
skoro baza jest "zatrzymana" to twoje podejscie jest jak najbardziej na miejscu

co do indeksu
ja bym zrobił btree na wielu polach bez kolumn odnoszących się do (pole not in (...))
zacząłbym od stałych .. con_direction ='OUT' and con_audit_cu='ftp' and con_channel in ('SMS','EML') na koniec data... no chyba że data przytnie nam wiecej wyników to zacząłbym od daty

ten bitmap index bardzo mi się nie podoba .... (tych wiele kolumn będzie miało całkiem sporą cardinality razem )

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Dziękuję wszystkim za pomoc.
@Łukasz zrobię, tak jak radzisz.
Pozdrawiam:)Michał Kowlaski edytował(a) ten post dnia 04.11.12 o godzinie 23:27

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Szymon G.:

Michał Kowlaski:
Witam,
muszę usunąć ok 3,7 mln rekordów z tabeli.
Dla bezpieczeństwa chcę je usuwać po 1000 sztuk z każdorazowym zatwierdzeniem transakcji.

To mnie niezmiernie ciekawi… czemu zatwierdzanie po 1 tys. dla bezpieczeństwa? Dla bezpieczeństwa to bym zrobił backup, a potem skasował dane (chociaż jak ostatnio robiłem to w Postgresie, to dało radę zrobić obie te rzeczy w jednym zapytaniu).

Czemu bezpieczeństwo oznacza robienie tego po 1 tys.?

Na Sybase (10 lat temu) zrobienie tego hurtem mogło spowodować złożenie serwera.

Może nie dosłowne, ale:
- przepełniały się bufory
- komputer zaczynał "mielić bez końca"
- nie pamiętam jak się ten dramat kończył, optymistycznie: po długich chwilach wyczekiwania transakcja się rollbackowała (mniej więcej tyle samo czasu co trwała, oczywiście cały czas skutecznie blokując dostęp do bazy).

W tym konkretnym przypadku, po pobieżnej lekturze proponuję:
0) zrobić backup całej tabeli
1) założyć tabelę tymczasową zawierającą tylko PK z kasowanej tabeli ib_crm_cc.cct_contacts
2) wypełnić tabelę tymczasową złożonym zapytaniem wybierającym rekordy do skasowania
2.1) założyć unikalny indeks na tabeli tymczasowej (po wszystkich polach PK)
3) z limitem po 1000 szt.
3.1) kasować ib_crm_cc.cct_contacts wybierając rekordy z tabeli tymczasowej
3.2) kasować z tabeli tymczasowej rekordy których PK już nie istnieje w ib_crm_cc.cct_contacts
3.3) wykonać commit
4) powtarzać pkt. 3 do momentu aż tabela tymczasowa będzie pusta

To jest bardzo toporne rozwiązanie, pewnie DBA Oracla by coś tu lepszego podał.

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Ja bym zrobił to tak:
1) wybrał ROWIDy rekordów do usunięcia
2) odłożył na boku rekordy,które są do usunięcia tak by później było z czego powrócić :-)
3) usuwał równolegle na N procesach

-- Tabela, z której będziemy usuwać te 3.7mln wierszy. Ja takiej nie mam, więc tworzę mniejszą do testów :-)

create table PKW_TESTOWA as

    select    /*+ FULL(t) */ t.* from DBA_USERS t where rownum<=1000;

-- Tabela, kontrolna. Odkładamy, te 3.7mln wierszy w miarę możliwości efektywnie (tj. full table scan na kilu procesach, o ile ma to sens w danej konfiguracji sprzętowej).

create table my_work_table as

    select    /*+ FULL(t) PARALLEL(t,4) */ rowid xxx_rid,  0 xxx_proc_no, cast(null as char) xxx_status, cast(null as timestamp) xxx_ts,t.* from pkw_testowa t;

Oprócz odłożenia kasowanych rekordów: (t.*), przewidujemy pola na:

xxx_rid 	      -- przechowuje ROWID wiersza do usunięcia

xxx_proc_no  -- rekord ma być przetworzony przez proces o danym numerze xxx_status    -- status przetwarzania

xxx_ts           -- czas przetworzenia rekordu

Skrypt shellowy (bash + sqlplus), który przetwarza na N procesach poniżej.

Przed uruchomieniem skryptu pozostaje utworzyć tabelę: MY_WORK_TABLE o wspomnianej strukturze i uruchomić skrypt np. na zdanej liczbie procesów.

Pozostaje dostosować do własnych potrzeb :-)

pozdr,
Paweł

#!/usr/bin/bash





# Ustaw srodowisko

export PATH=/oracle/product/10.2.0/bin:/usr/bin

export LIBPATH=/oracle/product/10.2.0/lib

export ORACLE_HOME=/oracle/product/10.2.0





# liczba procesow na ktorych przetwarzac

PROCESS_NUM=$1



# Ilosc wierszy w przetwarzanej paczce

BULK_SIZE=100



# namiary na baze

DB_USER=

DB_PASS=

DB_NAME=



# Tabela zrodlowa

SOURCE_TABLE=PKW_TESTOWA

CONTROL_TABLE=my_work_table



# Prefiks dla nazw kolumn kontrolnych

COL_PREFIX=XXX_





# Kontrola zmiennych

if [ -z "${DB_NAME}" ] ; then

        echo "Ustaw nazwe bazy w skrypcie. Zmienna DB_NAME"

        exit 10

fi



if [ -z "${DB_USER}" ] ; then

        echo "Ustaw nazwe uzytkownika w skrypcie. Zmienna DB_USER"

        exit 20

fi



if [ -z "${DB_PASS}" ] ; then

        echo "Ustaw haslo w skrypcie. Zmienna DB_PASS"

        exit 30

fi





if [ -z "${PROCESS_NUM}" ] ; then

        echo "Uruchomienie: $0 <liczba procesow> "

        exit 40

fi







# Rozdziel zadania na poszczegolne procesy

function split_work() {

sqlplus -s /nolog<<EOF

conn ${DB_USER}/${DB_PASS}@${DB_NAME}

whenever sqlerror exit -10

SET SERVEROUTPUT ON;

set feed off echo off





update ${CONTROL_TABLE} set ${COL_PREFIX}proc_no = mod(rownum,  ${PROCESS_NUM});

commit;



EOF

exit $?

}



# Wykonaj pojedyncze zadanie

function run_task() {

PROCESS_ID=$1



if [ -z "${PROCESS_ID}" ] ; then

        echo "run_task powinno zostac uruchomione z parametrem wskazujacym na numer procesu: 0..$((PROCESS_NUM-1))"

        exit 50

fi





sqlplus -s /nolog<<EOF

conn ${DB_USER}/${DB_PASS}@${DB_NAME}

whenever sqlerror exit -10

SET SERVEROUTPUT ON;

set feed off echo off

DECLARE



   TYPE T_ROWID_TAB IS TABLE OF ROWID INDEX BY BINARY_INTEGER;



   vProcess      NUMBER := ${PROCESS_ID};

   vMaxCnt       NUMBER := 0;

   vCnt          NUMBER := 0;

   v_bulk_size   NUMBER := ${BULK_SIZE};





   v_rowid              T_ROWID_TAB;    /* rowidy wskazujace na wiersze z tabeli przetwarzanej */

   v_status_rowid       T_ROWID_TAB;    /* rowidy wskazujace na wiersze z {CONTROL_TABLE} */





   /* krusor wybierajacy paczki do przetworzenia */

   CURSOR crs_to_process (pProcNum NUMBER)

   IS

      SELECT ${COL_PREFIX}rid, rowid rid_work FROM ${CONTROL_TABLE} WHERE ${COL_PREFIX}status IS NULL AND ${COL_PREFIX}proc_no = pProcNum;





BEGIN

   SELECT COUNT (*) INTO vMaxCnt FROM ${CONTROL_TABLE}  WHERE ${COL_PREFIX}proc_no = vProcess AND ${COL_PREFIX}status IS NULL;



   OPEN crs_to_process (vProcess);



   LOOP

      FETCH crs_to_process

      BULK COLLECT INTO v_rowid, v_status_rowid

      LIMIT V_BULK_SIZE;



      EXIT WHEN v_rowid.COUNT = 0;

      vcnt := vcnt + v_rowid.COUNT;



      /* update records */

      FORALL idx IN 1 .. v_rowid.COUNT

         DELETE ${SOURCE_TABLE} where ROWID=v_rowid(idx);





      /* update control status */

      FORALL idx IN 1 .. v_rowid.COUNT

         UPDATE ${CONTROL_TABLE} SET ${COL_PREFIX}status = 'Y', ${COL_PREFIX}ts = SYSTIMESTAMP WHERE ROWID = v_status_rowid (idx);



      commit;



      /* update */

      DBMS_APPLICATION_INFO.SET_CLIENT_INFO (

            'Process: '

         || vProcess

         || ' - '

         || ROUND (100 * vCnt / vMaxCnt, 2)

         || '%');

   END LOOP;



   CLOSE crs_to_process;

END;

/

exit 0

EOF

exit $?

}





##################

## Glowna petla ##

##################



echo "[KOORDYNATOR] Rozdziela zadania"

date;

split_work &

wait





echo "[KOORDYNATOR] Uruchamiam joby przetwarzajace"

date;

CNT=0;

while [[ ${CNT} -lt ${PROCESS_NUM} ]]; do

        run_task ${CNT}  &

        CNT=$((CNT+1)) ;

done



echo "[KOORDYNATOR] Czekam na zakonczenie procesow"

wait



echo "[KOORDYNATOR] Finito "

date

Temat: [Orcale] Usuwanie rekordów za pomocą skryptu z indeksem

Podejście Pawła jest jak najbardziej poprawne (sam go używałem tyle, że zamiast skryptów shellowych i sqlplus'a używałem javy, puli wątków przetwarzających i puli połączeń do bazy, dzięki czemu dla każdego przetwarzanego kawałka nie musiałem otwierać nowej sesji).

Ale jeśli masz bazę w wersji co najmniej 11g to użyj po prostu DBMS_PARALLEL_EXECUTE