Sql moje - lista 6

CREATE TABLESPACE moja_przestrzen DATAFILE 'dane_moja_przestrzen.dbf' SIZE 10M  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128k;

ms_dane

ALTER TABLE RAJD MOVE TABLESPACE ms_dane;
ALTER TABLE TRASA MOVE TABLESPACE ms_dane;

ALTER TABLE trasa
ADD CONSTRAINT trasa_id_rajdu
FOREIGN KEY (id_rajdu)
REFERENCES rajd(id_rajdu);

CREATE TABLE RAJD_KOPIA AS SELECT * FROM RAJD;
CREATE TABLE TRASA_KOPIA AS SELECT * FROM TRASA;

ALTER TABLE RAJD_KOPIA MOVE TABLESPACE ms_dane;
ALTER TABLE TRASA_KOPIA MOVE TABLESPACE ms_dane;

ALTER TABLE RAJD_KOPIA ADD PRIMARY KEY(id_rajdu);
ALTER TABLE TRASA_KOPIA ADD PRIMARY KEY(id_trasy);

BEGIN
FOR i IN 1..100000 LOOP
  INSERT INTO RAJD(id_rajdu,kraj) VALUES(i,'polska');
END LOOP;
END;

BEGIN
FOR i IN 1..100000 LOOP
  INSERT INTO TRASA(id_trasy,dlugosc) VALUES(i,'Krotka');
END LOOP;
END;


BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TRASA';
FOR i IN 1..100000 LOOP
  INSERT INTO TRASA(id_trasy,id_rajdu) VALUES(i, TRUNC(DBMS_RANDOM.VALUE(1,99999)));
END LOOP;
END;

DECLARE
czpoczatek NUMBER;
czkoniec NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE RAJD_KOPIA';
  czpoczatek := DBMS_UTILITY.get_time;
  INSERT INTO RAJD_KOPIA (SELECT * FROM RAJD);
  czkoniec := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.put_line('Czas trwania: '||TO_CHAR(czkoniec-czpoczatek));
END;  -- 76

DECLARE
czpoczatek NUMBER;
czkoniec NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE TRASA_KOPIA';
  czpoczatek := DBMS_UTILITY.get_time;
  INSERT INTO TRASA_KOPIA (SELECT * FROM TRASA);
  czkoniec := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.put_line('Czas trwania: '||TO_CHAR(czkoniec-czpoczatek));
END;  -- 71

SELECT TABLE_NAME, LOGGING FROM USER_TABLES WHERE TABLE_NAME ='RAJD_KOPIA';

ALTER TABLE RAJD_KOPIA NOLOGGING; -- wylaczamy dziennik powtorzen


TRUNCATE TABLE RAJD_KOPIA;
DECLARE
czpoczatek NUMBER;
czkoniec NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE RAJD_KOPIA';
  czpoczatek := DBMS_UTILITY.get_time;
  INSERT /*+APPEND*/ INTO RAJD_KOPIA (SELECT * FROM RAJD);
  czkoniec := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.put_line('Czas trwania: '||TO_CHAR(czkoniec-czpoczatek));
END;  -- 76


DECLARE
czpoczatek NUMBER;
czkoniec NUMBER;
x NUMBER;
BEGIN
czpoczatek := DBMS_UTILITY.get_time;
FOR I IN (SELECT * FROM RAJD WHERE id_rajdu<1000) LOOP
  SELECT COUNT(1) INTO x FROM TRASA WHERE id_rajdu=i.id_rajdu;
  END LOOP;
czkoniec := DBMS_UTILITY.get_time;
DBMS_OUTPUT.put_line('Czas trwania: '||TO_CHAR(czkoniec-czpoczatek));
END;

DECLARE
czpoczatek NUMBER;
czkoniec NUMBER;
x NUMBER;
BEGIN
czpoczatek := DBMS_UTILITY.get_time;
FOR I IN (SELECT * FROM RAJD_KOPIA WHERE id_rajdu<1000) LOOP
  SELECT COUNT(1) INTO x FROM TRASA_KOPIA WHERE id_rajdu=i.id_rajdu;
  END LOOP;
czkoniec := DBMS_UTILITY.get_time;
DBMS_OUTPUT.put_line('Czas trwania: '||TO_CHAR(czkoniec-czpoczatek));
END;

CREATE OR REPLACE TRIGGER t_update_id_rajdu
AFTER UPDATE OF id_rajdu
ON RAJD_KOPIA
FOR EACH ROW
BEGIN
  UPDATE TRASA_KOPIA
  SET id_rajdu=:NEW.id_rajdu
  WHERE id_rajdu =:old.id_rajdu;
END;

ALTER TABLE RAJD_KOPIA ADD CONSTRAINT CASCADE UPDATE;