김용주님의 블로그

DB Chapter 8

13 Jun 2019 |

Ch.8: Views and Indexes

1. Virtual Views

Virtual view: query를 통해 가상으로 만들어진 relation. 즉, 논리적으로만 존재하는 table. 보통 view라고 하면 이걸 말함

Materialized view: 실제로 DB에 저장되어 있는 view. 특정 튜플에 빠르게 접근하기 위해 사용.

Table 은 결과를 저장, View는 과정을 저장.

Likes(StudentID, book)
Sells(bookstore, book, price)
Frequents(StudentID, bookstore)

#Synergy라는 view를 만드는 방법. SID와 book, bookstore로 이루어져 있고, where 아래 3개를 만족.
CREATE VIEW Synergy AS
    SELECT Likes.StudentID, Likes.book, Sells.bookstore
    FROM Likes, Sells, Frequents
    WHERE Likes.StudentID = Frequents.StudentID AND
         Likes.book = Sells.book AND
         Sells.bookstore = Frequents.bookstore;
	
/*
Synergy에 insert할 때의 trigger. 
만약 Synergy에 값이 새로 들어오면 Likes에 새로 들어온 StudentID,book의 값이 저장되고, 
Sells의 bookstore, book에는 새로 들어온 bookstore, book의 값이 저장되고, 
Frequents에는 새로들어온 SID, bookstore의 값이 저장이 된다.
*/
CREATE TRIGGER ViewTrig
INSTEAD OF INSERT ON Synergy
FOR EACH ROW
	BEGIN
		INSERT INTO Likes VALUES(:new.StudentID, :new.book);
		INSERT INTO Sells(bookstore, book) VALUES(:new.bookstore, :new.book);
		INSERT INTO Frequents VALUES(:new.StudentID, :new.bookstore);
	END;
.
run

#책에 나온 예제. view에는 t, y만 저장해서 studioname에 해당하는 정보가 없어서 Movie에 저장될 때 t,y만 값이 저장이 되고 studioname에는 paramount가 아닌 NULL이 저장
CREATE VIEW ParamountMovies AS
	SELECT title, year
	FROM Movies
	WHERE studioName = 'Paramount'

#해결은 trigger로! ParamountMovies에 새로운 값이 들어오면 Movie에 title, year에는 새로운 값, studioName에는 paramount가 저장
CREATE TRIGGER ParamountInsert
INSTEAD OF INSERT ON ParamountMovies
REFFERENCING NEW ROW AS NewRow
FOR EACH ROW
INSERT INTO Movies(title, year, studioName) VALUES(NewRow.title, NewRow.year, 'Paramount');

###

3. Indexes in SQL

index: table에 저장된 tuple에 더 빨리 접근하기 위한 data structure.

B-Tree(Balance search Tree) 형식 SQL에서 data들은 heap에 저장이 되는데 select할 때 heap을 처음부터 다 확인하는데 index를 이용하면 더 빨리 확인 가능.

CREATE INDEX BookIndex ON Bookstores(book);
CREATE INDEX SellIndex ON Sells(bookstore, book);

근데 index를 쓴다고 해서 무조건 빨라지는게 아님. 적절한 tuning이 필요. 이 tuning을 돕는게 tuning advisor. 아래의 일을 함. 근데 뭔말인지 잘 모르겠다. 시험 안나오겟지?ㅎㅎ

  1. 해당 db에서 run한 적이 있는 랜덤 query를 선택하는 방법
  2. designer에게 sample workload를 제공(workload = 작업량)

4. Selection of Indexes

Calculation the Best Indexes to Create

이 문제에 들어가기 전에, 사실 이렇게 자세히 알 필요 없이 밑에 표가 주어지면 average를 구하고, p1과 p2의 값이 주어졌을 경우 어떤 것을 선택하는게 좋을지 정도만 구하면 되지만 처음부터 확인하는게 가장 best니까 봅시다.

StarsIn(title, year, starName)이 있고 Q1은 starName이 s(“배수지”)인 (title, year)를 선택하는 query고, Q2는 title = t(“건축학개론”), year = y(“2012”)인 (starName)을 선택하는 query라고 하자. (여기서 s, t, y는 상수, 정해진 어떤 값들)

그리고 I를 INSERT INTO StarsIn VALUES(t,y,s)라고 하자. (StarsIn에 t,y,s를 새로 넣는 작업)

그리고 data에 대해 5가지 assumption이 있다고 가정.

  1. StarsIn은 10page를 점유한다. 그래서 전체 relation을 검사하는데 10의 cost가 든다.
  2. 평균적으로 한 명의 star는 3개의 영화에, 한 영화에는 3명의 star가 나타난다.
  3. star와 movie는 10page에 골고루 있어서, starName에 대한 index와 title, year에 대한 index가 있어도 평균적으로 3개의 tuple을 찾는데 3번의 disk access가 필요함. index가 없으면 10번
  4. 하나의 disk가 접근하면 하나의 page를 읽어서 사용. 만약 수정이 필요하면 또 다른 disk가 와서 그 page를 수정
  5. 마찬가지로 insertion할 때, 하나의 disk가 새로운 tuple이 들어갈 page를 읽음 그리고 또 다른 disk가 가서 page에 새로운 data를 씀.

그럼 Q1에 드는 시간이 p1, Q2에 드는 시간이 p2라고 했을 때, Insertion의 경우 1-p1-p2만큼 사용.

DB8_1

  1. Index가 없으면 10page를 다 확인해야 하므로 Q1, Q2는 10. insertion을 할 때는 2만큼 들음. 새로 들어갈 data가 위치할 장소를 잡는데 1번의 disk access 그 장소에 data를 쓰는데도 1번의 disk access. 그래서 총 2번의 disk access.

    그래서 average는 10p1 + 10p2 + 2(1-p1-p2) = 2 + 8p1 + 8p2

  2. Q1이 star를 다루는 거였으니까 star에 index가 있는 경우. 맨처음 index page에 access하는데 1번, 그리고 index를 통해 각각의 tuple에 aceess하는데 3개의 tuple을 확인해야 하므로 3번. 합쳐서 총 4번. insertion의 경우 각 tuple에 read write하는 page, data를 위한 page가 필요. 그래서 총 4번의 disk access.

    그래서 average는 4p1 + 10p2 + 4(1-p1-p2) = 4 + 6p2

  3. Q2는 2번과 비슷하니까 skip

  4. index가 둘다 있을 경우 각각 4번의 disk access를 하고 insertion의 경우 2개의 index page를 읽고 쓰니까 4번, data page는 각각 1개씩 있으니까 2번. 합쳐서 총 6번의 disk access가 발생.(사실 잘 모르겟다 여긴)

    average는 4p1 + 4p2 + 6(1-p1-p2) = 6 - 2p1 - 2p2

이제 여기서 p1=p2=0.1일 경우 no index가 가장 빠르지만 둘 다 0.4일 경우는 둘다 index하는게 제일 빠르다. p1=0.5, p2=0.1일 경우는 Star index가 가장 빠르다. 매번 상황에 따라 달라지므로 index를 만들지 말지는 상황에 따라 결정하는게 바람직하다.

Related Posts