Orphaned Indexes: Oracle vs. YugabyteDB

If you’re migrating from Oracle to YugabyteDB, you may have run across the concept of orphaned indexes. In Oracle, there are views and flags that let you check if indexes have become detached from their parent tables. It’s natural to wonder: should I worry about the same issue in YugabyteDB?

This tip was written to address that concern. Spoiler: in YugabyteDB, the chance of seeing orphaned indexes is basically zero. But let’s walk through both sides.

What Is an Orphaned Index?

An orphaned index is one that:

● Still exists in the catalog
● But no longer has a valid parent table

These are usually a sign of failed DDL or catalog corruption.

Orphaned Indexes in Oracle

In Oracle, orphaned or unusable indexes can crop up in a few cases:

● After dropping partitions with UPDATE INDEXES
● Following a failed DROP TABLE or partial DDL rollback
● In rare catalog corruption scenarios

Oracle makes this visible through USER_INDEXES, ALL_INDEXES, and DBA_INDEXES. Some of these expose an ORPHANED_ENTRIES flag.

Example in Oracle:

				
					SELECT index_name, table_name, orphaned_entries
FROM user_indexes
WHERE orphaned_entries = 'YES';

If rows appear, you’ve got some cleanup to do.

Orphaned Indexes in YugabyteDB

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.

In contrast, YugabyteDB (via its PostgreSQL-compatible YSQL layer) handles index dependencies automatically:

● Dropping a table automatically drops all its indexes.
● Dropping a partition (child table) automatically drops its local indexes.
● YugabyteDB does not yet support global indexes on partitioned tables — so there’s nothing to “go stale.”

👉 Translation: under normal operation, there’s essentially a 0% chance you’ll ever encounter orphaned indexes in YugabyteDB.

If you do see one, it’s likely the result of manual catalog tampering or a serious bug… Make sure to report this to YugabyteDB Support!

Still Want to Check? Here’s How.

Even though you shouldn’t need to, you can query system catalogs to double-check:

				
					WITH idx AS (
  SELECT
    c.relname AS index_name,
    t.relname AS table_name,
    n.nspname AS index_schema
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  JOIN pg_index i     ON i.indexrelid = c.oid
  LEFT JOIN pg_class t ON t.oid = i.indrelid
  WHERE c.relkind IN ('i','I')  -- index, partitioned index
)
SELECT *
FROM idx
WHERE table_name IS NULL
ORDER BY index_schema, index_name;

Expected output in YugabyteDB: zero rows.

Bonus: Oracle-style `USER_INDEXES` View in YB

For migration scripts that expect an Oracle view, you can define:

				
					CREATE OR REPLACE VIEW user_indexes AS
SELECT
  c.relname  AS index_name,
  t.relname  AS table_name,
  CASE WHEN i.indisunique THEN 'UNIQUE' ELSE 'NONUNIQUE' END AS uniqueness,
  CASE WHEN i.indisvalid THEN 'VALID' ELSE 'INVALID' END     AS status,
  CASE WHEN t.oid IS NULL THEN 'YES' ELSE 'NO' END           AS orphaned_entries
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_class t ON t.oid = i.indrelid
WHERE c.relkind IN ('i','I')
  AND n.nspname = current_schema()
ORDER BY table_name, index_name;

Now you can run:

				
					SELECT *
FROM user_indexes
WHERE orphaned_entries = 'YES';

Again, you should see no rows in a healthy YugabyteDB.

Summary

● If you’re migrating from Oracle, you may worry about orphaned indexes.
● In Oracle, they can exist in edge cases, hence the ORPHANED_ENTRIES flag in USER_INDEXES.
● In YugabyteDB, the catalog is managed differently: orphaned indexes basically cannot occur.
● We created this tip to reassure Oracle-to-YB migrations and to show how you could check if you want.

Have Fun!