For the two queries which look almost identical
select max(ln(i))::int m from generate_series(1, 1e6) i;
and
select max(ln(i))::int m from generate_series(1, 1e6::int) i;
the execution time differs by orders of magnitude (0.3 sec vs 11+ sec).
Let’s see why and dive into some internals.
Continue reading “PostgreSQL. Do you really need THAT precision?”In this post I’m not going to compare two different RDBMS by doing load testing and measuring TPC. Neither I’m going to dive into different features or architecture. Instead the goal is to solve one specific SQL quiz and highlight some strengths and weaknesses for both RDBMS.
Continue reading “Oracle vs PostgreSQL. One specific query challenge.”It is well know that there are many ways to run third party executable from Oracle. In particular external procedures, external tables, dbms_scheduler, Oracle Text to name a few. In this post I will do quick end-to-end demo how to compile and run the binary with external tables followed by an explanation how we can avoid compilation stage.
Continue reading “Executing any binary from Oracle”One of the greatest features of Postgres is extensibility. In particular, extensions can help to dramatically improve performance troubleshooting capabilities. You can leverage built-in extensions or install from some third-party repositories managed by companies like Percona or EDB or simply install them from git sources.
One of the typical performance issues is when some process takes much longer than it used to take in the past. This post describes Top 5 extensions which can help with such retrospective analysis.
Continue reading “Enhancing PostgreSQL performance tuning capabilities”Some time ago I stumbled into this video on youtube with an interesting SQL quiz (they claim it is interview question by FAANG company). Solution from the video contains a number of subqueries and joins however none of that is actually needed. Let me show you how this can be solved with a single table scan.
Continue reading “SQL Interview Question by a FAANG company”A lot of programming languages allow to declare functions which accept arbitrary number of attributes and such functions are often referred as varargs functions. Even Oracle has such functions – for example, SQL function decode or PL/SQL function utl_lms.format_message. Unfortunately, Oracle does not allow to declare user defined varargs functions but this post is not about that.
From time to time Data Architects have to deal with the challenge to store entities with arbitrary attributes. If all possible entities are not known in advance during the schema design then it’s not possible to create a table with all the necessary attributes. Possible solutions in such cases are
Above solutions are not even close from performance perspective to the design when we can create a table with all needed attributes in advance. Nevertheless there is a solution with very similar performance.
Continue reading “Varattrs tables in Oracle”
Some queries can be easily expressed with MDX while it’s not possible to get the same result using SQL. For example, if we use Adventure Works sample cube and would like to display internet gross profits by country in a way so that we have separate column for each year it can be easily achieved with below MDX.
select non empty [Date].[Calendar Year].[Calendar Year] on 0, [Customer].[Country].[Country] * [Measures].[Internet Gross Profit] on 1 from [Adventure Works]
It displays column for each year which has data for internet gross profits. In SQL, however, all columns must be explicitly listed in a query (or derived from table structure in case of select *) – whether in select list or in pivot clause. Generically speaking, SQL is designed to return two-dimensional datasets with arbitrary number of rows and pre-defined number of columns while MDX can return data on multiple axes (dimensions) although it’s a tricky question how to visualize the output if we use more than 2 axes.
On the other hand, sometimes it quite challenging to implement the logic in MDX which can be expressed using SQL in a very trivial manner. Let’s consider a couple of cases.
Continue reading “Group by an expression… and limitations of MDX vs SQL”
Query rewrite an one of Query Transformations which makes possible to use materialized view to get results instead of querying underlying tables.
Basic prerequisites for query rewrite described in corresponding chapter of a Database Data Warehousing Guide. Let’s start with a simple example to show how it works.
In this I’ll do a quick performance comparison of built-in Oracle Text index versus the one created using Extensible indexing interface (described in this post).
First of all, Oracle Text option must be installed to use built-in text indexes. This functionality is getting installed under CTXSYS schema.
Speaking about DIY text indexes, it’s mandatory to have privileges to create index types and operators or simply RESOURCE role.
I assume it would be more correct to say “using scala functions in Oracle” but Oracle documentation uses terms like External Procedures or External Subprograms.
There are no specific details about scala with regard to external procedures in Oracle but given that scala code is translated into Java byte code there should be no difficulties in using scala as a language for stored code as well.
Of course, there is no scala compiler in Oracle so we cannot run statement like create or replace and compile java source but we can compile scala code in advance and upload classes using loadjava utility.
All java methods which are mapped to PL/SQL routines must be static and since there are no static methods in scala the typical approach to mimic them is by declaring functions in an object rather than in a class.
Continue reading “Scala external procedures in Oracle”
SQLMDX 