select count(*) from result r ;--vcetne dvou dni z prvniho behu 4 770 773 --vcetne dvou dni z druheho behu 5 436 411 --13232seconds , cca3,5h --vcetne dvou dni z tretiho behu 5 583 802 -- 16882 seconds , cca 4,6 h select min(r.systemvalidsince),max(r.systemvalidsince) from result r ; -- MERGE DIFS ON ADS MERGE INTO result T USING ( select r.systemvalidsince, r.systemid, t.rn, t.snap_date, t.next_date, t.prev_date, case when lag(systemvalidsince) over (partition by systemid order by systemvalidsince) is null then 1 when lead(systemvalidsince) over (partition by systemid order by systemvalidsince) is null then 1 when lead(systemvalidsince) over (partition by systemid order by systemvalidsince) = t.next_date and t.snap_date = r.systemvalidsince then 0 else 1 end dif_date, case when nvl(lag(advertSourceUri) over (partition by systemid order by systemvalidsince),0) = nvl(advertSourceUri,0) and nvl(lag(advertSourcePicture) over (partition by systemid order by systemvalidsince),0) = nvl(advertSourcePicture,0) and nvl(lag(advertDataSource) over (partition by systemid order by systemvalidsince),0) = nvl(advertDataSource,0) and nvl(lag(advertSourceOtherPictures) over (partition by systemid order by systemvalidsince),0) = nvl(advertSourceOtherPictures,0) and nvl(lag(advertSubtitle) over (partition by systemid order by systemvalidsince),0) = nvl(advertSubtitle,0) then 0 else 1 end dif_tech, case when nvl(lag(brand) over (partition by systemid order by systemvalidsince),0) = nvl(brand,0) and nvl(lag(model) over (partition by systemid order by systemvalidsince),0) = nvl(model,0) and nvl(lag(carosery) over (partition by systemid order by systemvalidsince),0) = nvl(carosery,0) and nvl(lag(currency) over (partition by systemid order by systemvalidsince),0) = nvl(currency,0) and nvl(lag(condition) over (partition by systemid order by systemvalidsince),0) = nvl(condition,0) and nvl(lag(manufactured) over (partition by systemid order by systemvalidsince),to_date('31.12.2999','DD.MM.YYYY')) = nvl(manufactured,to_date('31.12.2999','DD.MM.YYYY')) and nvl(lag(usedSince) over (partition by systemid order by systemvalidsince),to_date('31.12.2999','DD.MM.YYYY')) = nvl(usedSince,to_date('31.12.2999','DD.MM.YYYY')) and nvl(lag(vin) over (partition by systemid order by systemvalidsince),0) = nvl(vin,0) and nvl(lag(fuel) over (partition by systemid order by systemvalidsince),0) = nvl(fuel,0) and nvl(lag(hpower) over (partition by systemid order by systemvalidsince),0) = nvl(hpower,0) and nvl(lag(power) over (partition by systemid order by systemvalidsince),0) = nvl(power,0) and nvl(lag(cubicCapacity) over (partition by systemid order by systemvalidsince),0) = nvl(cubicCapacity,0) and nvl(lag(originCountry) over (partition by systemid order by systemvalidsince),0) = nvl(originCountry,0) and nvl(lag(fuelConsumption) over (partition by systemid order by systemvalidsince),0) = nvl(fuelConsumption,0) and nvl(lag(firstOwner) over (partition by systemid order by systemvalidsince),0) = nvl(firstOwner,0) and nvl(lag(serviceHistory) over (partition by systemid order by systemvalidsince),0) = nvl(serviceHistory,0) then 0 else 1 end dif_desc, case when nvl(lag(gearBox) over (partition by systemid order by systemvalidsince),0) = nvl(gearBox,0) and nvl(lag(color) over (partition by systemid order by systemvalidsince),0) = nvl(color,0) and nvl(lag(seats) over (partition by systemid order by systemvalidsince),0) = nvl(seats,0) and nvl(lag(doors) over (partition by systemid order by systemvalidsince),0) = nvl(doors,0) and nvl(lag(airbags) over (partition by systemid order by systemvalidsince),0) = nvl(airbags,0) and nvl(lag(clima) over (partition by systemid order by systemvalidsince),0) = nvl(clima,0) and nvl(lag(cdPlayer) over (partition by systemid order by systemvalidsince),0) = nvl(cdPlayer,0) and nvl(lag(centralLocking) over (partition by systemid order by systemvalidsince),0) = nvl(centralLocking,0) and nvl(lag(radio) over (partition by systemid order by systemvalidsince),0) = nvl(radio,0) and nvl(lag(ESP) over (partition by systemid order by systemvalidsince),0) = nvl(ESP,0) and nvl(lag(ABS) over (partition by systemid order by systemvalidsince),0) = nvl(ABS,0) and nvl(lag(electricHeatedSeats) over (partition by systemid order by systemvalidsince),0) = nvl(electricHeatedSeats,0) and nvl(lag(electricSeatAdjustment) over (partition by systemid order by systemvalidsince),0) = nvl(electricSeatAdjustment,0) and nvl(lag(electricSideMirror) over (partition by systemid order by systemvalidsince),0) = nvl(electricSideMirror,0) and nvl(lag(electricWindows) over (partition by systemid order by systemvalidsince),0) = nvl(electricWindows,0) and nvl(lag(multifunctionSteeringWheel) over (partition by systemid order by systemvalidsince),0) = nvl(multifunctionSteeringWheel,0) and nvl(lag(navigationSystem) over (partition by systemid order by systemvalidsince),0) = nvl(navigationSystem,0) and nvl(lag(cruiseControl) over (partition by systemid order by systemvalidsince),0) = nvl(cruiseControl,0) and nvl(lag(rainsensor) over (partition by systemid order by systemvalidsince),0) = nvl(rainsensor,0) and nvl(lag(startStopSystem) over (partition by systemid order by systemvalidsince),0) = nvl(startStopSystem,0) and nvl(lag(parkingSensors) over (partition by systemid order by systemvalidsince),0) = nvl(parkingSensors,0) and nvl(lag(alloyWheels) over (partition by systemid order by systemvalidsince),0) = nvl(alloyWheels,0) and nvl(lag(parkingAssistent) over (partition by systemid order by systemvalidsince),0) = nvl(parkingAssistent,0) and nvl(lag(isofix) over (partition by systemid order by systemvalidsince),0) = nvl(isofix,0) and nvl(lag(imobiliser) over (partition by systemid order by systemvalidsince),0) = nvl(imobiliser,0) and nvl(lag(xenonLights) over (partition by systemid order by systemvalidsince),0) = nvl(xenonLights,0) and nvl(lag(fogLights) over (partition by systemid order by systemvalidsince),0) = nvl(fogLights,0) then 0 else 1 end dif_eqpm, case when nvl(lag(km) over (partition by systemid order by systemvalidsince),0) = nvl(km,0) then 0 else 1 end dif_km, case when nvl(lag(price) over (partition by systemid order by systemvalidsince),0) = nvl(price,0) then 0 else 1 end dif_price, case when nvl(lag(geoLatitude) over (partition by systemid order by systemvalidsince),0) = nvl(geoLatitude,0) and nvl(lag(geoLongitude) over (partition by systemid order by systemvalidsince),0) = nvl(geoLongitude,0) and nvl(lag(sellerSystemId) over (partition by systemid order by systemvalidsince),0) = nvl(sellerSystemId,0) and nvl(lag(sellerName) over (partition by systemid order by systemvalidsince),0) = nvl(sellerName,0) and nvl(lag(sellerStreetAddress) over (partition by systemid order by systemvalidsince),0) = nvl(sellerStreetAddress,0) and nvl(lag(sellerAddressLocality) over (partition by systemid order by systemvalidsince),0) = nvl(sellerAddressLocality,0) and nvl(lag(sellerAddressRegion) over (partition by systemid order by systemvalidsince),0) = nvl(sellerAddressRegion,0) and nvl(lag(sellerPostalCode) over (partition by systemid order by systemvalidsince),0) = nvl(sellerPostalCode,0) and nvl(lag(sellerContact) over (partition by systemid order by systemvalidsince),0) = nvl(sellerContact,0) and nvl(lag(sellerLink) over (partition by systemid order by systemvalidsince),0) = nvl(sellerLink,0) and nvl(lag(sellerCategory) over (partition by systemid order by systemvalidsince),0) = nvl(sellerCategory,0) then 0 else 1 end dif_sllr, case when nvl(lag(serie) over (partition by systemid order by systemvalidsince),0) = nvl(serie,0) and nvl(lag(onRoadPerformance) over (partition by systemid order by systemvalidsince),0) = nvl(onRoadPerformance,0) and nvl(lag(passengerComfort) over (partition by systemid order by systemvalidsince),0) = nvl(passengerComfort,0) and nvl(lag(engineRating) over (partition by systemid order by systemvalidsince),0) = nvl(engineRating,0) then 0 else 1 end dif_extr from result r join time t on r.systemvalidsince = t.snap_date --and r.systemvalidsince <= to_date('28.02.2015','DD.MM.YYYY') --WHERE systemid = '9a36e156a49607d9bfe1d49ab8827963' ) S ON (T.systemid = S.systemid and t.systemvalidsince = s.systemvalidsince) WHEN MATCHED THEN UPDATE SET T.dif_date = S.dif_date, t.dif_km = s.dif_km, t.dif_price = s.dif_price, t.dif_tech = s.dif_tech, t.dif_desc = s.dif_desc, t.dif_eqpm = s.dif_eqpm, t.dif_sllr = s.dif_sllr, t.dif_extr = s.dif_extr ;