显示某个表的索引使用情况

--description:start with Oracle9i,can use this script to determine the index io.
--syntax:@segment_io(table_name)(schema)

SELECT OBJECT_NAME SEGMENT_NAME,
       max(DECODE(STATISTIC_NAME, 'segment scans', value, null)) full_scan,
       max(DECODE(STATISTIC_NAME, 'logical reads', value, null)) logical_reads,
       max(DECODE(STATISTIC_NAME, 'physical reads', value, null)) physical_reads,
       max(DECODE(STATISTIC_NAME, 'physical writes', value, null)) physical_writes,
       max(DECODE(STATISTIC_NAME, 'physical reads direct', value, null)) physical_reads_direct,
       max(DECODE(STATISTIC_NAME, 'physical writes direct', value, null)) physical_writes_direct
  FROM V$SEGMENT_STATISTICS
 WHERE OBJECT_NAME IN
       (SELECT INDEX_NAME
          FROM ALL_INDEXES
         WHERE table_name = UPPER('&table_name'))
   AND OWNER = UPPER('&OWNER')
   AND STATISTIC_NAME IN
       ('logical reads', 'physical reads', 'physical writes',
        'physical reads direct', 'physical writes direct', 'segment scans')
 group by OBJECT_NAME;