Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, DEVERNE.

Asked: November 08, 2011 - 3:38 pm UTC

Last updated: November 23, 2017 - 1:10 am UTC

Version: 11.2.0.2.0

Viewed 10K+ times! This question is

You Asked

Tom,

Thanks for taking my question. I am trying to conditionally index rows in a table. In SQL Server 2008 there is a feature called filtered indexes that allows you to create an index with a where clause. So I have a table abc:

create table abc
( id int primary key,
  complete_flag varchar2(1),
  type varchar2(10),
  status varchar2(10));


I would like to index the columns type and status only if the complete flag is 'N'. In SQL Server I can
create a filtered index where the complete_flag = 'N' as such:

create index abc_indx1 
on abc(type, status)
where complete_flag = 'N';

insert into abc values (1, 'Y', 'AAA', 'BBB');
insert into abc values (2, 'Y', 'AAA', 'BBB');
insert into abc values (3, 'N', 'AAA', 'BBB');
insert into abc values (4, 'N', 'AAA', 'BBB');


So in my example, the index would only contain entries for id 3 and 4. I am trying to figure out how I would do the same sort of thing in Oracle 11gR2.

Thanks,


DeVerne

and Tom said...

In Oracle it would work like this:


ops$tkyte%ORA11GR2> create table abc
  2  ( id int primary key,
  3    complete_flag varchar2(1),
  4    type varchar2(10),
  5    status varchar2(10)
  6  );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create index abc_indx1
  2  on abc( case when complete_flag = 'N' then type end,
  3          case when complete_flag = 'N' then status end
  4        )
  5  /

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into abc values (1, 'Y', 'AAA', 'BBB');

1 row created.

ops$tkyte%ORA11GR2> insert into abc values (2, 'Y', 'AAA', 'BBB');

1 row created.

ops$tkyte%ORA11GR2> insert into abc values (3, 'N', 'AAA', 'BBB');

1 row created.

ops$tkyte%ORA11GR2> insert into abc values (4, 'N', 'AAA', 'BBB');

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable bv1 varchar2(10)
ops$tkyte%ORA11GR2> variable bv2 varchar2(10)
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select *
  2    from abc
  3   where case when complete_flag = 'N' then type end = :bv1
  4     and case when complete_flag = 'N' then status end = :bv2
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1850876884

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    43 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ABC       |     1 |    43 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ABC_INDX1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access(CASE "COMPLETE_FLAG" WHEN 'N' THEN "TYPE" END =:BV1 AND CASE
              "COMPLETE_FLAG" WHEN 'N' THEN "STATUS" END =:BV2)

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

ops$tkyte%ORA11GR2> set autotrace off




Rating

  (4 ratings)

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

Comments

Size of index

Dana, November 09, 2011 - 8:52 am UTC

A benefit of function based index is for the "status update" of a column where an "active" flag is always a very few rows of many millions, and the application only queries for active rows. The function can case where active then 1 else null end;. And by doing so, create an index which remains much smaller and efficient as the table grows.

DEVERNE COLEMAN, November 10, 2011 - 3:35 pm UTC

Thanks for the help. I had tried this a while back. I think I was able to create the index but could not seem to get a query to use the new index. I was missing exactly how to set the values in the where clause with the case statement. Now I understand what I was doing wrong and have been to apply this to a real table here at work. Once again, thanks for your help.

I have a similar problem to solve but this solution is not appropriate in my case

Guru H G, November 21, 2017 - 2:49 pm UTC

The variables(bv1, bv2) you have declared are not initialized to appropriate values. So, how do we get the desired results by using them in where clause?

Instead, if we use respective columns instead of variables, like here:

where case when complete_flag = 'N' then type end = type
4 and case when complete_flag = 'N' then status end = status

then we get the desired results but Optimizer will not be using the index in this case instead does a full table scan. How should we write our query so that it uses the index and also performs condition checking as expected?

Connor McDonald
November 22, 2017 - 5:51 am UTC

Give us a complete test case to describe what you're after please.

A test case to describe the issue

Guru H G, November 22, 2017 - 3:19 pm UTC

I have a table T1 with three columns-> "Num", "Str", "_Id"
I want to index the data in the table when the "Num" is less than 1000 and greater than 950.

So, I have created a function based index like this:

Create Index "USER1"."T1_IDX" on "USER1"."T1"(CASE WHEN ("Num" < 1000 and "Num" > 950) THEN "Str" else NULL END,
CASE WHEN ("Num" < 1000 and "Num" > 950) THEN "_Id" else NULL END
);

I am executing a select query like here:

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
set autotrace on;
select /*+ INDEX(a1, T1_IDX) */ count(*)
from "USER1"."T1" a1
where (CASE WHEN ("Num" < 1000 and "Num" > 950) THEN "Str" else null END)="Str"
and (CASE WHEN ("Num" < 1000 and "Num" > 950) THEN "_Id" else null END)="_Id";

But, the execution plan explains that it is doing full table scan instead of using the index. How should we write our select query so that it uses the index?
Connor McDonald
November 23, 2017 - 1:10 am UTC

I think you're misinterpreting the intent of the function based index here.

It is for the case where we want to lookup particular *values*, eg

SQL> create table t1
  2  as select rownum num, rpad(rownum,500,'x') str, rownum id
  3  from dual
  4  connect by level <= 10000;

Table created.

SQL>
SQL> Create Index t1x on t1 (
  2    CASE WHEN (Num < 1000 and Num > 950) THEN Str else NULL END,
  3    CASE WHEN (Num < 1000 and Num > 950) THEN Id else NULL END
  4  );

Index created.

SQL> select *
  2  from t1
  3  where CASE WHEN (Num < 1000 and Num > 950) THEN Str else NULL END = :b1
  4  and CASE WHEN (Num < 1000 and Num > 950) THEN Id else NULL END = :b2;
  
--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      | 10000 |  4970K|     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1   | 10000 |  4970K|     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1X  |    49 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------


Isn't your query just logically equivalent to a simple predicate on NUM ?

SQL> select count(*)
  2  from t1
  3  where CASE WHEN (Num < 1000 and Num > 950) THEN Str else NULL END = str
  4  and CASE WHEN (Num < 1000 and Num > 950) THEN Id else NULL END = id;

  COUNT(*)
----------
        49

SQL> select count(*)
  2  from t1
  3  where Num < 1000 and Num > 950;

  COUNT(*)
----------
        49


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.