vidi jeste nas kupio oracle ali ovo je i dalje tema o MySQL-u ...
MySQL ne podrzava materialized view! Taj kod koji si okacio radi na ORACLE-u ..
Inace materialized views (tamo gde je podrzan) se ponasa kao staticna tabela i koristan je kada se cesto vrsi kompleksan upit nad podacima koji se retko menjaju. Sa MySQL-om mozes da izvedes materialized view uz pomoc trigera kao sto je dato primerom ovde:
http://www.elitesecurity.org/p2201384
tj nesto ovako:
Code:
create table t1 (id int auto_increment primary key, a int, b int) engine=innodb;
create table t2 (a int, x int) engine=innodb;
create table tmp1 (id int primary key, a int, b int, sx int) engine=innodb;
-- select t1.id, t2.a, t1.b, sum(t2.x) from t1,t2 where t1.id=t2.id group by t2.a;
DELIMITER $$
DROP TRIGGER IF EXISTS `tr1`$$
CREATE DEFINER = 'root'@'localhost' TRIGGER `tr1`
AFTER INSERT ON `t1`
FOR EACH ROW BEGIN
insert into tmp1 (id,a,b,sx) values(NEW.id,NEW.a,NEW.b, (select sum(x) from t2 where a=NEW.a) );
END;
$$
DROP TRIGGER IF EXISTS `tr2`$$
CREATE DEFINER = 'root'@'localhost' TRIGGER `tr2`
AFTER UPDATE ON `t1`
FOR EACH ROW BEGIN
-- ovde bi trebao jedan if new.id <> old.id al nema veze
update tmp1 set id = NEW.id WHERE id = OLD.id;
-- ovde bi trebao jedan if new.a <> old.a al nema veze
update tmp1 set sx = (select sum(x) from t2 where a=NEW.a) where id=NEW.id;
END;
$$
DROP TRIGGER IF EXISTS `tr3`$$
CREATE DEFINER = 'root'@'localhost' TRIGGER `tr3`
AFTER INSERT ON `t2`
FOR EACH ROW BEGIN
update tmp1 set sx = (select sum(t2.x) from t2 where t2.a=tmp1.a) WHERE tmp1.a = NEW.a;
END;
$$
DROP TRIGGER IF EXISTS `tr4`$$
CREATE DEFINER = 'root'@'localhost' TRIGGER `tr4`
AFTER UPDATE ON `t2`
FOR EACH ROW BEGIN
update tmp1 set sx = (select sum(t2.x) from t2 where t2.a=tmp1.a) where tmp1.a in ( NEW.a, ILD.a);
END;
$$
DELIMITER ;