How can I find if an index is filtered?
Filtered indexes (borrowing the term from SQL Server, or known as "partial indexes" in PostgreSQL) can have very high performance when they focus on small subsets of rows (a table that represents a queue of unprocessed orders or messages), or can work as concealed constraints.
For example:
create table product_price (
product_id int not null,
price numeric(12, 2) not null,
since date not null,
active int not null check (active in (0, 1))
);
Now, to ensure only one price is active per product, we can create the following filtered index:
create unique index i1 on product_price (
case when active = 1 then product_id end
);
If I try to insert a second active price for the product (4th insert), it fails:
insert into product_price (product_id, price, since, active)
values (123, 9.99, date '2020-06-01', 0); -- succeeds
insert into product_price (product_id, price, since, active)
values (123, 10.99, date '2020-12-01', 1); -- succeeds
insert into product_price (product_id, price, since, active)
values (456, 15.99, date '2020-07-01', 1); -- succeeds
insert into product_price (product_id, price, since, active)
values (456, 17.99, date '2020-11-01', 1); -- fails as expected
-- ORA-00001: unique constraint (I1) violated
In my real use case I need to identify these concealed constraints. They were created in the DEV and TEST schemas but not in PROD. I need to find the growing bad data in PROD (400+ tables) and decide how to tackle it.
Best Answer
It is incorrect to call functional indexes partial, because the term means something else in Oracle (specifically, indexes on subsets of partitions of partitioned tables).
Also, as you note, creating a unique index, functional or not, does not create a named constraint, and you cannot create a named constraint referencing an expression afterwards. As a result, the only place you can find these "constraints" are the
*_INDEXES
and*_IND_EXPRESSIONS
views: