Partial or full index in Oracle?

Question

I'm working on a library that retrieves database metadata for multiple databases, and I wanted to find if an index is partial or full in Oracle.

According to Oracle docs, an index is partial if all its columns can be null. In that case not all rows will be included in the index. I looked at the table all_indexes but couldn't find details if the index is full or partial.

For example:

create table t (
  a int not null,
  b int,
  c int not null
);

Is there any way of determining if the following indexes are partial or full?

create index ix1 on t (a); -- full index
create index ix2 on t (b); -- partial index
create index ix3 on t (a + c, a * c); -- full index
create index ix4 on t (a * b); -- partial index

-- Now, an unlisted table constraint (unique + partial index)
create unique index ix5 on t (case when b = 1 then a end);

We can see the unlisted constraint at work when we try to insert:

insert into t (a, b, c) values (123, 1, 5); -- succeeds
insert into t (a, b, c) values (123, 1, 6); -- fails as expected!

Determining the index is partial is crucial to find unlisted table constraints, such as ix5 above.

user_213136 · Answer

The information you want can be determined by querying the ALL_INDEXES, ALL_IND_COLUMNS, and ALL_TAB_COLUMNS views:

WITH cteIndex_column_info AS
(
    SELECT 
        ic.INDEX_OWNER,
        ic.INDEX_NAME,
        1 AS COLUMN_COUNT,
        CASE
           WHEN tc.NULLABLE = 'Y' THEN 1
           ELSE 0
        END AS NULLABLE_COLUMN_COUNT
    FROM 
        ALL_INDEXES i
    INNER JOIN 
        ALL_IND_COLUMNS ic ON ic.INDEX_OWNER = i.OWNER 
                           AND ic.INDEX_NAME  = i.INDEX_NAME
    INNER JOIN 
        ALL_TAB_COLUMNS tc ON tc.OWNER = i.TABLE_OWNER 
                           AND tc.TABLE_NAME  = i.TABLE_NAME 
                           AND tc.COLUMN_NAME = ic.COLUMN_NAME
)
SELECT 
    INDEX_OWNER,
    INDEX_NAME,
    CASE
       WHEN SUM(COLUMN_COUNT) = SUM(NULLABLE_COLUMN_COUNT) THEN 'PARTIAL'
       ELSE 'FULL'
    END AS INDEX_TYPE
FROM 
    cteIndex_column_info
GROUP BY 
    INDEX_OWNER, INDEX_NAME
ORDER BY 
    INDEX_OWNER, INDEX_NAME

db<>fiddle here

Partial or full index in Oracle?

1 Answers