Re: Function based index with <>

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Thu, 20 Aug 2009 08:08:04 +0200
Message-ID: <486b2b610908192308u6e615dbbl63e15a65c4050317_at_mail.gmail.com>

Yes, or perhaps VPD could help. Add the logically equivalent predicate to the query using a vpd policy. The optimizer might recognize it's cheaper to use the index if he has both predicates to choose from.

All seems a bit "overkill" -- have you tried contacting the vendor to get them to patch their query ?

Stefan

---

Stefan P Knecht
CEO & Founder
s_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch

---

On Thu, Aug 20, 2009 at 6:09 AM, Dion Cho <ukja.dion_at_gmail.com> wrote:

> 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 Thu Aug 20 2009 - 01:08:04 CDT