Well, I wasn't totally surprised by the results, but it always great when your theory and belief is backed up with some facts (albeit test results and not real life).
It is clear from the results that the 3rd scenario, IOT Table with a Primary Key index far out performs the others. I have added a blue line at the point of average time this scenario and it's clear that the average is faster than the fastest time in all scenarios except for an Partitioned IOT, where the average time is slightly slower anyway.
In fact, the IOT (3) is approximately 63% faster than a normal Heap Table with an Index.
Please note - these tests may not hold true in your environment, so further testing is required.
/*
Customer Lookup Table Performance Tests
These tests compare six table formats and the performance of looking up a
single ID at a time from the tables.
Feel free to use, resuse and share this test script, there is nothing sensitive.
Author: Zog Gibbens
Date: January 2016
Database Testing: Oracle 12c, version 12.1.0.2
*/
set linesize 1000;
-- create a table to hold the test timings
drop table test_timings purge
/
create table test_timings
(
test_group_id number not null,
test_description varchar2(50) not null,
test_id number not null,
test_iteration number not null,
start_time timestamp not null,
end_time timestamp not null,
constraint test_timings_pk primary key (test_id, test_group_id, test_iteration)
)
/
drop sequence test_timings_seq
/
create sequence test_timings_seq start with 1 increment by 1 nocycle
/
drop sequence test_group_seq
/
create sequence test_group_seq start with 1 increment by 1 nocycle
/
-- create the customer profile: standard heap table with primary key index.
create table customer_profile
(
customer_id number not null,
gender varchar2(1),
age number,
segment_id number,
profile_1 number,
profile_2 number,
profile_3 number,
profile_4 number,
profile_5 number,
profile_6 number,
constraint cutomer_profile_pk primary key (customer_id)
)
/
create or replace procedure customer_profile_insert(number_of_rows in number, number_of_repeats in number default 1)
as
num_rows number := 0;
begin
execute immediate 'truncate table customer_profile';
for indx in 1 .. customer_profile_insert.number_of_repeats loop
insert /*+ append */ into customer_profile
(customer_id, gender, age, segment_id, profile_1, profile_2, profile_3, profile_4, profile_5, profile_6)
with v1 as (select rownum n from dual connect by level <= customer_profile_insert.number_of_rows) -- create 2m rows
select (customer_profile_insert.number_of_rows * (indx-1)) + n customer_id,
case when round(sys.dbms_random.value(0,1)) = 0 then 'F' else 'M' end gender,
round(sys.dbms_random.value(16,80)) age,
round(sys.dbms_random.value(1,5)) segment_id,
round(sys.dbms_random.value(1,25)) profile_1,
round(sys.dbms_random.value(1,25)) profile_2,
round(sys.dbms_random.value(1,25)) profile_3,
round(sys.dbms_random.value(1,25)) profile_4,
round(sys.dbms_random.value(1,25)) profile_5,
round(sys.dbms_random.value(1,25)) profile_6
from v1
order by profile_6; -- randomise the customer_id order
commit;
end loop;
dbms_stats.gather_table_Stats(ownname => USER, tabname => 'CUSTOMER_PROFILE', estimate_percent => 5);
select count(*)
into num_rows
from customer_profile;
dbms_output.put_line(num_rows || ' rows inserted into customer_profile.');
exception
when others then
dbms_output.put_line(dbms_utility.format_error_stack());
dbms_output.put_line(dbms_utility.format_error_backtrace());
dbms_output.put_line(dbms_utility.format_call_stack());
raise;
end customer_profile_insert;
/
create or replace procedure customer_profile_select(number_of_iteractions in number, test_group in number, test_description in varchar2)
as
customer_profile_record customer_profile%rowtype;
max_customer_id customer_profile.customer_id%type;
lookup_customer_id customer_profile.customer_id%type;
test_seq_number test_timings.test_id%type := test_timings_seq.nextval;
start_time test_timings.start_time%type;
end_time test_timings.end_time%type;
begin
-- get the highest customer_id so we can set the upper limit in the query.
-- we know th lowest value is 1.
select max(customer_id)
into customer_profile_select.max_customer_id
from customer_profile;
for indx in 1 .. customer_profile_select.number_of_iteractions loop
customer_profile_select.lookup_customer_id := round(sys.dbms_random.value(1, customer_profile_select.max_customer_id));
start_time := systimestamp;
select *
into customer_profile_record
from customer_profile
where customer_id = customer_profile_select.lookup_customer_id;
end_time := systimestamp;
insert into test_timings
(test_id, test_description,
test_group_id, test_iteration, start_time,
end_time)
values
(customer_profile_select.test_seq_number, customer_profile_select.test_description,
customer_profile_select.test_group, indx, customer_profile_select.start_time,
customer_profile_select.end_time);
end loop;
commit;
exception
when others then
dbms_output.put_line(dbms_utility.format_error_stack());
dbms_output.put_line(dbms_utility.format_error_backtrace());
dbms_output.put_line(dbms_utility.format_call_stack());
raise;
end customer_profile_select;
/
create or replace procedure customer_profile_select_group(number_of_iteractions in number, test_description in varchar2)
as
test_group number := test_group_seq.nextval;
begin
for indx in 1 .. 20 loop
customer_profile_select(customer_profile_select_group.number_of_iteractions,
customer_profile_select_group.test_group,
customer_profile_select_group.test_description);
end loop;
exception
when others then
dbms_output.put_line(dbms_utility.format_error_stack());
dbms_output.put_line(dbms_utility.format_error_backtrace());
dbms_output.put_line(dbms_utility.format_call_stack());
raise;
end customer_profile_select_group;
/
/*
-- Reset Everything
*/
set serveroutput on;
truncate table test_timings;
drop sequence test_timings_seq;
create sequence test_timings_seq start with 1 increment by 1 nocycle;
drop sequence test_group_seq;
create sequence test_group_seq start with 1 increment by 1 nocycle;
/*
-- Test 1, starndard heap table with standard index
*/
drop table customer_profile purge;
create table customer_profile
(
customer_id number not null,
gender varchar2(1),
age number,
segment_id number,
profile_1 number,
profile_2 number,
profile_3 number,
profile_4 number,
profile_5 number,
profile_6 number,
constraint cutomer_profile_pk primary key (customer_id)
)
/
alter procedure customer_profile_insert compile;
alter procedure customer_profile_select compile;
alter procedure customer_profile_select_group compile;
exec customer_profile_insert(1000000,2)
/
exec customer_profile_select_group(200,'1. Heap Table+Index')
/
/*
-- Test 2, starndard heap table with partitions by hash
*/
drop table customer_profile purge;
create table customer_profile
(
customer_id number not null,
gender varchar2(1),
age number,
segment_id number,
profile_1 number,
profile_2 number,
profile_3 number,
profile_4 number,
profile_5 number,
profile_6 number,
constraint cutomer_profile_pk primary key (customer_id)
)
partition by hash (customer_id)
partitions 4;
/
alter procedure customer_profile_insert compile;
alter procedure customer_profile_select compile;
alter procedure customer_profile_select_group compile;
set serveroutput on;
exec customer_profile_insert(1000000,2)
/
exec customer_profile_select_group(200,'2. Heap Table+Index+Partition')
/
/*
-- Test 3, index organised table
*/
drop table customer_profile purge;
create table customer_profile
(
customer_id number not null,
gender varchar2(1),
age number,
segment_id number,
profile_1 number,
profile_2 number,
profile_3 number,
profile_4 number,
profile_5 number,
profile_6 number,
constraint cutomer_profile_pk primary key (customer_id)
)
organization index;
/
alter procedure customer_profile_insert compile;
alter procedure customer_profile_select compile;
alter procedure customer_profile_select_group compile;
set serveroutput on;
exec customer_profile_insert(1000000,2)
/
exec customer_profile_select_group(200,'3. IOT Table+Index')
/
/*
-- Test 4, index organised table hash partitioned
*/
drop table customer_profile purge;
create table customer_profile
(
customer_id number not null,
gender varchar2(1),
age number,
segment_id number,
profile_1 number,
profile_2 number,
profile_3 number,
profile_4 number,
profile_5 number,
profile_6 number,
constraint cutomer_profile_pk primary key (customer_id)
)
organization index
partition by hash (customer_id)
partitions 4;
/
alter procedure customer_profile_insert compile;
alter procedure customer_profile_select compile;
alter procedure customer_profile_select_group compile;
set serveroutput on;
exec customer_profile_insert(1000000,2)
/
exec customer_profile_select_group(200,'4. IOT Table+Index+Partition')
/
/*
-- Test 5, starndard heap table with standard index - InMemory
*/
drop table customer_profile purge;
create table customer_profile
(
customer_id number not null,
gender varchar2(1),
age number,
segment_id number,
profile_1 number,
profile_2 number,
profile_3 number,
profile_4 number,
profile_5 number,
profile_6 number,
constraint cutomer_profile_pk primary key (customer_id)
)
inmemory
/
alter procedure customer_profile_insert compile;
alter procedure customer_profile_select compile;
alter procedure customer_profile_select_group compile;
exec customer_profile_insert(1000000,2)
/
exec customer_profile_select_group(200,'5. InMemory+Heap Table+Index')
/
/*
-- Test 6, starndard heap table with partitions by hash - InMemory
*/
drop table customer_profile purge;
create table customer_profile
(
customer_id number not null,
gender varchar2(1),
age number,
segment_id number,
profile_1 number,
profile_2 number,
profile_3 number,
profile_4 number,
profile_5 number,
profile_6 number,
constraint cutomer_profile_pk primary key (customer_id)
)
partition by hash (customer_id)
partitions 4
inmemory;
/
alter procedure customer_profile_insert compile;
alter procedure customer_profile_select compile;
alter procedure customer_profile_select_group compile;
set serveroutput on;
exec customer_profile_insert(1000000,2)
/
exec customer_profile_select_group(200,'6. InMemoryHeap Table+Index+Partition')
/
/*
------------------------------------------------------------------------------------------
*/
select test_group_id, test_description,
sum(elapsed_seconds) sum_secs, avg(elapsed_seconds) avg_secs,
max(elapsed_seconds) max_secs, min(elapsed_seconds) min_secs
from (
select test_group_id, test_description, test_id, sum(extract(second from end_time-start_time)) elapsed_seconds
from test_timings
group by test_group_id, test_id, test_description
)
group by test_group_id, test_description
order by test_group_id, test_description;
/
select count(*) num_tests from test_timings
/
I suggest that companies need to make every effort to bring the IT and business users together to collaborate on best practice for your deployment. “It isn’t always easy but it’s critical. Equally, while traditional systems are better suited to a ‘waterfall’ approach where technology is introduced all at once, that’s not so true of big data projects. Through the collaboration between IT and the business, you need to focus on the goals and desired outcomes and introduce the technology in phases to reflect those.”
