RE: Filtered Index Equivalent in Oracle

IF you have control of your workflow, you *may* be able to do something like this:  

Rename X to X_base;

Create or replace synonym X as select * from X_base;  

Create view X_skinny as select * from X_base where sparse_column is not in (‘blah’, ‘blah2’, ‘blah3’);

create index x_skinny_sparse on X_skinny(sparse_column);  

And then, when you want to use the skinny version of the table for a query in your application’s workflow, insert  

create or replace synonym X as select * from X_skinny;

<run the application query>

Create or replace synonym X as select * from X_base;  

Now if you cannot control your workflows (or run the skinny queries from another USER with the skinny synonym), then don’t do this, because hilarity will ensue.  

If you CAN run the skinny queries from an alternate user, then this should work fine. (And you just leave the synonym X to X_skinny all the time for that user).  

In the alternate user scenario you don’t even have to do the rename or synonym, just create the skinny view and index in the alternate user referring to the application suite’s user.  

Notice that this is NOT a general solution and only works correctly when you CAN control the workflow and/or use an alternate user to run the “skinny” queries.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zahir Mohideen Sent: Sunday, February 12, 2023 11:39 AM To: xt.and.r_at_gmail.com
Cc: jlewisoracle_at_gmail.com; oracle-l_at_freelists.org Subject: 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