So data is something like this:
ID | START_DATE | END_DATE | UID | CANCELED
-------------------------------------------------
44 | 2015-10-20 22:30 | 2015-10-20 23:10 | 'one' |
52 | 2015-10-20 23:00 | 2015-10-20 23:30 | 'one' |
66 | 2015-10-21 13:00 | 2015-10-20 13:30 | 'two' |
There are more than 100k of these entries.
We can see that start_date of the second entry overlaps with the end_date of the first entry. When dates do overlap, entries with lower id should be marked as true in 'CANCELED' column.
I tried some queries but they take a really long time so I'm not sure even if they work. Also I want to cover all overlaping cases so this also seems to slow this down.
I am the one responsible for inserting/updating these entries using pl/sql
update table set column = 'value' where ID = '44';
if sql%rowcount = 0
then insert values(...)
end if
so I could maybe do this in this step. But all tables are updated/inserted using one big pl/sql created dynamically where all rows either get updated or new ones get inserted so once again this seems to get slow.
And of all the sql 'dialects' oracle one is the most cryptic I had chance to work with. Ideas?
EDIT: I forgot one important detail, there is also one more column (UID) which is to be matched, update above
I would start with this query:
update table t
set cancelled = true
where exists (select 1
from table t2
where t.end_date > t2.start_date and
t.uid = t2.uid and
t.id < t2.id
)
An index on table(uid, start_date, id) might help.
As a note: this is probably much easier to do when you create the table, because you can use lag().
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments