A Single Query with Many Filter Combinations

Let’s assume the EMPLOYEES table (from the HR schema) contains many records, and we want to write an (efficient) SQL query that filters it by one or more of the following columns: DEPARTMENT_ID, JOB_ID, MANAGER_ID and LAST_NAME.
For example:

  • in one execution we may want to get all the employees with DEPARTMENT_ID = 80
  • in a second execution all the employees that their LAST_NAME is King
  • in a third execution all the employees that their JOB_ID is ST_CLERK and their MANAGER_ID is 124
  • and so on

These columns are indexed, each one in a separate index:

SQL> select index_name,
  2         listagg(column_name, ',') within group(order by column_position) index_columns
  3  from user_ind_columns
  4  where table_name = 'EMPLOYEES'
  5  group by index_name;

INDEX_NAME           INDEX_COLUMNS
-------------------- --------------------
EMP_DEPARTMENT_IX    DEPARTMENT_ID
EMP_EMAIL_UK         EMAIL
EMP_EMP_ID_PK        EMPLOYEE_ID
EMP_JOB_IX           JOB_ID
EMP_MANAGER_IX       MANAGER_ID
EMP_NAME_IX          LAST_NAME,FIRST_NAME

6 rows selected.

Many Queries, Many Indexes

We can write 15 different queries – a query for every possible combination. Continue reading “A Single Query with Many Filter Combinations”

Constraint Optimization Summary

This is the last part of a series about Constraint Optimization.
In this post I’ll summarize the conclusions from the previous parts.

When we add a constraint to an existing table, there are two aspects that are worth taking into consideration: duration and availability.

Duration

When the table contains a significant number of rows, adding a constraint may take a lot of time. In some cases Oracle applies a very nice optimization that can reduce this time to almost zero.

In part 1 we saw that such optimization happens when adding a column and an inline check constraint on that column in a single statement; and that this optimization does not happen for out-of-line check constraint.
In part 2 we saw that this optimization may lead to data integrity violation in some extreme cases.
In part 3 we saw that such optimization happens when adding a column and a foreign key constraint on that column in a single statement (for both inline and out-of-line constraints).
In part 4 we saw that unfortunately this optimization never happens for unique constraints.
In part 5 we saw that this optimization doesn’t happen if the added column is defined with a default value.

Availability

By default, adding a constraint is an offline operation. It means that it cannot start as long as the table is locked by active transactions, and that it blocks DML statements on the table for the duration of the operation. Obviously, the longer the constraint addition takes, the higher the significance of the availability issue.

In part 6 we saw how to add check constraints and foreign key constraints in an online way, by splitting the single operation into several ones.
In part 7 we saw how to add unique constraints in an online way (assuming Enterprise Edition).

Summary

The following table summarizes all of the above:

Adding a column and an inline constraint in a single statement Adding a column and an out-of-line constraint in a single statement Adding a column and a constraint in separate statements
Check Constraint Fast Duration depends on table size Duration depends on table size
Offline Offline Online can be achieved
Foreign Key Constraint Fast Fast Duration depends on table size
Offline Offline Online can be achieved
Unique Constraint Duration depends on table size Duration depends on table size Duration depends on table size
Offline Offline Online can be achieved in Enterprise Edition

Adding a Unique Constraint in an Online Way

Note: unlike most of my posts, this one assumes using Enterprise Edition

I have a table t and I want to add a unique constraint on one of its columns – c1.

The Offline Way

The straightforward and most simple way to do it is using a single alter table statement:

SQL> alter table t add constraint c1_uk unique (c1);

Table altered.

By default, Oracle creates in this operation a unique constraint (named c1_uk) and a corresponding unique index (named c1_uk as well) that enforces the constraint.
The downside is that this is an offline operation – the table is locked in Share mode.
This is true even if we specify that the creation of the index is online:

SQL> alter table t add constraint c1_uk unique (c1) using index online;

Table altered.

If the table contains many records, the creation of the index may take a significant amount of time, during which the table is locked and DML operations on the table are blocked.

The Online Way

We can create the unique constraint in an online way, by splitting the operation into three steps: Continue reading “Adding a Unique Constraint in an Online Way”

Fast but Offline, or Online but Slow?

The Constraint Optimization series:

In the previous parts of this series I showed that Oracle does a nice optimization – that may save plenty of time – when we add in a single ALTER TABLE statement a new nullable with no default value column and a corresponding:
– inline (a.k.a. “column-level”) check constraint
or
– foreign key constraint (either inline or out-of-line)

In these cases Oracle enables the constraint (for future DML statements) and marks the constraint as VALIDATED (stating that existing records respect the constraint) without actually performing any check – counting on the fact that all the existing records have NULL in the new column. This is a beautiful optimization – if the table already contains many records, then skipping the (unnecessary) validation phase can save lots of time.

So, is it always better to add the new column and the corresponding constraint in a single ALTER TABLE statement? No, it isn’t. Sorry for the cliché, but… it depends.

The advantage is clear: skipping the unnecessary validation phase can save lots of time (the bigger the table, the higher the saving).

But there is also a downside: such a combined operation is an offline one. The table is locked; although for a short time, but in eXclusive mode.

If we cannot afford to do it in one short offline operation, we can do it in three separate online operations, but without the aforementioned optimization:

1. Adding the column (a short operation)

alter table t add (c number);

2. Adding the constraint without validating it (a short operation)

alter table t add (constraint c_chk check (c>0) enable novalidate);

3. Validating the constraint (the bigger the table, the longer this operation takes)

alter table t enable validate constraint c_chk;

By default, when we add a constraint it is both enabled and validated, and this is done as an offline operation. This is why I split it into two steps – both are online: the first step is adding the constraint and enabling it without validation, and the second one is validating the constraint.

EBR – Part 5: Explicit Actualization of Dependent Objects

This is part 5 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so changes can be applied in both online and safe way. I discussed enabling, creating and using editions; session edition; and editioned objects.
In part 4 we discussed another challenge of online upgrades – invalidation of dependent objects.
In this part I’ll demonstrate everything I wrote about in the previous post.

Visit the index page for all the parts of the series

Creating a New Edition

We want to change the PEOPLE_DL spec, and as in any online upgrade, we start by creating a new edition. Let’s create edition V2 and grant use on it to DEMO_EBR:

-- connected as system
ORA$BASE> create edition v2;

Edition created.

ORA$BASE> grant use on edition v2 to demo_ebr;

Grant succeeded.

Invalidation of Dependent Objects

Before doing the online upgrade properly (i.e., using the new edition), let’s see what happens in a non-EBR environment.
First let’s see the objects in our schema and the dependencies between them before the upgrade: Continue reading “EBR – Part 5: Explicit Actualization of Dependent Objects”

EBR – Part 4: Invalidation and Actualization of Dependent Objects

This is part 4 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so changes can be applied in both online and safe way. I discussed enabling, creating and using editions; session edition; and editioned objects.
In this part we’ll see more challenges that online upgrades bring – this time when changing a package spec.

Visit the index page for all the parts of the series

Invalidation

This time we need to change the PEOPLE_DL package spec. There are no table changes, and of course, as we speak about EBR, the upgrade from the previous version to the new one should be online.

An online upgrade means that the application users should be able to continue working uninterruptedly. The code objects that they use should remain valid and available at any time.

In addition to the challenges raised from the first use case – changing PEOPLE_DL package body – the current use case introduces another challenge: invalidation. Changing the PEOPLE_DL package spec will cause its dependent objects become invalid.
Even if the change does not cause any compilation errors in the dependent objects, they become invalid.
Actually, even if we just recompile an object, without making any changes in it, its dependent objects still become invalid.
Now, it’s true that in these cases revalidation will occur automatically as soon as the invalid object is used, but in an online upgrade scenario this is usually unacceptable.

Moreover, many times we need to change multiple interrelated objects. For example, changing the API of some procedure in one package, and consequently changing it in the package body and changing the calls to that procedure from another package. In this case we cannot avoid having broken objects – invalid and with compilation errors – during the upgrade, and this is obviously cannot be really considered an online upgrade. Continue reading “EBR – Part 4: Invalidation and Actualization of Dependent Objects”

EBR – Part 3: Editions and Editioned Objects

This is part 3 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – such as a package body compilation – can be dangerous in a busy system.
In this post we’ll see how EBR solves the problems described in the previous post, so such a change can be applied in both online and safe way.

Visit the index page for all the parts of the series

Overview

The basic concept of EBR is that while the clients are connected to the database and use the objects – say the package pkg from the previous post – we can create another instance of pkg – a copy of pkg – and make all the necessary changes in this new instance.
We create this new pkg in the same schema, so now we actually have two packages named pkg in one schema. But each session sees only one of these packages – the clients continue seeing the old instance of pkg, and we the developers currently see the new instance of pkg.
When we are happy with the new implementation, we can expose it, so clients that will open new connections to the database from now on, will see the new pkg.
We achieve this by editions. Imagine that our schema is divided into multiple separate areas. At any point in time, each session sees one and only one area.
These separate areas are called editions, and we can keep basically any code object there – procedures, functions, packages, triggers, views, and more.
We can’t have two objects named pkg in the same edition, but we can in two separate editions.

Editions

Editions are database-level objects Continue reading “EBR – Part 3: Editions and Editioned Objects”

EBR – Part 2: Locking, Blocking and ORA-04068

This is part 2 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In this post we’ll start handling the first type of change request: changing a package body.

Visit the index page for all the parts of the series

The Task

We need to change the implementation of the PEOPLE_DL package; i.e. we need to change the package body.
There are no API changes (the package spec is not changed) and no table changes.
And of course, as we speak about EBR, the upgrade from the base version to the new one should be online.

The Problems

Locking and Blocking

An online upgrade means Continue reading “EBR – Part 2: Locking, Blocking and ORA-04068”

EBR – Part 1: Overview and Setup

This is a link to an index page for all the parts of the series

I have been using EBR in a real production system for more than 4 years now.
EBR – an acronym for Edition-Based Redefinition – is a powerful and unique feature (or, more precisely, a set of features) that enables patching and upgrading live Oracle-based applications in an online fashion, with zero downtime.

As an Oracle Developer and DBA I find EBR one of the most important tools in my toolkit, and I take advantage of every opportunity to let other Oracle developers and DBAs know about it.

I love EBR because:

  • It enables us to do any change to active production systems – from a small bug fix to a major version upgrade – in an online fashion, using hot rollover: we expose a new version (a new edition) while users are still using the old version, and different users may use two (or even more) versions at the same time.
  • We perform the upgrade in the privacy of a new unexposed edition, which is a huge benefit, in my opinion, because it eliminates the regular pressure of completing the upgrade as soon as possible to reduce downtime, and the stress of not making any harm by mistake to a live system.
  • We can do the upgrade at any time, not necessarily at off-peak hours.
  • And EBR is supported in all the editions (since Oracle 11.2), including standard edition, and requires no special license.

I’m starting today a series of posts about Continue reading “EBR – Part 1: Overview and Setup”

ODC Appreciation Day: Collections in SQL

Here’s my contribution to the ODC Appreciation Day.

Overview

Last week I had the privilege to participate in the EOUC Database ACES Share Their Favorite Database Things session at Oracle OpenWorld, so I think that the best topic to write about, as part of the ODC Appreciation Day, is the one I talked about in this session.
My 5-minute presentation was about Collections in SQL.

Collections are very useful in PL/SQL development. This is a well-known fact, and collections are indeed used a lot by PL/SQL developers.
But it is less known that collections can be very useful also in SQL statements, not only in PL/SQL. The Oracle database supports user-defined collection types (Nested Tables and Varrays) and built-in functions that operate on collections, and they add yet more power to the already-powerful SQL language.

Examples

Here are some links to blog posts that I’ve written in the past, in which I used collections in SQL.

This post includes an example for a PL/SQL procedure that gets a collection parameter as input and needs to perform some manipulation on it. The implementation of this procedure becomes easy using Collection Unnesting – simply writing a SQL statement (MERGE in this case), that treats the collection parameter as if it were a simple table.

In this post we take advantage of the PowerMultiSet built-in function for creating a Kakuro cheat-sheet.

In this post we observe subtle differences between the two types of collections – Nested Tables and Varrays – with regard to the SQL functions that can be applied on them.

This post shows how sometimes collection functions such as SET and CARDINALITY enable solving a problem in a more concise and simple way than the alternatives.

And here is a simple solution for some problem that was asked on Ask TOM, using the SQL function with probably the longest name – POWERMULTISET_BY_CARDINALITY.

Documentation