Re: Function based index with <>

From: Adric Norris <spikey.mcmarbles_at_gmail.com>
Date: Thu, 20 Aug 2009 13:12:27 -0500
Message-ID: <d4beff360908201112h53909cfbmc4d047e7063392cd_at_mail.gmail.com>

If the query is the same each time, or has a reasonably small number of permutations, you may be able to accomplish your goal via Advanced Query Rewrite. Please note, however, that this in no way force the optimizer to make use of the index... it merely morphs the statement into a version which allows for the possibility.

Here's my test case:

SYS_at_testdb> create user test

  2     identified by test
  3     quota 10m on users;

User created.

SYS_at_testdb> grant create session, create table, create materialized view, plustrace to test;

Grant succeeded.

SYS_at_testdb> grant execute on dbms_advanced_rewrite to test;

Grant succeeded.

TEST_at_testdb> create table t (

  2     id   number(9) primary key,
  3     flag char(1) not null check (flag in ('Y','N'))

  4 );

Table created.

TEST_at_testdb> create index i on t (flag);

Index created.

TEST_at_testdb> begin

  2     for i in 1..100000 loop
  3        insert into t values (i, decode(mod(i,100),0,'N','Y'));
  4     end loop;

  5 end;
  6 /

PL/SQL procedure successfully completed.

TEST_at_testdb> commit;

Commit complete.

TEST_at_testdb> -- gather stats, and make the table appear MUCH larger to the optimizer
TEST_at_testdb> begin

  2     dbms_stats.gather_table_stats(user, 'T', cascade => TRUE,
  3        method_opt=> 'FOR COLUMNS id SIZE 1, COLUMNS flag SIZE 2');
  4     dbms_stats.set_table_stats(user, 'T', numblks => 100000);
  5     dbms_stats.set_index_stats(user, 'I', numlblks => 10000);

  6 end;
  7 /

PL/SQL procedure successfully completed.

TEST_at_testdb> -- this should result in a FTS
TEST_at_testdb> set autotrace traceonly explain
TEST_at_testdb> select * from t where flag <> 'Y';

Execution Plan

---

Plan hash value: 2153619298

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 1027 | 9243 | 10978 (1)| 00:02:45 |
|* 1 | TABLE ACCESS FULL| T | 1027 | 9243 | 10978 (1)| 00:02:45 |

---

Predicate Information (identified by operation id):

---

   1 - filter("FLAG"<>'Y')

TEST_at_testdb> set autotrace off

TEST_at_testdb> -- declare to the optimizer that our preferred version is equivalent
TEST_at_testdb> begin

  2     sys.dbms_advanced_rewrite.declare_rewrite_equivalence(
  3        name             => 'TEST',
  4        source_stmt      => 'select * from t where flag <> ''Y''',
  5        destination_stmt => 'select * from t where flag = ''N''',
  6        validate         => TRUE,
  7        rewrite_mode     => 'TEXT_MATCH');

  8 end;
  9 /

PL/SQL procedure successfully completed.

TEST_at_testdb> -- time to make the donuts!
TEST_at_testdb> set autotrace traceonly explain
TEST_at_testdb> select * from t where flag <> 'Y';

Execution Plan

---

Plan hash value: 1984501315

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |

---

| 0 | SELECT STATEMENT | | 1018 | 9162 | 105 (0)|
00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1018 | 9162 | 105 (0)|
00:00:02 |
|* 2 | INDEX RANGE SCAN | I | 1018 | | 101 (0)| 00:00:02 |

---

Predicate Information (identified by operation id):

---

   2 - access("FLAG"='N')

Obviously, you want to be *quite* certain that your version really is 100% equivalent to the original query.

If the application generates a multitude of distinct (but similar) queries, however, you *might* be able to use the GENERAL or RECURSIVE rewrite mode instead. I've only attempted to test the TEXT_MATCH mode, however, so no promises.

-- 
"I'm too sexy for my code." - Awk Sed Fred.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 20 2009 - 13:12:27 CDT