Query Evaluation

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

• Query Evaluation
• and Optimization
• Part 1

2
?????

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

3
?????

• 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)
• ??? ??????? ?????? ??????

4
????? ??????? ?????? ?????

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

5
????? ?????? ???????????

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

6
????? ?????

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

7
(No Transcript)
8
????? ??????

• ?????? ??? ???? ?????? ?? ????? ?? ????
• ???? ?????? ???? ??? 1K ?- 4K ????
• ?????? ?? ?? ???? ????? ?????? ?????? ??????, ???
  ??????? ????? ??????? ?????? ?- CPU
• ??? ???? ?????, ?? ?????? ????? ?????? ???? ????
  ??? ??????? ?????? ?????? ??????? ?- CPU

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

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

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

• ?????? ?????? ????? ????? ???? ?? ????? ?????
• ???? ?????? ????? ????? ?????? ???? ??
• ????? ????? ???? ????
• ????? ???? ?????? ?????? ??????
• ??? ???? (?? ??????) ??? 7

11
???? ?????

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

12
????????? ?? ????

• ???? ??????? ?? ????? B
• ???? ??????? ????? R
• ???? (??????) ????? ???? D
• ??????? ?????? ??????
  (pre-fetching) ?? ?????? ??? ????? ???, ?? ??
  ????? ?????? ?????? ?????? ???? ?????? ???????

13
??????? ?? ????

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

14
???? ????? ?????? ???????
????? ????? ????
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 ????? ?????
15
????????

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

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

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

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

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

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

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

19
????? ?????? ???? ????? ????

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

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

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

21
???? ????? ?????

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

22
???? ????? ?? ????????

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

23
????? ?? ?? B

• ???? ????? ????? ???? 9 ?? ?? B
• ??? 10 ???? ????????? ??????? ?? ?????
• ??? 11 ???? ????? ??????

24
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.

25
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!

26
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

27
?????? ?????? ?? ??? B ???? 9

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