Z ORACLE MI PO DRODZE

Jak Efektywnie Kopiować Dane Między Tabelami

23 maja, 2023 przez Szymon Lubinski

Poniżej przedstawiam dwa sposoby kopiowania danych między tabelami. Są to sposoby, które pokazują swoją moc, dopiero kiedy tabele są naprawdę duże. Każdy ze sposobów bazuje na innym silniku i sprawdza się w innych sytuacjach.

DROP TABLE T1
/
DROP TABLE T2
/

CREATE TABLE T1(id NUMBER, descr CHAR(2000))
/
CREATE TABLE T2(id NUMBER, descr CHAR(2000))
/

INSERT INTO T1
  SELECT ROWNUM, 'A' FROM DUAL
    CONNECT BY ROWNUM <= 1000000
/    

COMMIT
/

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (ownname => USER , tabname => 'T1', granularity => 'ALL', degree => 64);
END;
/

SELECT /*FIRST_ROWS(1)*/ bytes/1024/1024 AS mb FROM user_segments
 WHERE segment_name = 'T1'
/

Do przekopiowania jest nieco ponad 2GB danych.

MB
-------
2239.5

Pierwszy sposób bazuje na silniku PL/SQL, w którym to dane w paczkach po 50000 rekordów kopiowane są do tabeli T2. Jak każde rozwiązanie, również i to posiada swoje wady i zalety:

Zalety:

• Możliwość przechowywania mechanizmu w ciele procedur i funkcji
• Możliwość zwalniania pamięci podręcznej co określoną liczbę określoną parametrem LIMIT, co mniej obciąża dziennik wycofania.
• Podgląd postępu kopiowania w drugiej sesji dzięki temu, że polecenie COMMIT wykonywane jest co 50k rekordów

Wady:

• Wymuszony context switching, czyli przepinanie się między silnikami SQL i PL/SQL
• Zaangażowanie dwóch silników: SQL i PL/SQL
• Brak bezpośredniego działania na silniku SQL
• Zaangażowanie buffer cache, czyli części w pamięci SGA, która odpowiedzialna jest za przechowywanie bloków danych

DECLARE
  cur SYS_REFCURSOR;
  TYPE t IS TABLE OF T1%ROWTYPE;
  l_t1 t;  
BEGIN
  OPEN cur FOR ('SELECT * FROM T1');
  LOOP
    FETCH cur BULK COLLECT INTO l_t1 LIMIT 50000;
    EXIT WHEN l_t1.COUNT <= 0;
    
    FORALL x IN 1..l_t1.COUNT
      INSERT INTO T2 VALUES l_t1(x);

    COMMIT;
  END LOOP;
  CLOSE cur;

  COMMIT;
END;
/

Task completed in 13.815 seconds

Rozwiązanie numer dwa bazuje bezpośrednio na silniku SQL i wykorzystuje ‘wspomagacze’, w postaci ALTER’ów na sesji, które wymuszają zrównoleglenie na instrukcjach i braku zapisu do REDO.

Zalety:

• Najbardziej oczekiwany przy hurtowniach danych
• Możliwość pominięcia buffer cache
• Bazuje na czystym silniku SQL
• Brak context switchingu
• Możliwośc zrównoleglenia operacji
• Wstawianie rekordów bezposrednio za High Water Mark

Wady:

• Kopiowanie odbywa się w całości
• Brak możliwośc podglądu ile i jakie rekordy zostały już skopiowane
• W przypadku wystąpienia błędu wycofywana jest całość danych

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=512
/
ALTER SESSION SET DDL_LOCK_TIMEOUT = 1200
/
ALTER SESSION FORCE PARALLEL DML PARALLEL 8
/
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8
/
ALTER TABLE T2 NOLOGGING
/

INSERT /*+ APPEND PARALLEL(8) */ INTO T2
  SELECT /*+ PARALLEL(8) */ * FROM T1
/

COMMIT
/

ALTER TABLE T2 LOGGING
/

Task completed in 7.277 seconds

Choć drugi sposób kopiowania danych wydaje się dużo szybszy, to nie jest to remedium na każde powolne kopiowanie danych. Do każdej czynności kopiowania danych należy podchodzić indywidualnie i brać pod uwagę takie elementy jak utworzone indexy, partycje na tabeli, joby działające w tle, charakterystyke środowiska (OLAP czy OLTP), ilość dostępnej przestrzeni dyskowej, czy choćby wielkość pojedynczego rekordu.

Szymon

Komentarze |0|