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.

There is no indication of that in the dictionary views describing the indexes. You'll need to fetch the TAB_COLS data and see if columns used in the index are nullable or not. Out of curiosity, why do you want that? Even if a column is not declared NOT NULL, if may never have nulls in there. — gsalem, Dec 26 '20 at 14:09
@gsalem I want to determine if the index is partial to find constraints. If a partial index is also UNIQUE, then it becomes a **constraint** on the table. I wanted to list all constraints for each table. — The Impaler, Dec 26 '20 at 14:32
When someone talks about partial index, then for me it is this one: https://oracle-base.com/articles/12c/partial-indexes-for-partitioned-tables-12cr1 — Wernfried Domscheit, Dec 26 '20 at 15:18
A unique index is almost always a constraint on a table, no matter whether it's partial or not. The only case I see that a unique index is not a constraint on a table is when all its expressions are always null (as in `create unique index ix6 on t (case when 1 = 2 then a end);`). These can only be mistakes. Do you want to find these unlikely cases to tell them from the real constraints? — Thorsten Kettner, Dec 27 '20 at 21:39

score 1 · Answer 1 · edited Dec 27 '20 at 19:13

1

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

edited Dec 27 '20 at 19:13

marc_s

732,580
175
1,330
1,459

answered Dec 26 '20 at 15:14

Bob Jarvis - Слава Україні

48,992
9
77
110

Thank you. It seems this solution works well for indexes that do not include expressions. I just added a couple of cases to the fiddle and unfortunately they don't show up: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fc2fa4ff9307d0b151d6371264e63084 – The Impaler Dec 26 '20 at 15:23
Try changing to the DBA_ views (DBA_INDEXES, DBA_IND_COLUMNS, DBA_TAB_COLS). Computed indexes work by defining a hidden column on the table to hold the computed info, and those hidden columns are apparently not included in the ALL_ views. dbfiddle.uk doesn't provide access to the DBA_ views so I can't test it there - nonetheless, that's my best recommendation. – Bob Jarvis - Слава Україні Dec 26 '20 at 15:33
change all_tab_columns to al_tab_cols and it will show you your index created on an expression, As expected you index on expression will show as partial. I read your answer to my comment above, but don't understand what 'full' or 'partial' indexes have to do with constraints. user/dba/all constraints give you the constraints – gsalem Dec 26 '20 at 17:39
@gsalem Thank you. As you said I replaced the table with `ALL_TAB_COLS` and I can see the expressions; unfortunately the reported `NULLABLE` column for the expression is incorrect. As for why indexes have to do with constraints, see running example at https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=f0304f39fd43d167b1cd62fbde56d9f9 Why does the second insert fail? – The Impaler Dec 27 '20 at 21:14
It looks to me like your second insert fails because of index `ix5`. Try change it to be non-unique to test. – Bob Jarvis - Слава Україні Dec 27 '20 at 21:20
As bob said’ the error comes from having ix5 unique. Also, not every expression using a not null column is also not null: if A is declared ‘not null’ but B is nullable A+B is nullable, and a case expression involving both may be not nullable or nullable. Oracle will not try to find out. – gsalem Dec 28 '20 at 12:04

Partial or full index in Oracle?

1 Answers1