SBD_lista_5

-------------------------------------------------------------------------------------------------------------------------------------------
-- lista 5 --------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------

-- zad1 -----------------------------------------------------------------------------------------------------------------------
-- stworzenie przestrzeni tabel
CREATE TABLESPACE tablespace1
DATAFILE 'Dane_11.dbf' SIZE 200M
SEGMENT SPACE MANAGEMENT AUTO
extent management LOCAL uniform SIZE 128k;

-- wypisanie wszystkich przestrzeni tabel
SELECT * FROM user_tablespaces;

-- usuniecie przestrzeni tabel
DROP TABLESPACE tablespace1 INCLUDING CONTENTS AND Datafiles cascade constraints ;
DROP TABLE t1_user_copy;
DROP TABLE t2_ticket_copy;



-- zad2 -----------------------------------------------------------------------------------------------------------------------
CREATE TABLE t1_user
(
  id   INT PRIMARY KEY,
  name VARCHAR(30)
) TABLESPACE tablespace1;

CREATE TABLE t2_ticket
(
  id      INT PRIMARY KEY,
  id_user INT REFERENCES t1_user(id) ON DELETE cascade,
  name    VARCHAR(30)
) TABLESPACE tablespace1;

DROP TABLE t2_ticket_copy;
DROP TABLE t1_user_copy;
DROP TABLE t1_user_copy_copy;


DROP TABLE t1_user;
SELECT COUNT(*) FROM t2_ticket;
SELECT COUNT(*) FROM t1_user;





-- zad3 -----------------------------------------------------------------------------------------------------------------------
CREATE TABLE t1_user_copy AS SELECT * FROM t1_user;
CREATE TABLE t2_ticket_copy AS SELECT * FROM t2_ticket;

ALTER TABLE t1_user_copy ADD CONSTRAINT t1_user_copy_pk PRIMARY KEY (id);
ALTER TABLE t2_ticket_copy ADD CONSTRAINT t2_ticket_copy_pk PRIMARY KEY (id);






-- zad4 -----------------------------------------------------------------------------------------------------------------------
BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO t1_user(id, name) VALUES (i, 'User nr ' || i);
  END loop;
END;
/


BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO t2_ticket(id, id_user, name) VALUES(i, TRUNC(sys.DBMS_RANDOM.VALUE(1, 1000000)), 'Ticket nr ' || i);
  END loop;
END;
/




-- zad5,6,7 -----------------------------------------------------------------------------------------------------------------------
SET SERVEROUTPUT ON

-- czas kopiowania z LOGGING
DECLARE
  cz_poczatek NUMBER;
  cz_koniec   NUMBER;
BEGIN
  cz_poczatek := DBMS_UTILITY.GET_TIME;
  INSERT INTO t1_user_copy (SELECT * FROM t1_user);
  cz_koniec := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('czas trwania z logging:' || TO_CHAR(cz_koniec-cz_poczatek));
END;
/


TRUNCATE TABLE t1_user_copy;
ALTER TABLE t1_user_copy nologging;


-- zwiekszenie wydajnosci: atrybut logging ustawiony na NO, przy insercie sziezka bezwzgledna: /*+ APPEND */
DECLARE
  cz_poczatek NUMBER;
  cz_koniec   NUMBER;
BEGIN
  cz_poczatek := DBMS_UTILITY.GET_TIME;
  INSERT /*+ APPEND */ INTO t1_user_copy (SELECT * FROM t1_user);
  cz_koniec := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('czas trwania z NOlogging:' || TO_CHAR(cz_koniec-cz_poczatek));
END;
/




-- wypelnienie tabeli ticket
DECLARE
  cz_poczatek NUMBER;
  cz_koniec   NUMBER;
BEGIN
  cz_poczatek := DBMS_UTILITY.GET_TIME;
  INSERT INTO t2_ticket_copy (SELECT * FROM t2_ticket);
  cz_koniec := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('czas trwania z logging:' || TO_CHAR(cz_koniec-cz_poczatek));
END;
/





SELECT COUNT(1) FROM t1_user;
SELECT COUNT(1) FROM t1_user_copy;
SELECT COUNT(1) FROM t2_ticket;
SELECT COUNT(1) FROM t2_ticket_copy;


-- zad8 -----------------------------------------------------------------------------------------------------------------------
DECLARE
  cz_poczatek NUMBER;
  cz_koniec   NUMBER;
  ilosc       NUMBER;
BEGIN
  cz_poczatek := DBMS_UTILITY.GET_TIME;
  SELECT COUNT(*) INTO ilosc
  FROM t1_user_copy u, t2_ticket_copy t
  WHERE t.id_user = u.id AND u.name LIKE '%5%';
  cz_koniec := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('czas trwania na kopiach:' || TO_CHAR(cz_koniec-cz_poczatek) || ' ilosc: ' || TO_CHAR(ilosc));

  cz_poczatek := DBMS_UTILITY.GET_TIME;
  SELECT COUNT(*) INTO ilosc
  FROM t1_user u, t2_ticket t
  WHERE t.id_user = u.id AND u.name LIKE '%5%';
  cz_koniec := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('czas trwania na oryginalach:' || TO_CHAR(cz_koniec-cz_poczatek) || ' ilosc: ' || TO_CHAR(ilosc));
END;
/













-- zad9 -----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER triggerZad9
BEFORE UPDATE OF id ON t1_user_copy
FOR EACH ROW
  BEGIN
  --modyfikacja dodatkowej tabeli t_copy
  UPDATE t2_ticket_copy
  SET id_user = :NEW.id
  WHERE id_user = :OLD.id;
  END;
/


UPDATE t1_user_copy
SET id = 2000000
WHERE id=47613;




DROP TRIGGER triggerZad9;


-- zad10 --------------------------------------------------------------------------------------------------------------------

-- zeby mozna bylo updatowac t1_user, musimy zrobic wyzwalacz
CREATE OR REPLACE TRIGGER triggerZad92
BEFORE UPDATE OF id ON t1_user
FOR EACH ROW
  BEGIN
  --modyfikacja dodatkowej tabeli t2_ticket
  UPDATE t2_ticket
  SET id_user = :NEW.id
  WHERE id_user = :OLD.id;
  END;
/

DROP TRIGGER triggerZad92;


DECLARE
  pocz NUMBER;
  kon NUMBER;

BEGIN
  pocz := DBMS_UTILITY.GET_Time;
  UPDATE t1_user
  SET id = 6000000
  WHERE id=550000;
  kon := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('czas trwania update na oryginale:' || TO_CHAR(kon-pocz));

  pocz := DBMS_UTILITY.GET_Time;
  UPDATE t1_user_copy
  SET id = 6000000
  WHERE id=550000;
  kon := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('czas trwania update na kopii:' || TO_CHAR(kon-pocz));
END;
/
RAW Paste DATA