Wrong results with nested loops plan on join two partitioned tables

View All Public Scripts Login to Run Script

Script Name Wrong results with nested loops plan on join two partitioned tables
Visibility Unlisted - anyone with the share link can access
Description Wrong results with nested loops plan on join two partitioned tables serves as bug evidence for Oracle Support
Contributor Michal Telensky
Created Tuesday April 21, 2020

Statement 1
```
 DROP TABLE TEST_SAPFI_COICAR_AT5DAT11
```
ORA-00942: table or view does not exist

Statement 2

 CREATE TABLE TEST_SAPFI_COICAR_AT5DAT11 
   ("DATUCT" DATE, 
	"DATUMZPRAC" NUMBER(8,0) 
   )  
 ROW STORE COMPRESS ADVANCED  
  PARTITION BY LIST ("DATUMZPRAC")  
 (PARTITION "P20000101"  VALUES (20000101))

Table created.

Statement 3

alter table TEST_SAPFI_COICAR_AT5DAT11 add partition P20200414 values (20200414)

Table altered.

Statement 4

insert /*+ append */ into TEST_SAPFI_COICAR_AT5DAT11 
select date'2019-11-20' datuct, 20200414 datumzprac from dual connect by level < 2 union all 
select date'2019-12-20' datuct, 20200414 datumzprac from dual connect by level < 2 union all 
select date'2019-12-29' datuct, 20200414 datumzprac from dual connect by level < 4 union all 
select date'2020-01-01' datuct, 20200414 datumzprac from dual connect by level < 55 union all 
select date'2020-01-08' datuct, 20200414 datumzprac from dual connect by level < 3 union all 
select date'2020-01-13' datuct, 20200414 datumzprac from dual connect by level < 8 union all 
select date'2020-01-14' datuct, 20200414 datumzprac from dual connect by level < 117 union all 
select date'2020-01-15' datuct, 20200414 datumzprac from dual connect by level < 65 union all 
select date'2020-01-30' datuct, 20200414 datumzprac from dual connect by level < 2 union all 
select date'2020-01-31' datuct, 20200414 datumzprac from dual connect by level < 12 union all 
select date'2020-02-01' datuct, 20200414 datumzprac from dual connect by level < 20 union all 
select date'2020-02-05' datuct, 20200414 datumzprac from dual connect by level < 4 union all 
select date'2020-02-10' datuct, 20200414 datumzprac from dual connect by level < 5 union all 
select date'2020-02-12' datuct, 20200414 datumzprac from dual connect by level < 2 union all 
select date'2020-02-17' datuct, 20200414 datumzprac from dual connect by level < 2 union all 
select date'2020-02-21' datuct, 20200414 datumzprac from dual connect by level < 16 union all 
select date'2020-02-29' datuct, 20200414 datumzprac from dual connect by level < 37 union all 
select date'2020-03-01' datuct, 20200414 datumzprac from dual connect by level < 1851 union all 
select date'2020-03-02' datuct, 20200414 datumzprac from dual connect by level < 227 union all 
select date'2020-03-03' datuct, 20200414 datumzprac from dual connect by level < 75 union all 
select date'2020-03-04' datuct, 20200414 datumzprac from dual connect by level < 19 union all 
select date'2020-03-05' datuct, 20200414 datumzprac from dual connect by level < 107 union all 
select date'2020-03-06' datuct, 20200414 datumzprac from dual connect by level < 163 union all 
select date'2020-03-07' datuct, 20200414 datumzprac from dual connect by level < 72 union all 
select date'2020-03-08' datuct, 20200414 datumzprac from dual connect by level < 78 union all 
select date'2020-03-09' datuct, 20200414 datumzprac from dual connect by level < 187 union all 
select date'2020-03-10' datuct, 20200414 datumzprac from dual connect by level < 124 union all 
select date'2020-03-11' datuct, 20200414 datumzprac from dual connect by level < 92 union all 
select date'2020-03-12' datuct, 20200414 datumzprac from dual connect by level < 137 union all 
select date'2020-03-13' datuct, 20200414 datumzprac from dual connect by level < 397 union all 
select date'2020-03-14' datuct, 20200414 datumzprac from dual connect by level < 52 union all 
select date'2020-03-15' datuct, 20200414 datumzprac from dual connect by level < 16 union all 
select date'2020-03-16' datuct, 20200414 datumzprac from dual connect by level < 622 union all 
select date'2020-03-17' datuct, 20200414 datumzprac from dual connect by level < 215 union all 
select date'2020-03-18' datuct, 20200414 datumzprac from dual connect by level < 299 union all 
select date'2020-03-19' datuct, 20200414 datumzprac from dual connect by level < 265 union all 
select date'2020-03-20' datuct, 20200414 datumzprac from dual connect by level < 627 union all 
select date'2020-03-21' datuct, 20200414 datumzprac from dual connect by level < 52 union all 
select date'2020-03-22' datuct, 20200414 datumzprac from dual connect by level < 60 union all 
select date'2020-03-23' datuct, 20200414 datumzprac from dual connect by level < 168 union all 
select date'2020-03-24' datuct, 20200414 datumzprac from dual connect by level < 255 union all 
select date'2020-03-25' datuct, 20200414 datumzprac from dual connect by level < 185 union all 
select date'2020-03-26' datuct, 20200414 datumzprac from dual connect by level < 240 union all 
select date'2020-03-27' datuct, 20200414 datumzprac from dual connect by level < 663 union all 
select date'2020-03-28' datuct, 20200414 datumzprac from dual connect by level < 88 union all 
select date'2020-03-29' datuct, 20200414 datumzprac from dual connect by level < 771 union all 
select date'2020-03-30' datuct, 20200414 datumzprac from dual connect by level < 328 union all 
select date'2020-03-31' datuct, 20200414 datumzprac from dual connect by level < 1675 union all 
select date'2020-04-01' datuct, 20200414 datumzprac from dual connect by level < 641 union all 
select date'2020-04-02' datuct, 20200414 datumzprac from dual connect by level < 251 union all 
select date'2020-04-03' datuct, 20200414 datumzprac from dual connect by level < 84 union all 
select date'2020-04-06' datuct, 20200414 datumzprac from dual connect by level < 325 union all 
select date'2020-04-07' datuct, 20200414 datumzprac from dual connect by level < 366 union all 
select date'2020-04-08' datuct, 20200414 datumzprac from dual connect by level < 459 union all 
select date'2020-04-09' datuct, 20200414 datumzprac from dual connect by level < 2470 union all 
select date'2020-04-10' datuct, 20200414 datumzprac from dual connect by level < 16 union all 
select date'2020-04-11' datuct, 20200414 datumzprac from dual connect by level < 16 union all 
select date'2020-04-12' datuct, 20200414 datumzprac from dual connect by level < 24 union all 
select date'2020-04-13' datuct, 20200414 datumzprac from dual connect by level < 130 union all 
select date'2020-04-14' datuct, 20200414 datumzprac from dual connect by level < 9

15197 row(s) inserted.

Statement 5
```
commit
```
Statement processed.

Statement 6

declare 
    schema_name varchar2(128); 
begin 
    select sys_context('userenv', 'current_schema') into schema_name from dual; 
    dbms_stats.gather_table_stats( 
        ownname => schema_name, 
        tabname => 'TEST_SAPFI_COICAR_AT5DAT11', 
        partname => 'P20200414', 
        estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, 
        method_opt=> 'FOR ALL INDEXED COLUMNS SIZE AUTO' 
    ); 
end;

Statement processed.

Statement 7
```
 DROP TABLE TEST_DWF_SAPFI
```
ORA-00942: table or view does not exist

Statement 8

 CREATE TABLE TEST_DWF_SAPFI 
   ( 
	"DATUMUCETNIPOM_CODE" NUMBER(8,0) NOT NULL ENABLE 
   )  
 ROW STORE COMPRESS ADVANCED  
  PARTITION BY LIST ("DATUMUCETNIPOM_CODE")  
 (PARTITION "P20000101"  VALUES (20000101) )

Table created.

Statement 9

begin 
    for i in (select unique to_char(datuct, 'YYYYMMDD') DATUMUCETNIPOM_CODE from TEST_SAPFI_COICAR_AT5DAT11 order by 1) 
    loop 
        execute immediate 'alter table TEST_DWF_SAPFI add partition P' || i.DATUMUCETNIPOM_CODE || ' values (' || i.DATUMUCETNIPOM_CODE || ')'; 
    end loop; 
end;

Statement processed.

Statement 10

insert /*+ append */ into TEST_DWF_SAPFI select to_number(to_char(datuct, 'YYYYMMDD')) from TEST_SAPFI_COICAR_AT5DAT11 where datumzprac = 20200414

15197 row(s) inserted.

Statement 11
```
commit
```
Statement processed.

Statement 12

select /*+ use_hash(TEST_DWF_SAPFI) */ count(*) count_hash from TEST_DWF_SAPFI 
where exists (SELECT 1 FROM test_sapfi_coicar_at5dat11 
                WHERE DATUMZPRAC = 20200414 
                AND to_char(DATUCT,'YYYYMMDD') = DATUMUCETNIPOM_CODE 
)

COUNT_HASH
15197

Statement 13

select count(*) count_nl from TEST_DWF_SAPFI 
where exists (SELECT /*+ use_nl(test_sapfi_coicar_at5dat11) */ 1 FROM test_sapfi_coicar_at5dat11 
                WHERE DATUMZPRAC = 20200414 
                AND to_char(DATUCT,'YYYYMMDD') = DATUMUCETNIPOM_CODE 
)

COUNT_NL
8

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials