Re: Filtered Index Equivalent in Oracle

Thank you Mark, Sayan and Jonathan.
We can't change the source code and we don't have license for partitioning option.

I was reading articles on 23c.
Looks like , this feature is not in the roadmap.

On Fri, Feb 10, 2023, 1:55 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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