Reference Partition - DML Fetches Wrong Results From Child Table (Doc ID 1343294.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

Parent table list partitioned with primary key local index. Since child table FK(foreign key) index is specified Local, child table index partition is inherited from parent primary key index. In child table, inserted a record and then updated a foreign key value, querying it with foreign key criteria or selecting only foreign key column shows old value, a wrong result.

Example:
===========

-- Parent table partitioned


SQL> create table empl(
      comp_code varchar2(4) not null,
      emp_code varchar2(11) not null,
      ename varchar2(34) not null)
      partition by list (comp_code) ( partition C1 values ('C1'));


-- Parent table primary key  partitioned

SQL> alter table empl add constraint empl_pk primary key (comp_code,emp_code)
     using index local (partition C1);

-- Child Reference  partitioned table

SQL> create table emp_awards
     (
      award_id integer not null,
      comp_code varchar2(4) not null,
      emp_code varchar2(11) not null,
      award_name varchar2(100) not null,
      constraint e_a_fk foreign key (comp_code, emp_code)
      references empl (comp_code,emp_code)
      ) partition by reference (e_a_fk);


-- child foreign key index

SQL> create index e_a_fk on emp_awards(comp_code,emp_code) local;

-- Child table Primary Key

SQL> alter table emp_awards add constraint emp_award_pk primary key (award_id);


-- Sample data

SQL> insert into empl (comp_code, emp_code, ename) values ('C1', 'E1', 'ABC');
SQL> insert into empl (comp_code, emp_code, ename) values ('C1', 'E2', 'XYZ');
SQL> insert into emp_awards (award_id, comp_code, emp_code, award_name) values (1,   'C1', 'E1', 'A1');
SQL> commit;

-- Analyze index

SQL> begin
     DBMS_STATS.gather_index_stats(user,'E_A_FK');
     DBMS_STATS.gather_index_stats(user,'EMPL_PK');
     end;



SQL> select emp_code from emp_awards where comp_code='C1';

EMP_CODE
---------------------------------
E1



SQL> update emp_awards set emp_code='E2';
SQL> commit;

SQL> select emp_code from emp_awards where comp_code='C1';


EMP_CODE
---------------------------------
E1


Execution Plan
----------------------------------------------------------
Plan hash value: 216200818

-----------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |     1 |    11 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION REFERENCE SINGLE|        |     1 |    11 |     2   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX FAST FULL SCAN     | E_A_FK |     1 |    11 |     2   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------------


Note the incorrect result, the updated value is not shown. Also note that the index E_A_FK was used in the execution plan when the wrong result occurs.


If the index E_A_FK is not being used in the execution plan, correct result occurs.

SQL> select /*+FULL(emp_awards)*/ emp_code from emp_awards where comp_code='C1';

EMP_CODE
---------------------------------
E2


Execution Plan
----------------------------------------------------------
Plan hash value: 3504985375

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |            |     1 |    11 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION REFERENCE SINGLE|            |     1 |    11 |     3   (0)| 00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL        | EMP_AWARDS |     1 |    11 |     3   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------


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