Re: Function based index with <>
- From: Adric Norris <spikey.mcmarbles@xxxxxxxxx>
- To: stmontgo@xxxxxxxxx, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Aug 2009 13:12:27 -0500
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@testdb> create user test
2 identified by test
3 quota 10m on users;
User created.
SYS@testdb> grant create session, create table, create materialized view,
plustrace to test;
Grant succeeded.
SYS@testdb> grant execute on dbms_advanced_rewrite to test;
Grant succeeded.
TEST@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@testdb> create index i on t (flag);
Index created.
TEST@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@testdb> commit;
Commit complete.
TEST@testdb> -- gather stats, and make the table appear MUCH larger to the
optimizer
TEST@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@testdb> -- this should result in a FTS
TEST@testdb> set autotrace traceonly explain
TEST@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@testdb> set autotrace off
TEST@testdb> -- declare to the optimizer that our preferred version is
equivalent
TEST@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@testdb> -- time to make the donuts!
TEST@testdb> set autotrace traceonly explain
TEST@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.
Other related posts:
