Hello.
I take than interest result when working in big table.
If i create table and load data and then created index - this example work about 5 min
If i create table and then create index and loading data - this example work about 1 hour
I execute this in parallel.
-- Prepare test data
create table t_src
(
fk_id number not null
);
insert --+ append
into t_big_table
select level - case when mod(level, 2) = 0 then 1 else 0 end as fn_lvl
from dual
connect by level <= 100000000;
commit;
-- Example 1. This example work fast, about 10 min
create table t_dst
(
fk_id number not null
);
insert --+ append parallel(50)
into t_dst
select distinct
fk_id
from t_src;
commit;
create unique index cin_u_dst on t_dst(fk_id) parallel(32);
-- Example 2. This example work slow, about 1 hour
create table t_dst
(
fk_id number not null
);
create unique index cin_u_dst on t_dst(fk_id) parallel(32);
insert --+ append parallel(50)
into t_dst
select distinct
fk_id
from t_src;
commit;
The different between the two example in created the index after or before inserting data. Why such difference?