The Power of Oracle SQL

This page contains all versions of my book The Power of Oracle SQL (rus).

Published title Oracle SQL Revealed (eng).

Russian

• Version 1.0 (download pdf)
  Only first part of the book.
• Version 1.1 (download pdf)
  Both parts. Subsections are listed in the contents as well as chapters.
  A number of corrections has been made after feedbacks regarding the first part.

English

• Available on Amazon and Apress

Some changes in compare to Russian version:

Part I

• First chapter was thoroughly refactored in order to make it easier to read. It has a huge number of examples and they have been structured a bit better.
• New chapter “Aggregate Functions” (it also includes information about pivoting/unpivoting).
• Some details about performance and internal implementation were added regarding pattern matching.
• Chapter “Logical Execution Order of Query Clauses” was completely reworked. In Russian version it contains a few inaccuracies which may lead to wrong conclusions.

Part II

• Solutions for a few of quizzes have been improved or changed. In particular, for Ordering Dependencies and Resemblance Group.
• Some new approaches have been introduced, for instance, for Zeckendorf Representation, etc.

 

SQL Interview Question by a FAANG company

Some time ago I stumbled into this video on youtube with an interesting SQL quiz (they claim it is interview question by FAANG company). Provided solution contained a lot of subqueries and joins however none of that is actually needed. Let me show you below how this can be solved much more efficiently.

Varattrs tables in Oracle

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

• EAV design + pivot
• Storing entities in XML/JSON format or simply as varchar (latter would require implementing the logic for parsing)

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.

Group by an expression… and limitations of MDX vs SQL

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.

Query rewrite in Oracle

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.

Domain indexes. Built-in vs DIY.

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.

Scala external procedures in Oracle

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.

Domain indexes. Quick dive.

Extensible indexing interface

Domain indexes are used to speed up data retrieval for specific domains like text, spatial or image data. Oracle has built in indexes for above mentioned domains but developers can create their own using extensible indexing interface (ODCIIndex).

Let’s consider specific task when this can be useful and discover process end-to-end.

Evolution of regular expressions

Frankly speaking “evoulution” is not quite proper word to title this post.
I’m going to show some extensions in regular expressions in different languages in compare to their implementation in Oracle.

Oracle supports regular expressions according to POSIX standard draft 1003.2/D11.2 and two extensions

• matching capabilities for multilingual data
• some commonly used PERL regular expression operators (for example, character class shortcuts and the nongreedy modifier “?”)

Let’s consider quizzes which cannot be easily resolved using built-in capabilities in Oracle.

Differentiation of functions in Python and R

Calculating value of derivative function is a part of many applied tasks such
– gradient descent/ascent for finding minimum/maximum of a function
– Newton’s method for finding approximations to the roots (or zeroes) of a real-valued function
etc.

There are three main ways to calculate derivatives
1. Finite differences i.e. calculate approximation using (f(x+Δx)-f(x))/Δx when Δx is close to zero.
2. Symbolic differentiation – generates exact formula for derivative function.
3. Automatic differentiation – generates evaluations (and not formulas) of the derivatives. All intermediate expressions are evaluated as soon as possible.
A brilliant explanation of automatic differentiation may be found here in a paper by Warwick Tucker “One-dimensional, first-order, and Taylor-mode automatic differentiation with programming examples”.

So let’s have a closer look on some technics for derivation in Python/R.

Oracle OLAP option. Quick dive.

There are quite a few tools and ways to build and manage cubes in Oracle so in this post I’ll try to clarify some concepts.
Good top level introduction could be found in article by Arup Nanda – Data Warehousing and OLAP.
Detailed step by step tutorial – Building OLAP Cubes.
The easiest way to start is to explore existing sample schema.