Re: Question on IO consideration
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 21 Sep 2021 19:49:03 -0400
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.
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.
Tel: (347) 321-1217
Other related posts: