Find missing dates by comparing two columns in Table and View using PL/SQL

Question

I have to compare a date column available in table and view, any missing date in table will add record from the view. Both table and view has 100 millions of data. I have written below code but to iterate over 400K rows it takes 2hr each for table and view. Would like to know if I can improve my code to compare. Please don't ask why I add record from View to table, this is bit complicated setup I cannot help on this.

Note -

I have to run this for around 100+ tables.
There are different date formats for different tables/ views. But it remains same for the table and corresponding view with which we will do the comparison.
In some tables there are invalid dates, those records should be ignored. Am achieving this by using TO_DATE function in the code, if exception am continuing.
Date format for each table/ view is maintained in a MASTER table.

Did not include initial Code to read MASTER table with BEGIN and DECLARE

    query_str := 'select DISTINCT ' || date_column_name || ' as DATM FROM ' || tablename;
    OPEN c_query FOR query_str;

    LOOP
        BEGIN
            FETCH c_query INTO dateval;
            EXIT WHEN c_query%notfound;
            tmp := TO_DATE(
                dateval,
                dateformat
            );
            tabledatelist(dateval) := 'Date Val';
        EXCEPTION
            WHEN OTHERS THEN
                continue;
        END;
    END LOOP;

    CLOSE c_query;
    query_str := 'select DISTINCT ' || date_column_name || ' as DATM FROM ' || viewname;
    OPEN c_query FOR query_str;

    LOOP
        BEGIN
            FETCH c_query INTO dateval;
            EXIT WHEN c_query%notfound;
            tmp := TO_DATE(
                dateval,
                dateformat
            );
            IF
                NOT tabledatelist.EXISTS(dateval)
            THEN
                -- Code to add missing records. Using it as Insert into select col_name from table.
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                continue;
        END;
    END LOOP;

    CLOSE c_query;

You shouldn't be storing dates in a varchar column to begin with. Do you have a chance to fix that broken design before you proceed. — user330315
– user330315, Commented Jan 15, 2021 at 12:32

Littlefoot · Accepted Answer · 2021-01-15 12:31:35Z

1

Loops over millions of rows are slow. Why wouldn't you skip them entirely and do something like this?

insert into tablename (date_column_name)
select date_column_name from viewname
minus
select date_column_name from tablename;

I guess that this code lacks quite a lot of info (additional columns), but hey - you didn't post them either. So - see whether such a principle would work. If it has to be dynamic SQL (as you have 100+ tables), so be it. But, I presume & hope that it would perform better.

As of invalid dates: well, that's the cost of storing dates as strings.

One option might be to create a function which returns e.g. 1 (if date is valid) or 0 (if it is not). You'd use it as

select date_column_name from viewname 
where f_date_valid(date_column_name) = 1

but it'll affect performance.

Perhaps you could do it in two steps:

first insert all missing dates (including invalid ones)
then delete rows with invalid dates

Though, I think that whichever approach you take, performance will suffer, and all that because you store dates as strings ... bad, bad idea. I'm not saying it was yours (probably it is not).

edited Jan 15, 2021 at 12:31

answered Jan 15, 2021 at 12:13

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

s3-89 Over a year ago

With this approach how can I ignore the records with invalid dates? I mean some table has format YYYYMMDDHH24MISSbut value as 00000100000000. Do you want me to iterate over the result from the above query and use TO_DATE function on them?

Littlefoot Over a year ago

I edited the answer; have a look, please.

Andrew Sayer Over a year ago

Oracle 19c is being used here so the built in validate_conversion docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/… can be used

Littlefoot Over a year ago

Even better! I'm (mostly) still on 11g, rarely on 12c, never even saw 19c. Thank you, @Andrew, I hope the OP will be able to use it.

s3-89 Over a year ago

Thanks a lot. All the above inputs have helped me reduce significant amount of hours.

Collectives™ on Stack Overflow

Find missing dates by comparing two columns in Table and View using PL/SQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related