1

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.

3
  • Like in stackoverflow.com/questions/65456806/… you still misunderstand the Oracle term "partial index". Also term "unlisted constraint" is something I never heard before. – Wernfried Domscheit Dec 28 '20 at 16:15
  • @WernfriedDomscheit Yes, you are right. Unfortunately, Oracle defined "partial indexes" as a concept that differs from literature. Please disregard the other question, since this one is better formulated for a bounty. – The Impaler Dec 28 '20 at 17:36
  • I don't agree. A "partial index" is an index which covers only a subset of the entire table. In several RDBMS you can freely define this subset, in Oracle the subset is bound to partitions. (you can create partitioned indexes but the INDEXING ON/OFF value is bound to table partition) So, unless you have partitioned indexes and/or tables all indexes in Oracle are "FULL" – Wernfried Domscheit Dec 28 '20 at 22:45
4
+50

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:

select i.table_owner, i.table_name, i.index_name, e.column_expression 
from dba_indexes i inner join dba_ind_expressions e 
on i.index_name = e.index_name and i.owner = e.index_owner
where i.uniqueness = 'UNIQUE' and i.index_type like 'FUNCTION-BASED%'
6
  • It is a functional index, nothing else. In Oracle, keys that contain nulls for all columns are not stored in the index, I guess that's what you mean by "partial", but it's just a side effect of how Oracle indexes are implemented. – mustaccio Dec 26 '20 at 18:31
  • @mustaccio unless you use Bitmap-Index, they also store NULL values. – Wernfried Domscheit Dec 28 '20 at 16:10
  • @WernfriedDomscheit The subject of this question is an explicitly unique index, which cannot be bitmap. – mustaccio Dec 28 '20 at 17:26
  • Thanks. This query shows the index and its expressions, but it doesn't tell if it's a filtered index or not. – The Impaler Dec 28 '20 at 17:54
  • The TO uses terms like "unlisted constraints", "concealed constraints", "filtered indexes" - none of them exist in Oracle world. So, it is really difficult to find out for what he is actually looking for. – Wernfried Domscheit Dec 28 '20 at 22:37
3

A unique constraint is conceptually similar to a unique index, but there are differences that make them different concepts. What you have created is a unique index and it is not technically a constraint, though it does behave like a non-deferrable constraint and will raise a ORA-00001 error when uniqueness is violated.

All the constraints owned by a schema can be found in USER_CONSTRAINTS. Similarly, USER_INDEXES will show the indexes owned by a schema. If you look at those two views, you will see that "i1" shows up in USER_INDEXES and not USER_CONSTRAINTS. Thus, the following query will probably do what you want for a specific table:

SELECT * FROM user_indexes WHERE table_name='PRODUCT_PRICE' AND uniqueness='UNIQUE'

Depending on how you want to do the checking between DEV and PROD, you can modify the query to list some or all the tables in the schema. If you want to look outside your schema, you will need to use ALL_CONSTRAINTS and ALL_INDEXES, though you may need the DBA to grant access to those views.

1
  • Yes! I ran the query and it finds it as you say. However, it does not tell if it's a partial index or a full one. – The Impaler Dec 26 '20 at 18:26
1

Is there a reason that you don't simply look for all the indexes that exist in one environment and not another? That seems like the simpler approach.

If we use your definition of a "partial index", any index on a nullable column is potentially partial (it will depend on whether the table happens to have any rows that are actually null). It isn't clear whether you mean for your definition to only include function-based indexes where the expression produces a nullable result or whether you mean for your definition to include only unique indexes.

For whatever definition you choose, you can probably get the result you are after by querying all_indexes and all_tables. If you are looking, for example, for all function-based normal, unique indexes that have less than half the rows of their associated table, you could use something like this

select ind.*
  from all_indexes ind
       join all_tables tab
         on ind.table_owner = tab.owner
        and ind.table_name  = tab.table_name
 where ind.index_type = 'FUNCTION-BASED NORMAL'
   and ind.uniqueness = 'UNIQUE'
   and ind.num_rows   < 0.5 * tab.num_rows

Since num_rows in all_indexes and all_tables are gathered when statistics are gathered on the index and table, they will necessarily be out of date and will most likely be estimates. In a development environment with little or no activity, you could explicitly gather statistics with a 100% sample to ensure that the statistics are more accurate before running the query. Most likely, you'll want to use some sort of reasonable fudge-factor (such as 0.5 in this query) so that you're not chasing down small differences in statistics either due to a handful of null values in the underlying table or to DML changes that took place between the time when the statistics were gathered on the index and the table. If statistics are broadly out of date in your database, you could compare the last_analyzed and sample_size in the table and index statistics to identify cases where statistics on the table and index were gathered at very different times or with very different sample sizes, either of which could cause a comparison of the num_rows on the two statistics to be misleading.

If you want to consider non-function based indexes that contain fewer rows than their underlying table, you can eliminate the index_type predicate. If you want to consider non-unique indexes that have fewer rows than their underlying table, you can eliminate the uniqueness predicate.

1
  • Yes, I looked at the index but many of them (probably 30%) are not present in prod, The canned answer was that it was due to "microservices architecture", though I don't think that's the real reason. +1 – The Impaler Jan 2 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.