Re: Filtered Index Equivalent in Oracle

Hi Zahir,

No, there is no direct equivalent of a "Filtered Index" in, but there are 2 common ways to handle similar problems:

1. Use partitioned tables: you can place popular values into own partitions and use Partial indexes: https://docs.oracle.com/database/121/VLDBG/GUID-256BA7EE-BF49-42DE-9B38-CD2480A73129.htm This way, you can use index access for rare values and a full scan for popular ones. In this case, you don't need to change your code.
2. Function based indexes: to make it easier, you can create a virtual invisible column like `indexed_col ... invisible generated always as (case when col in/not in {...} then null else col end) virtual` and create an index on it. Unfortunately, in this case you will need to rewrite your queries.

On Fri, Feb 10, 2023 at 6:48 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> The sad answer is NO.
> It's been on my wish list every since I saw it some time in about 2008.
> As you say, the FBI is the closest you can get, but that usually requires
> a change to the code to force the index to be usable.
>
>
> Regards
> Jonathan Lewis
>
>
> On Fri, 10 Feb 2023 at 16:21, Zahir Mohideen <zahir.dba_at_gmail.com> wrote:
>
>> Do we have a direct equivalent of "Filtered Index ( MS SQL Server)".
>> The closest oracle feature is Function Based Index, I can think of.
>> But , this isn't the same.
>>
>> Is there any similar feature in Oracle ?
>>
>> Thanks
>>
>>
>> Zahir Mohideen
>> http://mfzahirdba.blogspot.com/
>>
>>
>> *Nothing so GREAT was achieved without enthusiasm*
>>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

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