VIEW: SYSTEM.MVIEW_RECOMMENDATIONS

Source

View Text - Preformatted

select
  t1.runid# as runid,
  t1.from_clause as all_tables,
  fact_tables,
  grouping_levels,
  query_text,
  rank# as recommendation_number,
  action_type as recommended_action,
  summary_owner as mview_owner,
  summary_name as mview_name,
  storage_in_bytes,
  pct_performance_gain,
  benefit_to_cost_ratio
from SYSTEM.MVIEW$_ADV_OUTPUT t1, SYSTEM.MVIEW$_ADV_LOG t2, ALL_USERS u
where
  t1.runid# = t2.runid# and
  u.username = t2.uname and
  u.user_id = userenv('SCHEMAID') and
  t1.output_type = 0
order by t1.rank#

View Text - HTML Formatted

SELECT T1.RUNID# AS RUNID
, T1.FROM_CLAUSE AS ALL_TABLES
, FACT_TABLES
, GROUPING_LEVELS
, QUERY_TEXT
, RANK# AS RECOMMENDATION_NUMBER
, ACTION_TYPE AS RECOMMENDED_ACTION
, SUMMARY_OWNER AS MVIEW_OWNER
, SUMMARY_NAME AS MVIEW_NAME
, STORAGE_IN_BYTES
, PCT_PERFORMANCE_GAIN
, BENEFIT_TO_COST_RATIO FROM SYSTEM.MVIEW$_ADV_OUTPUT T1
, SYSTEM.MVIEW$_ADV_LOG T2
, ALL_USERS U WHERE T1.RUNID# = T2.RUNID# AND U.USERNAME = T2.UNAME AND U.USER_ID = USERENV('SCHEMAID') AND T1.OUTPUT_TYPE = 0 ORDER BY T1.RANK#