Re: Original design approach to Oracle REDO Logs

I wouldn't call this an edge case, but what about the one where more than one thing is changing in the block, wherein the entire rows fit in single blocks as is typical? So maybe like this:

update someTable
  set someColumn1 = 1.234
    and someColumn2 = (someColumn3 * 2)

I think then it is absolutely necessary that the comparison be done on the column values themselves, and not at the data block level. Anything more complex than that, such as doing a block level check if and only if all of the set values are the same for every updated row, and then having a separate algorithm for the other scenarios, is not worth the performance savings nor the potential for code bugs leading to data loss.

Andy

On Thu, Jun 17, 2021 at 3:12 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> While I agreed with what JL wrote, IF we knew how fast processors are now,
> then an update all rows query might compare the block images on the fly and
> not write the blocks where all the rows in a block were updated to the same
> value so that the actual block image resulting from the update didn’t need
> to be written.
>
>
>
> Off the top of my head I **think** that would work and be efficient, but
> I am not engaged enough to ponder possible edge cases. But I **think**
> you have all the information you need in hand at commit time to flag blocks
> to **not** bother writing. If the buffer had to flush along the way
> before the commit, they would already be in the redo stream, but with the
> massive memory now available a private redo thread might be able to handle
> it both correctly and efficiently.
>
>
>
> I really like Oracle’s redo model though. Someone once put it to me
> thusly: Everything Bill Bridge starts ends up in checkmate. (Meaning Bill
> wins.) Making sure the bucket has no leaks was definitely priority number
> one.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Jonathan Lewis
> *Sent:* Thursday, June 17, 2021 10:16 AM
> *To:* ORACLE-L (oracle-l_at_freelists.org)
> *Subject:* Re: Original design approach to Oracle REDO Logs
>
>
>
> Mike,
>
>
>
> I don't think you can ask about hypothetical strategies for the redo log
> before stating what you think the purpose of the database should be.
>
>
>
> If you expect the database to be the reference of last resort for the
> correctness and (immediate) consistency of the data then you might like a
> different strategy from someone who thinks the database is some sort of
> record of evolution of the data that allows the current version of the data
> to be re-imaged by a client.
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Thu, 17 Jun 2021 at 13:55, Michael D O'Shea/Woodward Informatics Ltd <
> woodwardinformatics_at_strychnine.co.uk> wrote:
>
> Chaps, request for opinions/discussion/feedback .....
>
>
>
>
>
> Question: If Oracle were written today, would the same strategy behind the
> "redo log" be adopted.
>
>
>
>
>
> An example .... for a table of 10,000,000 rows, and a contrived piece of
> DML
>
>
>
> update someTable
>
> set someColumn1 = 1.234
>
>
>
> that "updated" all the rows
>
> *but* where someColumn1 was only updated to 1.234 for 6 rows as the
> remaining 10,000,000 - 6 rows were already 1.234
>
>
>
> Should (assuming just DML and also just the basic data types, number,
> varchar2, date, ... ) the redo log
>
> * record the data "change" for all 10,000,000 "updates"
>
> * record the real data change for just the 6 real updates
>
> * record *solely* the SQL used to perform the update for some playback
> purpose
>
> * send a message to some message broker such as Solace, Tibco, ....
> allowing subscribers to process the data change that way (might be
> replication/backup, some sort of refresh or push notification to other
> applications, email dispatch and so on)
>
> * some other approach else
>
>
>
> There is a considerable movement to event streaming technology such as
> Kafka to (indirectly) drive data change events to downstream and dependent
> systems (in Oracle assumably by polling the redo log file, or maybe some
> LogMiner interface .. I don't know the detail) in databases that include
> Oracle, MongoDB (referred to as Change Streams), and many more.
>
>
>
> My "ask" focuses more on "just the database" interoperability with the
> remainder of what is often a large tech stack, and not the original design
> decision around redo logs for data recovery.
>
>
>
> Mike
>
>
>
> http://www.strychnine.co.uk
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l