Re: Function based index with <>

From: Dion Cho <ukja.dion_at_gmail.com>
Date: Thu, 20 Aug 2009 13:09:52 +0900
Message-ID: <43c2e3d60908192109o73120101gb59ccfdde0551c0_at_mail.gmail.com>

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_at_gmail.com>

> 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_at_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)
>

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

Received on Wed Aug 19 2009 - 23:09:52 CDT