Add indexes to tables in a table hierarchy

When you define an index on a supertable in a hierarchy, any subtables that you define under that supertable also inherit the index. Suppose you have a table hierarchy that contains the tables tab_a, tab_b, and tab_c where tab_a is a supertable to tab_b, and tab_b is a supertable to tab_c. If you create an index on a column of tab_b, then that index will exist on that column in both tab_b and tab_c. If you create an index on a column of tab_a, then that index will span tab_a, tab_b, and tab_c.

Indexes, unique constraints, and primary keys are all closely related. When you specify a unique constraint or primary key, the database server automatically creates a unique index on the column. Consequently, a primary key or unique constraint that you define on a supertable applies to all the subtables. For example, suppose there are two tables (a supertable and subtable), both of which contain a column emp_id. If the supertable specifies that emp_id has a unique constraint, the subtable must contain emp_id values that are unique across both the subtable and the supertable.