CODEWITHSUNDEEP
oracle-databaseplsqlnull
user2068804
user2068804

Reputation: 55

Query to get Unique Indexes having NOT NULL columns - Oracle

Currently I am trying to find all the unique indexes defined in a table which are NOT NULL for Oracle database. What I mean by that is, Oracle allows creating unique indexes on columns which are even defined as NULL.

So if my table has two unique indexes, I want to retrieve the particular unique index which is having all the columns having the NOT NULL constraints.

I did come up with this query:

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

The above query is giving me all the unique indexes with the associated columns, but I am not sure, how should I join the above query with ALL_TAB_COLS which has the NULLABILITY data for all the columns of a table.

I tried joining this table with indexes and tried subquery as well, but not getting appropriate results.

Hence, would request you to please comment on same.

Upvotes: 0

Views: 265

Answers (1)

Jon Heller
Jon Heller

Reputation: 36832

Analytic functions and inline views can help.

The analytic functions let you return detailed data but also create a summary on that data, based on separate windows. The detailed results include index owner, index name, and column name, but the counts are only per index owner and index name.

The first inline view joins the three tables, returns the detailed information, and has analytic functions to generate the count of all columns and the count of all nullable columns. The second inline view only selects rows where those two counts are equal.

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

Analytic functions and inline views are tricky but they're very powerful once you learn how to use them.

Upvotes: 2

Related Questions