Re: Question on IO consideration

On 9/21/21 14:48, Lok P wrote:

> Hello Listers, We have oracle exadata databases which are performing
> mostly warehousing or batch type data processing. And few are hybrid
> i.e combination of both oltp+warehousing/analytics types processing.
> ETL jobs run on a few of these which move/read/write billions rows
> daily. The databases are 50TB to ~150TB in size currently. Few
> architecture team members suggested evaluating if we can use
> columnar database type of offering for IO reduction and thus better
> performance considering the future growth.  As per my understanding ,
> Oracle stores data in row format only, so is there any other offering
> from Oracle for columnar datastore format or columnar databases and we
> should evaluate that? Or is there any clustering kind of technique
> which can be evaluated which will help reduce IO? Want to understand
> experts' views here on this.
>
> Regards
> Lok

There are columnar databases like Amazon Redshaft, Snowflake or Vertica. Greenplum is an Exadata competitor which CAN store data in the columnar format but doesn't do that by default. Greenplum and Exadata were actually designed by the same guy, the only retired Oracle Ace in existence. Now, which one of those is right for you can only be decided by testing. I would advise a pilot project with one of those and see how does the configuration cope with tyour specific load. All of the databases above are SQL databases. If you include NoSQL databases, you can have a new cast of characters: MongoDB, Cassandra, CouchDB and many others. Each of those has its own access language and operating principles. None of them is fully ACID compliant and the languages are not standardized. There is no such thing as read committed transaction isolation or serializable or repeatable reads with Cassandra or CouchDB. I am not quite sure about Mongo. I think I've read that Mongo is ACID compliant, but Mongo doesn't do SQL, so I am not quite sure how that works.

And at this point, there are some things to be said about  SQL. SQL is the language created by a mathematician (actually, two of them) to implement so called "naive set theory" which is a mathematical term for the set theory which doesn't include Zermelo's axiom of choice, well ordering theorem, Zorn's lemma and ergodic theorems. In other words, SQL implements things line unions, intersections, relations, Cartesian products and alike. It is also a transaction manager. Commit and rollback used to be external commands. Some of the more junior members of this list have been working with DL/1, a hierarchical database once very popular with the mainframe users. DL/1 had external transaction managers, like IMS and CICS a good part of which was executing on FEP ("Front End Processor"). The SQL databases like Oracle, SQL/DS (may it rest in peace) and DB2 (hopefully, IBM will learn how to sell it) have integrated transaction management into the database and are doing an excellent job with that. SQL is doing something right, otherwise it wouldn't survive for as long as it has. There is a famous quote from the movie "Chinatown": politicians, ugly buildings, and whores all get respectable if they last long enough”. The same applies to programming languages. C and SQL are both respectable. SQL must be doing something right to last as long as it does. I would really think twice before abandoning SQL for a non-standard vendor lock-in.  I love the smell of SQL in the morning (that's not from "Chinatown").

-- 
I'll speak the key, the whole key and nothing but the key, so help me Codd.
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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