Infrastructure at your Service

Tag

SQL Archives - Blog dbi services

RDBMS (vs. NoSQL) scales the algorithm before the hardware

By | AWS, NoSQL | No Comments

By Franck Pachot . In The myth of NoSQL (vs. RDBMS) “joins dont scale” I explained that joins actually scale very well with an O(logN) on the input tables size, thanks to B*Tree index access, and can even be bounded by hash partitioning with local index, like in DynamoDB single-table design. Jonathan Lewis added a comment that, given the name of the tables (USERS and ORDERS). we should expect an increasing number of rows returned…

Read More
Franck Pachot

A lesson from NoSQL (vs. RDBMS): listen to your users

By | Database Administration & Monitoring, NoSQL | No Comments

By Franck Pachot . I have written a few blog posts about some NoSQL (vs. RDBMS) myths (“joins dont scale”, “agility: adding attributes” and “simpler API to bound resources”). And I’ll continue on other points that are claimed by some NoSQL vendors and are, in my opinion, misleading by lack of knowledge and facts about RDBMS databases. But here I’m sharing an opposite opinion: SQL being user-friendly is now a myth. Yes, that was the…

Read More
Franck Pachot

The myth of NoSQL (vs. RDBMS) “a simpler API to bound resources”

By | NoSQL, Oracle | No Comments

By Franck Pachot . NoSQL provides an API that is much simpler than SQL. And one advantage of it is that users cannot exceed a defined amount of resources in one call. You can read this in Alex DeBrie article https://www.alexdebrie.com/posts/dynamodb-no-bad-queries/#relational-queries-are-unbounded which I take as a base for some of my “Myth of NoSQL vs RDBMS” posts because he explains very well how SQL and NoSQL are perceived by the users. But this idea of…

Read More
Franck Pachot

The myth of NoSQL (vs. RDBMS) “joins dont scale”

By | AWS, Database Administration & Monitoring, NoSQL, Oracle, Postgres, SQL Server | 13 Comments

By Franck Pachot . I’ll reference Alex DeBrie article “SQL, NoSQL, and Scale: How DynamoDB scales where relational databases don’t“, especially the paragraph about “Why relational databases don’t scale”. But I want to make clear that my post here is not against this article, but against a very common myth that even precedes NoSQL databases. Actually, I’m taking this article as reference because the author, in his website and book, has really good points about…

Read More
Franck Pachot

Oracle non-linguistic varchar2 columns to order by without sorting

By | Cloud, Oracle | No Comments

By Franck Pachot . Sorting data is an expensive operation and many queries declare an ORDER BY. To avoid the sort operation you can build an index as it maintains a sorted structure. This helps with Top-N queries as you don’t have to read all rows but only those from a range of index entries. However, indexes are sorted by binary values. For NUMBER or DATE datatypes, the internal storage ensures that the order is…

Read More
Franck Pachot

No{Join,GroupBy}SQL – Analytic Views for BI

By | Cloud, Oracle | No Comments

By Franck Pachot . Advocates of NoSQL can query their structures without having to read a data model first. And without writing long table join clauses. They store and query a hierarchical structure without the need to follow relationships, and without the need to join tables on a foreign key name, in order to get a caption or description from a lookup table. The structure, like an XML or JSON document, provides metadata to understand…

Read More
Franck Pachot

The myth of NoSQL (vs. RDBMS) agility: adding attributes

By | AWS, Database Administration & Monitoring, NoSQL, Oracle, Postgres, SQL Server | 3 Comments

By Franck Pachot . There are good reasons for NoSQL and semi-structured databases. And there are also many mistakes and myths. If people move from RDBMS to NoSQL because of wrong reasons, they will have a bad experience and this finally deserves NoSQL reputation. Those myths were settled by some database newbies who didn’t learn SQL and relational databases. And, rather than learning the basics of data modeling, and capabilities of SQL for data sets…

Read More
Franck Pachot

Refactoring procedural to SQL – an example with MySQL Sakila

By | Development & Performance | 4 Comments

By Franck Pachot What I want to show in this blog post is that, as in mathematics where you have to apply some algebra rules to transform an equation to an equivalent one, the database developer must translate the business specification to an equivalent that is optimized (in performance, reliability and readability) for the data model. I was looking at the Sakila sample database provided with MySQL. It simulates a DVD rental store. For my…

Read More
Franck Pachot

Oracle 20c SQL Macros: a scalar example to join agility and performance

By | Cloud, DevOps, Oracle | 2 Comments

By Franck Pachot . Let’s say you have a PEOPLE table with FIRST_NAME and LAST_NAME and you want, in many places of your application, to display the full name. Usually my name will be displayed as ‘Franck Pachot’ and I can simply add a virtual column to my table, or view, as: initcap(FIRST_NAME)||’ ‘||initcap(LAST_NAME). Those are simple SQL functions. No need for procedural code there, right? But, one day, the business will come with new…

Read More
Lazhar Felahi

SQL Tuning – Mix NULL / NOT NULL Values

By | Development & Performance, Oracle | No Comments

One of the difficulty when writing a SQL query (static SQL) is to have in the same Where Clause different conditions handling Null Values and Not Null Values for a predica. Let’s me explain you by an example : Users can entered different values for a user field from an OBI report: – If no value entered then all rows must be returned. – If 1 value entered then only row(s) related to the filter…

Read More