COLLECT INTO inside a Cursor Definition?

Oren Nakdimon 1 Reply

I have been programming in PL/SQL since 1993, more or less continuously (I’ve just realized that it’s exactly half of my life until now), so I know the PL/SQL syntax quite well.
So when I saw the following piece of code a few days ago, I was willing to bet it would not compile:

declare
    v_number_array sys.odcinumberlist;
begin
    for v_rec in (select rownum as num
                  bulk   collect
                  into   v_number_array
                  from   dual
                  connect by level < = 5)
    loop
        dbms_output.put_line(v_rec.num);
    end loop;
end;

Note it's a Cursor FOR LOOP that includes a BULK COLLECT INTO clause inside the cursor definition.

Luckily, I did not bet. I would have lost... Continue reading

Excessive Locking when Dropping a Table

Oren Nakdimon Leave a Reply

I tried to drop a table today and failed due to “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.
That was weird because I knew that nobody had been using this table for months, and that the table had no enabled foreign keys.
A quick investigation revealed the cause – the DROP TABLE operation tried to lock another table (in the quite aggressive “Share” mode) that was referenced by a disabled foreign key from the table I was trying to drop. The referenced table was locked by other sessions, and therefore the DROP TABLE operation failed.

Even if the foreign key constraint is enabled, there is no good reason in my opinion to lock the referenced table; all the more so if it’s disabled.
There is a workaround (which I think proves my last sentence): it’s possible to drop the constraint first, and then to drop the table. Dropping the constraint does not lock the referenced table.

Here is a simple test I executed in 11.2.0.4, 12.1.0.2 and 12.2.0.1: Continue reading

A Single Query with Many Filter Combinations – Part 3

Oren Nakdimon Leave a Reply

In a recent post I suggested a way to write a single SQL query that filters a table by one or more of several columns.
Here is the query from that post:

select * from employees where rowid in (
  select rid from (
    select rowid rid from employees where department_id = :department_id
    union all
    select rowid rid from employees where job_id = :job_id
    union all
    select rowid rid from employees where manager_id = :manager_id
    union all
    select rowid rid from employees where last_name = :last_name)
  group by rid
  having count(*) = nvl2(:department_id,1,0) + 
                    nvl2(:job_id,1,0) + 
                    nvl2(:manager_id,1,0) + 
                    nvl2(:last_name,1,0)
);

This is actually a simplification of a real task I handled some time ago.
In today’s post I’d like to look at a variation of the problem, which is closer to the real issue I handled. Continue reading

A Single Query with Many Filter Combinations – Part 2

Oren Nakdimon Leave a Reply

In the previous post I suggested a way to write a single SQL query that filters the EMPLOYEES table (as an example) by one or more of the following columns: DEPARTMENT_ID, JOB_ID, MANAGER_ID and LAST_NAME.

Here is the query from the previous post:

select * from employees where rowid in (
  select rid from (
    select rowid rid from employees where department_id = :department_id
    union all
    select rowid rid from employees where job_id = :job_id
    union all
    select rowid rid from employees where manager_id = :manager_id
    union all
    select rowid rid from employees where last_name = :last_name)
  group by rid
  having count(*) = nvl2(:department_id,1,0) + 
                    nvl2(:job_id,1,0) + 
                    nvl2(:manager_id,1,0) + 
                    nvl2(:last_name,1,0)
);

In this query, it is assumed that at least one of the bind variables (:department_id, :job_id, :manager_id, :last_name) contains a non-null value.
If all of them contain nulls, then the result set of the query is empty. Note that in this case Oracle will not even try looking for data; as Stew Ashton commented in the previous post, the Index Range Scan will do no consistent gets for a predicate such as “column_name = :bind_variable” when :bind_variable is null (because Oracle knows that no row satisfies a “column=null” condition).

If we want to support filtering by zero or more of the columns, we should add (union) a subquery to handle the case when all the bind variables are nulls: Continue reading

A Single Query with Many Filter Combinations

Oren Nakdimon 10 Replies

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

Constraint Optimization Summary

Oren Nakdimon Leave a Reply

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

Oren Nakdimon 2 Replies

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

Fast but Offline, or Online but Slow?

Oren Nakdimon Leave a Reply

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

Oren Nakdimon 5 Replies

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 4: Invalidation and Actualization of Dependent Objects

Oren Nakdimon 4 Replies

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