Re: Function based index with <>
- From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
- To: stmontgo@xxxxxxxxx, ukja.dion@xxxxxxxxx
- Date: Thu, 20 Aug 2009 08:08:04 +0200
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@xxxxxxxx
10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland
Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info@xxxxxxxx
http://www.10046.ch
=========================
On Thu, Aug 20, 2009 at 6:09 AM, Dion Cho <ukja.dion@xxxxxxxxx> 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@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:
