Re: Function based index with <>
- From: Dion Cho <ukja.dion@xxxxxxxxx>
- To: tony.adolph.dba@xxxxxxxxx
- Date: Thu, 20 Aug 2009 13:09:52 +0900
Another trick is "advanced query rewrite".
http://dioncho.wordpress.com/2009/07/31/function-based-index-and-or-expansion/
================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://dioncho.blogspot.com (japanese)
http://ask.ex-em.com (q&a)
================================
2009/8/20 Tony Adolph <tony.adolph.dba@xxxxxxxxx>
> Fair doos,.. missed that.
>
> My thoughts then are along the lines of creating a view on the app's table
> (suitably renamed) with the decode/when "magically" embedded into it.
>
> Something like this (didn't include the table renaming here)
>
> create or replace view v1 as
> select t.id, case when flag <> 'Y' then flag end flag
> from t;
>
> BUT, that didn't work:
>
>
> 15:21:48 TONY@billdev1> explain plan for
> 15:21:58 2 select * from v1 where flag <> 'N';
>
> Explained.
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------
> Plan hash value: 1601196873
>
> --------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
> --------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 6 | 21960 (1)| 00:04:24 |
> |* 1 | TABLE ACCESS FULL| T | 1 | 6 | 21960 (1)| 00:04:24 |
> --------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter(CASE WHEN "FLAG"<>'Y' THEN "FLAG" END <>'N')
>
> So not too useful,... :-(
>
> Maybe this would be the way to go though.... some clever person out there
> can help?......
>
> PS: I know dodgy example breaks the rules (cant change the app), but I do
> mention that I didn't include teh essential table renaming. (All a bit
> academic anyhow as it doesn't work)
>
Other related posts:
