Performance Degradation of DMLs after Creating a 'PARTITION BY REFERENCE' Table.

(Doc ID 1064364.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 to 11.1.0.7 - Release: 11.1 to 11.1
Information in this document applies to any platform.
Reviewed on 22-JUL-2011.

Symptoms

With the presence of a "PARTITION BY REFERENCE" table, inserts into the corresponding parent table take longer.  

For example, if a second table exists "test_A " that has a "reference partition" to the parent table "test" then the inserts to the parent table are 20 times longer. 

Dropping the partition by reference table (test_A) leads to insert into parent table working fine.

Example:

-- Without the "partition by reference" table
SQL> conn test/test
Connected.
SQL> create table test
2 (
3 id number,
4 cr_date timestamp,
5 set_date timestamp ,
6 paid_date timestamp
7 ) partition by range (set_date)
8 (
9 partition p1 values less than (TO_TIMESTAMP('01-DEC-2009','DD-MON-YYYY')),
10 partition p2 values less than (TO_TIMESTAMP('01-JAN-2010','DD-MON-YYYY')),
11 partition p3 values less than (TO_TIMESTAMP('01-FEB-2010','DD-MON-YYYY')),
12 partition p4 values less than (TO_TIMESTAMP('01-MAR-2010','DD-MON-YYYY')),
13 partition p5 values less than (TO_TIMESTAMP('01-APR-2010','DD-MON-YYYY')),
14 partition p6 values less than (TO_TIMESTAMP('01-MAY-2010','DD-MON-YYYY')),
15 partition p7 values less than (TO_TIMESTAMP('01-JUN-2010','DD-MON-YYYY')),
16 partition p8 values less than (TO_TIMESTAMP('01-JUL-2010','DD-MON-YYYY')),
17 partition p9 values less than (TO_TIMESTAMP('01-AUG-2010','DD-MON-YYYY')),
18 partition p10 values less than (TO_TIMESTAMP('01-SEP-2010','DD-MON-YYYY')),
19 partition p11 values less than (TO_TIMESTAMP('01-OCT-2010','DD-MON-YYYY')),
20 partition p12 values less than (TO_TIMESTAMP('01-NOV-2010','DD-MON-YYYY'))
21 )
22 enable row movement;

Table created.

SQL> alter table test add partition p21 values less than (TO_TIMESTAMP('01-DEC-2010','DD-MON-YYYY'));
....
SQL> alter table test add partition p72 values less than (TO_TIMESTAMP('01-NOV-2013','DD-MON-YYYY'));

Table altered.

SQL> insert into test values(5,sysdate,sysdate+60,null);
SQL> insert into test values(6,sysdate,sysdate+90,null);

1 row created.

SQL> analyze table TEST compute statistics;

Table analyzed.

SQL> ALTER TABLE "TEST" ADD CONSTRAINT TEST_PK PRIMARY KEY ("ID") USING INDEX;

Table altered.

SQL> CREATE OR REPLACE PROCEDURE pop_test
2 AS
3 pop number:=2*100000;
4 cnt number:=0;
st number:=0;
5 6 day number:=1;
Cursor c1 IS
7 8 SELECT MAX(id) max_id
9 FROM test;
10 BEGIN
11
12 FOR test_rec IN c1
13 LOOP
14 st:=test_rec.max_id+1;
15 END LOOP;
16 cnt:=st;
17
18 WHILE ( cnt < st + pop ) LOOP
19 cnt:=cnt+1;
20 insert into test values(cnt,sysdate,sysdate+day,sysdate+day);
21 if mod(cnt,200000)=0 then
22 commit;
23 day:=day+1;
24 end if;
25
26 END LOOP;
27
28 END;
29
30 /

Procedure created.

-- Check the ellapsed time of the insert --

SQL> SET TIMING ON
SQL> exec pop_test;

PL/SQL procedure successfully completed.

Elapsed: 00:01:34.81


Note change in elapsed time with "partition by reference" table:
-- Now create the PARTITION BY REFERENCE table referring the TEST table and check the ellapsed time of the insert --

SQL> CREATE TABLE test_seln (
2 t_id number,
3 r_id number NOT NULL,
4 price number,
5 CONSTRAINT test_seln_fk
6 FOREIGN KEY(r_id) REFERENCES test(id)
7 )
8 PARTITION BY REFERENCE (test_seln_fk)
9 PARALLEL
10 ENABLE ROW MOVEMENT;

CREATE INDEX test_seln_fk_ind ON test_seln(r_id);


SQL> EXEC pop_test;

PL/SQL procedure successfully completed.

Elapsed: 00:20:10.13

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms