Oracle: global vs ordinary indexes

Create a demonstration table

drop table tq84_part_ix_test;

create table tq84_part_ix_test (
   id     number,
   meta   varchar2(10),
   val_1  number,
   val_2  number,
   val_3  number
)
partition    by range (id  ) interval (10000)
subpartition by list  (meta)
subpartition template (
   subpartition sp_foo values ('foo'),
   subpartition sp_bar values ('bar'),
   subpartition sp_baz values ('baz')
)(
   partition    p_lt_10000 values less than (10000)
);

create index tq84_part_ordinary_ix  on tq84_part_ix_test (val_1);

create index tq84_part_local_ix on tq84_part_ix_test (val_2)
   local (
      partition        p_lt_10000 (
         subpartition  sp_foo,
         subpartition  sp_bar,
         subpartition  sp_baz
      )
   );

Querying the data dictionary

select
   index_name,
   partitioned,
   status
from
   user_indexes
where
   index_name in ('TQ84_PART_LOCAL_IX', 'TQ84_PART_GLOBAL_IX');

select
   index_name,
   partition_name,
   global_stats,
   composite,
   status
from
   user_ind_partitions
where
   index_name in ('TQ84_PART_LOCAL_IX', 'TQ84_PART_GLOBAL_IX');

select
   index_name,
   partition_name,
   subpartition_name,
   global_stats,
   status
from
   user_ind_subpartitions
where
   index_name in ('TQ84_PART_LOCAL_IX', 'TQ84_PART_GLOBAL_IX');

Inserting some data

insert into tq84_part_ix_test values (10001, 'foo', 11, 13, 17);
insert into tq84_part_ix_test values (10002, 'bar', 23, 29, 31);
insert into tq84_part_ix_test values (20003, 'baz', 37, 39, 41);

commit;

Gathering statistics

exec dbms_stats.gather_table_stats(user, 'tq84_part_ix_test');

select
   object_type       ,
   index_name        ,
   partition_name    , subpartition_name
   partition_position, subpartition_position,
   num_rows,
   global_stats,
   last_analyzed,
   stale_stats
from
   user_ind_statistics
where
   table_name = 'TQ84_PART_IX_TEST';