Re: Snowflake on Oracle

I have worked on Oracle mainly and recently came across Snowflake. And I may be wrong in few but till now what i got to know is , the key thing which makes Snowflake so different would be its "shared nothing" memory/compute section. They call those warehouses and I can imagine those equivalent to Oracle SGA but here in Oracle is "shared everything" and that is where many times the contention(locks/latches etc) plays a key role. And many times we can't really go over a certain number of RAC nodes even oracle gives us the flexibility to go over many nodes/RAC. The storage in Snowflake is "shared everything" like Oracle has. This "shared nothing" memory/compute section provides an infinite amount of compute resources as your workload grows linearly because the number of warehouses can simply grow with the incoming workload. No talking happens between the data residing in those warehouses/memories like it happens in cases of oracle which we called the GC waits. But as storage is "shared everything" so to my understanding , it's not truly distributed in terms of storage and also I think there will be a situation where contention may happen in the storage section in case of extreme scenarios/higher loads and scaling may be a concern then.

Snowflake supports procedures/queries mostly similar to oracle but as it's new so less in features and the optimizer may not be that matured as Oracle has, but yes very less stuff is exposed to outside to play with anything in terms of tweaking query performance/plans by tweaking different stats/parameters or debugging any issues etc. Not sure if it's done deliberately to have the dependency created or it's because it's new so they have not provided/exposed those instrumentation to the public yet. We know Oracle is best in the world in terms of the amount of instrumentation it has provided to users and which is of immense help while debugging/fixing issues and additionally some of the gifted experts like "Jonathan Lewis" made that experience heavenly for Oracle in public domain.

The term Indexes does not exist in snowflake but 'search optimization service' is something they offer which can be utilized if we want to make query faster which uses filters on specific columns, so I believe that may be internally doing the indexing of data etc. Not sure though. So by default you will see the access path as tablescan only in the execution plan.

Column storage makes it different from oracle row storage , so better compression or less storage space is needed. I am not sure how it's comparable with Oracle's columnar compression or say inmemory database version which also claims to be columnar format, but snowflake by default stores data in columnar format unlike oracle which stores data in "row format" in blocks by architecture. Column format data store It's the base architecture for snowflake. Constraints in snowflakes are just for name sake but not enforced as oracle has. So these constraints I believe are only to help optimizers come up with better plans but nothing else.

Regards
Lok

On Thu, Apr 13, 2023 at 9:42 PM Jon Crisler <joncrisler_at_gmail.com> wrote:

> Kyle - I was about to make the same point. I have worked on a number of
> projects to move Oracle data to Snowflake for DW . Golden Gate has an
> adapter / agent to support Snowflake . There are also products from
> Fivetran , Confluent , and Delphix . I do not work on Snowflake directly
> , but my colleagues do , and the SQL interoperability with oracle is pretty
> good . Keep in mind that Snowflake is a cloud hosted system and not
> available as a product you can download , but I think a dev account is easy
> to get . All of my projects used SF on Azure but AWS and GCS also host it.
>
> Sent from my Atari 2600
>
> On Apr 13, 2023, at 12:31 AM, kyle Hailey <kylelf_at_gmail.com> wrote:
>
> 
>
> Totally new databse which is amazing ... ie that someone would try and
> write a database from scratch but these aren't just anybody . The principal
> architects are Thierry and Benoit who were kernel architects from Oracle,
> so no surprise there there might be some Oracle look and feel. The other
> founder Marcin had worked on a few database kernels though all I see via
> google searches is his DB Vectorwise.
>
> On Wed, Apr 12, 2023 at 7:44 PM Mladen Gogala <gogala.mladen_at_gmail.com>
> wrote:
>
>> On 4/12/23 19:50, Alan Sterger wrote:
>>
>> So the question, does anyone know if Snowflake is built on top of Oracle?
>>
>> Snowflake is a columnar store database, not meant for OLTP. The data is
>> stored in a series of columns, with compression, of course. It is not
>> related to Oracle in any shape or form. Having said that, they're both
>> RDBMS-es which run on Linux. That's as far as the similarity goes.
>> Snowflake is written completely anew, so it's not a derivative of any other
>> database. It supports ANSI 2000 SQL without anything resembling PL/SQL.
>> However, SF is a data warehouse software so it doesn't need PL/SQL. If you
>> need something to compare SF to, think Vertica, not Oracle. SF isn't
>> PostgreSQL compatible but the rest is quite similar: compression, columnar
>> store and optimization for math.
>>
>> --
>> Mladen Gogala
>> Database Consultant
>> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>>
>>

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