Abfrage, Partitionen ohne Statistiken

Um zu bestimmen, welche Partitionen keine Statistiken haben:

select ATP.TABLE_OWNER
     , ATP.TABLE_NAME
     , ATP.TABLESPACE_NAME
     , ATP.COMPRESSION
     , ATP.NUM_ROWS
     , ATP.LAST_ANALYZED
  from  ALL_TAB_PARTITIONS ATP
 where ATP.PARTITION_NAME = 'P_5'
 order by ATP.LAST_ANALYZED desc nulls first
 ;


-> fragt alle Tabellen mit gleichem Partitionsnamen ab.

select ATP.INDEX_OWNER
     , ATP.INDEX_NAME
     , ATP.TABLESPACE_NAME
     , ATP.COMPRESSION
     , ATP.NUM_ROWS
     , ATP.LAST_ANALYZED
  from ALL_IND_PARTITIONS ATP
where ATP.PARTITION_NAME = 'P_5'
order by ATP.LAST_ANALYZED desc nulls first
;

-> fragt alle Indizes mit gleichem Partitionsnamen ab.


Prüfen, ob die Tabellenstatistik älter als die Partitionsstatistik ist:
select ATP.TABLE_OWNER
     , ATP.TABLE_NAME
     , ATP.TABLESPACE_NAME
     , ATP.COMPRESSION
     , ATP.NUM_ROWS
     , ATP.LAST_ANALYZED
     , AT.LAST_ANALYZED
  from  ALL_TAB_PARTITIONS ATP
   left outer join all_tables AT
     on (AT.OWNER = ATP.TABLE_OWNER and AT.TABLE_NAME = ATP.TABLE_NAME)  
 where ATP.PARTITION_NAME = 'P_5'
   and ATP.LAST_ANALYZED > AT.LAST_ANALYZED
 order by ATP.LAST_ANALYZED desc nulls first
 ;