CODEWITHSUNDEEP
oracle-databaseplsqldeveloper
Andrey Romanov
Andrey Romanov

Reputation: 69

Transferring data to a test table

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?

Upvotes: 0

Views: 53

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

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;

Upvotes: 1

Related Questions