# SQLMDX

## The Power of Oracle SQL

Posted by sqlmdx on March 15, 2011

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

Russian

• Version 1.0
Only first part of the book.

• Version 1.1
Both parts. Subsections are listed in contents as well as chapters.
A number of corrections has been made made after feedbacks regarding fist part.

English

• Not published yet.

## Evolution of regular expressions

Posted by sqlmdx on July 2, 2016

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.
Read the rest of this entry »

## Differentiation of functions in Python and R

Posted by sqlmdx on May 21, 2016

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.
Read the rest of this entry »

## Oracle OLAP option. Quick dive.

Posted by sqlmdx on March 26, 2016

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.
Read the rest of this entry »

## Is a row locked?

Posted by sqlmdx on December 22, 2015

Let’s consider “hypothetical” case when it’s required to check if row is blocked without attempting to lock it.
And maybe we’ll find a way to see uncommitted data in Oracle. 🙂
Read the rest of this entry »

## fetch first … percent

Posted by sqlmdx on December 19, 2015

New syntax for so called “Top N” queries was introduced in Oracle 12c.

It can be used to return either fixed number of rows or specified percent of rows in a table.
Obviously latter requires to know the total number of rows to return the result so let’s check what is happening under the hood in this case.
Read the rest of this entry »

## Context switches may not be an issue any longer

Posted by sqlmdx on December 13, 2015

I’ve described how to measure context switches some time ago in this post.

Given that with_clause allows to declare PL/SQL functions in 12c it’s interesting to analyze overhead for context switches in this case.
Read the rest of this entry »

## Yet another approach to overcome bloom filter bug

Posted by sqlmdx on October 4, 2014

Bloom filter may help to dramatically reduce amount of data to be read from partitioned table by eliminating unneeded partitions.
Here is a nice introduction into bloom filters.

Unfortunately bloom filter doesn’t work with DML which causes a lot of challenges in 11gR2.
This bug is documented on metalink Bug 13801198 : BLOOM PRUNING/FILTER NOT USED IN DML STATEMENTS however that note doesn’t suggest any workarounds.
Read the rest of this entry »

## Measuring context switches

Posted by sqlmdx on September 30, 2014

Almost everyone who has some experience with Oracle knows about notorious notion called “context switches” between SQL and PL/SQL engines.
You can find a great explanation of context switches by Tom Kyte here.

Even though it’s quite simple to measure the overhead introduced by context switches, not so many developers know how to calculate exact number of context switches and moreover how to check whether they occurred or not in some cases.
Read the rest of this entry »

Posted in Oracle | Tagged: | 1 Comment »

## dbms_comparison

Posted by sqlmdx on September 27, 2014

dbms_comparison was introduced in 11g as a powerful tool to compare and synchronize table data in different tables.
Even though it’s quite flexible it has some limitations:
1. The tables are supposed to be in different databases. Therefore if you want to compare tables in the same database you will need to create fake db link.
2. The tables must have unique indexes.

Another consequence from the flexibility is that synchronizing is being applied in two steps (merge & delete) even though it could have been done using single merge statement.
Moreover those statements are based not only on tables that are synchronized but also on auxiliary data populated after comparison.
Read the rest of this entry »

## Amending statistics list for autotrace

Posted by sqlmdx on May 24, 2014

I wonder why Oracle has not added possibility to change the list of statistics for autotrace but unfortunately that’s a fact.

You cannot change the default format of the statistics report.

However there are at least three different ways to view the extended list of statistics for given session

1. Use dbms_session.set_identifier and dbms_monitor.client_id_stat_enable/dbms_monitor.client_id_stat_disable.
2. First drawback is that enabling and disabling of stats collecting must be executed in another session and second drawback is that it collects only 27 statistics.
Advantage is the ease of monitoring several sessions with the same client_id.

3. Use queries on v\$sesstat, v\$statname in another session and find difference between executions.
4. The disadvantage is the need to run service queries in second session to avoid side affects and positive aspect is that all existed statistics can be reported.
It’s not necessary to create another session manually though. It’s possible to implicitly execute some statements in another session, for example by using dbms_job/dbms_scheduler or autonomous transactions.

5. Use widely known package runstats_pkg by Tom Kyte.
6. The drawbacks are the same as for previous approach: either you have to run runstats in another session or results are not absolutely precise.

Even though documentation says that we cannot change the default format of the statistics report, it’s in fact possible to amend it.
The only restriction is that number of statistics cannot exceed 11.
Read the rest of this entry »