Wrong Results Caused by DELETE Statement That SELECT Subparition key value From Subquery Specified SUBPARTITION
(Doc ID 2658358.1)
Last updated on APRIL 02, 2025
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 19.5.0.0.0 [Release 12.2 to 19]Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A to N/A
Gen 2 Exadata Cloud at Customer - Version N/A to N/A [Release All Releases]
Information in this document applies to any platform.
Symptoms
- No data row was deleted when execute DELETE statement that SELECT subparition key value from subquery specified SUBPARTITION(subparition name), while delete rows correctly when execute DELETE statement specified literal subpartition key value.
SQL> select distinct <SUBPARTITION_KEY_COL>
2 from <PARTITION_TABLE2> SUBPARTITION(TBL_20191216_001)
3 where <ANOTHER_COL> = '64'
4 and <PARTITION_KEY_COL> = '201951'
5 ;
<SUBPARTITION_KEY_COL>
-----------
118146
SQL> --Correct results
SQL> DELETE FROM <PARTITION_TABLE1>
2 WHERE <ANOTHER_COL> = '64'
3 AND <PARTITION_KEY_COL> = '201951'
4 AND <SUBPARTITION_KEY_COL> in(118146)
5 ;
1010 rows deleted.
SQL> rollback;
Rollback complete.
SQL> --Wrong results
SQL> DELETE FROM <PARTITION_TABLE1>
2 WHERE <ANOTHER_COL> = '64'
3 AND <PARTITION_KEY_COL> = '201951'
4 AND <SUBPARTITION_KEY_COL> in(
5 select distinct <SUBPARTITION_KEY_COL>
6 from <PARTITION_TABLE2> SUBPARTITION(TBL_20191216_001)
7 where <ANOTHER_COL> = '64'
8 and <PARTITION_KEY_COL> = '201951'
9 )
10 ;
0 rows deleted.
SQL>
SQL> rollback;
Rollback complete.
SQL> - Execution plan shows that subparition key col self-join predicate applied on HASH JOIN SEMI operation.
-------------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
-------------------------------------------------------------+-----------------------------------+---------------+
| 0 | DELETE STATEMENT | | | | 3201 | | | |
| 1 | DELETE | <PARTITION_TABLE1> | | | | | | |
| 2 | HASH JOIN SEMI | | 199K | 8172K | 3201 | 00:00:33 | | |
| 3 | PARTITION RANGE SINGLE | | 199K | 5182K | 308 | 00:00:04 | 38 | 38 |
| 4 | PARTITION HASH ALL | | 199K | 5182K | 308 | 00:00:04 | 1 | 16 |
| 5 | INDEX RANGE SCAN |PK_<PARTITION_TABLE1>| 199K | 5182K | 308 | 00:00:04 | 593 | 608 |
| 6 | PARTITION COMBINED ITERATOR | | 1574K | 23M | 2733 | 00:00:28 | KEY | KEY |
| 7 | INDEX RANGE SCAN |PK_<PARTITION_TABLE2>| 1574K | 23M | 2733 | 00:00:28 | 593 | 593 |
-------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
2 - access("<SUBPARTITION_KEY_COL>"="<SUBPARTITION_KEY_COL>") *<<<<<<<<--Subparition key col self-join predicate
5 - access("<ANOTHER_COL>"='64' AND "<PARTITION_KEY_COL>"=201951)
5 - filter("<PARTITION_KEY_COL>"=201951)
7 - access("<ANOTHER_COL>"='64' AND "<PARTITION_KEY_COL>"=201951)
7 - filter("<PARTITION_KEY_COL>"=201951)
- 10046 trace shows that the cr (consistent read) of inner side of HASH JOIN SEMI is 0 and related table or index was not accessed.
0 0 0 DELETE <PARTITION_TABLE1> (cr=40 pr=0 pw=0 time=1 us)
0 0 0 HASH JOIN SEMI (cr=40 pr=0 pw=0 time=1 us cost=3405 size=8354734 card=203774)
5193 5193 5193 PARTITION RANGE SINGLE PARTITION: 38 38 (cr=40 pr=0 pw=0 time=1 us cost=307 size=5298124 card=203774)
5193 5193 5193 PARTITION HASH ALL PARTITION: 1 16 (cr=40 pr=0 pw=0 time=1 us cost=307 size=5298124 card=203774)
5193 5193 5193 INDEX RANGE SCAN PK_<PARTITION_TABLE1> PARTITION: 593 608 (cr=40 pr=0 pw=0 time=16 us cost=307 size=5298124 card=203774)(object id 1658530)
0 0 0 PARTITION COMBINED ITERATOR PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=1 us cost=2937 size=24334620 card=1622308) *<<<<<<<<--cr=0
0 0 0 INDEX RANGE SCAN PK_<PARTITION_TABLE2> PARTITION: 593 593 (cr=0 pr=0 pw=0 time=0 us cost=2937 size=24334620 card=1622308)(object id 1480618)*<<<<<<<<--cr=0 - This problem can be avoided by set "_and_pruning_enabled" = false, and 10046 trace shows that the cr (consistent read) of inner side of HASH JOIN SEMI is more than 0 and related table or index was fetched target rows.
0 0 0 DELETE <PARTITION_TABLE1> (cr=70 pr=93 pw=0 time=1 us)
1010 1010 1010 HASH JOIN SEMI (cr=64 pr=62 pw=0 time=1 us cost=3564 size=8354734 card=203774)
5193 5193 5193 PARTITION RANGE SINGLE PARTITION: 38 38 (cr=40 pr=40 pw=0 time=1 us cost=307 size=5298124 card=203774)
5193 5193 5193 PARTITION HASH ALL PARTITION: 1 16 (cr=40 pr=40 pw=0 time=1 us cost=307 size=5298124 card=203774)
5193 5193 5193 INDEX RANGE SCAN PK_<PARTITION_TABLE1> PARTITION: 593 608 (cr=40 pr=40 pw=0 time=16 us cost=307 size=5298124 card=203774)(object id 1658530)
8796 8796 8796 PARTITION COMBINED ITERATOR PARTITION: KEY KEY (cr=24 pr=22 pw=0 time=1 us cost=3096 size=24389415 card=1625961)*<<<<<<<<--
8796 8796 8796 INDEX RANGE SCAN <PARTITION_TABLE1_INDEX> PARTITION: 593 593 (cr=24 pr=22 pw=0 time=1 us cost=3096 size=24389415 card=1625961)(object id 1416586)*<<<<<<<<--
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 |
| Cause |
| Solution |
| References |