I'm not seeing anything like that kind of degradation on my machine
SQL> drop table DAILY_BS purge;
Table dropped.
SQL>
SQL> CREATE TABLE DAILY_BS
2 (
3 RDATE DATE NOT NULL ENABLE,
4 CALC_BS_ID NUMBER(12,0) NOT NULL ENABLE,
5 CONTRACT_ID NUMBER(12,0) NOT NULL ENABLE,
6 aq_id number(12,0) not null,
7 BALANCE_TYPE VARCHAR2(1 byte) not null,
8 BALANCE_NOM NUMBER NOT NULL ENABLE,
9 BALANCE_UAH NUMBER NOT NULL ENABLE
10 )
11 PARTITION BY RANGE ("RDATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
12 SUBPARTITION BY HASH ("CALC_BS_ID")
13 SUBPARTITIONS 32
14 (PARTITION "DAILY_BS_INIT" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
15 ;
Table created.
SQL>
SQL> CREATE INDEX DAILY_BS_INDX1 ON DAILY_BS (CONTRACT_ID ASC) LOCAL ;
Index created.
SQL> drop table t purge;
Table dropped.
SQL> create table t ( id int, s date, e date );
Table created.
SQL>
SQL> begin
2 for i in 0 .. 30 loop
3 insert into daily_bs values (
4 date '2014-01-01'+i,
5 i,
6 i,
7 1,
8 'X',
9 i,
10 i);
11 end loop;
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> create or replace
2 procedure hammer(p_id int, p_iter int default 50) is
3 type rnd_list is table of number index by pls_integer;
4 l_rnd rnd_list;
5 begin
6 select dbms_random.value(1,60000000)
7 bulk collect into l_rnd
8 from
9 ( select 1 from dual connect by level <= 2000 ),
10 ( select 1 from dual connect by level <= 2000 )
11 where rownum <= p_iter;
12
13 insert into t values(p_id,sysdate,null);
14 commit;
15
16 for i in 1 .. p_iter loop
17 insert into daily_bs values (
18 date '2014-01-01'+i/p_iter*30,
19 i,
20 l_rnd(i),
21 1,
22 'X',
23 i,
24 i);
25 commit;
26 end loop;
27 update t set e = sysdate where id = p_id;
28 commit;
29 end;
30 /
Procedure created.
SQL> sho err
No errors.
SQL>
SQL>
SQL> declare
2 j int;
3 begin
4 for i in 1 .. 4 loop
5 dbms_job.submit(j,'hammer('||i||',500000);');
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> delete from t;
0 rows deleted.
SQL> select what from user_jobs;
WHAT
------------------------------------------------------------
hammer(2,500000);
hammer(3,500000);
hammer(1,500000);
hammer(4,500000);
SQL> commit;
Commit complete.
SQL> select avg(e-s)*86400 from t;
AVG(E-S)*86400
--------------
130.75
versus
SQL> drop table DAILY_BS purge;
Table dropped.
SQL>
SQL> CREATE TABLE DAILY_BS
2 (
3 RDATE DATE NOT NULL ENABLE,
4 CALC_BS_ID NUMBER(12,0) NOT NULL ENABLE,
5 CONTRACT_ID NUMBER(12,0) NOT NULL ENABLE,
6 aq_id number(12,0) not null,
7 BALANCE_TYPE VARCHAR2(1 byte) not null,
8 BALANCE_NOM NUMBER NOT NULL ENABLE,
9 BALANCE_UAH NUMBER NOT NULL ENABLE
10 )
11 PARTITION BY RANGE ("RDATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
12 SUBPARTITION BY HASH ("CALC_BS_ID")
13 SUBPARTITIONS 32
14 (PARTITION "DAILY_BS_INIT" VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
15 ;
Table created.
SQL>
SQL> CREATE INDEX DAILY_BS_INDX1 ON DAILY_BS (CONTRACT_ID ASC)
2 GLOBAL PARTITION BY RANGE (CONTRACT_ID)
3 (
4 PARTITION DAILY_BS_INDX1_01 VALUES LESS THAN (2000000),
5 PARTITION DAILY_BS_INDX1_02 VALUES LESS THAN (4000000),
6 PARTITION DAILY_BS_INDX1_03 VALUES LESS THAN (6000000),
7 PARTITION DAILY_BS_INDX1_04 VALUES LESS THAN (8000000),
8 PARTITION DAILY_BS_INDX1_05 VALUES LESS THAN (10000000),
9 PARTITION DAILY_BS_INDX1_06 VALUES LESS THAN (12000000),
10 PARTITION DAILY_BS_INDX1_07 VALUES LESS THAN (14000000),
11 PARTITION DAILY_BS_INDX1_08 VALUES LESS THAN (16000000),
12 PARTITION DAILY_BS_INDX1_09 VALUES LESS THAN (18000000),
13 PARTITION DAILY_BS_INDX1_10 VALUES LESS THAN (20000000),
14 PARTITION DAILY_BS_INDX1_11 VALUES LESS THAN (22000000),
15 PARTITION DAILY_BS_INDX1_12 VALUES LESS THAN (24000000),
16 PARTITION DAILY_BS_INDX1_13 VALUES LESS THAN (26000000),
17 PARTITION DAILY_BS_INDX1_14 VALUES LESS THAN (28000000),
18 PARTITION DAILY_BS_INDX1_15 VALUES LESS THAN (30000000),
19 PARTITION DAILY_BS_INDX1_16 VALUES LESS THAN (32000000),
20 PARTITION DAILY_BS_INDX1_17 VALUES LESS THAN (34000000),
21 PARTITION DAILY_BS_INDX1_18 VALUES LESS THAN (36000000),
22 PARTITION DAILY_BS_INDX1_19 VALUES LESS THAN (38000000),
23 PARTITION DAILY_BS_INDX1_20 VALUES LESS THAN (40000000),
24 PARTITION DAILY_BS_INDX1_21 VALUES LESS THAN (42000000),
25 PARTITION DAILY_BS_INDX1_22 VALUES LESS THAN (44000000),
26 PARTITION DAILY_BS_INDX1_23 VALUES LESS THAN (46000000),
27 PARTITION DAILY_BS_INDX1_24 VALUES LESS THAN (48000000),
28 PARTITION DAILY_BS_INDX1_25 VALUES LESS THAN (50000000),
29 PARTITION DAILY_BS_INDX1_26 VALUES LESS THAN (52000000),
30 PARTITION DAILY_BS_INDX1_27 VALUES LESS THAN (54000000),
31 PARTITION DAILY_BS_INDX1_28 VALUES LESS THAN (56000000),
32 PARTITION DAILY_BS_INDX1_29 VALUES LESS THAN (58000000),
33 PARTITION DAILY_BS_INDX1_30 VALUES LESS THAN (60000000),
34 PARTITION DAILY_BS_INDX1_def VALUES LESS THAN (maxvalue)
35 )
36 COMPRESS;
Index created.
SQL>
SQL> drop table t purge;
Table dropped.
SQL> create table t ( id int, s date, e date );
Table created.
SQL>
SQL> begin
2 for i in 0 .. 30 loop
3 insert into daily_bs values (
4 date '2014-01-01'+i,
5 i,
6 i,
7 1,
8 'X',
9 i,
10 i);
11 end loop;
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> create or replace
2 procedure hammer(p_id int, p_iter int default 50) is
3 type rnd_list is table of number index by pls_integer;
4 l_rnd rnd_list;
5 begin
6 select dbms_random.value(1,60000000)
7 bulk collect into l_rnd
8 from
9 ( select 1 from dual connect by level <= 2000 ),
10 ( select 1 from dual connect by level <= 2000 )
11 where rownum <= p_iter;
12
13 insert into t values(p_id,sysdate,null);
14 commit;
15
16 for i in 1 .. p_iter loop
17 insert into daily_bs values (
18 date '2014-01-01'+i/p_iter*30,
19 i,
20 l_rnd(i),
21 1,
22 'X',
23 i,
24 i);
25 commit;
26 end loop;
27 update t set e = sysdate where id = p_id;
28 commit;
29 end;
30 /
Procedure created.
SQL> sho err
No errors.
SQL>
SQL>
SQL> declare
2 j int;
3 begin
4 for i in 1 .. 4 loop
5 dbms_job.submit(j,'hammer('||i||',500000);');
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> delete from t;
0 rows deleted.
SQL> select what from user_jobs;
WHAT
------------------------------------------------------------
hammer(1,500000);
hammer(2,500000);
hammer(3,500000);
hammer(4,500000);
SQL> commit;
Commit complete.
SQL> select avg(e-s)*86400 from t;
AVG(E-S)*86400
--------------
140.5
SQL>