Query Evaluation

1
????? ???????????? ?? ?????????? 1

• Query Evaluation
• and Optimization
• Part 1

2
????? ???? ????? ???????? ????????
3
????? ????? ??????? ????? ????? ??? ?????

• ????? ???? ??????? ????? ????
• ?? ???? ????? ?? ?? ??????? ??? ??? ???????
  ?????? ??? ?????? ??????
• ???? ???? ?? ?????? ?????? ??? ???
• ?????? ??? ???????? ??????? ??????
• ?????? ??? ??????? ??? ??????? ???????
  ??????
• ?????? ????? ?? ???? ?? ?????? ?????
• ?????? ???? ???

4
???? ????? ?- I/O ??? ????? ?????? ?????? ?? ???
?????? ?? ??????

• ?????? ?????? ?"? ?- CPU ?? ??????? ???????
  ??????? ?????? ???? ?????
• ?????? ?????? ??????? ????? ??? ?????? (?????) ??
  ??? ???????? ???????? ??????? ??????
• ?????, ???? ????? ?????? ?????? ??????? ?? ?????
  ??? ????? ?????? ?????? ?? ??? ?????? ?? ???????

5
??????? ???????? ???????? ?? ???? ????? ?- I/O

• ????? ??????? ?? ?????
• ????? ?? ?????? ?? ??? ????????
• ?????? ???? ????? ????? ?????? ????? ?????? (??"?
  ???? ????? ??????)
• ???? ????? ?- I/O ???? ?????
• ??????? ?????? ?????? ??????? ????, ???
• ????? ?????? ?????? ?? ??? ????????
• ???? ????? ?????? ???????

6
??????? ???????? ?????

• ???? ???????? ?????? ?? ????? ????? ?????? ??????
  ???-??? (I/O)
• ????? ?????? ?? ??? ???????? ?????????
• ??? ????? ?? ????? ?????? ??????, ???? ??????
  ????? ????? ???????? ?????
• ???????? ???????????? ?? ??????, ????? ??
• ????? ?????????? ?????? ?? ??? ????????, ???
• ???? ????? ????? ?????? ???????

7
?????

• R(A,B) ? S(B,C) ? T(C,D)
• ????? ??? ??????????, ??? ?? ??? ???????? ??????
  ?????? ??"?
• R(A,B) ? (S(B,C) ? T(C,D))
• (R(A,B) ? S(B,C)) ? T(C,D)
• ????? ??? ?? ?????????, ??? ?? ?????? ?????
• (R(A,B) ? T(C,D)) ? S(B,C)
• ??? ??????? ?????? ??????

8
???? ??????

• ????? ?? 3 ???????? ?????? 2 ?????? ?????
• R(A,B) ? (S(B,C) ? T(C,D))
• (R(A,B) ? S(B,C)) ? T(C,D)
• (R(A,B) ? T(C,D)) ? S(B,C)
• ??? ???????? ???????? ?? ???? ?????????
• ????? ?????? ?? ?????
• ???? ????? ???????
• ???? ???? ????? ????? ??????? ??? ??? ??? ??? ???
  ?????

9
????? ??????? ?????? ?????? ?? ??? ?????

• ????? ??? ?????? ???????? ????? ????? ???????
  ????? ???? ????? ????? ?????
• Block Nested-Loops Join
• Index Nested-Loops Join
• Sort-Merge Join
• Hash-Join
• ?????, ???? ?? ??? ??????? ????
• ??? ??, ???? ????? ?????? ??????? ????????? ??????

10
????? ?????? ????????????

• ???? R(A,B) ? S(B,C) ? T(B,D) ???? ?????
• ???? ?????? ??? ??? ???? ?????? ??????
• ??? ???? ????? ???? ???? ???? ???????
• ??? ????? ?????? ???????
• ?? ???? ?? ?? ???????? ????? ?????
• ?????? ?? ????? ??? ????? ????? ????? ????? ????
  ???????
• ????? ?????????? ?????? ?????? ??? R ???? S ?????
  ????? ????? ????? ??? ????? ?? ????? ????? ?? ???

11
???? ??????????? ??? ??????

• ??? ??????? ?????????? ?????? ?????? ???? ??????
  ????? ?????? ???? ?????? ???? ????? ???? ????
• ???? ????? ????? ????? ????? ???? ???? ????
• ?? ?? ?? ???? ????? ?? ??????? ?????? ?????
  ???? ?????? ?????? ?????? ?????

12
????? ??????? ?? ?????,???? ?????????? ??????
?? ?????? I/O
13
????? ?????

• ????? ???????? ?????? ?? ??????
• ???? ????? ??????? (????) ???? ???? ????
• ???? ???? ?????? ?? ?????? ???????
• ???? ?????? ?? ????? ???? ???? ???? ????? ??????
  ????? ?? ????? ????? ?????
• ?????, ???? ????? ?????? ????? ?????? ?????
• ?????? ??????? ????? ??? ?????? ????? ?? ???
  ?????? ?????? ???????? ??????

14
(No Transcript)
15
????? ??????

• ?????? ??? ???? ?????? ?? ????? ?? ????
• ???? ?????? ???? ??? 1K ?- 4K ???? (bytes)
• ?????, ????? ?? ????? ?????? ??? ???? ?????
  ?????? ?????? ??????, ??? ??????? ????? ???????
  ?????? ?- CPU
• ??? ???? ?????, ?? ?????? ????? ?????? ???? ????
  ??? ??????? ?????? ?????? ??????? ?- CPU

16
????? ??? ?????

• ?? ??? ?????? ????? ????
• ???? ???? ??????? (??????? ?? ????)
• ??? ???? ?????? ????
• ????? ?????? ????? ????? ?????
• ????? ?? ????? ?????? ?? ????? ????? ????? ??
  ?????? ??????? ??????, ????? ??????, ?????? ?????
  ????? ?????

17
????? ?????? ?????

• ?????? ?????? ????? ????? ???? ?? ????? ?????
• ???? ?????? ????? ????? ?????? ???? ??
• ????? ????? ???? ????
• ????? ???? ?????? ?????? ??????

18
?????? ?????? ?? ????? ?????? ?????

• ????? ????? ?"? RID (Record ID), ?????? ?????
  ????? ?????? ?????? ???? ?????
• ???? ????? ?? ???? ????? ???? ?????? ?? ?????
  ?????? ?????
• ???? ?- RID ?? ????? ???? ?????? ??????? ????
  ???? ??? ???? ??? ?? ????? ?? ???????? ??????
• ???, ?? ???? ????? ?? ?- RID (??? ????? ???? ???)

19
????? ?? ????? ?????? ?????
Rid (i,N)
Page i
Rid (i,2)
Rid (i,1)
Pointer to start of free space
16
24
N
20
N 2 1
slots
SLOT DIRECTORY

• ???? ????? ?? ??????? ???? ????? ??? ????? ?? ?-
  RID ????

20
???? (Buffer)

• ??? ???????? ????? ?????, ?????? ?????? ??????
  ??????
• ??????? ????????? ?????? ?? ??????? (????)
  ??????? ?????
• ???? ?????? ?????? ????? ????? ?????, ???? ????
  ?? ????? ????? ???? ???
• ?? ????? ????? ???? ???? ?????, ???? ????? ????
  ????? ???? ?????? ?? ????? ????? ???? ???

21
????? ????
Page Requests from Higher Levels
BUFFER POOL
disk page
free frame
MAIN MEMORY
DISK
choice of frame dictated by replacement policy

• Data must be in RAM for DBMS to operate on it
• Table of ltframe, pageidgt pairs is maintained

22
???? ??????? ???? ???? ???? ?? ??????(???????
????? Replacement Policy)

• LRU (Least Recently Used) ??????? ?? ?????
  ????? ?? ????? ??? ???? ????
• ??????? ?????? ?????? ?????? ???????? ?"? ?- OS
• MRU (Most Recently Used)
• ?????? ??????? ??? ???? ????, ???? ????? ????? ??
  ????, ????
• ???? ????? ?? ????? ????? ????? ???? ?????
• ???? ??????? ????? ??? ????? ??????? ?????

23
???? ?????? ?????? ?????? ?????? ???????, ???

• ?????? ?????? ???? ?????? ??????? ???? ?? ???????
  ?????, ??
• ?? ?? ???? ????? ?????? ?? ?- DBMS ????? ??????
  ????? ?????
• ?? ?????? ??? ?????? ??????? ????? ???????? ?????
  ?????
• ??????? ????? ?????? ???????? ????? ????
• ??????? ????? ???? ??? ??????? ??????? ????????
• ????? ?????? ?? ?????? ???? ?????

24
????? ?????? ?? ?????? ???? ?????

• ?????, ???? ???? ?????? I/O ?????? ????????
  ???????? ?"? ?- CPU
• ?? ??????? ?????? ?????? ????? ??? ????? ?? ????
  ????? ?????, ?? ???? ????? ??? ??? ???? ??????
  ???? ????? (????? ????? ?????), ???? ????? ????
  ?- CPU ???? ?? ??????? ?????? ???? ?????

25
???? ?????
26
???? ?????

• ???? ???? (Heap file)
• ??????? ???????? (??? ????) ???????, ????????? ??
  ???
• ???? ????? (Sorted file)
• ??? ?????? ?? ??????? ??? ??? ????? ?? ??????
  ??????? ????? ????? ?? ?????
• ???? ????? (Hash file)
• ??????? ?????? ????? ????? ???? ????? ??????
  ?????? ?? ????? ???

27
??????? ?? ????

• ????? ?? ????? ????? ?????
• ????? ??? ??? ???? ?????
• ???? ????
• ??????? ???? ?????
• ???? ?????
• ????? ????? ??? ?????
• ??????? ?? ????? ???? ?????
• ???? ?????
• ??? overflow, 80 ?????

28
????????? ?? ???? ??????? ?? ???? ????? ??????
???????

• ???? ??????? ?? ????? B
• ???? ??????? ????? R
• ???? (??????) ????? ???? D
• ????? ???????? ???? ???? ?? ???? (?? ?????? ?-
  I/O) ????? ?????? ??????? ????? ?????? ??????

29
????

• ??????? ?????? ??????
  (pre-fetching) ?? ??????
• ????? ?????? ????? ???, ?? ??? ?????? ??????
  ?????? ?????? ???? ?????? ???????
• ??? ??? ????? ???? ?????? ????? ?????
• ????? ????? ????? ?? ???? ???? ????? ??????

30
???? ????? ?????? ???????
????? ????? ????
1.25BD BD BD ???? ?? ?? ???????
D Dlog2B 0.5BD ????? ????? ??? ????
1.25BD D(log2B of pages with matches) BD ????? ?? ??????? ????? ????
2D Search BD 2D ????? ?????
2D Search BD Search D ????? ?????
31
????????

• ?????? ??? ???? (???, ???) ???? ???? ???? ?????
  (search key)
• ???? ????? ??? ???? ????? ?? ????, ?????? ?????
  ???? ?? ????
• ?????? ??? ???? ???? ??????, ??????? ????? ?????
  ?????? ??? ??????? ????? ???? ????? (????? ?????
  ???? ?????? ??? ?? ???? ??? ???? ???? ??????)

32
????????? ????? ??????? ?? ??????

• ??????? ????? ?????? ?????? ????? ???? ?????
• ?????? ?????? ?? ???? ?????? ???
• ??????? ??? ??? ????? ????? (????? ?? ??????? ???
  ?? ????? ?????)
• ??? ????? ?????? ??? ???? ????? ???
• ????? ????? ????? ?????? ???
• ??? ?????? ???????? ????? ??????? ??? ???????
  ??????? ?? ?????, ??? ??? ?? ???? ?????? ????
  ???? ??????
• ????? ?? ??????? ??? ???? ????

33
????????? ????? ??????? ?????

• ????? ????? ?????? (k, rid), ???? k ??? ??? ?-
  rid (record identifier) ??? ????? ?????? ?? ????
  ?? ???? k
• ???? ????? ????? ???? ??????? ????? ???? ??????
  ???? k
• ????? ????? ?????? (k, list of rids)
• ???? ????? ????? ????? ??? ?? ????? ??? ???? k

34
????? ?????????????? ???? ????? ?????? ????

• ?????? ???? ???? ?????? ???? ???? ???? ?? ????
• ?????? ???? ????
• ????? ??? ?????? ???? ???? ?????? ?? ????, ????
  ??????? ???? ???? ????? ?????

35
????? ?????????????? ???? ????? ?????? ?????
????

• ?????? ???? (clustered index) ??? ???????
  ??????? ??? ?? "????" ???? ??????? ?????
• ?????? ?? ???? (unclustered index) ????
• ?????? ????? ?????? ?? ???? ??? ???? ????, ???
  ?????? ???? ???? ????? ???? ?? ?????? ?????
• ??? ????? ?????? ??? ???? ????? ????
• ????? ?????? ??? ?????? ?? ???? ????? ???? ???

36
????? ?????? ???? ????? ????

• ???? ?? ????? ????? ??? ???? ??? ???? ??????
  ?????? ?????
• ????? ???? ????? ???? ??????? ?? overflow ?????
  ????? ?????? ??? ???? ?? ?????? ??????? ????,
  ??? ?? ???, ??? ?? ?????? ?????

Index entries
UNCLUSTERED
CLUSTERED
direct search for
data entries
Data entries
Data entries
(Index File)
(Data file)
Data Records
Data Records
37
?????? ???? ??????? ????

• ?????? ???? ???? ?? ??? ??????? ?? ???? ??????
• ???? ????? ?????? ????
• ??? ????? ?? ??????? ?? ????? ?? ???? k ??????
  ??? b ?? ?-
• b ???? ????? ????? ??????? ?????? ???? ???? ?- k
• ??????? ???? ????? ???? ???? ??????? ??????? ??
  ???? b

38
???? ????? ?????

• ???? ???? ????? ???? ??? ????, ???? ????? ??
  ????? ?????? ??? ???? ?????????? (?? ????? ?????
  ????? ??????? ????), ??? ????? ???? ???? ???????
  ????? ???? ?? ??? ??????
• ??????, ?????? ?? (age, sal)
• ??? ?????? ?? age20
• ??? ?????? ?? age20 and salgt1000
• ??? ?? ??????? ?? sal20 ???? ????? ?? ??? ??
  ???????? ??????? ?- 1000

39
???? ????? ?? ????????

• ?????? ????? ?? ?? ????? ?????
• ?? B ??? ????? ?????
• ????? ????? ??? ????
• ?????? ????? ?? ?????
• ????? ????? ??-????? (extendible hashing), ?????
  ?????? ?????? ?? ???? ?????? (?????? ????? ??
  ??????? ??????) ???? ???????? ??????
• ????? ????? ?? ??? ??????

40
????? ?? ?? B

• ???? ????? ????? ???? 9 ?? ?? B

41
B Tree The Most Widely Used Index

• Insert/delete at log F N cost keep tree
  height-balanced. (F fanout, N leaf pages)
• Minimum 50 occupancy (except for root). Each
  node contains d lt m lt 2d entries. The
  parameter d is called the order of the tree.
• Supports equality and range-searches efficiently.

42
Example B Tree

• Search begins at root, and key comparisons direct
  it to a leaf Search for 5, 15, all data entries
  gt 24 ...

Root
17
24
30
13
39
3
5
19
20
22
24
27
38
2
7
14
16
29
33
34

• Based on the search for 15, we know it is not
  in the tree!

43
B Trees in Practice

• Typical order 100. Typical fill-factor 67.
• average fanout 133
• Typical capacities
• Height 4 1334 312,900,700 records
• Height 3 1333 2,352,637 records
• Can often hold top levels in buffer pool
• Level 1 1 page 8 Kbytes
• Level 2 133 pages 1 Mbyte
• Level 3 17,689 pages 133 MBytes

44
?????? ?????? ?? ??? B ???? 9

• ????? ?????? ?? ??????
• ???? ????? ??? ???? ????? ????? ??? ???
• ???? ????? ???? ????? ?????
• ????? ??????? ?? ?? B ???? ???? ????
• ????? ?????? ????? ????????
• ????? ?? ?- fan-out ???? ????? ?? ???? ???

45
?????? ?????? ??? ????

• ??? 10 ???? ????????? ??????? ?? ?????
• ??? 11 ???? ????? ??????
• ?????, ????? ???? ???? ?? ???? ????? ??? ??? ??
  ?? ??????? ???? ??????? ??????
• ???? ?????? ????
• ???? ????? ????? ??????
• ?????? ?????? ?????? ?? ?????? (DISTINCT)
• ????? ????? group by
• ?????? sort-merge join (??? ?????? ?? ?????)