My Oracle Support Banner

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

Last updated on FEBRUARY 25, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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 XXX (
      c_code varchar2(4) not null,
      e_code varchar2(11) not null,
      abcd varchar2(34) not null)
      partition by list (c_code) ( partition C1 values ('C1'));



-- Parent table primary key  partitioned

SQL> alter table XXX add constraint XXX_pk primary key (c_code,e_code)
     using index local (partition C1);


-- Child Reference  partitioned table

SQL> create table yyy
     (
      abc_id integer not null,
      c_code varchar2(4) not null,
      e_code varchar2(11) not null,
      xyz_01 varchar2(100) not null,
      constraint FK1 foreign key (c_code, e_code)
      references XXX (c_code,e_code)
      ) partition by reference (FK1);



-- child foreign key index

SQL> create index FK1 on yyy(c_code,e_code) local;


-- Child table Primary Key

SQL> alter table yyy add constraint PK1 primary key (abc_id);



-- Sample data

SQL> insert into XXX (c_code, e_code, abcd) values ('C1', 'E1', 'ABC');
SQL> insert into XXX (c_code, e_code, abcd) values ('C1', 'E2', 'XYZ');
SQL> insert into yyy (abc_id, c_code, e_code, xyz_01) values (1,   'C1', 'E1', 'A1');
SQL> commit;


-- Analyze index

SQL> begin
     DBMS_STATS.gather_index_stats(user,'FK1');
     DBMS_STATS.gather_index_stats(user,'PK1');
     end;




SQL> select e_code from yyy where c_code='C1';

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



SQL> update yyy set e_code='E2';
SQL> commit;

SQL> select e_code from yyy where c_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     | FK1|     1 |    11 |     2   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------------
 


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


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

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

e_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        | yyy   |     1 |    11 |     3   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------



Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.