spool "star_transform_perf_test.log"

SET LINES 400;
SET FEEDBACK OFF;
SET PAGES 500;
SET AUTOT ON;
SET TIMING ON;

ALTER SYSTEM SET star_transformation_enabled=true;
alter system flush BUFFER_CACHE;
alter system flush SHARED_POOL;

PROMPT 'Cost with Star Tranformation Enabled but not explicit hinted...'

SELECT pd.player_name, td.team_name, gd.game_season, sum(bf.at_bats) "no at bats"
  FROM sh.batting_fact bf, sh.player_dim pd, sh.team_dim td, sh.game_dim gd
 WHERE bf.player_id = pd.player_id
   AND bf.team_id = td.team_id
   AND bf.game_id = gd.game_id
   AND pd.player_name = 'James Colestock'
   AND td.team_name = 'CHICAGO CUBS'
   AND gd.game_no between 1 and 162
   AND td.team_division='MIDWEST'
   AND gd.game_season between '1950' and '1951'
  GROUP BY pd.player_name, td.team_name, rollup(gd.game_season)
  ORDER BY 3;  

ALTER SYSTEM SET star_transformation_enabled=false;
alter system flush BUFFER_CACHE;
alter system flush SHARED_POOL;

PROMPT 'Cost with Star Tranformation disabled...'

SELECT pd.player_name, td.team_name, gd.game_season, sum(bf.at_bats) "no at bats"
  FROM sh.batting_fact bf, sh.player_dim pd, sh.team_dim td, sh.game_dim gd
 WHERE bf.player_id = pd.player_id
   AND bf.team_id = td.team_id
   AND bf.game_id = gd.game_id
   AND pd.player_name = 'James Colestock'
   AND td.team_name = 'CHICAGO CUBS'
   AND gd.game_no between 1 and 162
   AND td.team_division='MIDWEST'
   AND gd.game_season between '1950' and '1951'
  GROUP BY pd.player_name, td.team_name, rollup(gd.game_season)
  ORDER BY 3;  

spool off;