Wrong Results Of Select Using Group By On Partition By Reference Table

(Doc ID 1352988.1)

Last updated on JULY 07, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

 

When hash-group function is used the result of the query is different than if sort-group function is used.
So the result is completely different when "order by" is added to the select. Without it the result is incorrect.


Example:

SQL> CREATE TABLE t_master (
2 master_id NUMBER NOT NULL,
3 part_id NUMBER (10,0) NOT NULL,
4 part_date DATE NOT NULL,
constraint pk_master primary key (part_id, master_id)
5 6 )
7 PARTITION BY LIST (part_id)
8 SUBPARTITION BY RANGE (part_date)
9 SUBPARTITION TEMPLATE(
10 SUBPARTITION SP_OLD_VALUES VALUES LESS THAN (TO_DATE('01-10-2007','DD-MM-YYYY')),
11 SUBPARTITION SP_2007_10 VALUES LESS THAN (TO_DATE('01-11-2007','DD-MM-YYYY')),
12 SUBPARTITION SP_2007_11 VALUES LESS THAN (TO_DATE('01-12-2007','DD-MM-YYYY')),
13 SUBPARTITION SP_2007_12 VALUES LESS THAN (TO_DATE('01-01-2008','DD-MM-YYYY')),
SUBPARTITION SP_2008_01 VALUES LESS THAN (TO_DATE('01-02-2008','DD-MM-YYYY')),
14 15 SUBPARTITION SP_2008_02 VALUES LESS THAN (TO_DATE('01-03-2008','DD-MM-YYYY')),
SUBPARTITION SP_2008_03 VALUES LESS THAN (TO_DATE('01-04-2008','DD-MM-YYYY')),
16 17 SUBPARTITION SP_2008_04 VALUES LESS THAN (TO_DATE('01-05-2008','DD-MM-YYYY')),
SUBPARTITION SP_2008_05 VALUES LESS THAN (TO_DATE('01-06-2008','DD-MM-YYYY')),
18 19 SUBPARTITION SP_2008_06 VALUES LESS THAN (TO_DATE('01-07-2008','DD-MM-YYYY')),
SUBPARTITION SP_2008_07 VALUES LESS THAN (TO_DATE('01-08-2008','DD-MM-YYYY')),
20 21 SUBPARTITION SP_2008_08 VALUES LESS THAN (TO_DATE('01-09-2008','DD-MM-YYYY')),
22 SUBPARTITION SP_2008_09 VALUES LESS THAN (TO_DATE('01-10-2008','DD-MM-YYYY')),
23 SUBPARTITION SP_2008_10 VALUES LESS THAN (TO_DATE('01-11-2008','DD-MM-YYYY')),
SUBPARTITION SP_2008_11 VALUES LESS THAN (TO_DATE('01-12-2008','DD-MM-YYYY')),
24 25 SUBPARTITION SP_2008_12 VALUES LESS THAN (TO_DATE('01-01-2009','DD-MM-YYYY')),
26 SUBPARTITION SP_2009_01 VALUES LESS THAN (TO_DATE('01-02-2009','DD-MM-YYYY')),
SUBPARTITION SP_2009_02 VALUES LESS THAN (TO_DATE('01-03-2009','DD-MM-YYYY')),
27 28 SUBPARTITION SP_2009_03 VALUES LESS THAN (TO_DATE('01-04-2009','DD-MM-YYYY')),
29 SUBPARTITION SP_2009_04 VALUES LESS THAN (TO_DATE('01-05-2009','DD-MM-YYYY')),
30 SUBPARTITION SP_2009_05 VALUES LESS THAN (TO_DATE('01-06-2009','DD-MM-YYYY')),
31 SUBPARTITION SP_2009_06 VALUES LESS THAN (TO_DATE('01-07-2009','DD-MM-YYYY')),
SUBPARTITION SP_2009_07 VALUES LESS THAN (TO_DATE('01-08-2009','DD-MM-YYYY')),
32 33 SUBPARTITION SP_2009_08 VALUES LESS THAN (TO_DATE('01-09-2009','DD-MM-YYYY')),
34 SUBPARTITION SP_2009_09 VALUES LESS THAN (TO_DATE('01-10-2009','DD-MM-YYYY')),
35 SUBPARTITION SP_2009_10 VALUES LESS THAN (TO_DATE('01-11-2009','DD-MM-YYYY')),
36 SUBPARTITION SP_2009_11 VALUES LESS THAN (TO_DATE('01-12-2009','DD-MM-YYYY')),
37 SUBPARTITION SP_2009_12 VALUES LESS THAN (TO_DATE('01-01-2010','DD-MM-YYYY')),
38 SUBPARTITION SP_2010_01 VALUES LESS THAN (TO_DATE('01-02-2010','DD-MM-YYYY')),
39 SUBPARTITION SP_2010_02 VALUES LESS THAN (TO_DATE('01-03-2010','DD-MM-YYYY')),
40 SUBPARTITION SP_2010_03 VALUES LESS THAN (TO_DATE('01-04-2010','DD-MM-YYYY')),
41 SUBPARTITION SP_2010_04 VALUES LESS THAN (TO_DATE('01-05-2010','DD-MM-YYYY')),
42 SUBPARTITION SP_2010_05 VALUES LESS THAN (TO_DATE('01-06-2010','DD-MM-YYYY')),
43 SUBPARTITION SP_2010_06 VALUES LESS THAN (TO_DATE('01-07-2010','DD-MM-YYYY')),
SUBPARTITION SP_2010_07 VALUES LESS THAN (TO_DATE('01-08-2010','DD-MM-YYYY')),
44 45 SUBPARTITION SP_2010_08 VALUES LESS THAN (TO_DATE('01-09-2010','DD-MM-YYYY')),
46 SUBPARTITION SP_2010_09 VALUES LESS THAN (TO_DATE('01-10-2010','DD-MM-YYYY')),
47 SUBPARTITION SP_2010_10 VALUES LESS THAN (TO_DATE('01-11-2010','DD-MM-YYYY')),
48 SUBPARTITION SP_2010_11 VALUES LESS THAN (TO_DATE('01-12-2010','DD-MM-YYYY')),
49 SUBPARTITION SP_2010_12 VALUES LESS THAN (TO_DATE('01-01-2011','DD-MM-YYYY'))
50 )
51 (
52 PARTITION DEF_PART VALUES (0)
53 )
ENABLE ROW MOVEMENT; 54

Table created.

SQL> alter table t_master noparallel;

Table altered.



SQL> alter table t_master add partition PART1 values (1);

Table altered.

SQL>alter table t_master add partition PART2 values (2);

Table altered.

SQL>alter table t_master add partition PART3 values (3);

Table altered.

SQL> insert into t_master
(
master_id ,
part_id,
part_date
) select
rownum,
1,
TO_DATE('01-01-2011','DD-MM-YYYY') - rownum
from all_objects
where rownum < 1000; 2 3 4 5 6 7 8 9 10 11 12

999 rows created.

SQL> insert into t_master
(
master_id ,
part_id,
part_date
)
select
rownum,
2,
TO_DATE('01-01-2011','DD-MM-YYYY') - rownum
from all_objects
where rownum < 1000; 2 3 4 5 6 7 8 9 10 11 12

999 rows created.

SQL> insert into t_master
(
master_id ,
part_id,
part_date
)
select
rownum,
3,
TO_DATE('01-01-2011','DD-MM-YYYY') - rownum
from all_objects
where rownum < 1000; 2 3 4 5 6 7 8 9 10 11 12

999 rows created.

SQL> commit;

Commit complete.

SQL> CREATE TABLE t_detail(
2 master_Id NUMBER not null,
3 part_id NUMBER not null,
4 CONSTRAINT FK_master_id FOREIGN KEY (part_id, master_id)
5 REFERENCES T_master(part_id, master_id)
6 ON DELETE CASCADE
7 )
PARTITION BY reference (FK_master_id)
8 9 ENABLE ROW MOVEMENT;


Table created.

SQL> SQL>
SQL>
SQL>
SQL> alter table t_detail add constraint PK_detail
primary key (part_id, master_id ) using index
(create unique index i_detail on t_detail (part_id, master_id) local); 2 3

Table altered.

SQL> insert into t_detail
(
part_id,
master_id
)
select part_id, master_id from t_master;

commit; 2 3 4 5 6
2997 rows created.

SQL> SQL>

Commit complete.

SQL> select count(*), part_id from t_master group by part_id;

COUNT(*) PART_ID
---------- ----------
999 1
999 2
999 3

3 rows selected.
Result: Correct.

SQL> select count(*), part_id from t_master group by part_id order by part_id;

COUNT(*) PART_ID
---------- ----------
999 1
999 2
999 3

3 rows selected.
Result: Correct.

SQL> select count(*), part_id from t_detail group by part_id order by part_id;

COUNT(*) PART_ID
---------- ----------
999 1
999 2
999 3
3 rows selected.

Result: Correct.

Explain plan:

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3035 | 39455 | 8 (13)| 00:00:01 | | |
| 1 | SORT GROUP BY | | 3035 | 39455 | 8 (13)| 00:00:01 | | |
| 2 | PARTITION REFERENCE ALL| | 3035 | 39455 | 7 (0)| 00:00:01 | 1 | 160 |
| 3 | TABLE ACCESS FULL | T_DETAIL | 3035 | 39455 | 7 (0)| 00:00:01 | 1 | 160 |
-----------------------------------------------------------------------------------------------------

SQL> select count(*), part_id from t_detail group by part_id;

COUNT(*) PART_ID
---------- ----------
24 1
31 1
30 1
... ...
... ...

24 2
31 2
30 2
... ...
... ...

24 3
31 3
30 3
... ...

... ...

99 rows selected.
Result: Wrong( It should be 3 rows selected)

Explain plan:

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3035 | 39455 | 8 (13)| 00:00:01 | | |
| 1 | PARTITION REFERENCE ALL| | 3035 | 39455 | 8 (13)| 00:00:01 | 1 | 160 |
| 2 | HASH GROUP BY | | 3035 | 39455 | 8 (13)| 00:00:01 | | |
| 3 | TABLE ACCESS FULL | T_DETAIL | 3035 | 39455 | 7 (0)| 00:00:01 | 1 | 160 |
----------------------------------------------------------------------------------------------------

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