Scribbles for my memory

Oracle 19c has no filtered index like SQL Server's WHERE clause or PostgreSQL's partial index. You can get the same effect from one B-tree rule: a row gets no index entry only when every indexed column is NULL.

A common misreading of that rule is "if the first column is NULL, the row isn't indexed." That's wrong. I checked it with a unique index on (A, B), inserting each pair twice:

CREATE TABLE t (A NUMBER, B NUMBER);
CREATE UNIQUE INDEX t_uq ON t (A, B);

INSERT INTO t VALUES (NULL, NULL);  -- twice: both succeed
INSERT INTO t VALUES (NULL, 5);     -- twice: ORA-00001 on the second
INSERT INTO t VALUES (5, NULL);     -- twice: ORA-00001 on the second

After gathering stats, the table had 4 rows and the index had 2 entries. The two missing entries were the all-NULL rows. A leading NULL gets indexed and takes part in the uniqueness check like any other value.

To index only the rows that matter, wrap every column in the same condition with a CASE expression. Rows that fail the condition then have an all-NULL key and get no entry:

-- one open account per (parent, type); closed rows are left out of the index
CREATE UNIQUE INDEX acct_one_open_uq ON acct (
    CASE WHEN status <> 'CLOSED' THEN parent_id END,
    CASE WHEN status <> 'CLOSED' THEN type_id END
);

The index stays as small as the set of open rows, and you can keep as many closed duplicates as you like. If you wrap only the first column, closed rows still get a (NULL, type_id) entry. The index then keeps every row, and two closed rows of the same type collide.

The catch: the optimizer only uses this index when a query repeats the exact CASE expression. A plain WHERE parent_id = :p AND type_id = :t won't use it. Treat it as a constraint, and keep a normal index if you also need lookups on those columns.