查询以获取具有非 NULL 列的唯一索引 - Oracle

Question

目前我正在尝试查找表中定义的所有唯一索引，这些索引对于Oracle数据库来说不是NULL。我的意思是，Oracle 允许在甚至定义为 NULL 的列上创建唯一的索引。

因此，如果我的表有两个唯一索引，我想检索具有 NOT NULL 约束的所有列的特定唯一索引。

我确实提出了这个查询：

select ind.index_name, ind_col.column_name, ind.index_type, ind.uniqueness
from sys.dba_indexes ind
inner join sys.dba_ind_columns ind_col on ind.owner = ind_col.index_owner and ind.index_name = ind_col.index_name
where ind.owner in ('ISADRM') and ind.table_name in ('TH_RHELOR') and ind.uniqueness IN ('UNIQUE')

上面的查询为我提供了带有关联列的所有唯一索引，但我不确定，我应该如何将上述查询与具有表所有列的 NULLABILITY 数据的ALL_TAB_COLS联接。

我尝试使用索引连接此表并尝试子查询，但没有得到适当的结果。

因此，请您对此发表评论。

Answer 1

分析函数和内联视图可以提供帮助。

分析函数允许您返回详细数据，还可以基于单独的窗口创建该数据的摘要。 详细结果包括索引所有者、索引名称和列名称，但计数仅针对每个索引所有者和索引名称。

第一个内联视图联接三个表，返回详细信息，并具有分析函数来生成所有列的计数和所有可为 null 的列的计数。 第二个内联视图仅选择这两个计数相等的行。

--Unique indexes and columns where every column is NOT NULL.
select owner, index_name, column_name
from
(
    --All relevant columns and counts of columns and not null columns.
    select
        dba_indexes.owner,
        dba_indexes.index_name,
        dba_tab_columns.column_name,
        dba_tab_columns.nullable,
        count(*) over (partition by dba_indexes.owner, dba_indexes.index_name) total_columns,
        sum(case when nullable = 'N' then 1 else 0 end)
            over (partition by dba_indexes.owner, dba_indexes.index_name) total_not_null_columns
    from dba_indexes
    join dba_ind_columns
        on dba_indexes.owner = dba_ind_columns.index_owner
        and dba_indexes.index_name = dba_ind_columns.index_name
    join dba_tab_columns
        on dba_ind_columns.table_name = dba_tab_columns.table_name
        and dba_ind_columns.column_name = dba_tab_columns.column_name
    where dba_indexes.owner = user
        and dba_indexes.uniqueness = 'UNIQUE'
    order by 1,2,3
)
where total_columns = total_not_null_columns
order by 1,2,3;

分析函数和内联视图很棘手，但一旦您学会了如何使用它们，它们就会非常强大。