Query Evaluation - PowerPoint PPT Presentation

About This Presentation
Title:

Query Evaluation

Description:

... ???? k ??? ??? ?- rid (record identifier) ??? ????? ?????? ?? ???? ?? ???? k ... The parameter d is called the order of the tree. ... Example B Tree ... – PowerPoint PPT presentation

Number of Views:12
Avg rating:3.0/5.0
Slides: 28
Provided by: yehoshu
Category:

less

Transcript and Presenter's Notes

Title: 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 ???? ????? ?? ???? ???
Write a Comment
User Comments (0)
About PowerShow.com