text: -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
  -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
  
  SET LINES 1000 PAGES 5000 TRIMSPOOL ON TRIMOUT ON TAB OFF
  
  COL owner FOR A15
  COL object_name FOR A30
  
  WITH trends AS (
      SELECT
          o.owner
        , o.object_name
        , o.subobject_name
        , o.object_type
        , MIN(ih.savtime) first_sample
        , MAX(ih.savtime) last_sample
        , REGR_SLOPE(ih.rowcnt / NULLIF(ih.leafcnt,0), (SYSDATE-CAST(ih.savtime AS DATE)) ) regr1
        , REGR_SLOPE(ih.rowcnt, ih.leafcnt) regr2
        , ROUND(MIN(ih.rowcnt / NULLIF(ih.leafcnt,0))) min_avg_rows_per_block
        , ROUND(MAX(ih.rowcnt / NULLIF(ih.leafcnt,0))) max_avg_rows_per_block
        , MIN(ih.rowcnt)                               min_rowcnt
        , MAX(ih.rowcnt)                               max_rowcnt
        , MIN(ih.leafcnt)                              min_leafcnt
        , MAX(ih.leafcnt)                              max_leafcnt
        , MIN(ih.lblkkey)                              min_lblkkey
        , MAX(ih.lblkkey)                              max_lblkkey
        , MIN(ih.dblkkey)                              min_dblkkey
        , MAX(ih.dblkkey)                              max_dblkkey
        , MIN(ih.blevel)+1                             min_height              
        , MAX(ih.blevel)+1                             max_height
      FROM
          dba_objects o
        , sys.wri$_optstat_ind_history ih
      WHERE
          o.object_id       = ih.obj#
      AND o.object_type     LIKE 'INDEX%'
      AND (
          UPPER(o.object_name) LIKE
                      UPPER(CASE
                          WHEN INSTR('&1','.') > 0 THEN
                              SUBSTR('&1',INSTR('&1','.')+1)
                          ELSE
                              '&1'
                          END
                           )
      AND UPPER(o.owner) LIKE
              CASE WHEN INSTR('&1','.') > 0 THEN
                  UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
              ELSE
                  user
              END
      )
      GROUP BY
          o.owner
        , o.object_name
        , o.subobject_name
        , o.object_type
      ORDER BY
      --    ih.savtime
          regr1 DESC NULLS LAST
  )
  SELECT * FROM (
      SELECT
          t.owner
        , t.object_name
        , t.subobject_name partition_name
        , t.object_type
        , ROUND(s.bytes / 1048576) current_mb
        , CAST(first_sample AS DATE) first_sample
        , CAST(last_sample  AS DATE) last_sample
        , min_avg_rows_per_block
        , max_avg_rows_per_block
        , min_leafcnt
        , max_leafcnt
        , min_lblkkey
        , max_lblkkey
        , min_dblkkey
        , max_dblkkey
        , t.regr1
        , t.regr2
        --, ROUND(SUM(s.bytes) / 1048576) mb_sum
        --, COUNT(*)
      FROM
          trends t
        , dba_segments s
      WHERE
          t.owner           = s.owner
      AND t.object_name     = s.segment_name
      AND t.object_type     = s.segment_type
      AND (t.subobject_name = s.partition_name OR (t.subobject_name IS NULL AND s.partition_name IS NULL))
      --GROUP BY
      --    t.owner
      --  , t.object_name
      --  , t.object_type
      --  , t.subobject_name
      ORDER BY regr1 DESC NULLS LAST
  )
  WHERE
      ROWNUM<=20
  / 
  