SBD_lista_6

-- zad1,2 -----------------------------------------------------------------------------------------------------------------------
-- stworzenie przestrzeni tabel
CREATE TABLESPACE tablespace2
DATAFILE 'Dane_22dbf' SIZE 500M
extent management LOCAL uniform SIZE 128k;

-- dodanie miejsca, niezbedne zeby moglo dodac 1000000 milion rekordow
ALTER TABLESPACE tablespace2 ADD DATAFILE
   'Dane_22.dbf' SIZE 200M
   AUTOEXTEND ON MAXSIZE UNLIMITED;

-- wypisanie wszystkich przestrzeni tabel
SELECT * FROM user_tablespaces;

-- usuniecie przestrzeni tabel
DROP TABLESPACE tablespace2 INCLUDING CONTENTS AND Datafiles;



CREATE TABLE book
(
  id_book   INT PRIMARY KEY,
  name      VARCHAR(30)
) TABLESPACE tablespace2;

CREATE TABLE userr
(
  id_user  INT PRIMARY KEY,
  name     VARCHAR(30)
) TABLESPACE tablespace2;

CREATE TABLE borrow
(
  id      INT PRIMARY KEY,
  id_user INT REFERENCES userr(id_user) ON DELETE cascade,
  id_book INT REFERENCES book(id_book) ON DELETE cascade
) TABLESPACE tablespace2;


BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO userr(id_user, name) VALUES (i, 'User nr ' || i);
    INSERT INTO book(id_book, name)  VALUES (i, 'Book nr ' || i);
  END loop;
END;
/

BEGIN
  FOR i IN 1..500000 LOOP
    INSERT INTO borrow(id, id_user, id_book) VALUES(i, TRUNC(sys.DBMS_RANDOM.VALUE(1, 1000000)), TRUNC(sys.DBMS_RANDOM.VALUE(1, 1000000)));
  END loop;
END;
/

SET SERVEROUTPUT ON

DECLARE
  cz_poczatek NUMBER;
  cz_koniec   NUMBER;
  ilosc       NUMBER;
BEGIN
  cz_poczatek := DBMS_UTILITY.GET_TIME;
  SELECT COUNT(*) INTO ilosc
  FROM userr u, book b, borrow bu
  WHERE u.id_user = bu.id_user AND bu.id_book=b.id_book AND bu.id_user=701771;
  cz_koniec := DBMS_UTILITY.GET_TIME;
  --DBMS_OUTPUT.PUT_LINE('czas trwania bez indeksów:' || TO_CHAR(cz_koniec-cz_poczatek) || ' ilość:' || ilosc);
  DBMS_OUTPUT.PUT_LINE('czas trwania z indeksami:' || TO_CHAR(cz_koniec-cz_poczatek) || ' ilość:' || ilosc);
END;
/





CREATE INDEX indexborrowiduser ON
borrow(id_user);

CREATE INDEX indexborrowidbook ON
borrow(id_book);

DROP INDEX indexborrowiduser;
DROP INDEX indexborrowidbook;





-- zad3,4,5 -----------------------------------------------------------------------------------------------------------------------
-- stworzenie przestrzeni tabel
CREATE TABLESPACE tablespace3
DATAFILE 'Dane_33dbf' SIZE 500M
extent management LOCAL uniform SIZE 128k;

-- dodanie miejsca, niezbedne zeby moglo dodac 1000000 milion rekordow
ALTER TABLESPACE tablespace3 ADD DATAFILE
   'Dane_33.dbf' SIZE 500M
   AUTOEXTEND ON MAXSIZE UNLIMITED;

-- wypisanie wszystkich przestrzeni tabel
SELECT * FROM user_tablespaces;

-- usuniecie przestrzeni tabel
DROP TABLESPACE tablespace3 INCLUDING CONTENTS AND Datafiles cascade constraints ;



CREATE TABLE type2
(
  id_type INT PRIMARY KEY,
  name     VARCHAR(30)
) TABLESPACE tablespace3;

CREATE TABLE author2
(
  id_author INT PRIMARY KEY,
  name     VARCHAR(30)
) TABLESPACE tablespace3;

CREATE TABLE book2
(
  id_book   INT PRIMARY KEY,
  name      VARCHAR(30),
  id_type   INT REFERENCES type2(id_type)     ON DELETE cascade,
  id_author INT REFERENCES author2(id_author) ON DELETE cascade
) TABLESPACE tablespace3;

CREATE TABLE user2
(
  id_user  INT PRIMARY KEY,
  name     VARCHAR(30)
) TABLESPACE tablespace3;

CREATE TABLE borrow2
(
  id      INT PRIMARY KEY,
  id_user INT REFERENCES user2(id_user) ON DELETE cascade,
  id_book INT REFERENCES book2(id_book) ON DELETE cascade
) TABLESPACE tablespace3;


BEGIN
  FOR i IN 1..5 LOOP
    INSERT INTO type2(id_type, name)   VALUES (i, 'Type nr ' || i);
    INSERT INTO author2(id_author, name) VALUES (i, 'Author nr ' || i);
  END loop;
END;
/

BEGIN
  FOR i IN 1..1000000 LOOP
    INSERT INTO user2(id_user, name) VALUES (i, 'User nr ' || i);
    INSERT INTO book2(id_book, name, id_type, id_author)  VALUES (i, 'Book nr ' || i, TRUNC(sys.DBMS_RANDOM.VALUE(1, 5)), TRUNC(sys.DBMS_RANDOM.VALUE(1, 5)));
  END loop;
END;
/

BEGIN
  FOR i IN 1..500000 LOOP
    INSERT INTO borrow2(id, id_user, id_book) VALUES(i, TRUNC(sys.DBMS_RANDOM.VALUE(1, 1000000)), TRUNC(sys.DBMS_RANDOM.VALUE(1, 1000000)));
  END loop;
END;
/

SELECT id_user, COUNT(1)
FROM borrow2
GROUP BY id_user
ORDER BY COUNT(1) DESC;


DECLARE
  cz_poczatek NUMBER;
  cz_koniec   NUMBER;
  ilosc       NUMBER;
BEGIN
  cz_poczatek := DBMS_UTILITY.GET_TIME;
  SELECT COUNT(*) INTO ilosc
  FROM user2 u, book2 b, borrow2 bu, type2 t, author2 a
  WHERE u.id_user = bu.id_user AND bu.id_book=b.id_book AND b.id_type=t.id_type AND b.id_author=a.id_author
        AND bu.id_user=420483;
  cz_koniec := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('czas trwania bez indeksów:' || TO_CHAR(cz_koniec-cz_poczatek) || ' ilość:' || ilosc);
  DBMS_OUTPUT.PUT_LINE('czas trwania z indeksami :' || TO_CHAR(cz_koniec-cz_poczatek) || ' ilość:' || ilosc);
END;
/

CREATE INDEX indexborrowiduser ON
borrow2(id_user);

DROP INDEX indexborrowiduser;

CREATE INDEX indexborrowidbook ON
borrow2(id_book);