Mohamed Houri’s Oracle Notes

October 23, 2013

CBO and unusable unique index

Filed under: CBO — hourim @ 6:50 am

Very recently a question came up on oracle-list where the original poster was wondering about two things (a) how could a refresh on materialized view allows duplicate key to be possible in the presence of a unique index and (b) he was struggling about a particular select which is giving a wrong results.

The answer to the first question is easy and I have already blogged about it. He was refreshing the materialized view using a FALSE value for the parameter atomic refresh. With this particular refresh parameter, the materialized view is refreshed  using a rapid truncate table followed by a direct path insert. Direct path load as shown in my blog, will silently disable the unique index allowing duplicate keys to be accepted. Thought that this seems to be true (in this context of materialized view refresh) only in 11gR2. The preceding release (10gR2) is not allowing duplicate keys during this kind of refresh as I’ve shown in my answer in the oracle-list forum. Does this mean that 10gR2 is not direct path loading when atomic refresh is set to FALSE? I have to check.

But what motivated the current blog article is the second question. See with me

SQL> create table a(id int,val number);
Table created.

SQL> insert into a select 1, 1 from dual;
1 row created.

SQL> create table b(id int);
Table created.

SQL> create unique index uq_b on b(id);
Index created.

Then I will use a sqlloader to load data into table b using a direct path load in order to silently disable the unique index. The control file(c.ctl) I will be using resembles to:

LOAD DATA
INFILE *
REPLACE
INTO TABLE B
(id position(1:1) TERMINATED BY ",")
BEGINDATA
1;
1;

And now I will launch the sqlloader

C:\>sqlldr user/paswd@database control=c.ctl direct=true

SQL*Loader: Release 10.2.0.3.0 - Production on Tue Oct 22 16:46:06 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Load completed - logical record count 3.

What do you think it happens to the unique index and to the table b after this direct path load?


SQL> select index_name, status from user_indexes where index_name ='UQ_B';

INDEX_NAME                     STATUS
------------------------------ --------
UQ_B                           UNUSABLE

SQL> select count(1) from b;

COUNT(1)
----------
2

The unique index has been disabled and there are duplicate keys in table b.

So far so good.

Let’s now start exploring the Original Poster queries problem

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    15 (100)|          |
|   1 |  TABLE ACCESS FULL| A    |     1 |    26 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

The CBO knows that there is a unique index on b(id). And, as far as there is one id in table a, the CBO, obviously, assumes that there will be only one record for the corresponding id in table b. This is why table b is not present in the execution plan by the way. Unfortunately the unique index has been disabled by the direct path load and has permitted the presence of duplicate record in table b. This is the reason why the query is producing a wrong result.

If we force the CBO to access the table b the result is however correct

SQL>  select a.*,b.id
from a, b
where a.id = b.id(+);

ID        VAL         ID
---------- ---------- ----------
1          1          1
1          1          1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     2 |    78 |    36   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |    26 |    15   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    26 |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")

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

It is clear that the CBO is not looking the unique index status during the optimization (plan generation) phase.

But, what if instead of the unusable unique index, we have a disabled unique constraint? Will the CBO consider the status of the unique constraint in this case?

SQL> alter table b add constraint b_uk unique (id) disable;
Table altered.

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1

Oups. The CBO is still wrong. What if we drop the culprit index?

SQL> drop index uq_b;
Index dropped.

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1
1          1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     2 |    18 |    36   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |     6 |    15   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |     6 |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")

Which finally gives a correct result.

Bottom line: always make sure your unique indexes are usable.

UPDATE 27-12-2013 :  following a discussion here,Ted from the Oracle Global Customer Support, pointed me to the bug number 17533502 which seems very close the problem exposed here. Ted also pointedme to a work arround solution as shown below:

SQL> select * from v$version where rownum=1;

BANNER                                                                       CON_ID
---------------------------------------------------------------------------- -------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

SQL> select a.*
 2 from a, b
 3 where a.id = b.id(+);

ID   VAL
--- ----
 1   1

SQL> ALTER SESSION SET "_optimizer_join_elimination_enabled" = false;

Session altered.

SQL> select a.*
 2 from a, b
 3 where a.id = b.id(+);

ID  VAL
--- -----
 1  1
 1  1
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 96 other followers

%d bloggers like this: