Re: Snowflake on Oracle
Date: Tue, 18 Apr 2023 16:07:02 -0400
Message-ID: <20281e7a-6ab5-4469-248b-18511d398f73_at_gmail.com>
On 4/18/23 15:38, yudhi s wrote:
> /"2. No indexes in Snowflake :)"
> /
> Regarding the second point, though the name is different, isn't
> snowflake "search optimization service" indirectly creating indexes
> under the hood ?
Nope. Snowflake is a columnar storage database. Everything is stored in memory, in compressed format. Indexes are structures that live on disk. In principle, you don't need indexes on Snowflake. Snowflake is not your run of the mill OLTP relational database like Oracle, DB2, SQL Server, PostgreSQL or MySQL. Corollary: when you are searching for stuff in a table, you will read the entire table, most of the time. Here is a good article, albeit not about Snowflake:
You might not need a columnstore index. Rowstore (or B-tree) tables with heaps or clustered indexes perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of large range table scans.
That's the difference between data warehouse and OLTP. Data warehouse is usually used for computing trends and making predictions. OLTP is a different animal altogether. Oracle, DB2 and SQL Server (aka "the big 3") are jacks of all trades but are primarily geared toward OLTP. DW databases like Snowflake or Vertica usually use full table scans to read and analyze large quantities of data. That is why Oracle has created Exadata. Exadata is a DW machine which speeds up full table scans. EMC Greenplum has similar design, which is no wonder since both Exadata and Greenplum have the same designer, who is the only retired Oracle Ace in existence.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 18 2023 - 22:07:02 CEST