Infrastructure at your Service


SQL Archives - Blog dbi services

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
Franck Pachot

A tribute to Natural Join

By | Oracle | No Comments

By Franck Pachot . I know that lot of people are against the ANSI join syntax in Oracle. And this goes beyond the limits when talking about NATURAL JOIN. But I like them and use them quite often. Why is Natural Join bad? Natural join is bad because it relies on column names, and, at the time of writing the query, you don’t know which columns will be added or removed later. Here is an…

Read More
Stéphane Haby

SQL Server 2017: TRIM not only a replacement of RTRIM and LTRIM

By | Database Administration & Monitoring, Development & Performance, Technology Survey | 5 Comments

Last month, I present in the IT-Tage 2017 in Frankfurt am Main, a session about SQL Server 2017 overview. During my session I made a demo on one of the new string T-SQL Commands: TRIM. My demo was very simple but I have also 2 very good questions. Before, I write about these question, I will show you what is TRIM. This function is a replacement of RTRIM and LTRIM to delete all space character…

Read More
Morgan Patou

Documentum – DFC traces setup & investigation

By | Application integration & Middleware, Entreprise content management | 2 Comments

When working with Documentum, you will most probably have to enable the DFC traces one day or another and then work with these traces to analyze them. The purpose of this blog is simply to show how the DFC traces can be enabled, which tools can be used to quickly process them and what are the limitations of such things. Enabling the DFC traces can be done very easily by updating the file of…

Read More
Alain Lacour

APEX Connect 2017 – Day 1

By | Development & Performance | No Comments

This year again the APEX connect conference spans over three days with mixed topics around APEX, like JavaScript, CSS, SQL and much more. After the welcome speech and the Keynote about “Reconciling APEX and the Thick Database Paradigm” by Bryn Llewellyn I decided to attend presentations on following topics: – Temporal Validity and Flashback Data Archive – Universal Theme and Custom Themes in APEX 5.1 – Using REST Services in APEX applications the nice way…

Read More
Daniel Westermann

How to destroy your performance: PL/SQL vs SQL

By | Database Administration & Monitoring | No Comments

Disclaimer: This is in no way a recommendation to avoid PL/SQL. This post just describes a case I faced at a customer with a specific implementation in PL/SQL the customer (and me) believed is the most efficient way of doing it in PL/SQL. This was a very good example for myself to remind me to check the documentation and to verify if what I believed a feature does is really what the feature is actually…

Read More