-------------------------------------------------------------------------------------------------
-- Script : nologging_inserts_03.sql
-------------------------------------------------------------------------------------------------
-- This script demonstrates effect of nologging on heap table for bulk insert.
-- Behavior is different from the multi row insert.
--
-- Author : Riyaj Shamsudeen
-- No implied or explicit warranty !
-------------------------------------------------------------------------------------------------
set serveroutput on size 1000000
prompt Test case : Nologging on HEAP table : Bulk inserts : No index
set timing off
set feedback off
drop table ilp_activity_heap;
create table ilp_activity_heap
(
item number not null,
period number not null,
location number not null,
item_type number not null,
sales_units number(*,8),
sales_price number(*,8),
ap_units number (*,8),
ap_price number(*,8),
fc_units number (*,8),
fc_price number(*,8)
)
nologging
storage (initial 10M next 10M pctincrease 0)
;
drop type ilp_activity_table
/
create or replace type ilp_activity_type as object
(
item number ,
period number ,
location number ,
item_type number,
sales_units number,
sales_price number,
ap_units number,
ap_price number,
fc_units number ,
fc_price number
)
/
create or replace type ilp_activity_table is table of ilp_activity_type
/
set feedback on
declare
i number;
l_begin_redo_size number;
l_end_redo_size number;
l_generated_redo number;
l_begin_time number;
l_end_time number;
l_elapsed_time number;
type item_tab is table of ilp_activity_heap.item%TYPE;
type period_tab is table of ilp_activity_heap.period%TYPE;
type location_tab is table of ilp_activity_heap.location%TYPE;
type item_type_tab is table of ilp_activity_heap.item_type%TYPE;
type sales_units_tab is table of ilp_activity_heap.sales_units%TYPE;
type sales_price_tab is table of ilp_activity_heap.sales_price%TYPE;
type ap_units_tab is table of ilp_activity_heap.ap_units%TYPE;
type ap_price_tab is table of ilp_activity_heap.ap_price%TYPE;
type fc_units_tab is table of ilp_activity_heap.fc_units%TYPE;
type fc_price_tab is table of ilp_activity_heap.fc_price%TYPE;
l_item_tab item_tab := item_tab ();
l_period_tab period_tab:= period_tab();
l_location_tab location_tab:=location_tab();
l_item_type_tab item_type_tab:=item_type_tab();
l_sales_units_tab sales_units_tab:=sales_units_tab();
l_sales_price_tab sales_price_tab:=sales_price_tab();
l_ap_units_tab ap_units_tab:=ap_units_tab();
l_ap_price_tab ap_price_tab:=ap_price_tab();
l_fc_units_tab fc_units_tab:=fc_units_tab();
l_fc_price_tab fc_price_tab:=fc_price_tab();
l_index number := 1;
begin
l_item_tab.extend (79950);
l_period_tab.extend (79950);
l_location_tab.extend (79950);
l_item_type_tab.extend (79950);
l_sales_units_tab.extend (79950);
l_sales_price_tab.extend (79950);
l_ap_units_tab.extend (79950);
l_ap_price_tab.extend (79950);
l_fc_units_tab.extend (79950);
l_fc_price_tab.extend (79950);
for i in 1 .. 6 loop
for j in 1 .. 5 loop
for k in 1 .. 533 loop
for l in 1 .. 5 loop
l_item_tab (l_index) := i;
l_period_tab (l_index) := j;
l_location_tab (l_index) := k;
l_item_type_tab (l_index) := l;
l_sales_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_sales_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_ap_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_ap_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_fc_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_fc_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_index := l_index +1;
end loop;
end loop;
end loop;
end loop;
select get_my_statistics('redo size') into l_begin_redo_size from dual;
l_begin_time := dbms_utility.get_time;
FORALL j in l_item_tab.first .. l_item_tab.last
insert into ilp_activity_heap
values
(
l_item_tab (j),
l_period_tab (j),
l_location_tab (j) ,
l_item_type_tab (j),
l_sales_units_tab (j),
l_sales_price_tab (j),
l_ap_units_tab (j),
l_ap_price_tab (j) ,
l_fc_units_tab (j) ,
l_fc_price_tab (j)
);
commit;
select get_my_statistics('redo size') into l_end_redo_size from dual;
l_end_time := dbms_utility.get_time;
dbms_output.put_line ('Total redo generated for heap table with no index ==>'||to_char( l_end_redo_size - l_begin_redo_size));
dbms_output.put_line ('Elapsed time in seconds ==>'||to_char(trunc(( l_end_time-l_begin_time)/100, 2 )));
end;
/
prompt Test case : Nologging on HEAP :Bulk inserts with append hint- no index
set timing off
set feedback off
drop table ilp_activity_heap;
create table ilp_activity_heap
(
item number not null,
period number not null,
location number not null,
item_type number not null,
sales_units number(*,8),
sales_price number(*,8),
ap_units number (*,8),
ap_price number(*,8),
fc_units number (*,8),
fc_price number(*,8)
)
nologging
storage (initial 10M next 10M pctincrease 0)
;
drop type ilp_activity_table
/
create or replace type ilp_activity_type as object
(
item number ,
period number ,
location number ,
item_type number,
sales_units number,
sales_price number,
ap_units number,
ap_price number,
fc_units number ,
fc_price number
)
/
create or replace type ilp_activity_table is table of ilp_activity_type
/
set feedback on
declare
i number;
l_begin_redo_size number;
l_end_redo_size number;
l_generated_redo number;
l_begin_time number;
l_end_time number;
l_elapsed_time number;
type item_tab is table of ilp_activity_heap.item%TYPE;
type period_tab is table of ilp_activity_heap.period%TYPE;
type location_tab is table of ilp_activity_heap.location%TYPE;
type item_type_tab is table of ilp_activity_heap.item_type%TYPE;
type sales_units_tab is table of ilp_activity_heap.sales_units%TYPE;
type sales_price_tab is table of ilp_activity_heap.sales_price%TYPE;
type ap_units_tab is table of ilp_activity_heap.ap_units%TYPE;
type ap_price_tab is table of ilp_activity_heap.ap_price%TYPE;
type fc_units_tab is table of ilp_activity_heap.fc_units%TYPE;
type fc_price_tab is table of ilp_activity_heap.fc_price%TYPE;
l_item_tab item_tab := item_tab ();
l_period_tab period_tab:= period_tab();
l_location_tab location_tab:=location_tab();
l_item_type_tab item_type_tab:=item_type_tab();
l_sales_units_tab sales_units_tab:=sales_units_tab();
l_sales_price_tab sales_price_tab:=sales_price_tab();
l_ap_units_tab ap_units_tab:=ap_units_tab();
l_ap_price_tab ap_price_tab:=ap_price_tab();
l_fc_units_tab fc_units_tab:=fc_units_tab();
l_fc_price_tab fc_price_tab:=fc_price_tab();
l_index number := 1;
begin
l_item_tab.extend (79950);
l_period_tab.extend (79950);
l_location_tab.extend (79950);
l_item_type_tab.extend (79950);
l_sales_units_tab.extend (79950);
l_sales_price_tab.extend (79950);
l_ap_units_tab.extend (79950);
l_ap_price_tab.extend (79950);
l_fc_units_tab.extend (79950);
l_fc_price_tab.extend (79950);
for i in 1 .. 6 loop
for j in 1 .. 5 loop
for k in 1 .. 533 loop
for l in 1 .. 5 loop
l_item_tab (l_index) := i;
l_period_tab (l_index) := j;
l_location_tab (l_index) := k;
l_item_type_tab (l_index) := l;
l_sales_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_sales_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_ap_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_ap_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_fc_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_fc_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_index := l_index +1;
end loop;
end loop;
end loop;
end loop;
select get_my_statistics('redo size') into l_begin_redo_size from dual;
l_begin_time := dbms_utility.get_time;
FORALL j in l_item_tab.first .. l_item_tab.last
insert /*+ append */ into ilp_activity_heap
values
(
l_item_tab (j),
l_period_tab (j),
l_location_tab (j) ,
l_item_type_tab (j),
l_sales_units_tab (j),
l_sales_price_tab (j),
l_ap_units_tab (j),
l_ap_price_tab (j) ,
l_fc_units_tab (j) ,
l_fc_price_tab (j)
);
commit;
select get_my_statistics('redo size') into l_end_redo_size from dual;
l_end_time := dbms_utility.get_time;
dbms_output.put_line ('Total redo generated with no compression ==>'||to_char( l_end_redo_size - l_begin_redo_size));
dbms_output.put_line ('Elapsed time in seconds ==>'||to_char(trunc(( l_end_time-l_begin_time)/100, 2 )));
end;
/
prompt Test case : Nologging on HEAP table : Bulk inserts : one index
set timing off
set feedback off
drop table ilp_activity_heap;
create table ilp_activity_heap
(
item number not null,
period number not null,
location number not null,
item_type number not null,
sales_units number(*,8),
sales_price number(*,8),
ap_units number (*,8),
ap_price number(*,8),
fc_units number (*,8),
fc_price number(*,8)
)
nologging
storage (initial 10M next 10M pctincrease 0)
;
create index ilp_activity_heap_index on ilp_activity_heap ( item,period,location,item_type)
nologging
storage (initial 10M next 10M )
;
drop type ilp_activity_table
/
create or replace type ilp_activity_type as object
(
item number ,
period number ,
location number ,
item_type number,
sales_units number,
sales_price number,
ap_units number,
ap_price number,
fc_units number ,
fc_price number
)
/
create or replace type ilp_activity_table is table of ilp_activity_type
/
set feedback on
declare
i number;
l_begin_redo_size number;
l_end_redo_size number;
l_generated_redo number;
l_begin_time number;
l_end_time number;
l_elapsed_time number;
type item_tab is table of ilp_activity_heap.item%TYPE;
type period_tab is table of ilp_activity_heap.period%TYPE;
type location_tab is table of ilp_activity_heap.location%TYPE;
type item_type_tab is table of ilp_activity_heap.item_type%TYPE;
type sales_units_tab is table of ilp_activity_heap.sales_units%TYPE;
type sales_price_tab is table of ilp_activity_heap.sales_price%TYPE;
type ap_units_tab is table of ilp_activity_heap.ap_units%TYPE;
type ap_price_tab is table of ilp_activity_heap.ap_price%TYPE;
type fc_units_tab is table of ilp_activity_heap.fc_units%TYPE;
type fc_price_tab is table of ilp_activity_heap.fc_price%TYPE;
l_item_tab item_tab := item_tab ();
l_period_tab period_tab:= period_tab();
l_location_tab location_tab:=location_tab();
l_item_type_tab item_type_tab:=item_type_tab();
l_sales_units_tab sales_units_tab:=sales_units_tab();
l_sales_price_tab sales_price_tab:=sales_price_tab();
l_ap_units_tab ap_units_tab:=ap_units_tab();
l_ap_price_tab ap_price_tab:=ap_price_tab();
l_fc_units_tab fc_units_tab:=fc_units_tab();
l_fc_price_tab fc_price_tab:=fc_price_tab();
l_index number := 1;
begin
l_item_tab.extend (79950);
l_period_tab.extend (79950);
l_location_tab.extend (79950);
l_item_type_tab.extend (79950);
l_sales_units_tab.extend (79950);
l_sales_price_tab.extend (79950);
l_ap_units_tab.extend (79950);
l_ap_price_tab.extend (79950);
l_fc_units_tab.extend (79950);
l_fc_price_tab.extend (79950);
for i in 1 .. 6 loop
for j in 1 .. 5 loop
for k in 1 .. 533 loop
for l in 1 .. 5 loop
l_item_tab (l_index) := i;
l_period_tab (l_index) := j;
l_location_tab (l_index) := k;
l_item_type_tab (l_index) := l;
l_sales_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_sales_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_ap_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_ap_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_fc_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_fc_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_index := l_index +1;
end loop;
end loop;
end loop;
end loop;
select get_my_statistics('redo size') into l_begin_redo_size from dual;
l_begin_time := dbms_utility.get_time;
FORALL j in l_item_tab.first .. l_item_tab.last
insert into ilp_activity_heap
values
(
l_item_tab (j),
l_period_tab (j),
l_location_tab (j) ,
l_item_type_tab (j),
l_sales_units_tab (j),
l_sales_price_tab (j),
l_ap_units_tab (j),
l_ap_price_tab (j) ,
l_fc_units_tab (j) ,
l_fc_price_tab (j)
);
commit;
select get_my_statistics('redo size') into l_end_redo_size from dual;
l_end_time := dbms_utility.get_time;
dbms_output.put_line ('Total redo generated for heap table with no index ==>'||to_char( l_end_redo_size - l_begin_redo_size));
dbms_output.put_line ('Elapsed time in seconds ==>'||to_char(trunc(( l_end_time-l_begin_time)/100, 2 )));
end;
/
prompt Test case : Nologging on HEAP :Bulk inserts with append hint- no index
set timing off
set feedback off
drop table ilp_activity_heap;
create table ilp_activity_heap
(
item number not null,
period number not null,
location number not null,
item_type number not null,
sales_units number(*,8),
sales_price number(*,8),
ap_units number (*,8),
ap_price number(*,8),
fc_units number (*,8),
fc_price number(*,8)
)
nologging
storage (initial 10M next 10M pctincrease 0)
;
create index ilp_activity_index_heap on ilp_activity_heap ( item,period,location,item_type)
nologging
storage (initial 10M next 10M )
;
drop type ilp_activity_table
/
create or replace type ilp_activity_type as object
(
item number ,
period number ,
location number ,
item_type number,
sales_units number,
sales_price number,
ap_units number,
ap_price number,
fc_units number ,
fc_price number
)
/
create or replace type ilp_activity_table is table of ilp_activity_type
/
set feedback on
declare
i number;
l_begin_redo_size number;
l_end_redo_size number;
l_generated_redo number;
l_begin_time number;
l_end_time number;
l_elapsed_time number;
type item_tab is table of ilp_activity_heap.item%TYPE;
type period_tab is table of ilp_activity_heap.period%TYPE;
type location_tab is table of ilp_activity_heap.location%TYPE;
type item_type_tab is table of ilp_activity_heap.item_type%TYPE;
type sales_units_tab is table of ilp_activity_heap.sales_units%TYPE;
type sales_price_tab is table of ilp_activity_heap.sales_price%TYPE;
type ap_units_tab is table of ilp_activity_heap.ap_units%TYPE;
type ap_price_tab is table of ilp_activity_heap.ap_price%TYPE;
type fc_units_tab is table of ilp_activity_heap.fc_units%TYPE;
type fc_price_tab is table of ilp_activity_heap.fc_price%TYPE;
l_item_tab item_tab := item_tab ();
l_period_tab period_tab:= period_tab();
l_location_tab location_tab:=location_tab();
l_item_type_tab item_type_tab:=item_type_tab();
l_sales_units_tab sales_units_tab:=sales_units_tab();
l_sales_price_tab sales_price_tab:=sales_price_tab();
l_ap_units_tab ap_units_tab:=ap_units_tab();
l_ap_price_tab ap_price_tab:=ap_price_tab();
l_fc_units_tab fc_units_tab:=fc_units_tab();
l_fc_price_tab fc_price_tab:=fc_price_tab();
l_index number := 1;
begin
l_item_tab.extend (79950);
l_period_tab.extend (79950);
l_location_tab.extend (79950);
l_item_type_tab.extend (79950);
l_sales_units_tab.extend (79950);
l_sales_price_tab.extend (79950);
l_ap_units_tab.extend (79950);
l_ap_price_tab.extend (79950);
l_fc_units_tab.extend (79950);
l_fc_price_tab.extend (79950);
for i in 1 .. 6 loop
for j in 1 .. 5 loop
for k in 1 .. 533 loop
for l in 1 .. 5 loop
l_item_tab (l_index) := i;
l_period_tab (l_index) := j;
l_location_tab (l_index) := k;
l_item_type_tab (l_index) := l;
l_sales_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_sales_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_ap_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_ap_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_fc_units_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_fc_price_tab (l_index) := trunc(dbms_random.value(1, 100),8);
l_index := l_index +1;
end loop;
end loop;
end loop;
end loop;
select get_my_statistics('redo size') into l_begin_redo_size from dual;
l_begin_time := dbms_utility.get_time;
FORALL j in l_item_tab.first .. l_item_tab.last
insert /*+ append */ into ilp_activity_heap
values
(
l_item_tab (j),
l_period_tab (j),
l_location_tab (j) ,
l_item_type_tab (j),
l_sales_units_tab (j),
l_sales_price_tab (j),
l_ap_units_tab (j),
l_ap_price_tab (j) ,
l_fc_units_tab (j) ,
l_fc_price_tab (j)
);
commit;
select get_my_statistics('redo size') into l_end_redo_size from dual;
l_end_time := dbms_utility.get_time;
dbms_output.put_line ('Total redo generated with no compression ==>'||to_char( l_end_redo_size - l_begin_redo_size));
dbms_output.put_line ('Elapsed time in seconds ==>'||to_char(trunc(( l_end_time-l_begin_time)/100, 2 )));
end;
/
