Author: Oren Nakdimon

Wrong Results with IOT, Added Column and Secondary Index

Update: I reported about this bug to Oracle, and they fixed it in version 20.1

I found a “wrong results” bug yesterday, easily reproduced in 11g, 12c and 18c.

In short, we may get wrong results under the following circumstances:

  • We have an Index-Organized Table (IOT) with multi-column primary key, populated with rows
  • The table has a secondary index on part of the primary key columns
  • We add another column to the existing IOT
  • We select from the IOT while accessing it via the secondary index

Following is a simple example (also uploaded to livesql.oracle.com).

We create an index-organized table named iot, including the columns x and y.
The primary key is comprised of both columns.

SQL> create table iot (
  2    x number,
  3    y number,
  4    constraint iot_pk primary key (x,y)
  5  ) organization index;

Table created.

We create a secondary index on y:

SQL> create index secondary_idx on iot (y);

Index created.

We populate the table with some rows:

SQL> insert into iot select rownum,mod(rownum,3)
  2  from dual
  3  connect by level<=7;

7 rows created.

SQL> commit;

Commit complete.

Now we add another column, z, to the table, and we set some value in z in one row:

SQL> alter table iot add z number;

Table altered.

SQL> update iot set z=42 where x=1;

1 row updated.

SQL> commit;

Commit complete.

So currently this is the content of the table:

SQL> select * from iot;

         X          Y          Z
---------- ---------- ----------
         1          1         42
         2          2
         3          0
         4          1
         5          2
         6          0
         7          1

7 rows selected.

Note that z contains the value 42 in the first row and null in all the other rows.
But if we access the table via the secondary index – for example getting all the records where y=1 – the value of z from the first row “spills over” into the other rows, and we get wrong results:

SQL> set autotrace on explain
SQL> select * from iot where y=1;

         X          Y          Z
---------- ---------- ----------
         1          1         42
         4          1         42
         7          1         42

3 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 177722221

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     3 |   117 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| IOT_PK        |     3 |   117 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| SECONDARY_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("Y"=1)
   2 - access("Y"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

It seems that the problem is not with the secondary index itself, as we get the correct rows, but something with accessing the table via the secondary index causes the wrong results.
If we hint the query to access the table directly we get the correct results:

SQL> select /*+ index_ffs (iot) */ * from iot where y=1;

         X          Y          Z
---------- ---------- ----------
         1          1         42
         4          1
         7          1

3 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3252171408

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     3 |   117 |     2   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| IOT_PK |     3 |   117 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("Y"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

EBR – Part 9: Adding a New Column

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

Visit the index page for all the parts of the series

Introduction

In part 7 (“Editioning Views”) I introduced our third development use case – adding a new column that represents a new business logic.

I emphasize the fact it’s a new business logic, because sometimes we add new columns that come instead of existing columns, for replacing an existing business logic. As we’ll see in future posts, such cases require some additional handling in online upgrades.

Now, after part 8 (“The Last Planned Downtime”), in which we converted our schema to use editioning views and made it fully ready for online upgrades, we can implement the requirement in an online way.

The Task

We want to add a new attribute – Eye Color – to the Person entity.
For that we need to add a new column – EYE_COLOR – to the PEOPLE table, add make the corresponding changes in the PEOPLE_DL and APP_MGR packages (adding an input parameter – i_eye_color – to the people_dl.add procedure and changing the implementation of people_dl.add and app_mgr.do_something accordingly).

The Current State

At this point, V2 is our latest edition and it is exposed to the production clients. When connected to V2, we see the following objects: Continue reading “EBR – Part 9: Adding a New Column”

EBR – Part 8: The Last Planned Downtime

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

Visit the index page for all the parts of the series

Introduction

I concluded the previous post by stating that the application code should never reference tables directly; instead, every table should be covered by an editioning view and the application code should reference the views.

Starting from Scratch?

If you start developing a new system from scratch, I strongly recommend to follow this practice, even if initially there is no requirement for online upgrades. That way, you will be ready for online upgrades from day one, so should such a requirement arise at a later stage you will not need to change anything – neither your data structures nor your code. The only overhead is maintaining an editioning view for every table, and I’m sure you’ll realize very quickly that this overhead is negligible. As long as downtime during your upgrades is acceptable, you only need to enable your schema owner for editions (a one-time operation, as shown in part 3), and (by default) you will simply use a single edition – the default one.

Existing Systems

If you have an existing system, and you want to enjoy the benefits of online upgrades and zero downtime, you need to do a one-time conversion of your schema – to cover all the tables by editioning views. Actually, even if you have an existing system and you do not want to enjoy these benefits, I would still recommend getting ready for online upgrades now. The rationale is the same as in the previous section (“Starting from Scratch?”).

Converting the Demo Schema

Let’s handle first the demo schema I’ve been using in this post series. We currently have the following objects: Continue reading “EBR – Part 8: The Last Planned Downtime”

EBR – Part 7: Editioning Views

This is part 7 of a post series about EBR.

Visit the index page for all the parts of the series

Introduction

Our next use case is adding a column that represents a new logic to the PEOPLE table, and making the corresponding changes in the PEOPLE_DL and APP_MGR packages. 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 and second use cases, the current use case introduces another challenge. And unlike the previous challenges, this one cannot be overcome just by using a new edition.

The Current State

At this point, after completing two upgrades already – in part 3 and part 5, we have three editions, with the following actual objects:

V2> select object_name,object_type,status, edition_name
  2  from   user_objects
  3  order  by object_name,object_type;

OBJECT_NAME  OBJECT_TYPE  STATUS  EDITION_NAME
------------ ------------ ------- ------------
APP_MGR      PACKAGE      VALID   ORA$BASE
APP_MGR      PACKAGE BODY VALID   V2
PEOPLE       TABLE        VALID
PEOPLE_DL    PACKAGE      VALID   V2
PEOPLE_DL    PACKAGE BODY VALID   V2
PEOPLE_PK    INDEX        VALID

6 rows selected.

As discussed in part 6, a table is a non-editioned object, and therefore we have a single instance of the PEOPLE table – referenced by all the instances of the PEOPLE_DL package body in all the editions.

The Task

We want to add a new attribute – Eye Color – to the Person entity.
For that we need to add a new column – EYE_COLOR – to the PEOPLE table, add make the corresponding changes in the PEOPLE_DL and APP_MGR packages (adding an input parameter – i_eye_color – to the people_dl.add procedure and changing the implementation of people_dl.add and app_mgr.do_something accordingly).

Our Problem

If we try to add the new column to the table, then Continue reading “EBR – Part 7: Editioning Views”

The Performance of the FIRST and LAST Functions

Overview

One of the first posts I wrote in this blog (almost five years ago) was about the FIRST and LAST aggregate functions.
These functions are, in a way, extended versions of the much more popular aggregate functions MIN and MAX.
MIN and MAX allow you (conceptually) to sort a group of rows by some column and return the value of that column from the first or last row in the sorted group.
The FIRST and LAST functions extend this ability, and allow you to sort a group of rows by one column, but return the value of another column from the first or last row in the sorted group.
You are welcome to read the original post for more details about the functionality and syntax of these functions, and for seeing some examples.

In this post I’d like to focus on the performance of the FIRST and LAST functions.
Continue reading “The Performance of the FIRST and LAST Functions”

EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned

This is part 6 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 part 5 we saw the importance of explicit actualization of dependent objects.
In this part we’ll dive into the differences between editioned and non-editioned objects, editionable and non-editionable object types, and the relationships between them.

Visit the index page for all the parts of the series

In a previous post, we enabled the user DEMO_EBR for editions. As a result, some of the existing objects of DEMO_EBR (the package specs and package bodies) have become editioned, and the others (a table and an index) have not. Why is that?
Before I answer this question, let’s see what an editioned object is exactly, and what a non-editioned object is.

Editioned and Non-Editioned Objects

An editioned object is Continue reading “EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned”

COLLECT INTO inside a Cursor Definition?

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 "COLLECT INTO inside a Cursor Definition?"

Excessive Locking when Dropping a Table

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 “Excessive Locking when Dropping a Table”

A Single Query with Many Filter Combinations – Part 3

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 3”

A Single Query with Many Filter Combinations – Part 2

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 – Part 2”