Problēma: Lielāka datu apjoma labošana iekš PL/SQL vietās kur vienkārši SQL rakstīt būtu nepatīkami/neefektīvi.

Risinājums #1:

izmantot FOR ciklu lai apstaigātu datu kursoru un ar UPDATE veikt labojumi ierakstam.

Risinājums #2:

izmantot oracle kolekcijas

  • datus ielādēt kolekcijā izmantojot BULK COLLECT (ja atmiņa pietiks tad dati glabāsies iekš RAM)
SELECT * BULK COLLECT INTO v_bench_data_table FROM bench_data_table;
  • ciklā apstaigāt kolekciju (situācijas kurās vajag, piemēram, apskatīt vēlreiz iepriekšējo ierakstu vai kas sarežģītāks, kas SQL būtu nesmuki)
FOR i IN v_bench_data_table.first .. v_bench_data_table.last LOOP
    v_bench_data_table(i).x := dbms_random.string( 'a', TRUNC( dbms_random.value( 5, 30 ) ) ); -- tikai piemērs, te būtu jābūt daudz sarežģītākam darbam.
END LOOP;
  • saglabāt rezultātu no kolekcijas uz datu tabulu izmantojot FORALL
FORALL i IN v_bench_data_table.first .. v_bench_data_table.last
   UPDATE bench_data_table set x = v_bench_data_table(i).x WHERE x = v_bench_data_table(i).x;

Eksperiments:

CREATE GLOBAL TEMPORARY TABLE bench_data_table ( x VARCHAR2(50) );

INSERT INTO bench_data_table
  SELECT dbms_random.string( 'a', TRUNC( dbms_random.value( 5, 30 ) ) ) x
  FROM dual
    CONNECT BY level <= 10000;

DECLARE
  t TIMESTAMP;
  TYPE t_rows IS TABLE OF bench_data_table%rowtype;
  v_bench_data_table t_rows := t_rows();
BEGIN
  t := systimestamp;
  -- ielasam visus datus kolekcijā
  SELECT * BULK COLLECT INTO v_bench_data_table FROM bench_data_table;
  -- apstradājam katru kolekcijas ierakstu
  FOR i IN v_bench_data_table.first .. v_bench_data_table.last LOOP
    v_bench_data_table(i).x := dbms_random.string( 'a', TRUNC( dbms_random.value( 5, 30 ) ) ); -- tikai piemērs, te būtu jābūt daudz sarežģītākam darbam.
  END LOOP;

  -- saglabājam rezultātu
  FORALL i IN v_bench_data_table.first .. v_bench_data_table.last
    update bench_data_table set x = v_bench_data_table(i).x WHERE x = v_bench_data_table(i).x;
  dbms_output.put_line('BULK COLLECT + FORALL ' || extract (second from systimestamp - t) * 1000 || '(ms)');
  -- tas pasts tikai izmantojot update iekš cikla
  t := systimestamp;
  FOR i IN (SELECT x FROM bench_data_table) LOOP
    UPDATE bench_data_table SET x = dbms_random.string( 'a', TRUNC( dbms_random.value( 5, 30 ) ) ) WHERE x = i.x;
  END LOOP;
  dbms_output.put_line('UPDATE + FOR LOOP ' || extract (second from systimestamp - t) * 1000 || '(ms)');
END;
/
DROP table bench_data_table;

Skripts salīdzina abus rezultātus un izvada patērēto laiku abiem risinājumiem un izvada uz DBMS_OUTPUT.

Risinājums#2 strādā par ~24% ātrāk nekā Risinājums#1.

Rezultāta piemērs:

BULK COLLECT + FORALL 3220,063(ms)
UPDATE + FOR LOOP 4206,575(ms)

Diemžēl Oracle kolekcijas nav tā sintaktiski patīkamākā fīča, bet ātrdarbības uzlabojums kritiskās vietās var noderēt. Var arī rasties problēmas, ja apstrādājamo datu apjoms ir lielāks nekā RAMā lien.