Re: Big Table ?? vs Oracle and Yugabyte

On 6/7/23 10:52, Franky Weber Faust wrote:
> Hi Kyle,
>
> I don't know about Yugabyte, but BigTable has a lot of limitations
> compared to what we can do with Oracle or Postgres. Big Table is a
> wide-column database, so it isn't very compatible with Oracle SQL. I'm
> pretty sure you can insert a massive amount of data into it (as you
> can in Cassandra, which I'm more experienced with), but for every
> different query predicate you'll need a new table, or a materialized
> view, or a secondary index, which all have a cost on resources.
> BigTable it is not ACID compliant either.
> I think you won't find any other database technology as mature as
> Oracle is, mainly in regard to online operations that you wish. You
> probably know that you need Oracle Enterprise Edition to leverage
> parallelism and online operations and an extra cost feature for
> partitioning. So it will be expensive, but if that's what your
> business needs and it pays out compared to the management overhead and
> downtime of other solutions, I'm 100% sure that is the way to go. I
> know SQL Server implemented a lot of online operations lately as well,
> maybe it is an option for you as well; it's been a while I don't work
> with it directly.
>
Well, that is my impression with the most of the NoSQL databases I've encountered on my quest to boldly go where no DBA has gone before. I've encountered Mongo, Couch and Cassandra, which all claim that they're much faster than Oracle. They achieve the blinding speed by sacrificing ACID requirements. The most problematic thing is "read committed" isolation level which mandates that transaction can only see the stuff committed before the moment the transaction has started. In particular, that means that the old value of the rows has to be reconstructed if the table was modified after the transaction has started. In Oracle, the old data has to be read from the UNDO segments. Postgres keeps the data within the tables themselves and has so called visibility map, but both databases will reconstruct the old data. Mongo, Couch and Cassandra will not do that. The utility of that depends on the legal framework for which I am no expert.

Relational databases, especially the "read committed" isolation mode, were modeled after the banking business. That is why the most frequent explanation of the transaction is the procedure of the payment with a check. ACID rules were written with the banking industry in mind. In the early 70's, when Codd and Date were tasked with coming up with something better than hierarchical databases like DL/1, only banks had enough money to purchase a really big mainframe, like 3090/600J with a whopping 64 MB of memory. There is an urban legend about the implementation of the work done by Codd and Date. The implementation was called System R (not to be confused with Method R) and was allegedly sold for approximately $100 to 3 guys: Larry Ellison, Bob Miner and Ed Oates. I think we all know what did the three of them do with that software. Larry Ellison was a programmer at that time, pretty good one, too. He was allegedly the author of the venerable RPT/RPF reporting tool.

For those of us young enough to have worked with MVS and CICS/DL1 combination, we know that transactions were being implemented by an external piece of software called "TP monitor" (CICS, IMS, Tuxedo and later app servers like WebLogic and WebSphere). CICS has even had something called "temporary tape" from which the old values were read, in case of a rollback.

I find Mongo and its ilk most useful as a document store, instead of the endless pain and misery with the CLOB columns. However, the lack of the enforcement of the ACID requirements will keep those databases at a fringe of the IT world. NoSQL databases typically do not support XA protocol which may create a problem in the application server environment and the application which connects to multiple databases, coordinating commits through the app server.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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