Podstawowe informacje na temat ładowania danych za pomocą SQL można znaleźć w artykule SQL - ładowanie danych. Omówiona została tam klauzula LOG ERRORS umożliwiająca logowanie błędnych rekordów do tabeli logów bez utraty zapisu pozostałych, poprawnych rekordów.
Po zakończonym ładowaniu istotną informacją jest, czy proces przebiegł bezbłędnie czy jednak zostały zalogowane jakieś błędy.
Pierwszym, najprostszym sposobem jest sprawdzenie tabeli logów czy istnieją rekordy dla naszego tagu.
select count(*) from ERR$_myEmp
where ora_err_tag$ = 'INS:20022020 194749';
COUNT(*)
----------
4
Rozwiązanie jest banalnie proste ale jeśli tabela logów jest duża, przeszukiwanie po niej może być niewydajne. Będzie to miało bezpośredni wpływ na wydajność procesu ładowania danych. W takiej sytuacji powinniśmy poszukać innego rozwiązania.
W pierwszej kolejności spróbujmy informację o wystąpieniu błędu zapisać do tabeli. Jeśli potrzebujemy informacji tylko w tej chwili - może to być tabela tymczasowa, rekordy będą usuwane po zatwierdzeniu transakcji. Jeśli potrzebujemy informacji stałych - możemy utworzyć zwykłą tabelę, gdzie będziemy przechowywać informację statystyczne z procesów ładowania danych.
W przykładzie zaprezentuję obie metody jednocześnie, ale oczywiście wystarczy zapis do jednej z nich :)
Skorzystam ze standardowego schematu HR (Jak odblokować schemat HR?)
W pierwszej kolejności utworzę tabele:
myEmp
tabela testowa, do której będziemy ładować dane. Zmniejszę długość kolumny last_name by spowodować błąd podczas ładowania.
myEmpCnt_gtt
global temporary table do tymczasowego przechowywania informacji o ilości rekordów.
Tabela będzie zawierała jedną kolumnę cnt - informacja o ilości rekordów, które powinny się załadować
myEmpCnt_tab
tabela standardowa do przechowywania informacji statystycznych dla procesów ładowań. Tabela będzie zawierała kolumnę
TAG - identyfikacja konkretnego procesu ładowania danych,
cntAll - ilość wszystkich rekordów, które powinny się załadować,
cntInserted - ilość rekordów załadowanych
Na tej tabeli założymy też index na kolumnie TAG, by efektywnie pobierać informacje o ładowaniach.
create global temporary table myEmpCnt_gtt (cnt number) ON COMMIT DELETE ROWS;
create table myEmpCnt_tab (tag varchar2(100), cntAll number, cntInserted number);
create index myEmpCnt_tab_idx on myEmpCnt_tab(tag);
create table myEmp as select * from employees where 1 =2 ;
alter table myEmp modify (last_name varchar2(9));
EXEC DBMS_STATS.gather_table_stats('HR', 'myEmpCnt_tab');
EXEC DBMS_STATS.gather_table_stats('HR', 'myEmp');
Utwórzmy tabelę logów:
call dbms_errlog.create_error_log('myEmp');
Do zapisu informacji o ilości rekordów do tabel myEmpCnt_tab oraz myEmpCnt_gtt użyję klauzuli INSERT ALL. Dzięki temu mogę wykonać insert nie tylko ten odpowiedzialny za ładowanie danych ale również inserty do tabel dodatkowych, takich jak nasze tabele statystyczne.
Żeby nie wykonywać dodatkowych insertów dla wszystkich insertowanych rekordów (wydajność!) - wykonam dodatkowe inserty tylko dla pierwszego wiersza (rownum = 1).
Informację o ilości rekordów pobranych do ładowania wyliczę w select'cie za pomocą funkcji analitycznej count(*) over.
W tabeli źródłowej mam 107 rekordów, z czego spodziewam się 4 błędów (za długa wartość w kolumnie last_name).
Spójrzmy na przykład:
set serveroutput on;
declare
ncnt_ins number;
nAll_gtt number;
nAll_tab number;
vTag varchar2(100);
begin
vTag := 'INS'||to_char(sysdate, 'ddmmyyyy hh24miss');
insert all
when rownum = 1 then into myEmpCnt_gtt (cnt) values (cnt)
when rownum = 1 then into myEmpCnt_tab (tag, cntAll) values (vTag, cnt)
when rownum > 0 then
into myEmp( employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id) values ( employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id)
log errors (vTag) reject limit unlimited
select rownum , count(*) over () cnt , employee_id,
first_name,
last_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id
from employees;
--- inserted rows
ncnt_ins := sql%rowcount;
-- select from gtt how many rows should be inserted
select cnt into nAll_gtt from myEmpCnt_gtt;
dbms_output.put_line ('GTT all rows: '||nAll_gtt ||' , Inserted: ' ||ncnt_ins||', Errors: '||to_char(nAll_gtt - ncnt_ins));
-- update myEmpCnt_tab and return how many rows should be inserted
update myEmpCnt_tab
set cntInserted = ncnt_ins
where tag = vTag
returning cntAll into nAll_tab;
dbms_output.put_line ('Tab all rows: '||nAll_tab ||' , Inserted: ' ||ncnt_ins||', Errors: '||to_char(nAll_tab - ncnt_ins));
end;
/
Mamy więc takie wartości:
ncnt_ins - ilość zainsertowanych faktycznie wierszy czyli SQL%ROWCOUNT
nAll_gtt - ilość wierszy pobranych do ładowania, informacja z tabeli myEmpCnt_gtt
nAll_tab - ilość wierszy pobranych do ładowania, informacja z tabeli myEmpCnt_tab
Na tej podstawie możemy wyliczyć ilość błędów:
dla tabeli myEmpCnt_gtt: nAll_gtt - nct_ins
dla tabely myEmpCnt_tab: nAll_tab - nct_ins
Spójrzmy więc na wynik naszej procedury:
GTT all rows: 107 , Inserted: 104, Errors: 3
Tab all rows: 107 , Inserted: 104, Errors: 3
W tym rozwiązaniu by pobrać informacje o ilości rekordów również musimy sięgać do tabeli. Jednak tak GTT jak i standardowa tabela, szczególnie zaindeksowana, są dużo mniejsze i wydajniejsze niż tabela logów.
Trzeba jednak pamiętać, że jeśli będziemy insertowali dane do więcej niż jednej tabeli - nie będziemy mogli wyliczyć ilości błędów dla każdej tabeli osobno. Będziemy mogli wyliczyć tylko sumaryczne ilości insertowanych oraz błędnych rekordów. Jeśli jednak nie będzie potrzeba takich detali, rozwiązanie jest proste i łatwe do zastosowania.
Komentarze
Prześlij komentarz