You Asked

Hi.
I am forced to use multiple UNION ALL just because ORACLE will disregard all my Indexes if I use CASE statement in WHERE clause.
This is a huge inconvenience for me because I have a large query and a lot of CASE(s), so I have to repeat the same query many times.
And result is dramatic:
with UNION AL - query runs 1 SEC.
with CASE statements in WHERE clause - it runs 30 MIN.

So, the question is - WHY???

I used to work with other databases, and coming to ORACLE makes me feel as on a large minefield, when you always afraid to do something that will cause ORACLE to stop using indexes.

and Chris said...

Applying any function to a column will "break" the indexes in Oracle. So you'll get a full table scan. AFAIK other RDBMSes behave in a similar way.

For example, the following three queries all return the same row. But only the first one uses the index. Because it's the only one without a function applied to the column:

create table t (
  x int not null,
  y varchar2(10) not null
);

insert into t
  select rownum, 'XXXXXXXX' from dual connect by level <= 1000;
  
commit;

create index i on t (x);
exec dbms_stats.gather_table_stats(user, 't');

select /*+ gather_plan_statistics */* from t
where  x = 2;

         X Y
---------- ----------
         2 XXXXXXXX

select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */* from t where  x = 2

Plan hash value: 1667465620

--------------------------------------------
| Id  | Operation                   | Name |
--------------------------------------------
|   0 | SELECT STATEMENT            |      |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |
|   2 |   INDEX RANGE SCAN          | I    |
--------------------------------------------

select /*+ gather_plan_statistics */* from t
where  x + 1 = 3;

         X Y
---------- ----------
         2 XXXXXXXX

select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */* from t where  x + 1 = 3

Plan hash value: 2498539100

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T    |
----------------------------------

select /*+ gather_plan_statistics */* from t
where  case when x = 2 then x else 1 end = 2;

         X Y
---------- ----------
         2 XXXXXXXX

select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */* from t where  case when x = 2
then x else 1 end = 2

Plan hash value: 2498539100

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T    |
----------------------------------

To overcome this, you need to create a function-based index. The index must have the exact same function you use in your where clause:

create index i1 on t (x + 1);
create index i2 on t (case when x = 2 then x else 1 end);

select /*+ gather_plan_statistics */* from t
where  x + 1 = 3;

         X Y
---------- ----------
         2 XXXXXXXX

select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */* from t where  x + 1 = 3

Plan hash value: 2702475061

--------------------------------------------
| Id  | Operation                   | Name |
--------------------------------------------
|   0 | SELECT STATEMENT            |      |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |
|   2 |   INDEX RANGE SCAN          | I1   |
--------------------------------------------

select /*+ gather_plan_statistics */* from t
where  case when x = 2 then x else 1 end = 2;

         X Y
---------- ----------
         2 XXXXXXXX

select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */* from t where  case when x = 2
then x else 1 end = 2

Plan hash value: 3121986482

--------------------------------------------
| Id  | Operation                   | Name |
--------------------------------------------
|   0 | SELECT STATEMENT            |      |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |
|   2 |   INDEX RANGE SCAN          | I2   |
--------------------------------------------

Of course, that's not the whole story. Oracle can use plain indexes on columns with functions applied if:

- It's a multi-column index and there are no functions on the other columns OR
- Your query only access the columns in the index:

drop index i1;
drop index i2;

create index i3 on t(y, x);

select /*+ gather_plan_statistics */* from t t
where  y = 'XXXXXXXX'
and    to_char(x) = '2';

         X Y
---------- ----------
         2 XXXXXXXX

select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */* from t t where  y = 'XXXXXXXX'
and    to_char(x) = '2'

Plan hash value: 1086377500

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  INDEX FAST FULL SCAN| I3   |
-------------------------------------

drop index i3;
select /*+ gather_plan_statistics */x from t t
where  to_char(x) = '2';

         X
----------
         2

select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */x from t t where  to_char(x) = '2'

Plan hash value: 53475921

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  INDEX FAST FULL SCAN| I    |
-------------------------------------

In the second scenario, the column must be mandatory (not null):

alter table t modify x null;

select /*+ gather_plan_statistics */x from t t
where  to_char(x) = '2';

         X
----------
         2

select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------

EXPLAINED SQL STATEMENT:
------------------------
select /*+ gather_plan_statistics */x from t t where  to_char(x) = '2'

Plan hash value: 2498539100

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T    |
----------------------------------

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ig, September 09, 2016 - 5:06 pm UTC

I don't agree. At least SqlServer, Sybase, DB2 and MySQL will not drop your indexes just because you used function on a column that is not even used in filtering. From technical point of view it does not make any sense.

September 10, 2016 - 8:38 am UTC

We used the term "break" here in the sense of:

If you have an index on column "C", then a query on (say)

where SQRT(C) = ...

will not be able to use the index for an accessed path lookup.

We were not meaning to convey that the index is lost in any way

To Igor | on Xplan "Predicate Information"

Rajeshwaran Jeyabal, September 12, 2016 - 8:00 am UTC

demo@ORA12C> create table t as select * from all_objects;

Table created.

demo@ORA12C> create unique index t_idx on t(object_id);

Index created.

When an indexed column(s) (the leading portion of indexed columns or the non-leading portion of indexed column in case of Skip scans) is evaluated against
1) a Bind variable or
2) a literal or
3) against an expression/result of an expression

and if they are considered to return the very few rows from the table, where the CBO feels that using index would be the best way then the index will be picked by the optimizer

demo@ORA12C> set autotrace traceonly explain
demo@ORA12C> select * from t where object_id = 55;

Execution Plan
----------------------------------------------------------
Plan hash value: 2929955852

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   116 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   116 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=55)

demo@ORA12C> select * from t where object_id = 55+25 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 2929955852

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   116 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   116 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=80)

The predicate section from the explain plan provides the necessary information about what is being used for the index lookup.

Incase of expression bounded over the indexed column(s), optimizer has no clue about what value is used to lookup the index - since the result of that expression will be available after it got evaluated - the same happened with CASE statements.

demo@ORA12C> select * from t where case when object_id = 55 then 1 else 2 end = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   843 | 97788 |   504  (22)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   843 | 97788 |   504  (22)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(CASE "OBJECT_ID" WHEN 55 THEN 1 ELSE 2 END =1)

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

To Igor | on Xplan "Predicate Information"

More to Explore

Hints

Oracle optimizer stop using Indexes when there is a CASE in WHERE clause.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

To Igor | on Xplan "Predicate Information"

More to Explore

Hints