SQL - ładowanie danych.

Ładowanie dużych wolumenów danych do bazy danych to jedno z wyzwań dla programistów Oracle. Procesy ładujące w PL/SQL są łatwe do zarządzania i analizy ewentualnych błędów. Jednak - nawet dobrze napisane nie są tak szybkie jak czysty DML.

DML faktycznie jest szybki. Jednak ma też swoje bolączki. Jeśli ładujemy tysiące danych a jeden rekord spowoduje błąd to standardowo wszystko zostanie wycofane, nawet poprawne rekordy nie zostaną zapisane. Co gorsza - wprawdzie dostaniemy informację o błędzie, który przerwał nasz proces, niemniej nie będziemy wiedzieli, który rekord spowodował ten błąd. A przy wolumenach danych idących w setki tysięcy czy nawet miliony rekordów - znalezienie właściwego graniczy z cudem.

Nie mówiąc już o wydajności - być może czekaliśmy kilka godziny na załadowanie danych. Jeśli wystąpił błąd - musimy czekać kolejne godziny na wycofanie zmian!

Z tego też powodu często zaleca się jednak ładowanie danych za pomocą funkcji PL/SQL, gdzie w przypadku błędu dla jednego rekordu nie tracimy zapisu pozostałych (o ile tak sobie napiszemy :D) .

Ale spójrzmy na przykład. 

Korzystam ze standardowego schematu HR (jak odblokować schemat HR).

Na potrzeby testu tworzę pustą tabelę myEmp, jako kopię tabeli Employees.

Następnie zmniejszę długość kolumny last_name na 9 znaków (w tabeli employees są 4 rekordy z last_name > 9) by wymusić podczas ładowania danych błąd.

-- create test empty table
create table myEmp as select * from employees where 1 = 2;
 
-- change column length to induce error (max lenght of the last_name column is 11)
alter table myEmp modify (last_name varchar2(9));

W tabeli źródłowej mamy 107 rekordów ( w moim przykładzie), z czego spodziewamy się 4 błędów.

select case when length(last_name ) <= 9 then ' ok rows' else ' errors' end typ,  count(*) 
from employees 
group by case when length(last_name ) <= 9 then ' ok rows' else ' errors' end;

TYP        COUNT(*)
-------- ----------
 errors           4
 ok rows        103

Spróbujmy załadować teraz dane:

-- load employee data   - we expect some errors
insert into myEmp  select * from employees;

Error starting at line : 11 in command -
insert into myEmp  select * from employees
Error report -
ORA-12899: wartość zbyt duża dla kolumny "HR"."MYEMP"."LAST_NAME" (obecna: 10, maksymalna: 9)

select * from myEmp;

no rows selected

Ładowanie nie powiodło się. Do tabeli myEmp nie został zapisany ani jeden rekord.

Mamy oczywiście informację o błędzie: 

ORA-12899: wartość zbyt duża dla kolumny "HR"."MYEMP"."LAST_NAME" (obecna: 10, maksymalna: 9)

lecz nie wiemy, którego rekordu błąd ten dotyczy! Szukanie takiego rekordu to, szczególnie w dużych tabelach, jak szukanie igły w stogu siana.

Czy w takim razie faktycznie jesteśmy skazani na powolny PL/SQL?

Na szczęście nie!

Dla DML został stworzony od wersji 10 mechanizm logowania: LOG ERROR.

Dzięki tej funkcjonalności nie dość, że dostaniemy pełną informację o wystąpieniu błędu to jeszcze pozostałe rekordy zostaną zapisane!

Przyjrzymy się składni:

LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]

TABLE 

opcjonalne, nazwa tabeli logu. Jeśli nie podamy tego parametru - błędy zostaną zapisane do tabeli na której wykonywany był DML z przedrostkiem ERR$_

SIMPLE_EXPRESSION

tag, którym możemy oznaczyć dany proces DML, na przykład datę ładowania. Pamiętajmy, że tak jest typu varchar2(2000).

REJECT LIMIT

maksymalna ilość zalogowanych błędów zanim proces się przerwie. UNLIMITED - bez limitu.

Żeby móc skorzystać z LOG ERRORS musimy utworzyć tabelę, do której będziemy zapisywać błędy. Tabelę można utworzyć korzystając z procedury

-- create table for errors
-- syntax
DBMS_ERRLOG.CREATE_ERROR_LOG (
   dml_table_name            IN VARCHAR2,          --orginal table name
   err_log_table_name        IN VARCHAR2 := NULL, -- err_log table name, default ERR$_<orginal_table_name>
   err_log_table_owner       IN VARCHAR2 := NULL, -- err_log table owner, default orginal table schema owner
   err_log_table_space       IN VARCHAR2 := NULL, -- err_log tablespace, default orginal table tablespace 
   skip_unsupported          IN BOOLEAN := FALSE);  -- TRUE column not supported will be skipped, FALSE - unsupported column type will cause the procedure to terminate (LONG, CLOB, BLOB, BFILE, ADT)
   
/

Najczęściej wywołamy po prostu procedurę z parametrem nazwy tabeli, do której tworzymy log.

-- Create the error logging table.
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'myEmp');
END;
/

Tabela logów utworzona w ten sposób będzie miała nazwę ERR$_myEmp.

Załadujmy dane do tabeli myEmp z wykorzystaniem LOG ERRORS.

insert into myEmp 
select * from employees
LOG ERRORS ('INS:' || to_char(sysdate,'ddmmyyyy hh24miss')) REJECT LIMIT UNLIMITED;

103 rows inserted.

Mamy 103 rekordy! Ale co z czterema błędnymi rekordami? Zostały zalogowane do tabeli błędów ERR$_myEmp. Mamy tam informację o tagu, błędzie oraz cały błędny rekord.


Podsumowując:
Jeśli potrzebujemy załadować  i przetworzyć dane do systemu - najlepiej do tego nadadzą się funkcje ładujące napisane w PL/SQL z użyciem BULK COLLECT.
Jeśli zaś ładujemy dane i nie chcemy ich konwertować (może troszeczkę) ani przetwarzać (w tym momencie) to warto zastanowić się właśnie nad DML wraz z LOG ERRORS. Zaoszczędzi nam to sporo czasu a nie tracimy przy tym szczegółowych informacji o ewentualnych błędach.