Özet: Veritabanı üzerinde çok fazla veri üzerinde işlem yapacaksanız(örneğin çok fazla kayıt insert edecekseniz) sürecinizi çok hızlandıracak “Bulk Collect” yöntemi anlatılıyor.
Çok fazla veri üzerinde işlem yapmamız gerekiyorsa Oracle’ın “Bulk Collect” özelliğinin nimetlerinden yararlanabiliriz. Aşağıdaki örnek kullanımı step by step anlatalım, test edelim;
— step -1)
Öncelikle örnek bir dataya ihtiyacımız var. Bunu örneğin DBA_TAB_SUBPARTITIONS dic. view inden elde edebilirim (siz herhangi bir veriyle doldurabilirsiniz tablonuzu). Bu view in yapısına benzer “orhan1″ ve “orhan2″ isimlerinde tablo create ediyorum. orhan1 tablosunu
CREATE TABLE orhan2 --sonra da orhan1 tablosu için yap. ( TABLE_NAME VARCHAR2(30 BYTE) NOT NULL, PARTITION_NAME VARCHAR2(30 BYTE), SUBPARTITION_NAME VARCHAR2(30 BYTE), SUBPARTITION_POSITION NUMBER, TABLESPACE_NAME VARCHAR2(30 BYTE) NOT NULL, PCT_FREE NUMBER NOT NULL, PCT_USED NUMBER, INI_TRANS NUMBER NOT NULL, MAX_TRANS NUMBER NOT NULL, INITIAL_EXTENT NUMBER, NEXT_EXTENT NUMBER, MIN_EXTENT NUMBER, MAX_EXTENT NUMBER, MAX_SIZE NUMBER, PCT_INCREASE NUMBER, FREELISTS NUMBER, FREELIST_GROUPS NUMBER, LOGGING VARCHAR2(3 BYTE), COMPRESSION VARCHAR2(8 BYTE), COMPRESS_FOR VARCHAR2(12 BYTE), NUM_ROWS NUMBER, BLOCKS NUMBER, EMPTY_BLOCKS NUMBER, AVG_SPACE NUMBER, CHAIN_CNT NUMBER, AVG_ROW_LEN NUMBER, SAMPLE_SIZE NUMBER, LAST_ANALYZED DATE, BUFFER_POOL VARCHAR2(7 BYTE), FLASH_CACHE VARCHAR2(7 BYTE), CELL_FLASH_CACHE VARCHAR2(7 BYTE), GLOBAL_STATS VARCHAR2(3 BYTE), USER_STATS VARCHAR2(3 BYTE), INTERVAL VARCHAR2(3 BYTE), SEGMENT_CREATED VARCHAR2(3 BYTE) );
— step -2)
aşağıdaki gibi bir döngü ile datayı çoğaltıyorum;
begin for j in 1..10 loop for i in 1..10 loop insert into orhan2 select TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION, TABLESPACE_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, MAX_SIZE, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION, COMPRESS_FOR, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, GLOBAL_STATS, USER_STATS, INTERVAL, SEGMENT_CREATED from DBA_TAB_SUBPARTITIONS; end loop; commit; end loop; end;
— step -3)
6 milyondan fazla data var elimde şuan. Birindeki dataları diğer tabloya aktarmanın en kısa yollarını arıyorum.
Test için yeterli bir data;
select count(*) from orhan2;
— 6854700
— step -4)
Imlicit cursor (for döngüsü ile yapılan işlem) ile klasik yöntemde öncelikle orhan2 tablo datalarını orhan1 tablosuna insert ederek süresini ölçeceğiz;
----------Implicit (For) Cursor -----------
BEGIN
DBMS_OUTPUT.put_line ('Start:' || SYSTIMESTAMP);
--for a in 1 .. 1000
--loop
FOR x IN (SELECT * FROM orhan2)
LOOP
INSERT INTO orhan1
(TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION,
TABLESPACE_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS,
INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, MAX_SIZE,
PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION,
COMPRESS_FOR, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE,
CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL,
FLASH_CACHE, CELL_FLASH_CACHE, GLOBAL_STATS, USER_STATS, INTERVAL,
SEGMENT_CREATED)
VALUES
(x.TABLE_NAME, x.PARTITION_NAME, x.SUBPARTITION_NAME, x.SUBPARTITION_POSITION,
x.TABLESPACE_NAME, x.PCT_FREE, x.PCT_USED, x.INI_TRANS, x.MAX_TRANS,
x.INITIAL_EXTENT, x.NEXT_EXTENT, x.MIN_EXTENT, x.MAX_EXTENT, x.MAX_SIZE,
x.PCT_INCREASE, x.FREELISTS, x.FREELIST_GROUPS, x.LOGGING, x.COMPRESSION,
x.COMPRESS_FOR, x.NUM_ROWS, x.BLOCKS, x.EMPTY_BLOCKS, x.AVG_SPACE,
x.CHAIN_CNT, x.AVG_ROW_LEN, x.SAMPLE_SIZE, x.LAST_ANALYZED, x.BUFFER_POOL,
x.FLASH_CACHE, x.CELL_FLASH_CACHE, x.GLOBAL_STATS, x.USER_STATS, x.INTERVAL,
x.SEGMENT_CREATED);
END LOOP;
COMMIT;
-- end loop;
DBMS_OUTPUT.put_line ('End:' || SYSTIMESTAMP);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line (SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
Start:12/03/2017 22:18:39,596217000 +03:00
End:12/03/2017 22:48:51,845936000 +03:00
30 dk. sürdü
Data sayımız aynı, zaten farklı beklemiyoruz;
select count(*) from orhan1;
— 6854700
— step 5)
ikinci testimiz için orhan1 tablosunu truncate ederek içini boşaltıyoruz;
truncate table orhan1;
— step 6)
Şimdi “Bulk Collect” özelliğini kullanarak insert edeceğiz ve süreleri karşılaştıracağız.
Aşağıdaki örnek plsql’i kendi kodunuza göre uyarlayarak kullanabilirsiniz. Bu yöntem sayesinde vereceğimiz limit’deki 10.000, 100.000 gibi değer kadar toplu olarak her defasında veriyi yakalayacaktır (fetch) ve çok daha hızlı sonuç alabileceğiz.
DECLARE
CURSOR cur_1 IS
SELECT * FROM orhan2;
TYPE fetch_1 IS TABLE OF cur_1%ROWTYPE;
v_1 fetch_1;
BEGIN
DBMS_OUTPUT.put_line ('Start:' || SYSTIMESTAMP);
OPEN cur_1;
LOOP
FETCH cur_1 BULK COLLECT INTO v_1 LIMIT 10000;
FORALL i IN 1..v_1.COUNT
INSERT INTO orhan1 VALUES v_1(i);
EXIT WHEN cur_1%NOTFOUND;
END LOOP;
CLOSE cur_1;
COMMIT;
DBMS_OUTPUT.put_line ('End:' || SYSTIMESTAMP);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line (SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
Start:12/03/2017 23:39:56,790871000 +03:00
End:12/03/2017 23:41:38,890317000 +03:00
Muhteşem bir sonuç; 1 dk. 42 saniyede bitti! 🙂
Klasik yöntemde çok fazla kaydı insert etmek yerine “Bulk collect” ile çok daha kısa sürede yapabiliriz, benzer şekilde update, delete işlemlerimiz için de bunu uyarlayabilirsiniz.
Faydası olması dileklerimizle…
Orhan Eripek's Oracle Blog 