在 Oracle 表上查找過濾索引

如何查找索引是否被過濾？

過濾索引（借用 SQL Server 中的術語，或在 PostgreSQL 中稱為“部分索引”）在專注於行的小子集（表示未處理訂單或消息隊列的表）時可以具有非常高的性能，或者可以工作作為隱藏的約束。

例如：

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))
);

現在，為了確保每個產品只有一個價格有效，我們可以創建以下過濾索引：

create unique index i1 on product_price (
 case when active = 1 then product_id end
);

如果我嘗試為產品插入第二個有效價格（第 4 個插入），它會失敗：

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

在我的真實案例中，我需要辨識這些隱藏的約束。它們是在 DEV 和 TEST 模式中創建的，但不是在 PROD 中創建的。我需要在 PROD（400 多個表）中找到不斷增長的不良數據並決定如何解決它。

將功能索引稱為部分索引是不正確的，因為該術語在 Oracle 中具有其他含義（特別是分區表分區子集上的索引）。

此外，正如您所注意到的，創建唯一索引（無論是否具有功能）都不會創建命名約束，並且您不能在之後創建引用表達式的命名約束。因此，您可以找到這些“約束”的唯一地方是*_INDEXES和*_IND_EXPRESSIONS視圖：

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%'

唯一約束在概念上類似於唯一索引，但存在一些差異，使它們成為不同的概念。您創建的是唯一索引，從技術上講，它不是一個約束，儘管它的行為類似於不可延遲的約束，並且在違反唯一性時會引發 ORA-00001 錯誤。

架構擁有的所有約束都可以在 USER_CONSTRAINTS 中找到。同樣， USER_INDEXES 將顯示模式擁有的索引。如果您查看這兩個視圖，您會看到“i1”出現在 USER_INDEXES 而不是 USER_CONSTRAINTS。因此，以下查詢可能會為特定表執行您想要的操作：

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

根據您希望如何在 DEV 和 PROD 之間進行檢查，您可以修改查詢以列出架構中的部分或全部表。如果您想查看架構之外的內容，則需要使用 ALL_CONSTRAINTS 和 ALL_INDEXES，儘管您可能需要 DBA 授予對這些視圖的訪問權限。

引用自：https://dba.stackexchange.com/questions/282167