SORT GROUP BY NOSORT ROLLUP

Uncategorized
| Jul 6, 2019

In the previous post we saw that the Oracle Optimizer has a special operation – SORT GROUP BY ROLLUP – for performing several aggregations of the same population in a single scan, as long as the grouping keys are in a “rollup form”; for example:

(a)
(a,b,c)
(a,b,c,d)

If there is an index that its leading part is the same as the rollup grouping key, and that at least one of its columns is defined (more...)

Not all GROUPING SETS are created equal

Uncategorized
| Jul 4, 2019

Introduction

I’ve just realized, once again, that the Oracle Optimizer is even smarter than I thought.
The comments (by Iudith Mentzel and Thomas Mautsch) to my previous post, GROUPING SETS and COLLECT don’t get along, made me understand that not all GROUPING SETS were created equal.

The examples in this post are from Oracle 18.3

Extended Aggregation Options

GROUPING SETS, ROLLUP and CUBE are great features that enable us to perform several aggregations (more...)

GROUPING SETS and COLLECT Don’t Get Along

Uncategorized
| Jun 28, 2019

I’ve recently got reminded that assuming something will work, just because it makes sense, doesn’t mean it will really work.

While reviewing some code a few days ago, I saw a query of the following form:

select 'X='||x, collect(z)
from t
group by x
union all
select 'Y='||y, collect(z)
from t
group by y;

I immediately recommended to convert it to use GROUPING SETS; like this:

select decode(grouping(x), 0, 'X='||x, 'Y='||y),
       collect(z)
from t
 (more...)

EXPAND_SQL_TEXT – Much More Than Just Expanding Views

Uncategorized
| Jun 25, 2019

Overview

There are features in Oracle SQL that are implemented by other, older, features. This is a clever way for supporting a new syntax with low efforts and low risk – the Oracle Corp engineers only need to convert the SQL statement with the new syntax to an equivalent statement that uses the old syntax they already support. And Oracle has a perfect place for doing this conversion – the expansion stage in the parsing (more...)

EBR – Part 12: Editions and Services

Uncategorized
| May 9, 2019

This is part 12 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

In the previous part of this series I wrote about using the Database Default Edition as a way for exposing new editions when using EBR for online application upgrades. As I wrote there, this is a simple method, but in my opinion it is also an impractical method in most cases. (more...)

EBR – Part 11: Database-Level Default Edition

Uncategorized
| Mar 18, 2019

This is part 11 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

As we’ve seen in the previous posts, the process of an online application upgrade, in high level, includes the following steps:

  • Creating a new edition
  • Applying all the necessary changes in the privacy of the new, unexposed, edition
  • Making sure that all the objects in the new edition are valid, (more...)

ENABLE NOVALIDATE – Too Polite?

Uncategorized
| Dec 28, 2018

Onine DDL operations are much more polite than offline DDL operations. They usually wait patiently for transactions that hold resources they need until these transactions end, and they do not block new DML statements.

As I wrote in the past, adding a constraint as Enabled and Validated (which is the default for new constrtaints) is an offline operation, but if we split it into two DDL statements – one for adding the constraint as (more...)

ODC Appreciation Day : Pattern Matching in SQL

Uncategorized
| Oct 10, 2018

Here’s my contribution to the ODC Appreciation Day.

Pattern Matching in SQL, using the MATCH_RECOGNIZE clause, is one of my favorite features, but only recently I’ve used it “for real”.
MATCH_RECOGNIZE allows us to perform enhanced analysis of row sequences, and to detect sequences that match complex patterns.
This feature gave a significant boost to the analytical capabilities of SQL. It enables solving various types of problems in a simpler way than before, in much (more...)

Index Hints and Distributed Queries

Uncategorized
| Aug 28, 2018

There are two ways to specify indexes in optimizer hints: by the index name, or by the index’s (leading) columns:

The latter is usually preferred, as the writer’s intention is clearer, and it’s immune to changes of the index name.

Note: one can present the opposite argument, that specifying the index name is immune to changes of column names

Recently I’ve used the option that I prefer – specifying the column names and not the (more...)

Dropping Virtual Columns Causes Unnecessary Invalidation

Uncategorized
| Aug 23, 2018

When you drop a column from a table (or set a column unused), any view that references this column becomes invalid.
As of Oracle 11gR1, where Fine Grained Dependency Tracking was introduced, views that reference other columns of the table, but do not reference the dropped column, should not become invalid. And this is usually true, but this week I discovered a case where it’s not.

When dropping a virtual column, even views that (more...)