Hey all,
I think I have found a reproducible testcase for select with wrong results at 19.6 DB version. I can reproduce it at our databases, virtual appliance by Oracle, even at LiveSql. But Oracle Support still does not think so and cannot reproduce :) and want from me stats (which is not exported stats as I thought first):
When I say I need stats for the tables, I mean I need a reproducible testcase that includes stats!
So could you please tell me, if you can reproduce same results from this script:
Script at LiveSql: https://livesql.oracle.com/apex/livesql/s/jzc2uyw6ecf2z2ul35nyrxelv
DROP TABLE TEST_SAPFI_COICAR_AT5DAT11;
CREATE TABLE TEST_SAPFI_COICAR_AT5DAT11
("DATUCT" DATE,
"DATUMZPRAC" NUMBER(8,0)
)
ROW STORE COMPRESS ADVANCED
PARTITION BY LIST ("DATUMZPRAC")
(PARTITION "P20000101" VALUES (20000101));
alter table TEST_SAPFI_COICAR_AT5DAT11 add partition P20200414 values (20200414);
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;
commit;
/
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;
/
DROP TABLE TEST_DWF_SAPFI;
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) );
/
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;
/
insert /*+ append */ into TEST_DWF_SAPFI select to_number(to_char(datuct, 'YYYYMMDD')) from TEST_SAPFI_COICAR_AT5DAT11 where datumzprac = 20200414;
commit;
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
);
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
);
Selects at the end are identical expect hint.
From my view first select returns correct result: 15197, but the second returns 8, which is wrong.
Or any advices how to help Support with reproducing? Thanks!