View by Category

Loading...

PPT – Basic Algorithms for Executing Query Operations PowerPoint presentation | free to view

The Adobe Flash plugin is needed to view this content

About This Presentation

Write a Comment

User Comments (0)

Transcript and Presenter's Notes

Basic Algorithms for Executing Query Operations

- Session-7
- Data Management for Decision Support

Outline of Lecture

- Catalog Information for Cost Estimation
- Selection Operation
- Join Operation

Catalog Information for Cost Estimates

- Keep statistics in catalog to estimate size of

result and cost for various operations - If we wish to maintain accurate statistics, then,

every time a relation is modified, we must also

update statistics - Expensive!
- Instead, find compromise between accuracy of

statistics and query response - Updates are done during periods of light load
- Note real-world optimizers maintain lots more

variables

Simple Catalog

- nr number of tuples in relation r
- br number of blocks containing tuples in r
- sr size of tuple in relation r (bytes)
- fr blocking factor of r
- V(A,r) number of distinct values in r for

attribute A

Simple Catalog

- SC(A,r)
- Selection cardinality SC is average number of

records satisfying condition on A, r(R) is

total number of records in R - SC(A,r) (r(R)/V(A,r)
- e.g., SC(A,r) 1 if A is key of R and cond. is

equality - HTi number of levels in index i

Measures of Query Cost

- Could be measured in terms of disk accesses, CPU

time to execute query, cost of communication

(e.g., distributed or parallel systems) - Disk accesses is usually most important time
- Measuring CPU time is hard
- Disk-access cost is considered reasonable measure

of the cost of a query evaluation plan - Assume all transfers of blocks have same cost
- Use number of block transfers from disk as a

measure of the actual query cost

Simple SELECT Operation

- Linear search Retrieve every record in file and

test condition

E br Selection on key attribute, assume E br/2

- Binary search If selection involves equality

comparison on attribute on which file is ordered,

use binary search

E ?log2(br)? (SC(A,r)/fr) - 1 E ?log2(br)?

, if attribute is key

Simple SELECT Operation

- Using primary index If selection involves

equality on key attribute with primary index, use

primary index to retrieve (at most one) record

E HTi 1

- Using primary index to retrieve multiple records

If selection condition involves range on key

field with primary index, use index to find

record satisfying corresponding equality

condition then retrieve subsequent records

E HTi br/2 assume half of tuples satisfy

condition E HTi ?c/fr? if actual value used

in comparison is known

SELECT Operation

- Use a clustering index to retrieve multiple

records If selection condition involves equality

comparison on non-key attribute with clustering

index, use index to retrieve all records

satisfying condition

E HTi ?SC(A,r)/fr?

SELECT Operation

- Using a secondary index If selection condition

involves equality or inequality on key or non-key

field with secondary index (non-ordering field),

use index to retrieve records

E HTi SC(A,r)

Implementing the JOIN Operation

- Cartesian product
- Iteration (nested loops)
- Merge join
- Join with index
- Hash join

Estimating Size of Joins

- Assume relations R and S, with nr and ns tuples

respectively

- Cartesian product r x s
- E nr ns each tuple occupies sr ss bytes

- Natural join
- 1) R ? S ?, then r join s r x s E nr ns

2) R ? S key for R (R ? S key for S is

symmetric) ? a tuple of s will join with at most

one tuple from r ? r join s ? ns Also, if R

? S foreign key for R ? r join s ns

Estimating Size of Joins

3) R ? S A neither key for R nor S Consider

tuple t of r, estimate that t produces ns/V(A,s)

tuples in r join s Consider all tuples in r, we

estimate that there are nr ns /V(A,s) in r join

s Repeat estimate with reverse roles ns nr

/V(A,r) in r join s Final estimate, take lower

of the two!

Nested-Loops Join

outer relation

- Iteration join (conceptually)
- for each tuple r ? R do
- for each tuple s ? S do
- if r and s satisfy join condition,
- then output r,s pair

inner relation

Nested-Loops Join Performance

- Nested-loops Join
- Can always be used, doesnt require index
- Expensive, examines every pair of tuples
- Evaluation
- Number of pairs of tuples to be considered is nr

ns - For each record in r, perform complete scan on s
- Worst case, buffer can only hold one block from

each relation (plus output buffer) - Best case, both relations fit into memory
- If file with fewer blocks fits into memory, use

as inner-loop file

Example

- Assume Employee JOINDN0Dnumber Department
- Department 50 records, bD10
- Employee 6000 records, bE2,000
- (1) Best case Both relations fit into memory br

bs - Ex. bDbE 2,010 blocks
- (2) Worst case MM can only hold one block of

each relation (plus one output block) - br brbs, where r is outer relation and s is

inner relation - Department outer, Employee inner 10 10 2,000

20,010 block reads - Employee outer, Department inner 2,000 200010

22,000 block read - (3) case B buffers br br/(B-1) bs Let B6

(read 1 block of S and (B-1) or R - Department outer, Employee inner 10

(10/5)2,000 4,010 block reads - Employee outer, Department inner 2,000

(2,000/5)10 6,000 block read

Simple Sort-Join

- 2-Pass Algorithm based on sorting
- Merge join (conceptually), let C be join

attribute - (1) if R and S not sorted, sort them
- (2) i ? 1 j ? 1
- While (i ? nr) ? (j ? ns) do
- if R i .C S j .C then outputTuples
- else if R i .C gt S j .C then j ? j1
- else if R i .C lt S j .C then i ? i1

Outputting Tuples

- While (R i .C S j .C) ? (i ? nr) do
- jj ? j
- while (R i .C S jj .C) ? (jj ? ns)

do - output pair R i , S jj
- jj ? jj1
- i ? i1

Example

- i Ri.C Sj.C j
- 1 10 5 1
- 2 20 20 2
- 3 20 20 3
- 4 30 30 4
- 5 40 30 5
- 50 6
- 52 7

Evaluation of Sort-Join

- Works for natural join and equi-joins only
- Efficient, after sorting, only one pass over both

relations - E br bs
- Sorting is O(n log n)

Join with Index

- For each r ? R do
- X ? index (S, C, r.C)
- for each s ? X do
- output r,s pair

Assume index on inner relation S S.C

Note X ? index(rel, attr, value) then X set

of rel tuples with attr value

Evaluation of Join with Index

- For each tuple in outer relation r, lookup

performed on index for s, relevant tuples

retrieved - Worst case Space in buffer for one page of r and

1 index page - br disk accesses needed to read r and, for each

tuple in r (assuming R is clustered), index

lookup on s - E br nrc, c cost of single selection on s

using join condition (e.g., (SC(A,s)/fs)) - Use one with fewer tuples as outer
- Example Employee JOINDN0Dnumber Department
- Assume Employee has B-Tree of height 3
- E 10 50 4 210 (less than nested-loops

join), assuming each tuple t in r joins with at

most one tuple t in s

About PowerShow.com

PowerShow.com is a leading presentation/slideshow sharing website. Whether your application is business, how-to, education, medicine, school, church, sales, marketing, online training or just for fun, PowerShow.com is a great resource. And, best of all, most of its cool features are free and easy to use.

You can use PowerShow.com to find and download example online PowerPoint ppt presentations on just about any topic you can imagine so you can learn how to improve your own slides and presentations for free. Or use it to find and download high-quality how-to PowerPoint ppt presentations with illustrated or animated slides that will teach you how to do something new, also for free. Or use it to upload your own PowerPoint slides so you can share them with your teachers, class, students, bosses, employees, customers, potential investors or the world. Or use it to create really cool photo slideshows - with 2D and 3D transitions, animation, and your choice of music - that you can share with your Facebook friends or Google+ circles. That's all free as well!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

You can use PowerShow.com to find and download example online PowerPoint ppt presentations on just about any topic you can imagine so you can learn how to improve your own slides and presentations for free. Or use it to find and download high-quality how-to PowerPoint ppt presentations with illustrated or animated slides that will teach you how to do something new, also for free. Or use it to upload your own PowerPoint slides so you can share them with your teachers, class, students, bosses, employees, customers, potential investors or the world. Or use it to create really cool photo slideshows - with 2D and 3D transitions, animation, and your choice of music - that you can share with your Facebook friends or Google+ circles. That's all free as well!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

presentations for free. Or use it to find and download high-quality how-to PowerPoint ppt presentations with illustrated or animated slides that will teach you how to do something new, also for free. Or use it to upload your own PowerPoint slides so you can share them with your teachers, class, students, bosses, employees, customers, potential investors or the world. Or use it to create really cool photo slideshows - with 2D and 3D transitions, animation, and your choice of music - that you can share with your Facebook friends or Google+ circles. That's all free as well!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

Recommended

«

/ »

Page of

«

/ »

Promoted Presentations

Related Presentations

Page of

Page of

CrystalGraphics Sales Tel: (800) 394-0700 x 1 or Send an email

Home About Us Terms and Conditions Privacy Policy Contact Us Send Us Feedback

Copyright 2015 CrystalGraphics, Inc. — All rights Reserved. PowerShow.com is a trademark of CrystalGraphics, Inc.

Copyright 2015 CrystalGraphics, Inc. — All rights Reserved. PowerShow.com is a trademark of CrystalGraphics, Inc.

The PowerPoint PPT presentation: "Basic Algorithms for Executing Query Operations" is the property of its rightful owner.

Do you have PowerPoint slides to share? If so, share your PPT presentation slides online with PowerShow.com. It's FREE!