Transferring data to a test table

Question

There is a table contact_history with 1.244.000.000 number of data (from 04.03.22-05.06.2022) and with fields contact_dt and contact_dttm. I tried to transfer all the data to test using contact_dt with script:

**DECLARE  
dat date;
begin
dat:= TO_DATE('04.03.2022', 'dd.mm.yyyy');  
while dat<= TO_DATE('05.06.2022', 'dd.mm.yyyy') loop 
INSERT /*+ append enable_parallel_dml parallel(16)*/
INTO CONTACT_HISTORY_TEST ct
SELECT -- + parallel(16) 
     ch.sas_contact_id,
     ch.contact_source,
     ch.client_id,
     ch.contact_dttm,
     ch.contact_dt,
     ch.sas_contact_error_desc,
     ch.sas_contact_status
FROM CONTACT_HISTORY ch
WHERE ch.contact_dt = dat;
commit;
dat:= dat+1;
end loop;
end;**

There is such a problem that when SELECT COUNT(*) FROM CONTACT_HISTORY_TEST shows only 1.200.000.000 data in the test table, when in general table 1.244.000.000.

And there is such a moment that when checking

SELECT COUNT(*) 
FROM CONTACT_HISTORY 
WHERE CONTACT_DT>= TO_DATE('04.03.2021', 'dd.mm.yyyy') 
AND CONTACT_DT<= TO_DATE('05.06.2022', 'dd.mm.yyyy');
SELECT COUNT(*)
FROM CONTACT_HISTORY_TEST
WHERE CONTACT_DT>= TO_DATE('04.03.2021', 'dd.mm.yyyy')
AND CONTACT_DT<= TO_DATE('05.06.2022', 'dd.mm.yyyy')

In both tables, there are 1.200.000.000 data, please tell me where the remaining 44 million data have gone and how can I completely transfer the data from the table or how to do it right?

Littlefoot · Answer 1 · 2022-06-06T06:51:40.837

1

I presume that contact_dt column contains date values that have time component; for example, it isn't just 04.03.2021, but 04.03.2021 13:23:45.

Code you posted handles "start" of the period correctly as 04.03.2021 actually represents 04.03.2021 00:00:00.

However, the last day of that period isn't handled correctly - you're missing (almost) the whole last day because you copied only rows whose contact_dt is equal to 05.06.2022 00:00:00. What about eg. 05.06.2022 08:32:13?

Therefore, modify something. If contact_dt column is indexed, you shouldn't truncate it, so the simplest option is to change this

while dat <= TO_DATE('05.06.2022', 'dd.mm.yyyy') loop

to

while dat <  TO_DATE('06.06.2022', 'dd.mm.yyyy') loop

As @APC commented, where clause should then also be fixed to

where ch.contact_dt >= dat and ch.contact_dt < dat + 1

To verify number of rows and date values, run the following code in both schemas and then post the result (edit the question, not as a comment):

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

select min(contact_dt) min_dat, max(contact_dt) max_dat, count(*) cnt 
from contact_history;

edited Jun 06 '22 at 06:51

answered Jun 06 '22 at 05:03

Littlefoot

131,892
15
35
57

2

If your guess is correct (and I suspect it is) the Seeker will also need to change the WHERE clause to something like `WHERE ch.contact_dt >= dat and ch.contact_dt < dat + 1`. – APC Jun 06 '22 at 05:08
The number of data received did not change, the number remained the same 1.200.000.000 SELECT COUNT (*) FROM DWH_EXPORT_E01.EXP_MA_CONTACT_HISTORY_6M WHERE CONTACT_DT >= TO_DATE('04.03.2021', 'dd.mm.yyyy') AND CONTACT_DT < TO_DATE('06.06.2022', 'dd.mm.yyyy') – Andrey Romanov Jun 06 '22 at 05:17
I'll try using method as @APC said, thanks – Andrey Romanov Jun 06 '22 at 05:18
1

@SultanZhubaniyazov - so what was the query you ran which told you CONTACT_HISTORY had 1.244.000.000 rows? Obviously not the same query which is telling you it only has 1.200.000.000 rows. – APC Jun 06 '22 at 05:19
select count(*) from contact_history shows 1.244.000.000 data @apc – Andrey Romanov Jun 06 '22 at 05:29
That's the total number of rows (as there's no WHERE clause). What does `select min(contact_dt), max(contact_dt) from contact_history` return? – Littlefoot Jun 06 '22 at 06:20
min date of contact_dt is 04.03.2022 and max date is 05.06.2022 in contact history @littlefoot – Andrey Romanov Jun 06 '22 at 06:36
Have a look at the bottom of edited answer; run that piece of code in both schemas and post the result. – Littlefoot Jun 06 '22 at 06:52
@SultanZhubaniyazov - Prediction: you have 44 million rows where `contact_dt` IS NULL – APC Jun 06 '22 at 09:28
@APC I solved my problem by taking CONTACT_DTTM and selecting the range CONTACT_DTTM > 03/04/22 AND CONTACT_DTTM <= 06/05/2022 since they don't have null values. I could have just added empty contact_dts with empty values, but I got a strange ORA error. – Andrey Romanov Jun 06 '22 at 10:54

Transferring data to a test table

1 Answers1