Transcript Sections 13.1 – 13.3 - San Jose State University

CS257 – Final Exam
Ramya Karri
IDNo - 206
SECONDARY STORAGE
MANAGEMENT
Sections 13.1 – 13.3
Presentation Outline
 13.1 The Memory Hierarchy
13.1.1 The Memory Hierarchy
13.1.2 Transfer of Data Between Levels
13.1.3 Volatile and Nonvolatile Storage
13.1.4 Virtual Memory
 13.2 Disks
13.2.1 Mechanics of Disks
13.2.2 The Disk Controller
13.2.3 Disk Access Characteristics
Presentation Outline (con’t)
13.3 Accelerating Access to
Secondary Storage
13.3.1 The I/O Model of Computation
13.3.2 Organizing Data by Cylinders
13.3.3 Using Multiple Disks
13.3.4 Mirroring Disks
13.3.5 Disk Scheduling and the
Elevator
Algorithm
13.3.6 Prefetching and Large-Scale
Buffering
13.1.1 Memory Hierarchy
• Several components for data storage having
different data capacities available
• Cost per byte to store data also varies
• Device with smallest capacity offer the fastest
speed with highest cost per bit
• The order for cost per bit is cache>Main
Memory> Secondary Memory> Tertiary
Memory
Memory Hierarchy Diagram
Programs,
DBMS
Main Memory DBMS’s
As Visual Memory
Tertiary Storage
Disk
Main Memory
Cache
File System
13.1.1 Memory Hierarchy
• Cache
– Lowest level of the hierarchy
– Data items are copies of certain locations of main
memory
– Sometimes, values in cache are changed and
corresponding changes to main memory are delayed
– Machine looks for instructions as well as data for
those instructions in the cache
– Holds limited amount of data
– Highest cost per bit
13.1.1 Memory Hierarchy (con’t)
• No need to update the data in main memory
immediately in a single processor computer
• In multiple processors data is updated
immediately to main memory….called as write
through
• Typical time to move the data from main
memory to cache is at the rate of 10-100ns
Main Memory
• Everything happens in the computer i.e.
instruction execution, data manipulation, as
working on information that is resident in
main memory
• Main memories are random access….one can
obtain any byte in the same amount of time
Secondary storage
• Used to store data and programs when they
are not being processed
• More permanent than main memory, as data
and programs are retained when the power is
turned off
• E.g. magnetic disks, hard disks
Tertiary Storage
• Holds data volumes in terabytes
• Used for databases much larger than what can
be stored on disk
• Has higher read/write time than secondary
storages
13.1.2 Transfer of Data Between levels
• Data moves between adjacent levels of the
hierarchy
• At the secondary or tertiary levels accessing
the desired data or finding the desired place
to store the data takes a lot of time
• Disk is organized into bocks
• Entire blocks are moved to and from memory
called a buffer
13.1.2 Transfer of Data Between level
(cont’d)
• A key technique for speeding up database
operations is to arrange the data so that when
one piece of data block is needed it is likely
that other data on the same block will be
needed at the same time
• Same idea applies to other hierarchy levels
13.1.3 Volatile and Non Volatile
Storage
• A volatile device forgets what data is stored on
it after power off
• Ex: Cache
• Non volatile holds data for longer period even
when device is turned off
• Ex: Secondary Device
• All the secondary and tertiary devices are non
volatile and main memory is volatile
13.1.4 Virtual Memory
• Typical software executes in virtual memory
• Address space is typically 32 bit or 232 bytes
or 4GB
• Transfer between memory and disk is in terms
of blocks
13.2.1 Mechanism of Disk
• Mechanisms of Disks
– Use of secondary storage is one of the important
characteristic of DBMS
– Consists of 2 moving pieces of a disk
• 1. disk assembly
• 2. head assembly
– Disk assembly consists of 1 or more circular
platters that rotate around a central spindle
– Bits are stored on upper and lower surfaces of
platters
13.2.1 Mechanism of Disk
• Disk is organized into tracks
• The track that are at fixed radius from center
form one cylinder
• Tracks are organized into sectors
• Tracks are the segments on a disk circle
separated by gap
Typical Disk
13.2.2 Disk Controller
• One or more disks are controlled by disk
controllers
• Disks controllers are capable of
– Controlling the mechanical actuator that moves
the head assembly
– Selecting the sector from among all those in the
cylinder at which heads are positioned
– Transferring bits between desired sector and main
memory
– Possible buffering an entire track
13.2.3 Disk Access Characteristics
• Accessing (reading/writing) a block requires 3
steps
– Disk controller positions the head assembly at the
cylinder containing the track on which the block is
located. It is called ‘seek time’
– The disk controller waits while the first sector of
the block moves under the head. This is called
‘rotational latency’
– All the sectors and the gaps between them pass
the head, while disk controller reads or writes
data in these sectors. This is called ‘transfer time’
13.3 Accelerating Access to Secondary
Storage
Several approaches for more-efficiently accessing
data in secondary storage:
Place blocks that are together in the same cylinder.
Divide the data among multiple disks.
Mirror disks.
Use disk-scheduling algorithms.
Prefetch blocks into main memory.
Scheduling Latency – added delay in accessing
data caused by a disk scheduling algorithm.
Throughput – the number of disk accesses per
second that the system can accommodate.
13.3.1 The I/O Model of Computation
 The number of block accesses (Disk I/O’s) is a good time
approximation for the algorithm.
 This should be minimized.
 Ex 13.3: You want to have an index on R to identify the block
on which the desired tuple appears, but not where on the
block it resides.
 For Megatron 747 (M747) example, it takes 11ms to read
a 16k block.
 A standard microprocessor can execute millions of
instruction in 11ms, making any delay in searching for the
desired tuple negligible.
13.3.2 Organizing Data by Cylinders
If we read all blocks on a single track or
cylinder consecutively, then we can neglect all
but first seek time and first rotational latency.
Ex 13.4: We request 1024 blocks of M747.
If data is randomly distributed, average latency is
10.76ms by Ex 13.2 (in text book), making total
latency 11s.
If all blocks are consecutively stored on 1 cylinder:
6.46ms + 8.33ms * 16 = 139ms
(1 average seek)
(time per rotation)
(# rotations)
13.3.3 Using Multiple Disks
If we have n disks, read/write performance will
increase by a factor of n.
Striping – distributing a relation across multiple
disks following this pattern:
Data on disk R1: R1, R1+n, R1+2n,…
Data on disk R2: R2, R2+n, R2+2n,…
…
Data on disk Rn: Rn, Rn+n, Rn+2n, …
 Ex 13.5: We request 1024 blocks with n = 4.
6.46ms + (8.33ms * (16/4)) = 39.8ms
(1 average seek) (time per rotation) (# rotations)
13.3.4 Mirroring Disks
Mirroring Disks – having 2 or more disks hold
identical copied of data.
Benefit 1: If n disks are mirrors of each other,
the system can survive a crash by n-1 disks.
Benefit 2: If we have n disks, read
performance increases by a factor of n.
Performance increases further by having the
controller select the disk which has its head
closest to desired data block for each read.
13.3.5 Disk Scheduling and the
Elevator Problem
Disk controller will run this algorithm to select
which of several requests to process first.
Pseudo code:
requests[] // array of all non-processed data requests
upon receiving new data request:
requests[].add(new request)
while(requests[] is not empty)
move head to next location
if(head location is at data in requests[])
 retrieve data
 remove data from requests[]
if(head reaches end)
 reverse head direction
13.3.5 Disk Scheduling and the
Elevator Problem (con’t)
Events:
Head starting point
Request data at 8000
Request data at 24000
Request data at 56000
Get data at 8000
Request data at 16000
Get data at 24000
Request data at 64000
Get data at 56000
Request Data at 40000
Get data at 64000
Get data at 40000
Get data at 16000
64000
56000
48000
40000
32000
24000
16000
8000
Current time
13.6
26.9
34.2
45.5
56.8
4.3
10
20
30
0
data
time
8000..
4.3
24000..
13.6
56000..
26.9
64000..
34.2
40000..
45.5
16000..
56.8
13.3.5 Disk Scheduling and the
Elevator Problem (con’t)
Elevator
Algorithm
data
time
FIFO
Algorithm
data
time
8000..
4.3
8000..
4.3
24000..
13.6
24000..
13.6
56000..
26.9
56000..
26.9
64000..
34.2
16000..
42.2
40000..
45.5
64000..
59.5
16000..
56.8
40000..
70.8
Disk Scheduler Algorithm
• In short the disk scheduler algorithm can be
defined as , the controller we serve the
requests in the order of the moving i.e., if the
request which approached is in the same
direction of movement the controller finishes
the task.
13.3.6 Prefetching and Large-Scale
Buffering
If at the application level, we can predict the
order blocks will be requested, we can load
them into main memory before they are
needed.
13.4.Disk Failures
-Disk failure ways and their
mitigation
Ways in which disks can fail
• Intermittent failure
• Media Decay
• Write failure
• Disk Crash
Intermittent Failures.
• Read or write operation on a sector successful
not on first try, but after repeated tries.
• The most common type of failure.
• Parity checks can be used to detect this kind
of failure.
Media Decay.
• Serious form of failure.
• Bit/Bits are permanently corrupted.
• Impossible to read a sector correctly even
after many trials.
• Stable storage technique for organizing a disk
is used to avoid this failure.
Write failure
• Attempt to write a sector is not possible.
• Attempt to retrieve previously written sector is
unsuccessful.
• Possible reason – power outage while writing of
the sector.
• Stable Storage Technique can be used to avoid
this.
Disk Crash
• Most serious form of disk failure.
• Entire disk becomes unreadable, suddenly and
permanently.
• RAID techniques can be used for coping with
disk crashes.
More on Intermittent failures…
• When we try to read a sector, but the correct
content of that sector is not delivered to the
disk controller.
• If the controller has a way to tell that the
sector is good or bad (checksums), it can then
reissue the read request when bad data is
read.
More on Intermittent Failures..
• The controller can attempt to write a sector, but
the contents of the sector are not what was
intended.
• The only way to check this is to let the disk go
around again read the sector.
• One way to perform the check is to read the
sector and compare it with the sector we intend
to write.
Contd..
• Instead of performing the complete
comparison at the disk controller, simpler way
is to read the sector and see if a good sector
was read.
• If it is good sector, then the write was correct
otherwise the write was unsuccessful and
must be repeated.
Checksums.
• Technique used to determine the good/bad
status of a sector.
• Each sector has some additional bits called the
checksum that are set depending on the
values of the data bits in that sector.
• If checksum is not proper on reading, then
there is an error in reading.
Checksums(contd..)
• There is a small chance that the block was not
read correctly even if the checksum is proper.
• The probability of correctness can be
increased by using many checksum bits.
Checksum calculation.
• Checksum is based on the parity of all bits in the
sector.
• If there are odd number of 1’s among a collection
of bits, the bits are said to have odd parity then a
parity bit ‘1’ is added to the existing bits.
• If there are even number of 1’s then the
collection of bits is said to have even parity. A
parity bit ‘0’ is added to the existing bits .
Checksum calculation(contd..)
• The number of 1’s among a collection of bits
and their parity bit is always even.
• During a write operation, the disk controller
calculates the parity bit and append it to the
sequence of bits written in the sector.
• Every sector will have a even parity.
Examples…
• A sequence of bits 01101000 has odd number of
1’s. The parity bit will be 1. So the sequence with
the parity bit will now be 011010001. Now the
parity becomes even.
• A sequence of bits 11101110 will have an even
parity as it has even number of 1’s. So with the
parity bit 0, the sequence will be 111011100. As
we have added 0 the parity still be even.
Checksum calculation(contd..)
• Any one-bit error in reading or writing the
bits results in a sequence of bits that has oddparity.
• The disk controller can count the number of
1’s and can determine if the sector has odd
parity in the presence of an error.
Odds.
• There are chances that more than one bit can
be corrupted and the error can be unnoticed.
• Increasing the number of parity bits can
increase the chances of detecting errors.
• In general, if there are n independent bits as
checksum, the chance of error will be one in
2n.
Stable Storage
• Background:
– Checksums can detect the error but cannot correct it.
– Sometimes we overwrite the previous contents of a
sector and yet cannot read the new contents
correctly.
• To deal with these problems, Stable Storage
policy can be implemented on the disks.
Stable-Storage(contd..)
• Sectors are paired and each pair represents
one sector-contents X.
• The left copy of the sector may be
represented as XL and XR as the right copy.
Assumptions.
• We assume that copies are written with
sufficient number of parity bits to decrease
the chance of bad sector looks good when the
parity checks are considered.
• Also, If the read function returns a good value
w for either XL or XR, then it is assumed that w
is the true value of X.
Stable -Storage Writing Policy
1. Write the value of X into XL
2. Check the value has status “good”; i.e., the
parity-check bits are correct in the written copy.
If not repeat write.
3. If after a set number of write attempts, we have
not successfully written X in XL, assume that
there is a media failure in this sector.
4. A fix-up such as substituting a spare sector for XL
must be adopted.
5. Repeat (1) for XR.
Stable-Storage Reading Policy:
• The policy is to alternate trying to read XL and
XR until a good value is returned.
• If a good value is not returned after pre
chosen number of attempts, then it is
assumed that X is truly unreadable.
Error-Handling capabilities:
Media failures:
• After storing X in sectors XL and XR, if one of
them undergoes media failure and becomes
permanently unreadable, we can read from the
second one.
• If both the sectors have failed to read, then
sector X cannot be read.
• The probability of both failing is extremely small.
Error-Handling Capabilities(contd..)
Write Failure:
• When writing X, if there is a system failure(like
power shortage), the X in the main memory is
lost and the copy of X being written will be
erroneous.
• Half of the sector may be written with part of
new value of X, while the other half remains as it
was.
Error-Handling Capabilities(contd..)
 The possible cases when the system becomes
available:
1. The failure occurred when writing to XL. Then XL
is considered bad. Since XR was never changed,
its status is good. We can make a copy of XR into
XL, which is the old value of X.
2. The failure occurred after XL is written. Then XL
will have the good status and XR which has the
old value of XR has bad status. We can copy the
new value of X to XR from XL.
Recovery from Disk Crashes.
• To reduce the data loss by Dish crashes, schemes
which involve redundancy, extending the idea of
parity checks or duplicate sectors can be applied.
• The term used for These strategies are called
RAID or Redundant Arrays of Independent Disks.
• In general, if the mean time to failure of disks is n
years, then in any given year, 1/nth of the
surviving disks fail.
Recovery from Disk Crashes(contd..)
 Each of the RAID schemes has data disks and redundant
disks.
 Data disks are one or more disks that hold the data.
 Redundant disks are one or more disks that hold
information that is completely determined by the contents
of the data disks.
 When there is a disk crash of either of the disks, then the
other disks can be used to restore the failed disk to avoid a
permanent information loss.
Disk Failures
Xiaqing He
ID: 204
Dr. Lin
Content
1)Focus on :
“How to recover from disk crashes”
common term RAID
“redundancy array of independent disks”
2)Several schemes to recover from disk crashes:
 Mirroring—RAID level 1;
 Parity checks--RAID 4;
 Improvement--RAID 5;
 RAID 6;
1) Mirroring
• The simplest scheme to recovery from Disk
Crashes
• How does Mirror work?
-- making two or more copied of the data on
different disks
• Benefit:
-- save data in case of one disk will fail;
-- divide data on several disks and let access to
several blocks at once
1) Mirroring (con’t)
 The simplest scheme is to mirror each disk. One disk is data disk and other
is redundant disk
 For mirroring, when the data can be lost?
-- the only way data can be lost if there is a second (mirror/redundant) disk
crash while the first (data) disk crash is being repaired.
Now both copies of at least part of the data have been lost, there is no
way to recover
 Possibility:
Suppose:
• One disk: mean time to failure = 10 years;
• One of the two disk: average of mean time to failure = 5 years;
• The process of replacing the failed disk= 3 hours=1/2920 year;
So:
• the possibility of the mirror disk will fail=1/10 * 1/2,920 =1/29,200;
• The possibility of data loss by mirroring: 1/5 * 1/29,200 = 1/146,000
2)Parity Blocks
 why changes?
-- disadvantages of Mirroring:
redundant disks
uses so many
 What’s new?
-- RAID level 4: uses only one redundant disk, no
matter how may disks they are
 How this one redundant disk works?
-- modulo-2 sum;
-- the jth bit of the redundant disk is the modulo-2 sum
of the jth bits of all the data disks.
 Example
2)Parity Blocks(con’t)___Example
Consider following bit sequences
Data disks:
• Disk1: 11110000
• Disk2: 10101010
• Disk3: 00111000
Now the Redundant disk:
• Disk4: 01100010
2)RAID 4 (con’t)
 Reading
-- Similar with reading blocks from any disk;
 Writing
 When we write a new block, do the following
1)change the block;
2)change the corresponding block of the redundant disk;
• Why?
-- hold the parity checks for the corresponding blocks of
all the data disks
2)RAID 4 (con’t) _ writing
For a total N data disks:
1) naïve way:
• read N data disks and compute the modulo-2 sum of the
corresponding blocks;
• rewrite the redundant disk according to modulo-2 sum of
the data disks;
2) better way:
• Take modulo-2 sum of the old and new version of the data
block which was rewritten;
• Change the position of the redundant disk which was 1’s in
the modulo-2 sum;
2)RAID 4 (con’t) _ writing_Example

•
•
•
Data disks:
Disk1: 11110000
Disk2: 10101010  01100110
Disk3: 00111000
 to do:
• Modulo-2 sum of the old and new version of disk 2: 11001100
• So, we need to change the positions 1,2,5,6 of the redundant disk.
 Redundant disk:
• Disk4: 01100010  10101110
2)RAID 4 (con’t) _failure recovery
 Redundant disk crash:
-- swap a new one and recomputed data from all the data disks;
 One of Data disks crash:
-- swap a new one;
-- recomputed data from the other disks including data disks and
redundant disk;
How to recomputed? (same rule, that’s why there will be some
improvement)
-- A bit in any position is the modulo-2 sum of all the corresponding
bits of all the other disks
3) An Improvement: RAID 5
 Why need a improvement?
-- Shortcoming of RAID level 4: suffers from a bottleneck defect (when
updating data disk need to read and write the redundant disk);
 Principle of RAID level 5 (RAID 5):
-- treat each disk as the redundant disk for some of the blocks;
 Why it is feasible?
The rule of failure recovery for redundant disk and data disk is the same:
“take modulo-2 sum of all the corresponding bits of all the other disks”
So, there is no need to retreat one as redundant disk and others as data disks
3) RAID 5 (con’t)
 How to recognize which blocks of each disk treat this disk as
redundant disk?
-- if there are n+1 disks which were labeled from
0 to N, then we can treat the i cylinder of disk
J as redundant if J is the remainder when I is
divided by n+1;
th
Example:
3) RAID 5 (con’t)_example
•
•
•
•
N=3;
The first disk, labeled as 0 : 4,8,12…;
The second disk, labeled as 1 : 1,5,9…;
The third disk, labeled as 2 : 2,6,10…;
……….
Suppose all the 4 disks are equally likely to be written,
for one of the 4 disks, the possibility of being written:
• 1/4 + 3 /4 * 1/3 =1/2
• If N=m => 1/m +(m-1)/m * 1/(m-1) = 2/m
4) Coping with multiple disk crashes
 RAID 6
– deal with any number of disk crashes if using enough
redundant disks
The following example based on simplest error correcting
code known as hamming code
 Example
a system of seven disks ( four data disks_numer 1-4 and 3
redundant disks_ number 5-7);
• How to set up this 3*7 matrix ?
(why is 3? – there are 3 redundant disks)
1)every column values three 1’s and 0’s except for all three 0’s;
2) column of the redundant disk has single 1’s;
3) column of the data disk has at least two 1’s;
4) Coping with multiple disk crashes (con’t)
Reading:
• read form the data disks and ignore the
redundant disk
Writing:
• Change the data disk
• change the corresponding bits of all the
redundant disks
4) Coping with multiple disk crashes (con’t)
 In those system which has 4 data disks and 3 redundant
disk, how they can correct up to 2 disk crashes?
• Suppose disk a and b failed:
• find some row r (in 3*7 matrix)in which the column for
a and b are different (suppose a is 0’s and b is 1’s);
• Compute the correct b by taking modulo-2 sum of the
corresponding bits from all the other disks other than b
which have 1’s in row r;
• After getting the correct b, Compute the correct a with
all other disks available;
 Example
4) Coping with
(con’t)_example
multiple
disk
crashes
3*7 matrix
data disk
disk number
1
2
3
redundant disk
4
5
6
7
1
1
1
0
1
0
0
1
1
0
1
0
1
0
1
0
1
1
0
0
1
4) Coping with multiple disk crashes
(con’t)_example
First block of all the disks
disk
1)
2)
3)
4)
5)
6)
7)
contents
11110000
10101010
00111000
01000001
01100010
00011011
10001001
4) Coping with multiple disk crashes
(con’t)_example
Two disks crashes;
disk
1)
2)
3)
4)
5)
6)
7)
contents
11110000
?????????
00111000
01000001
?????????
00011011
10001001
4) Coping with multiple disk crashes
(con’t)_example
In that 3*7 matrix, find in row 2, disk 2 and 5 have different
value and disk 2’s value is 1 and 5’s value is 0.
so: compute the first block of disk 2 by modulo-2 sum of all
the corresponding bits of disk 1,4,6; i.e., bitwise sum of 1, 4, 6
then compute the first block of disk 2 by modulo-2 sum of
all the corresponding bits of disk 1,2,3;
1)
2)
3)
4)
5)
6)
7)
11110000
????????? => 00001111
00111000
01000001
????????? => 01100010
00011011
10001001
13.5 Arranging data on disk
Data elements are represented as records, which stores
in consecutive bytes in same same disk block.
Basic layout techniques of storing data :
Fixed-Length Records: The simplest sort of record
consists of fixed length fields
Allocation criteria - data should start at word boundary.
All fields must start at a byte that is a multiple of four
Fixed Length record header consists of
1. A pointer to record schema.
2. The length of the record.
3. Timestamps to indicate last modified or last read.
Example
CREATE TABLE employee(
name CHAR(30) PRIMARY KEY,
address VARCHAR(255),
gender CHAR(1),
birthdate DATE
);
Data should start at word boundary and contain header and four fields
name, address, gender and birthdate.
However as the size should be multiple of 4, we used 32 bits for name
instead of 30 bits
Packing Fixed-Length Records into Blocks :
Records are stored in the form of blocks on the disk and they
move into main memory when we need to update or access
them.
A block header is written first, and it is followed by series of
blocks.
Block header contains the following information :
 Links to one or more blocks that are part of a network of
blocks.
 Information about the role played by this block in such a
network.
 Information about the relation, the tuples in this block
belong to.
 A "directory" giving the offset of each record in the block.
 Time stamp(s) to indicate time of the block's last
modification and/or access.
Example
Along with the header we can pack as many record as we
can in one block as shown in the figure and remaining
space will be unused.
However if we do not know how many records are
contained in the block, we use the initial part of block to
represent the pointers to the record, and the end of the
block we store the records
13.6 Representing Block and
Record Addresses
Ramya Karri
Introduction
• Address of a block and Record
– In Main Memory
• Address of the block is the virtual memory address of the first byte
• Address of the record within the block is the virtual memory address
of the first byte of the record
– In Secondary Memory: sequence of bytes describe the
location of the block in the overall system
• Sequence of Bytes describe the location of the
block : the device Id for the disk, Cylinder
number, etc.
Addresses in Client-Server Systems
• The addresses in address space are represented in two
ways
– Physical Addresses: byte strings that determine the place within
the secondary storage system where the record can be found.
– Logical Addresses: arbitrary string of bytes of some fixed length
• Physical Address bits are used to indicate:
–
–
–
–
–
Host to which the storage is attached
Identifier for the disk
Number of the cylinder
Number of the track
Offset of the beginning of the record
ADDRESSES IN CLIENT-SERVER SYSTEMS (CONTD..)
• Map Table relates logical addresses to physical
addresses.
Logical
Physical
Logical Address
Physical Address
Logical and Structured Addresses
• Purpose of logical address?
• Gives more flexibility, when we
– Move the record around within the block
– Move the record to another block
Unused
• Gives us an option of
deciding what to do
Recor Recor
when a record is deleted? Recor
d4
d3
d2
Offset table
Header
Recor
d1
Pointer Swizzling
• Having pointers is common in an objectrelational database systems
• Important to learn about the management of
pointers
• Every data item (block, record, etc.) has two
addresses:
– database address: address on the disk
– memory address, if the item is in virtual memory
Pointer Swizzling (Contd…)
• Translation Table: Maps database address to memory
address
Dbaddr
Mem-addr
Database address
Memory Address
• All addressable items in the database have entries
in
the map table, while only those items currently in
memory are mentioned in the translation table
Pointer Swizzling (Contd…)
• Pointer consists of the following two fields
– Bit indicating the type of address
– Database or memory address
– Example 13.17
Disk
Memory
Swizzled
Block 1
Block 1
Unswizzled
Block 2
Example 13.7
• Block 1 has a record with pointers to a second
record on the same block and to a record on
another block
• If Block 1 is copied to the memory
– The first pointer which points within Block 1 can
be swizzled so it points directly to the memory
address of the target record
– Since Block 2 is not in memory, we cannot swizzle
the second pointer
Pointer Swizzling (Contd…)
• Three types of swizzling
– Automatic Swizzling
• As soon as block is brought into memory, swizzle all
relevant pointers.
– Swizzling on Demand
• Only swizzle a pointer if and when it is actually
followed.
– No Swizzling
• Pointers are not swizzled they are accesses using the
database address.
Programmer Control of Swizzling
• Unswizzling
– When a block is moved from memory back to disk,
all pointers must go back to database (disk)
addresses
– Use translation table again
– Important to have an efficient data structure for
the translation table
Pinned records and Blocks
• A block in memory is said to be pinned if it
cannot be written back to disk safely.
• If block B1 has swizzled pointer to an item in
block B2, then B2 is pinned
– Unpin a block, we must unswizzle any pointers to it
– Keep in the translation table the places in memory
holding swizzled pointers to that item
– Unswizzle those pointers (use translation table to
replace the memory addresses with database (disk)
addresses
Variable Length Data and
Records
Chap: 13.7
Topics
Records with Variable Length Fields
Records with Repeating Fields
Variable Format Records
Records that do not fit in a block
BLOBS
Example
name
0
address
30
gender
286
birth date
287
Fig 1 : Movie star record with four fields
•
•
•
Above is a block representing a movie star
Size of attributes are name – 30 bits
Address – 256 bits, gender – 1 bit, birth date – 10 bits
297
Records with Variable Fields
An effective way to represent variable length
records is as follows
 Fixed length fields are kept ahead of the
variable length fields
Record header contains
• Length of the record
• Pointers to the beginning of all variable
length fields except the first one.
Records with Variable Length Fields
header information
record length
Pointer to
address
gender
birth date
name
address
Figure 2 : A Movie Star record with name and address implemented as
variable length character strings
Records with Repeating Fields
Records contains variable number of occurrences of a field F
All occurrences of field F are grouped together and the record
header contains a pointers to the first occurrence of field F
 L bytes are devoted to one instance of field F
 Locating an occurrence of field F within the record
• Add to the offset for the field F which are the integer
multiples of L starting with 0 , L ,2L,3L and so on to locate
•We can stop upon reaching the offset of the field F.
Records with Repeating Fields
other header information
record length
to address
to movie pointers
name
address
pointers to movies
Figure 3 : A record with a repeating group of references to movies
Records with Repeating Fields
record header
information
address
to name
length of name
to address
length of address
to movie references
number of references
name
Figure 4 : Storing variable-length fields separately from the record
Records with Repeating Fields
Advantage
 Keeping the record itself fixed length allows record to be
searched more efficiently, minimizes the overhead in the block
headers, and allows records to be moved within or among the
blocks with minimum effort.
Disadvantage
 Storing variable length components on another block increases
the number of disk I/O’s needed to examine all components of a
record.
Records with Repeating Fields
A compromise strategy is to allocate a fixed portion of
the record for the repeating fields
 If the number of repeating fields is lesser than
allocated space, then there will be some unused space
 If the number of repeating fields is greater than
allocated space, then extra fields are stored in a
different location and
Pointer to that location and count of additional
occurrences is stored in the record
Variable Format Records
 These are records that do not have fixed schema
 Variable format records are represented by sequence of
tagged fields
 Each of the tagged fields consist of below information
• Attribute or field name
• Type of the field
• Length of the field
• Value of the field
 Why use tagged fields
• Information – Integration applications
• Records with a very flexible schema
Variable Format Records
code for name
code for string type
length
N
S
14
Clint Eastwood
code for restaurant owned
code for string type
length
R
S
16
Fig 5 : A record with tagged fields
Hog’s Breath Inn
Records that do not fit in a block
What happens when the record does not fit in the block?
When the length of a record is greater than block size then
then record is divided and placed into two or more blocks
 Portion of the record in each block is referred to as a
RECORD FRAGMENT
 Record with two or more fragments is called
SPANNED RECORD
 Record that do not cross a block boundary is called
UNSPANNED RECORD
Spanned Records
Spanned records require the following extra header
information
• A bit indicates whether it is fragment or not
• A bit indicates whether it is first or last fragment of
a record
• Pointers to the next or previous fragment for the
same record
Records that do not fit in a block
block header
record header
record 1
block 1
record
2-a
record
2-b
record 3
block 2
Figure 6 : Storing spanned records across blocks
BLOBS
 Large binary objects are called BLOBS (Binary
Large Objects)
e.g. : audio files, video files
Storage of BLOBS: Stored on a sequence of
blocks
Retrieval of BLOBS: when the client wants a
record the block containing the record is passed
from the database server to the client
Record Modifications
Chapter 13
Section 13.8
111
Modification types
 Three operations are done on a record
• Insertion
• Deletion
• Update
112
Insertion
 If the records in relation have no particular order
Records can be placed in a block with empty space or in a new block.
if the records in relation are in fixed order,
 Check if Space available in the block
 No space available in the block (outside the block)
Structured address
Pointer to a record from outside the block.
113
Insertion in fixed order
If space available is available within the block
 Use of an offset table in the header of each block with pointers to the location of
each record in the block.
 The records are slid within the block and the pointers in the offset table are
adjusted.
Offset
table
header
unused
Record 4
Record 3
Record 2
Record 1
114
Insertion in fixed order
No space available within the block (outside the block)

Find space on a “nearby” block.
•
•

In case of no space available on a block, look at the following block in sorted order of
blocks.
If space is available in that block ,move the highest records of first block 1 to block 2
and slide the records around on both blocks.
Create an overflow block
•
•
•
Records can be stored in overflow block.
Each block has place for a pointer to an overflow block in its header.
The overflow block can point to a second overflow block as shown below.
Block
B
Overflow
block for B
115
Deletion
 Recover space after deletion
 When using an offset table, the records can be slid around the block so there
will be an unused region in the center that can be recovered.
 Using this approach we need not know the exact number of records prior to
creating block
 In case we cannot slide records, an available space list can be maintained in
the block header.
 The list head goes in the block header and available regions hold the links in
the list.
116
Deletion

Use of tombstone

The tombstone is placed in a record in order to avoid pointers to the deleted
record to point to new records.

The tombstone is permanent until the entire database is reconstructed.

If pointers go to fixed locations from which the location of the record is
found then we put the tombstone in that fixed location. (See examples)

Where a tombstone is placed depends on the nature of the record pointers.

Map table is used to translate logical record address to physical address.
117
Deletion

Use of tombstone

If we need to replace records by tombstones, place the bit that serves as the
tombstone at the beginning of the record.

This bit remains the record location and subsequent bytes can be reused for
another record
Record 1
Record 2
Record 1 can be replaced, but the tombstone remains, record 2 has no
tombstone and can be seen when we follow a pointer to it.
118
Update
 Fixed Length update
No effect on storage system as it occupies same space as
before update.
 Variable length update
 Longer length
 Short length
119
Update
Variable length update (longer length)
If the updated record is longer than the old version,
then we may need to create more space on its
block
 Stored on the same block:
 Sliding records
 Creation of overflow block.
 Stored on another block
 Move records around that block
 Create a new block for storing variable length fields.
120
Update
Variable length update (Shorter length)
 Same as deletion
 Recover space
 Consolidate space.
121
BTrees & Bitmap Indexes
14.2
B Trees ►►
Structure
• A balanced tree, meaning that all paths from the
leaf node have the same length.
• There is a parameter n associated with each Btree
block. Each block will have space for n searchkeys
and n+1 pointers.
• The root may have only 1 parameter, but all other
blocks most be at least half full.
Structure
● A typical node
is shown in the figure
● a typical interior
node would have
pointers pointing to
leaves with out values
● a typical leaf would
have pointers point
to records
N search keys
N+1 pointers
Application
• The search key of the Btree is the primary key
for the data file.
• Data file is sorted by its primary key.
• Data file is sorted by an attribute that is not a
key,and this attribute is the search key for the
Btree.
Lookup
If at an interior node, choose the correct pointer to use. This is
done by comparing keys to search value.
If in the following example is key is less than 57 choose the left
tree
Lookup
If at a leaf node, choose the key that matches
what
you are looking for and the pointer for that leads
to the data.
Insertion
• When inserting, choose the correct leaf node to
put pointer to data.
• If node is full, create a new node and split keys
between the two.
• Recursively move up, if cannot create new
pointer to new node because full, create new
node.
• This would end with creating a new root node,
if the current root was full.
Deletion
Perform lookup to find node to delete and delete
it.
If node is no longer half full, perform join on
adjacent node and recursively delete up, or key
move if that node is full and recursively
change pointer up.
Efficiency
Btrees allow lookup, insertion, and deletion of
records using very few disk I/Os.
To reach the required node, we require one
read at each level. Then you would follow the
pointer of that to the next or final read.
Efficiency
Three levels are sufficient for Btrees. Having each block have
255 pointers, 255^3 is about 16.6 million.
You can even reduce disk I/Os by keeping a level of a Btree in
main memory. Keeping the first block with 255 pointers would
reduce the reads to 2, and even possible to keep the next 255
pointers in memory to reduce reads to 1.
BTrees & Bitmap Indexes
14.7
Bitmap Indexes ►►
Definition
A bitmap index for a field F is a collection of
bit-vectors of length n, one for each possible
value that may appear in that field F.[1]
What does that mean?
• Assume relation R
with
– 2 attributes A and
B.
– Attribute A is of
type Integer and B
is of type String.
– 6 records,
numbered 1
through 6 as
shown.
A
B
1
30
foo
2
30
bar
3
40
baz
4
50
foo
5
40
bar
6
30
baz
Example Continued…
• A bitmap for attribute B is:
Value
foo
bar
baz
Vector
100100
010010
001001
A
B
1
30
foo
2
30
bar
3
40
baz
4
50
foo
5
40
bar
6
30
baz
Where do we reach?
• A bitmap index is a special kind of database
index that uses bitmaps.[2]
• Bitmap indexes have traditionally been
considered to work well for data with less
number of attributes such as gender, which has
a small number of distinct values, e.g., male
and female, but many occurrences of those
values.[2]
A little more…
• A bitmap index for attribute A of relation R is:
– A collection of bit-vectors
– The number of bit-vectors = the number of distinct
values of A in R.
– The length of each bit-vector = the cardinality of R.
– The bit-vector for value v has 1 in position i, if the ith
record has v in attribute A, and it has 0 there if not.[3]
– Eg: if B has foo in 1 and 4 we have bitmap as 1001000
• Records are allocated permanent numbers.[3]
• There is a mapping between record numbers and record
addresses.[3]
Motivation for Bitmap Indexes
• Very efficient when used for partial match queries.[3]
• They offer the advantage of buckets [2]
– Where we find tuples with several specified
attributes without first retrieving all the record that
matched in each of the attributes.
• They can also help answer range queries [3]
Another Example
Multidimensional Array of multiple types
{(5,d),(79,t),(4,d),(79,d),(5,t),(6,a)}
5
79
4
6
d
t
a
= 100010
= 010100
= 001000
= 000001
= 101100
= 010010
= 000001
Example Continued…
{(5,d),(79,t),(4,d),(79,d),(5,t),(6,a)}
Searching for items is easy, just AND together.
To search for (5,d)
5 = 100010
d = 101100
100010 AND 101100 = 100000
The location of the
record has been traced!
Compressed Bitmaps
• Assume:
– The number of records in R are n
– Attribute A has m distinct values in R
• The size of a bitmap index on attribute A is m*n.
• If m is large, then the number of 1’s will be around 1/m.
– Opportunity to encode
• A common encoding approach is called run-length
encoding.[1]
Run-length encoding
•
Represents runs
– A run is a sequence of i 0’s followed by a 1, by some suitable binary encoding
of the integer i.
•
A run of i 0’s followed by a 1 is encoded by:
– First computing how many bits are needed to represent i, Say k
– Then represent the run by k-1 1’s and a single 0 followed by k bits which
represent i in binary.
– The encoding for i = 1 is 01. k = 1
– The encoding for i = 0 is 00. k = 1
•
We concatenate the codes for each run together, and the sequence of bits is the
encoding of the entire bit-vector
Understanding with an Example
• Let us decode the sequence 11101101001011
• Staring at the beginning (left most bit):
– First run: The first 0 is at position 4, so k = 4. Take next 4 bits are 1101,
so we know that the first integer is i = 13
– Second run: 001011
• k = 1; because the zero is at first digit
• i=0
– Last run: 1011
• k = 2, because the zero is at position 2
• i=3
• Our entire run length is thus 13,0,3, hence our bit-vector is:
0000000000000110001
Managing Bitmap Indexes
1) How do you find a specific bit-vector for a
value efficiently?
2) After selecting results that match, how do you
retrieve the results efficiently?
3) When data is changed, do you you alter
bitmap index?
1) Finding bit vectors
– Think of each bit-vector as a key to a value.[1]
– Any secondary storage technique will be efficient
in retrieving the values.[1]
– Create secondary key with the attribute value as a
search key [3]
• Btree
• Hash
2) Finding Records
• Create secondary key with the record number as a search key
[3]
• Or in other words,
– Once you learn that you need record k, you can
create a secondary index using the kth position as
a search key.[1]
3) Handling Modifications
Two things to remember:
Record numbers must remain fixed once assigned
Changes to data file require changes to bitmap index
Deletion
–Tombstone replaces deleted record
–Corresponding bit is set to 0
Insertion
–Record assigned the next record number.
–A bit of value 0 or 1 is appended to each bit
vector
–If new record contains a new value of the
attribute, add one bit-vector.
Modification
–Change the bit corresponding to the old
value of the modified record to 0
–Change the bit corresponding to the new
value of the modified record to 1
–If the new value is a new value of A, then
insert a new bit-vector.
Query Execution
One-Pass Algorithms for Database Operations (15.2)
153
Introduction (Updated)
• The choice of an algorithm for each operator is an essential part of
the process of transforming a logical query plan into a physical
query plan.
• Main classes of Algorithms:
– Sorting-based methods
– Hash-based methods
– Index-based methods
• Division based on degree difficulty and cost:
– 1-pass algorithms – These involve reading the data only once from
disk; these work only when at least one of the arguments of the
operation fits in the main memory
– 2-pass algorithms - These work for data that is too long to fit in the
available main memory
– 3 or more pass algorithms – These work without any limit on the size
of the data
154
One-Pass Algorithm Methods
• Tuple-at-a-time, unary operations: (selection & projection)
– Have algorithms regardless of whether the relation fits in the memory
• Full-relation, unary operations
– Require seeing all or most of the tuples in memory at once
• Full-relation, binary operations (set & bag versions of union)
– All operations are in this class; set an bag versions of union,
intersection, difference, joins, and products
155
One-Pass Algorithms for Tuple-at-a-Time
Operations
• Tuple-at-a-time operations are selection and projection
– read the blocks of R one at a time into an input buffer
– perform the operation on each tuple
– move the selected tuples or the projected tuples to the output
buffer
• The disk I/O requirement for this process depends only on
how the argument relation R is provided.
– If R is initially on disk, then the cost is whatever it takes to
perform a table-scan or index-scan of R.
156
A selection or projection being performed
on a relation R
157
One-Pass Algorithms for Unary, fill-Relation
Operations
• Duplicate Elimination
– To eliminate duplicates, we can read each block of R one at
a time, but for each tuple we need to make a decision as to
whether:
1.
2.
It is the first time we have seen this tuple, in which case we
copy it to the output, or
We have seen the tuple before, in which case we must not
output this tuple.
– One memory buffer holds one block of R's tuples, and the
remaining M - 1 buffers can be used to hold a single copy of
every tuple.
158
Managing memory for a one-pass
duplicate-elimination
159
Duplicate Elimination
• When a new tuple from R is considered, we compare it with all
tuples seen so far
– if it is not equal: we copy both to the output and add it to the inmemory list of tuples we have seen.
– if there are n tuples in main memory: each new tuple takes processor
time proportional to n, so the complete operation takes processor
time proportional to n2.
• We need a main-memory structure that allows each of the
operations:
– Add a new tuple, and
– Tell whether a given tuple is already there
– When storing the already seen tuples, we must be careful about
the main memory data structure we use
160
Duplicate Elimination (…contd.)
• The different structures that can be used for such main
memory structures are:
– Hash table
– Balanced binary search tree
• Each of these structures has some space overhead in
addition to the space needed to store the tuples
161
One-Pass Algorithms for Unary, fill-Relation
Operations
• Grouping
– The grouping operation gives us zero or more grouping
attributes and presumably one or more aggregated attributes
– If we create in main memory one entry for each group then we
can scan the tuples of R, one block at a time.
– The entry for a group consists of values for the grouping
attributes and an accumulated value or values for each
aggregation.
162
Grouping
• The accumulated value is:
– For MIN(a) or MAX(a) aggregate, record minimum
/maximum value, respectively.
– Note: Change this minimum or maximum if appropriate
each time a tuple of the group is seen
– For any COUNT aggregation, add 1 for each tuple of group.
– For SUM(a), add value of attribute a to the accumulated
sum for its group.
– AVG(a) is a hard case. We must maintain 2 accumulations:
count of no. of tuples in the group & sum of a-values of
these tuples. Each is computed as we would for a COUNT &
SUM aggregation, respectively. After all tuples of R are seen,
take quotient of sum & count to obtain average.
163
One-Pass Algorithms for Binary Operations
• Binary operations include:
–
–
–
–
–
Union
Intersection
Difference
Product
Join
• Bag union can be computed by a simple one pass algorithm,
to compute RUS, we copy each tuple of R to the output and
then copy tuple of S
164
Set Union
• We read S into M - 1 buffers of main memory and build a
search structure where the search key is the entire tuple.
• All these tuples are also copied to the output.
• Read each block of R into the Mth buffer, one at a time.
• For each tuple t of R, see if t is in S, and if not, we copy t to
the output. If t is also in S, we skip t.
165
Set Intersection
• Read S into M - 1 buffers and build a search structure with
full tuples as the search key.
• Read each block of R, and for each tuple t of R, see if t is
also in S. If so, copy t to the output, and if not, ignore t.
166
Set Difference
• Read S into M - 1 buffers and build a search structure with full
tuples as the search key.
• To compute R -s S, read each block of R and examine each tuple
t on that block. If t is in S, then ignore t; if it is not in S then copy
t to the output.
• To compute S -s R, read the blocks of R and examine each tuple t
in turn. If t is in S, then delete t from the copy of S in main
memory, while if t is not in S do nothing.
• After considering each tuple of R, copy to the output those
tuples of S that remain.
167
Bag Intersection (modified)
• Read S into M - 1 buffers.
• Multiple copies of a tuple t are not stored individually. Rather
store 1 copy of t & associate with it a count equal to no. of
times t occurs.
• Next, read each block of R, & for each tuple t of R see whether t
occurs in S. If not ignore t; it cannot appear in the intersection.
• If t appears in S, & count associated with t is (+)ve, then output
t & decrement count by 1. If t appears in S, but count has
reached 0, then do not output t; we have already produced as
many copies of t in output as there were copies in S.
168
Bag Difference
• To compute S -B R, read tuples of S into main memory &
count no. of occurrences of each distinct tuple.
• Then read R; check each tuple t to see whether t occurs in
S, and if so, decrement its associated count. At the end,
copy to output each tuple in main memory whose count is
positive, & no. of times we copy it equals that count.
• To compute R -B S, read tuples of S into main memory &
count no. of occurrences of distinct tuples.
169
Bag Difference (…contd.)
• Think of a tuple t with a count of c as c reasons not to copy t
to the output as we read tuples of R.
• Read a tuple t of R; check if t occurs in S. If not, then copy t to
the output. If t does occur in S, then we look at current count
c associated with t. If c = 0, then copy t to output. If c > 0, do
not copy t to output, but decrement c by 1.
170
Product
• Read S into M - 1 buffers of main memory
• Then read each block of R, and for each tuple t of R
concatenate t with each tuple of S in main memory.
• Output each concatenated tuple as it is formed.
• This algorithm may take a considerable amount of
processor time per tuple of R, because each such tuple
must be matched with M - 1 blocks full of tuples.
However, output size is also large, & time/output tuple is
small.
171
Natural Join
• Convention: R(X, Y) is being joined with S(Y, Z), where Y
represents all the attributes that R and S have in common, X is
all attributes of R that are not in the schema of S, & Z is all
attributes of S that are not in the schema of R. Assume that S
is the smaller relation.
• To compute the natural join, do the following:
1. Read all tuples of S & form them into a main-memory
search structure.
Hash table or balanced tree are good e.g. of such
structures. Use M - 1 blocks of memory for this purpose.
172
Natural Join (…contd.)
2. Read each block of R into 1 remaining main-memory
buffer.
For each tuple t of R, find tuples of S that agree with t on
all attributes of Y, using the search structure.
For each matching tuple of S, form a tuple by joining it
with t, & move resulting tuple to output.
173
Query Execution
15.5 Two-pass Algorithms based on Hashing
At a glimpse
•
•
•
•
•
•
•
•
Introduction
Partitioning Relations by Hashing
Algorithm for Duplicate Elimination
Grouping and Aggregation
Union, Intersection, and Difference
Hash-Join Algorithm
Sort based Vs Hash based
Summary
Requirements
• Number of disk I/O's: 3*B(R)
– B(R) < M(M-1), only then the two-pass, hash-based
algorithm will work
• In order for this to work, we need:
– hash function h evenly distributes the tuples among
the buckets
– each bucket Ri fits in main memory (to allow the onepass algorithm)
– i.e., B(R) ≤ M2
Introduction
Hashing is done if the data is too big to store in main memory
buffers.
– Hash all the tuples of the argument(s) using an appropriate hash
key.
– For all the common operations, there is a way to select the hash
key so all the tuples that need to be considered together when
we perform the operation have the same hash value.
– This reduces the size of the operand(s) by a factor equal to the
number of buckets.
– We perform the operation by working on one bucket at at a time
– If we have M buffers available we can pick M as the number of
buckets, thus gaining a factor of M in the size of the relations.
Partitioning Relations by Hashing
Algorithm:
initialize M-1 buckets using M-1 empty buffers;
FOR each block b of relation R DO BEGIN
read block b into the Mth buffer;
FOR each tuple t in b DO BEGIN
IF the buffer for bucket h(t) has no room for t THEN
BEGIN
copy the buffer t o disk;
initialize a new empty block in that buffer;
END;
copy t to the buffer for bucket h(t);
END ;
END ;
FOR each bucket DO
IF the buffer for this bucket is not empty THEN
write the buffer to disk;
Duplicate Elimination
• Duplication elimination is represented by δ(R)
• For the operation δ(R) hash R to M-1 Buckets.
(Note that two copies of the same tuple t will hash to the same
bucket)
• Do duplicate elimination on each bucket Ri independently,
using one-pass algorithm
• The result is the union of δ(Ri), where Ri is the portion of R
that hashes to the ith bucket
• It eliminates duplicates from each Ri and writes out the
resulting unique tuples
Grouping and Aggregation
 In order to make sure that all tuples of the same group wind
up in the same bucket, we must choose a hash function that
depends only on the grouping attributes of the list L
 Hash all the tuples of relation R to M-1 buckets, using a hash
function that depends only on the grouping attributes
(Note: all tuples in the same group end up in the same bucket)
 Use the one-pass algorithm to process each bucket
independently
 Uses 3*B(R) disk I/O's, requires B(R) ≤ M2
Union, Intersection, and Difference
• For binary operation we use the same hash
function to hash tuples of both arguments.
• R U S we hash both R and S to M-1
• R ∩ S we hash both R and S to 2(M-1)
• R-S we hash both R and S to 2(M-1)
• Requires 3(B(R)+B(S)) disk I/O’s.
• Two pass hash based algorithm requires
min(B(R)+B(S))≤ M2
Hash-Join Algorithm
• Use same hash function for both relations; hash function
should depend only on the join attributes
•
•
•
•
Hash R to M-1 buckets R1, R2, …, RM-1
Hash S to M-1 buckets S1, S2, …, SM-1
Do one-pass join of Ri and Si, for all i
3*(B(R) + B(S)) disk I/O's; min(B(R),B(S)) ≤ M2
Sort based Vs Hash based
• For binary operations, hash-based only limits size
to min of arguments, not sum
• Sort-based can produce output in sorted order,
which can be helpful
• Hash-based depends on buckets being of equal
size
• Sort-based algorithms can experience reduced
rotational latency or seek time
Chapter 15.7
Buffer Management
What does a buffer manager do?
Assume there are M of main-memory buffers needed for
the operators on relations to store needed data.
In practice:
1) rarely allocated in advance
2) the value of M may vary depending on system
conditions
The task of Main memory buffers available to
processes queries that act on the database
Therefore, buffer manager is used to allow processes
to get the memory they need, while minimizing the
delay and unclassifiable requests.
The role of the buffer manager
Read/Writes
Requests
Buffers
Buffer
manager
Figure 1: The role of the buffer manager : responds to requests for
main-memory access to disk blocks
15.7.1 Buffer Management Architecture
Two broad architectures for a buffer manager:
1) The buffer manager controls main memory directly.
• Relational DBMS
2) The buffer manager allocates buffers in virtual memory,
allowing the OS to decide how to use buffers.
• “main-memory” DBMS
• “object-oriented” DBMS
Buffer Pool
Key setting for the Buffer manager to be efficient:
The buffer manager should limit the number of buffers in
use so that they fit in the available main memory, i.e.
they Don’t exceed available space.
The number of buffers is a parameter set when the DBMS is
initialized.
No matter which architecture of buffering is used, we simply
assume that there is a fixed-size buffer pool, a set of
buffers available to queries and other database actions.
Buffer Pool
Page Requests from Higher Levels
BUFFER POOL
disk page
free frame
MAIN MEMORY
DISK
•
•
DB
choice of frame dictated
by replacement policy
Data must be in RAM for DBMS to operate on it!
Buffer Manager hides the fact that not all data is in RAM.
15.7.2 Buffer Management Strategies
Buffer-replacement strategies:
When a buffer is needed for a newly requested
block and the buffer pool is full, what block to
throw out the buffer pool?
Buffer-replacement strategy -- LRU
Least-Recently Used (LRU):
Rule: To throw out the block that has not been read or
written for the longest time.
It requires that the buffer manager maintain a table
indicating the last time the block in each buffer was
accessed
• Requires more maintenance but it is effective.
• Update the time table for every access.
• Least-Recently Used blocks are usually less likely to
be accessed sooner than other blocks.
Buffer-replacement strategy -- FIFO
First-In-First-Out (FIFO):
The buffer that has been occupied the longest by the
same block is emptied and used for the new block.
The buffer manager needs to know only the time at which
the block currently occupying a buffer was loaded into
that buffer
• Requires less maintenance but it can make more
mistakes.
• Keep only the loading time
• The oldest block doesn’t mean it is less likely to be
accessed.
Example: the root block of a B-tree index
Buffer-replacement strategy – “Clock”
The “Clock” Algorithm (“Second Chance”)
Think of the 8 buffers as arranged in a circle, shown as
Figure 3
Flag 0 and 1:
buffers with a 0 flag are ok to sent their contents
back to disk, i.e. ok to be replaced
buffers with a 1 flag are not ok to be replaced
Buffer-replacement strategy – “Clock”
0
0
1
0
the buffer with
a 0 flag will
be replaced
0
0
1
1
Start point to
search a 0 flag
The flag will
be set to 0
By next time the hand
reaches it, if the content of
this buffer is not accessed,
i.e. flag=0, this buffer will
be replaced.
That’s “Second Chance”.
Figure 3: the clock algorithm visits buffers in
a round robin fashion replaces 01 ---1 with
Buffer-replacement strategy -- Clock
a buffer’s flag set to 1 when:
a block is read into a buffer
the contents of the buffer is accessed
a buffer’s flag set to 0 when:
the buffer manager needs a buffer for a new block, it
looks for the first 0 it can find, rotating clockwise. If it
passes 1’s, it sets them to 0.
System Control helps Buffer-replacement strategy
System Control
The query processor or other components of a DBMS can
give advice to the buffer manager in order to avoid some
of the mistakes that would occur with a strict policy such
as LRU,FIFO or Clock.
For example:
A “pinned” block means it can’t be moved to disk without
first modifying certain other blocks that point to it.
In FIFO, use “pinned” to force root of a B-tree to remain
in memory at all times.
15.7.3 The Relationship Between Physical
Operator Selection and Buffer Management
Problem:
Physical Operator expected certain number of
buffers M for execution to be available for
execution.
However, the buffer manager may not be able
to guarantee these M buffers are available.
15.7.3 The Relationship Between Physical
Operator Selection and Buffer Management
Questions:
Can the algorithm adapt to changes of M, the
number of main-memory buffers available?
When available buffers are less than M, and some
blocks have to be put in disk instead of in memory.
How the buffer-replacement strategy impact the
performance (i.e. the number of additional I/O’s)?
Example
FOR each chunk of M-1 blocks of S DO BEGIN
read these blocks into main-memory buffers;
organize their tuples into a search structure whose
search key is the common attributes of R and S;
FOR each block b of R DO BEGIN
read b into main memory;
FOR each tuple t of b DO BEGIN
find the tuples of S in main memory that
join with t ;
output the join of t with each of these tuples;
END ;
END ;
END ;
Figure 15.8: The nested-loop join algorithm
Example
The outer loop number (M-1) depends on the average
number of buffers are available at each iteration.
The outer loop use M-1 buffers and 1 is reserved for a block
of R, the relation of the inner loop.
If we pin the M-1 blocks we use for S on one iteration of the
outer loop, we shall not lose their buffers during the round.
Also, more buffers may become available and then we could
keep more than one block of R in memory.
Will these extra buffers improve the running time?
Example
CASE1: NO
Buffer-replacement strategy: LRU
Buffers for R: k
We read each block of R in order into buffers.
By end of the iteration of the outer loop will be the last k
blocks of R are in buffers.
However, next iteration will start from the beginning of R
again.
Therefore, the k buffers for R will need to be replaced.
Example
CASE 2: YES
Use nested loop join when strategy is LRU
Buffer-replacement strategy: LRU
Buffers for R: k
We read the blocks of R in an order that alternates:
firstlast and then lastfirst.
In this way, we save k disk I/Os on each iteration of the outer
loop except the first iteration.
Other Algorithms and M buffers
Other Algorithms also are impact by M and the
buffer-replacement strategy.
Sort-based algorithm
If M shrinks, we can change the size of a
sublist.
Unexpected result: too many sublists to
allocate each sublist a buffer.
Hash-based algorithm
If M shrinks, we can reduce the number of
buckets, as long as the buckets still can fit in M
buffers.
Chapter 15 Query Execution
15.8 Algorithms using more than two
passes
Intro
• Why we use more than 2 passes
• Multi-pass Sort-based Algorithms
• Conclusion
Reason that we use more than two passes:
Two passes are usually enough, however, for
the largest relation, we use as many passes as
necessary.
Multi-pass Sort-based Algorithms:
Suppose we have M main-memory buffers
available to sort a relation R, which we
assume is stored clustered.
Then we do the following:
BASIS:
If R fits in M blocks (i.e., B(R)<=M)
1. Read R into main memory.
2. Sort it using any main-memory
sorting algorithm.
3. Write the sorted relation to disk.
INDUCTION:
If R does not fit into main memory.
1. Partition the blocks holding R into
M
groups, which we shall call R1, R2, R3…
2. Recursively sort Ri for each i=1,2,3…M.
3. Merge the M sorted sublists.
If we are not merely sorting R, but performing
a unary operation such as δ or γ on R.
We can modify the above so that at the final
merge we perform the operation on the
tuples at the front of the sorted sublists.
That is:
• For a δ(selection), output one copy of each
distinct tuple, and skip over copies of the tuple.
• For a γ, sort on the grouping attributes only, and
combine the tuples with a given value of these
grouping attributes.
• When we want to perform a binary operation,
such as intersection or join, the two relations
are first divided into a total of M sublists
Each sublist id sorted by the recursive algorithm
and finally we reads each of the M sublists,
each into one buffer
Conclusion
The two pass algorithms based on sorting or
hashing have natural recursive analogs that
take three or more passes and will work for
larger amounts of data.
The Query Compiler
16.1 Parsing and Preprocessing
Presentation Outline
16.1 Parsing and Preprocessing
16.1.1 Syntax Analysis and Parse Tree
16.1.2 A Grammar for Simple Subset of SQL
16.1.3 The Preprocessor
16.1.4 Processing Queries Involving Views
Query compilation is divided
into three steps
1. Parsing: Parse SQL query into parser tree.
2. Logical query plan: Transforms parse tree into expression
tree of relational algebra. This tree is improved using relational
algebra
3.Physical query plan:
Transforms logical query plan into
physical query plan.
. Operation performed
. Order of operation
. Algorithm used
. The way in which stored data is obtained and passed from one
Query
Parser
Preprocessor
Logical Query plan
generator
Query rewrite
Preferred logical
query plan
Form a query to a logical query plan
Syntax Analysis and Parse Tree
Parser takes the sql query and convert it to parse
tree. Nodes of parse tree:
1. Atoms: known as Lexical elements such as key
words, constants, parentheses, operators, and
other schema elements.
2. Syntactic categories: Subparts that plays a
similar role in a query as <Query> , <Condition>
If condition as a subquery it is treated as a normal
query
Grammar for Simple Subset of SQL
<Query> ::= <SFW>
<Query> ::= (<Query>)
<SFW> ::= SELECT <SelList> FROM <FromList> WHERE <Condition>
<SelList> ::= <Attribute>,<SelList>
<SelList> ::= <Attribute>
<FromList> ::= <Relation>, <FromList>
<FromList> ::= <Relation>
<Condition> ::= <Condition> AND <Condition>
<Condition> ::= <Tuple> IN <Query>
<Condition> ::= <Attribute> = <Attribute>
<Condition> ::= <Attribute> LIKE <Pattern>
<Tuple> ::= <Attribute>
Atoms(constants), <syntactic categories>(variable),
::= (can be expressed/defined as)
Query and Parse Tree
Consider following tables
StarsIn(title,year,starName)
MovieStar(name,address,gender,birthdate)
Query:
Give titles of movies that have at least one star born in 1960
SELECT title FROM StarsIn WHERE starName IN
(
SELECT name FROM MovieStar WHERE
birthdate LIKE '%1960%'
);
The query can be represented as tree as follows
In the figure SFW: Select From Where
Another query equivalent
Consider another example
SELECT title
FROM StarsIn, MovieStar
WHERE starName = name AND
birthdate LIKE '%1960%' ;
Parse Tree
<Query>
<SFW>
SELECT <SelList> FROM
<Attribute>
<FromList>
WHERE
<RelName> , <FromList>
title
StarsIn
<Condition>
AND
<RelName>
MovieStar
<Query>
<Condition>
<Attribute>
starName
=
<Attribute>
name
<Condition>
<Attribute> LIKE <Pattern>
birthdate
‘%1960’
The Preprocessor
Functions of Preprocessor
. If a relation used in the query is virtual view then each use of this
relation in the form-list must replace by parser tree that describe the
view.
It main purpose is to convert parse tree to logical query tree.
. It is also responsible for semantic checking
1. Checks relation uses : Every relation mentioned in FROMclause must be a relation or a view in current schema.
2. Check and resolve attribute uses: Every attribute mentioned
in SELECT or WHERE clause must be an attribute of same
relation in the current scope.
3. Check types: All attributes must be of a type appropriate to
their uses.
StarsIn(title,year,starName)
MovieStar(name,address,gender,birthdate)
Query:
Give titles of movies that have at least one star born in 1960
SELECT title FROM StarsIn WHERE starName IN
(
SELECT name FROM MovieStar WHERE
birthdate LIKE '%1960%'
);
Preprocessing Queries Involving Views
When an operand in a query is a virtual view, the preprocessor
needs to replace the operand by a piece of parse tree that
represents how the view is constructed from base table.
Base Table: Movies( title, year, length, genre, studioname,
producerC#)
View definition : CREATE VIEW ParamountMovies AS
SELECT title, year FROM movies
WHERE studioName = 'Paramount';
Example based on view:
SELECT title FROM ParamountMovies WHERE year = 1979;
16.2 ALGEBRAIC LAWS FOR
IMPROVING QUERY PLANS
Ramya Karri
ID: 206
Optimizing the Logical Query Plan
• The translation rules converting a parse tree to a logical query
tree do not always produce the best logical query tree.
• It is often possible to optimize the logical query tree by
applying relational algebra laws to convert the original tree
into a more efficient logical query tree.
• Optimizing a logical query tree using relational algebra laws is
called heuristic optimization
Relational Algebra Laws
These laws often involve the properties of:
– commutativity - operator can be applied to operands
independent of order.
• E.g. A + B = B + A - The “+” operator is commutative.
– associativity - operator is independent of operand grouping.
• E.g. A + (B + C) = (A + B) + C - The “+” operator is
associative.
Associative and Commutative
Operators
• The relational algebra operators of cross-product (×), join
(⋈), union, and intersection are all associative and
commutative.
Commutative
Associative
R X S=S X R
(R X S) X T = S X (R X T)
R⋈S=S⋈R
(R ⋈ S) ⋈ T= S ⋈ (R ⋈ T)
RS=SR
(R  S)  T = S  (R  T)
R ∩S =S∩ R
(R ∩ S) ∩ T = S ∩ (R ∩ T)
Laws Involving Selection
•
•
Complex selections involving AND or OR can be broken into two or
more selections: (splitting laws)
σC1 AND C2 (R) = σC1( σC2 (R))
σC1 OR C2 (R) = ( σC1 (R) ) S ( σC2 (R) )
Example
–
–
–
–
–
–
R={a,a,b,b,b,c}
p1 satisfied by a,b, p2 satisfied by b,c
σp1vp2 (R) = {a,a,b,b,b,c}
σp1(R) = {a,a,b,b,b}
σp2(R) = {b,b,b,c}
σp1 (R) U σp2 (R) = {a,a,b,b,b,c}
Laws Involving Selection (Contd..)
• Selection is pushed through both arguments
for union:
σC(R  S) = σC(R)  σC(S)
• Selection is pushed to the first argument and
optionally the second for difference:
σC(R - S) = σC(R) - S
σC(R - S) = σC(R) - σC(S)
Laws Involving Selection (Contd..)
• All other operators require selection to be pushed to
only one of the arguments.
• For joins, may not be able to push selection to both if
argument does not have attributes selection requires.
σC(R × S) = σC(R) × S
σC(R ∩ S) = σC(R) ∩ S
σC(R ⋈ S) = σC(R) ⋈ S
σC(R ⋈D S) = σC(R) ⋈D S
Laws Involving Selection (Contd..)
• Example
• Consider relations R(a,b) and S(b,c) and the
expression
• σ (a=1 OR a=3) AND b<c (R ⋈S)
• σ a=1 OR a=3(σ b<c (R ⋈S))
• σ a=1 OR a=3(R ⋈ σ b<c (S))
• σ a=1 OR a=3(R) ⋈ σ b<c (S)
Laws Involving Projection
• Like selections, it is also possible to push
projections down the logical query tree.
However, the performance gained is less than
selections because projections just reduce
the number of attributes instead of reducing
the number of tuples.
Laws Involving Projection
• Laws for pushing projections with joins:
πL(R × S) = πL(πM(R) × πN(S))
πL(R ⋈ S) = πL((πM(R) ⋈ πN(S))
πL(R ⋈D S) = πL((πM(R) ⋈D πN(S))
Laws Involving Projection
• Laws for pushing projections with set operations.
• Projection can be performed entirely before union.
πL(R UB S) = πL(R) UB πL(S)
• Projection can be pushed below selection as long as we
also keep all attributes needed for the selection (M = L
 attr(C)).
πL ( σC (R)) = πL( σC (πM(R)))
Laws Involving Join
• We have previously seen these important
rules about joins:
1. Joins are commutative and associative.
2. Selection can be distributed into joins.
3. Projection can be distributed into joins.
Laws Involving Duplicate
Elimination
• The duplicate elimination operator (δ) can
be pushed through many operators.
• R has two copies of tuples t, S has one copy
of t,
• δ (RUS)=one copy of t
• δ (R) U δ (S)=two copies of t
Laws Involving Duplicate
Elimination
• Laws for pushing duplicate elimination
operator (δ):
δ(R × S) = δ(R) × δ(S)
δ(R S) = δ(R)
δ(S)
δ(R D S) = δ(R)
D δ(S)
δ( σC(R) = σC(δ(R))
Laws Involving Duplicate
Elimination
• The duplicate elimination operator (δ) can
also be pushed through bag intersection, but
not across union, difference, or projection in
general.
δ(R ∩ S) = δ(R) ∩ δ(S)
Laws Involving Grouping
• The grouping operator (γ) laws depend on the aggregate operators
used.
• There is one general rule, however, that grouping subsumes duplicate
elimination:
δ(γL(R)) = γL(R)
• The reason is that some aggregate functions are unaffected by
duplicates (MIN and MAX) while other functions are (SUM, COUNT,
and AVG).
Thank you
The Query Compiler
Section 16.3
Topics to be covered
• From Parse tree to Logical Query
Plans
–
–
–
–
Conversion to Relational Algebra
Removing Subqueries From Conditions
Improving the Logical Query Plan
Grouping Associative/ Commutative Operators
16.3 From Parse to Logical Query Plans ►
Review
Query
Parser
Section 16.1
Preprocessor
Logical query
plan generator
Section 16.3
Query Rewriter
Preferred logical query plan
Two steps to turn Parse tree into Preferred
Logical Query Plan
• Replace the nodes and structures of the parse tree, in
appropriate groups, by an operator or operators of
relational algebra.
• Take the relational algebra expression and turn it into an
expression that we expect can be converted to the most
efficient physical query plan. This is done by first
converting the parse tree to logical query and then this
logical query is transformed to physical query plan
Reference Relations
• StarsIn (movieTitle, movieYear, starName)
• MovieStar (name, address, gender,
birthdate)
Conversion to Relational Algebra
• If we have a <Query> with a <Condition> that has
no subqueries, then we may replace the entire
construct – the select-list, from-list, and condition –
by a relational-algebra expression.
• If condition contains a query it will addressed as
regular one is the above steps are followed
• The relational-algebra expression consists of
the following from bottom to top:
– The products of all the relations mentioned in the
<FromList>, which Is the argument of:
– A selection σC, where C is the <Condition> expression
in the construct being replaced, which in turn is the
argument of:
– A projection πL , where L is the list of attributes in the
<SelList>
A query : Example
• SELECT movieTitle
FROM Starsin, MovieStar
WHERE starName = name AND
birthdate LIKE ‘%1960’;
SELECT movieTitle
FROM Starsin, MovieStar
WHERE starName = name AND
birthdate LIKE ‘%1960’;
Translation to an algebraic expression
tree
Removing Subqueries From Conditions
• For parse trees with a <Condition> that has a
subquery
• Intermediate operator – two argument
selection
• It is intermediate in between the syntactic
categories of the parse tree and the relationalalgebra operators that apply to relations.
Using a two-argument σ
πmovieTitle
σ
<Condition>
StarsIn
<Tuple>
<Attribute>
starName
IN
πname
σ birthdate LIKE ‘%1960'
MovieStar
Two argument selection with condition
involving IN
•
Now say we have, two arguments – some relation and
the second argument is a <Condition> of the form t IN
S.
•
•
•
‘t’ – tuple composed of some attributes of R i.e., the list of
attributes that need to be projected
‘S’ – uncorrelated subquery
Steps to be followed:
1.
2.
3.
Replace the <Condition> by the tree that is the expression for S
( δ is used to remove duplicates)
Replace the two-argument selection by a one-argument
selection σC.
Give σC an argument that is the product of R and S. and apply
Two argument selection with condition
involving IN
σ
R
σC
<Condition>
t
IN
X
S
R
δ
S
The effect
Improving the Logical Query Plan
• Algebraic laws to improve logical query plans:
– Selections can be pushed down the expression tree
as far as they can go. In this way the number of
tuples can be reduced with selection
– Similarly, projections can be pushed down the tree,
or new projections can be added.
– Duplicate eliminations can sometimes be removed,
or moved to a more convenient position in the tree.
– Certain selections can be combined with a product
below to turn the pair of operations into an
equijoin.
Grouping Associative/ Commutative
Operators
• An operator that is associative and commutative
operators may be though of as having any number of
operands.
• We need to reorder these operands so that the
multiway join is executed as sequence of binary joins.
• Its more time consuming to execute them in the order
suggested by parse tree.
• For each portion of subtree that consists of nodes
with the same associative and commutative operator
(natural join, union, and intersection), we group the
nodes with these operators into a single node with
many children.
The effect of query rewriting
Π movieTitle
Starname = name
StarsIn
σbirthdate LIKE ‘%1960’
MovieStar
Final step in producing logical query plan
=>
R
U
U
U
R
S
T
V
W
U
U
S
T
V
W
An Example to summarize
• “find movies where the average age of the stars was at
most 40 when the movie was made”
• SELECT distinct m1.movieTitle, m1,movieYear
FROM StarsIn m1
WHERE m1.movieYear – 40 <= (
SELECT AVG (birthdate)
FROM StartsIn m2, MovieStar s
WHERE m2.starName = s.name AND
m1.movieTitle = m2.movieTitle AND
m1.movieYear = m2.movieyear
);
SELECT distinct m1.movieTitle, m1,movieYear
FROM StarsIn m1
WHERE m1.movieYear – 40 <= (
SELECT AVG (birthdate)
FROM StartsIn m2, MovieStar s
WHERE m2.starName = s.name AND
m1.movieTitle = m2.movieTitle AND
m1.movieYear = m2.movieyear );
Selections combined with a product to
turn the pair of operations into an
equijoin…
Condition pushed up the expression tree…
`
Selections combined…
Choosing an Order for Joins
Introduction
• This section focuses on critical problem in
cost-based optimization:
– Selecting order for natural join of three or more
relations
– Ordering the relations makes a lot of difference in
cost
• Compared to other binary operations, joins
take more time and therefore need effective
optimization techniques
Introduction
Significance of Left and Right Join
Arguments
• The argument relations in joins determine the
cost of the join
• The left argument of the join is
– Called the build relation
– Assumed to be smaller
– Stored in main-memory
Significance of Left and Right Join
Arguments
• The right argument of the join is
– Called the probe relation
– Read a block at a time
– Its tuples are matched with those of build relation
• The join algorithms which distinguish between
the arguments are:
– One-pass join
– Nested-loop join
– Index join
Join Trees
• Order of arguments is important for joining
two relations
• Left argument, since stored in main-memory,
should be smaller
• With two relations only two choices of join
tree
• With more than two relations, there are n!
ways to order the arguments and therefore n!
join trees, where n is the no. of relations
Join Trees
• Order of arguments is important for joining
two relations
• Left argument, since stored in main-memory,
should be smaller
• With two relations only two choices of join
tree
• With more than two relations, there are n!
ways to order the arguments and therefore n!
join trees, where n is the no. of relations
Join Trees
• Total (number of)# of tree shapes T(n) for n
relations given by recurrence:
•
•
•
•
T(1) = 1
T(2) = 1
T(3) = 2
T(4) = 5 … etc
Left-Deep Join Trees
• Consider 4 relations. Different ways to join
them are as follows
• If all the right children are leaves, then it is
called left-deep tree, and if all the left children
are leaves then it is called right deep tree
• In fig (a) all the right children are leaves. This
is a left-deep tree
• In fig (c) all the left children are leaves. This is
a right-deep tree
• Fig (b) is a bushy tree
• Considering left-deep trees is advantageous
for deciding join orders
Join order
• Join order selection
– A1
A2
A3
..
– Left deep join trees
An
An
Ai
– Dynamic programming
• Best plan computed for each subset of relations
– Best plan (A1, .., An) = min cost plan of(
Best plan(A2, .., An)
A1
Best plan(A1, A3, .., An)
A2
….
Best plan(A1, .., An-1))
An
Dynamic Programming to Select a Join
Order and Grouping
• Three choices to pick an order for the join of many relations
are:
– Consider all of the relations
– Consider a subset
– Use a heuristic o pick one
• Dynamic programming is used either to consider all or a
subset
– Construct a table of costs based on relation size
– Remember only the minimum entry which will required to
proceed
Dynamic Programming to Select a Join
Order and Grouping
• Size is the number of tuples, so on join the value is calculated
by multiplication
Dynamic Programming to Select a Join
Order and Grouping
Dynamic Programming to Select a Join
Order and Grouping
• When find the size of R, S, T is multiplication of the size of all
the three tables
Dynamic Programming to Select a Join
Order and Grouping
A Greedy Algorithm for Selecting a Join
Order
• It is expensive to use an exhaustive method
like dynamic programming
• Better approach is to use a join-order heuristic
for the query optimization
• Greedy algorithm is an example of that
– Make one decision at a time about order of join
and never backtrack on the decisions once made
Completing the Physical-Query-Plan
and Chapter 16 Summary (16.7-16.8)
Outline
16.7 Completing the Physical-Query-Plan
I. Choosing a Selection Method
II. Choosing a Join Method
III. Pipelining Versus Materialization
IV. Pipelining Unary Operations
V. Pipelining Binary Operations
VI. Notation for Physical Query Plan
VII. Ordering the Physical Operations
16.8 Summary of Chapter 16
288
Before complete Physical-Query-Plan
• A query previously has been
– Parsed and Preprocessed (16.1)
– Converted to Logical Query Plans (16.3)
– Estimated the Costs of Operations (16.4)
– Determined costs by Cost-Based Plan Selection
(16.5)
– Weighed costs of join operations by choosing an
Order for Joins
289
16.7 Completing the Physical-Query-Plan
•
3 topics related to turning LP into a
complete physical plan
1. Choosing of physical implementations such as
Selection and Join methods
2. Decisions regarding when intermediate results
(Materialized or Pipelined) will be materialized
3. Notation for physical-query-plan operators,
which must include details regarding access
methods for stored relations and algorithms
290
I. Choosing a Selection Method (A)
• Algorithms for each selection operators
1. Can we use an created index on an attribute?
– If yes, index-scan. Otherwise table-scan)
2. After retrieve all condition-satisfied tuples in (1),
then filter them with the rest selection conditions
291
Choosing a Selection Method(A) (cont.)
•
•
Recall  Cost of query = # disk I/O’s
How costs for various plans are estimated from σC(R) operation
1. Cost of table-scan algorithm
a) B(R)
if R is clustered
b) T(R)
if R is not clustered
2. Cost of a plan picking an equality term (e.g. a = 10) w/ index-scan; used to find
the matching tuples
a) B(R) / V(R, a)
clustering index
b) T(R) / V(R, a)
nonclustering index
3. Cost of a plan picking an inequality term (e.g. b < 20) w/ index-scan; uses indexscan to retrieve the matching tuples
a) B(R) / 3
clustering index
b) T(R) / 3
nonclustering index
292
Example
Selection: σx=1 AND y=2 AND z<5 (R)
- Where parameters of R(x, y, z) are :
T(R)=5000,
B(R)=200,
V(R,x)=100, and V(R, y)=500
- Relation R is clustered
- x, y have nonclustering indexes, only index on z is
clustering.
293
Example (cont.)
Selection options:
1.
2.
3.
4.
Table-scan  filter x, y, z. Cost is B(R) = 200 since R is
clustered.
Use index on x =1  filter on y, z. Cost is 50 since
T(R) / V(R, x) is (5000/100) = 50 tuples, index is not
clustering.
Use index on y =2  filter on x, z. Cost is 10 since
T(R) / V(R, y) is (5000/500) = 10 tuples using
nonclustering index.
Index-scan on clustering index w/ z < 5  filter x ,y.
Cost is about B(R)/3 = 67
294
Example (cont.)
•
Costs
option 1 = 200
option 2 = 50
option 3 = 10 
option 4 = 67
The lowest Cost is option 3.
• Therefore, the preferred physical plan
1. retrieves all tuples with y = 2
2. then filters for the rest two conditions (x, z).
295
II. Choosing a Join Method
• Determine costs associated with each join algorithms:
1. One-pass join, and nested-loop join devotes enough buffer to
joining
2. Sort-join is preferred when attributes are pre-sorted or two or
more join on the same attribute such as
(R(a, b) S(a, c)) T(a, d)
- where sorting R and S on a will produce result of R S to be
sorted on a and used directly in next join
Sort join is good choice when either:
One or both arguments are already sorted on their join attributes
296
Choosing a Join Method (cont.)
3. Index-join for a join with high chance of using
index created on the join attribute such as R(a, b)
S(b, c)
4. Hashing join is the best choice for unsorted or
non-indexing relations which needs multipass join.
297
III. Pipelining Versus Materialization
• The naïve way to execute a query plan is to order the operations
appropriately and store the result of each operation on disk until it
is needed by another operation
• Materialization (naïve way)
– store (intermediate) result of each operations on disk
• Pipelining (more efficient way)
– Interleave the execution of several operations, the tuples produced by one
operation are passed directly to the operations that used it
– store (intermediate) result of each operations on buffer, which is
implemented on main memory
298
IV. Pipelining Unary Operations
• Unary = a-tuple-at-a-time or full relation
• selection and projection are the best
candidates for pipelining.
In buf
Unary
operation
Out buf
Unary
operation
Out buf
R
In buf
M-1 buffers
299
Pipelining Unary Operations (cont.)
• Pipelining Unary Operations are implemented by
iterators
300
V. Pipelining Binary Operations
• The results of binary operations can also be
pipelined
• Binary operations : ,  , - , , x
• Use one buffer to pass result to its consumer,
one block at a time.
• The extended example shows tradeoffs and
opportunities
301
Example
• Consider physical query plan for the expression
(R(w, x)
• Assumption
S(x, y))
U(y, z)
– R occupies 5,000 blocks, S and U each 10,000 blocks.
– The intermediate result R S occupies k blocks for some
k.
– Both joins will be implemented as hash-joins, either
one-pass or two-pass depending on k
– There are 101 buffers available.
302
Example (cont.)
• First consider join
R S, neither relations
fits in buffers
• Needs two-pass
hash-join to partition
R into 100 buckets
(maximum possible) each bucket has 50 blocks
• The 2nd pass hash-join uses 51 buffers, leaving the
rest 50 buffers for joining result of R S with U.
303
Example (cont.)
•
•
Case 1: suppose k  49, the result of
occupies at most 49 blocks.
Steps
R
S
1. Pipeline in R S into 49 buffers
2. Organize them for lookup as a hash table
3. Use one buffer left to read each block of U in turn
4. Execute the second join as one-pass join.
The total number of disk I/0s is
45, 000 to perform the two pass hash joins
304
Example (cont.)
• The total number of I/O’s
is 55,000
– 45,000 for two-pass hash
join of R and S
– 10,000 to read U for onepass hash join of
(R S) U.
305
Example (cont.)
•
Case 2: suppose k > 49 but < 5,000, we can still
pipeline, but need another strategy which
intermediate results join with U in a 50-bucket,
two-pass hash-join. Steps are:
1.
Before start on R S, we hash U into 50 buckets of 200
blocks each.
Perform two-pass hash join of R and U using 51 buffers as
case 1, and placing results in 50 remaining buffers to form
50 buckets for the join of R S with U.
Finally, join R S with U bucket by bucket.
Since k<5000 the buckets for R S will be size at most 100
buckets
2.
3.
4.
306
Example (cont.)
• The number of disk I/O’s is:
– 20,000 to read U and write its tuples into buckets
– 45,000 for two-pass hash-join R S
– k to write out the buckets of R S
– k+10,000 to read the buckets of R S and U in the
final join
• The total cost is 75,000+2k.
307
Example (cont.)
• Compare Increasing I/O’s between case 1 and
case 2
– k  49 (case 1)
• Disk I/O’s is 55,000
– k > 50  5000 (case 2)
• k=50 , I/O’s is 75,000+(2*50) = 75,100
• k=51 , I/O’s is 75,000+(2*51) = 75,102
• k=52 , I/O’s is 75,000+(2*52) = 75,104
Notice: I/O’s discretely grows as k increases from 49 50.
308
Example (cont.)
•
Case 3: k > 5,000, we cannot perform twopass join in 50 buffers available if result of
R S is pipelined. Steps are
1. Compute R S using two-pass join and store the
result on disk.
2. Join result on (1) with U, using two-pass join.
309
Example (cont.)
• The number of disk I/O’s is:
– 45,000 for two-pass hash-join R and S
– k to store R S on disk
– 30,000 + k for two-pass join of U in R S
• The total cost is 75,000+4k.
310
Example (cont.)
• In summary, costs of physical plan as
function of R S size.
311
VI. Notation for Physical Query Plans
•
Several types of operators:
1.
2.
3.
4.
•
Operators for leaves
(Physical) operators for Selection
(Physical) Sorts Operators
Other Relational-Algebra Operations
In practice, each DBMS uses its own internal
notation for physical query plan.
312
Notation for Physical Query Plans (cont.)
1. Operator for leaves
– A leaf operand is replaced in LQP tree
• TableScan(R) : read all blocks
• SortScan(R, L) : read in order according to L
• IndexScan(R, C): scan index attribute A by
condition C of form Aθc.
• IndexScan(R, A) : scan index attribute R.A. This
behaves like TableScan but more efficient if R is not
clustered.
313
Notation for Physical Query Plans (cont.)
2. (Physical) operators for Selection
– Logical operator σC(R) is often combined with
access methods.
•
•
If σC(R) is replaced by Filter(C), and there is no
index on R or an attribute on condition C
– Use TableScan or SortScan(R, L) to access R
If condition C  Aθc AND D for condition D, and
there is an index on R.A, then we may
– Use operator IndexScan(R, Aθc) to access R and
– Use Filter(D) in place of the selection σC(R)
314
Notation for Physical Query Plans (cont.)
3. (Physical) Sort Operators
– Sorting can occur any point in physical plan,
which use a notation SortScan(R, L).
– It is common to use an explicit operator Sort(L)
to sort relation that is not stored.
– Can apply at the top of physical-query-plan tree
if the result needs to be sorted with ORDER BY
clause (г).
315
Notation for Physical Query Plans (cont.)
4. Other Relational-Algebra Operations
–
Descriptive text definitions and signs to elaborate
• Operations performed e.g. Join or grouping.
• Necessary parameters e.g. theta-join or list of
elements in a grouping.
• A general strategy for the algorithm e.g. sort-based,
hashed based, or index-based.
• A decision about number of passed to be used e.g.
one-pass, two-pass or multipass.
• An anticipated number of buffers the operations will
required.
316
Notation for Physical Query Plans (cont.)
• Example of a physical-query-plan
– A physical-query-plan in example 16.36 for the case k >
5000
•
•
•
•
TableScan
Two-pass hash join
Materialize (double line)
Store operator
317
Notation for Physical Query Plans (cont.)
• Another example
– A physical-query-plan in example 16.36 for the case k <
49
•
•
•
•
•
TableScan
(2) Two-pass hash join
Pipelining
Different buffers needs
Store operator
318
Notation for Physical Query Plans (cont.)
• A physical-query-plan in example 16.35
– Use Index on condition y = 2 first
– Filter with the rest condition later on.
319
VII. Ordering of Physical Operations
•
•
The PQP is represented as a tree structure
implied order of operations.
Still, the order of evaluation of interior
nodes may not always be clear.
– Iterators are used in pipeline manner
– Overlapped time of various nodes will make
“ordering” no sense.
320
Ordering of Physical Operations (cont.)
•
3 rules summarize the ordering of events in
a PQP tree:
1. Break the tree into sub-trees at each edge that
represent materialization.
•
Execute one subtree at a time.
2. Order the execution of the subtree
•
•
Bottom-top
Left-to-right
3. All nodes of each sub-tree are executed
simultaneously.
321
COMPILATION OF QUERIES
• Compilation means turning a query into a
physical query plan, which can be
implemented by query engine.
• Steps of query compilation :
– Parsing
– Semantic checking
– Selection of the preferred logical query plan
– Generating the best physical plan
322
THE PARSER
• The first step of SQL query processing.
• Generates a parse tree- Convert a query to
parse tree
• Nodes in the parse tree corresponds to the
SQL constructs
• Similar to the compiler of a programming
language
323
VIEW EXPANSION
• A very critical part of query compilation.
• Expands the view references in the query
tree to the actual view.
• Provides opportunities for the query
optimization.
324
SEMANTIC CHECKING
• Checks the semantics of a SQL query.
• Examines a parse tree.
• Checks :
– Attributes
– Relation names
– Types
• Resolves attribute references.
325
CONVERSION TO A LOGICAL QUERY
PLAN
• Converts a semantically parsed tree to a
algebraic expression.
• Conversion is straightforward but sub
queries need to be optimized.
• Two argument selection approach can be
used.
• Logical query is improved based on relational
algebra
326
ALGEBRAIC TRANSFORMATION
• Many different ways to transform a logical query plan to an
actual plan using algebraic transformations.
• The laws used for this transformation :
– Commutative and associative laws
– Laws involving selection
– Pushing selection
– Laws involving projection
– Laws about joins and products
– Laws involving duplicate eliminations
– Laws involving grouping and aggregation
327
ESTIMATING SIZES OF RELATIONS
• True running time is taken into consideration
when selecting the best logical plan.
• Two factors the affects the most in
estimating the sizes of relation :
– Size of relations ( No. of tuples )
– No. of distinct values for each attribute of each
relation
• Histograms are used by some systems.
328
COST BASED OPTIMIZING
• Best physical query plan represents the least
costly plan.
• Improve the physical plan by ordering the
attributes based on their cost
• Factors that decide the cost of a query plan :
– Order and grouping operations like joins, unions and
intersections.
– Nested loop and the hash loop joins used.
– Scanning and sorting operations.
– Storing intermediate results.
329
PLAN ENUMERATION STRATEGIES
• Common approaches for searching the space
for best physical plan .
– Dynamic programming : Tabularizing the best plan
for each sub expression
– Selinger style programming : sort-order the results
as a part of table
– Greedy approaches : Making a series of locally
optimal decisions
– Branch-and-bound : Starts with enumerating the
worst plans and reach the best plan
330
LEFT-DEEP JOIN TREES
• Left – Deep Join Trees are the binary trees
with a single spine down the left edge and
with leaves as right children.
• This strategy reduces the number of plans to
be considered for the best physical plan.
• Restrict the search to Left – Deep Join Trees
when picking a grouping and order for the
join of several relations.
331
PHYSICAL PLANS FOR SELECTION
• Breaking a selection into an index-scan of
relation, followed by a filter operation.
• The filter then examines the tuples retrieved
by the index-scan.
• Allows only those to pass which meet the
portions of selection condition.
332
PIPELINING VERSUS MATERIALIZING
• This flow of data between the operators can be controlled
to implement “ Pipelining “ .
• The intermediate results should be removed from main
memory to save space for other operators.
• This techniques can implemented using “ materialization “ .
• Both the pipelining and the materialization should be
considered by the physical query plan generator.
• An operator always consumes the result of other operator
and is passed through the main memory.
333
Concurrency Control
18.1 – 18.2
Chiu Luk
CS257 Database Systems Principles
Spring 2009
Concurrency Control
• Concurrency control in database management systems (DBMS)
ensures that database transactions are performed concurrently
without the concurrency violating the data integrity of a
database.
• Executed transactions should follow the ACID rules. The DBMS
must guarantee that only serializable (unless Serializability is
intentionally relaxed), recoverable schedules are generated.
• It also guarantees that no effect of committed transactions is
lost, and no effect of aborted (rolled back) transactions remains
in the related database.
Transaction ACID rules
All the transactions are ensured to follow these properties
so that the database operations do not fall in deadlock
Atomicity - Either the effects of all or none of its operations
remain when a transaction is completed - in other words, to
the outside world the transaction appears to be indivisible,
atomic.
Consistency - Every transaction must leave the database in
a consistent state.
Isolation - Transactions cannot interfere with each other.
Providing isolation is the main goal of concurrency control.
Durability - Successful transactions must persist through
crashes.
Serial and Serializable Schedules
In the field of databases, a schedule is a list of actions, (i.e. reading, writing,
aborting, committing), from a set of transactions.
 In this example, Schedule D is the set of 3 transactions T1, T2, T3. The schedule
describes the actions of the transactions as seen by the DBMS. T1 Reads and
writes to object X, and then T2 Reads and writes to object Y, and finally T3 Reads
and writes to object Z. This is an example of a serial schedule, because the actions
of the 3 transactions are not interleaved.

Serial and Serializable Schedules
•
•
•
A schedule that is equivalent to a serial schedule has the serializability property.
And the operations are interleaved
In schedule E, the order in which the actions of the transactions are executed is not the same
as in D, but in the end, E gives the same result as D.
Serial Schedule TI precedes T2
It is serial because they got same results and not interleaved
A
B
T1
T2
Read(A); A  A+100
Write(A);
Read(B); B  B+100;
Write(B);
25
25
125
Read(A);A  A2;
Write(A);
Read(B);B  B2;
Write(B);
125
250
250
250
250
Serial Schedule T2 precedes Tl
T1
T2
Read(A);A  A2;
Write(A);
Read(B);B  B2;
Write(B);
Read(A); A  A+100
Write(A);
Read(B); B  B+100;
Write(B);
A
25
B
25
50
50
150
150
150
150
serializable, but not serial, schedule
A
25
T1
T2
Read(A); A  A+100
Write(A);
Read(A);A  A2;
Write(A);
Read(B); B  B+100;
Write(B);
125
250
125
Read(B);B  B2;
Write(B);
250
r1(A); w1 (A): r2(A); w2(A); r1 (B); w1 (B); r2(B); w2(B);
B
25
250
250
nonserializable schedule because
they do not have same resultA
T1
Read(A); A  A+100
Write(A);
T2
25
Read(A);A  A2;
Write(A);
Read(B);B  B2;
Write(B);
B
25
125
250
Read(B); B  B+100;
Write(B);
50
250
150
150
schedule that is serializable only because of the detailed behavior
of the transactions
T1
Read(A); A  A+100
Write(A);
A
25
T2’
Read(A);A  A1;
Write(A);
Read(B);B  B1;
Write(B);
125
125
Read(B); B  B+100;
Write(B);
•
regardless of the consistent initial state: the final state will be consistent.
B
25
25
125
125
125
Non-Conflicting Actions
Two actions are non-conflicting if whenever they
occur consecutively in a schedule, swapping them
does not affect the final state produced by the
schedule. Otherwise, they are conflicting.
Conflicting Actions: General Rules
• Two actions of the same transaction conflict:
– r1(A) w1(B)
• Two actions over the same database element
conflict, if one of them is a write
– r1(A) w2(A); because once read, if some other
operation writes it, then the value gets changed
– w1(A) w2(A)
Conflict actions
•
•
•
Two or more actions are said to be in conflict if:
–
The actions belong to different transactions.
–
At least one of the actions is a write operation.
–
The actions access the same object (read or write).
The following set of actions is conflicting:
–
T1:R(X), T2:W(X), T3:W(X)
While the following sets of actions are not:
–
T1:R(X), T2:R(X), T3:R(X)
–
T1:R(X), T2:W(Y), T3:R(X)
Conflict Serializable
We may take any schedule and make as many
nonconflicting swaps as we wish.

With the goal of turning the schedule into a serial
schedule.

If we can do so, then the original schedule is
serializable, because its effect on the database
state remains the same as we perform each of the
nonconflicting
swaps.

Conflict Serializable
•
•
•
A schedule is said to be conflict-serializable when the schedule is conflict-equivalent to one or
more serial schedules.
Another definition for conflict-serializability is that a schedule is conflict-serializable if and only
if there exists an acyclic precedence graph/serializability graph for the schedule.
Which is conflict-equivalent to the serial schedule <T1,T2>, but not <T2,T1>.
Conflict equivalent / conflict-serializable
• Let Ai and Aj are consecutive non-conflicting actions that
belongs to different transactions. We can swap Ai and Aj
without changing the result.
• Two schedules are conflict equivalent if they can be turned
one into the other by a sequence of non-conflicting swaps of
adjacent actions.
• We shall call a schedule conflict-serializable if it is conflictequivalent to a serial schedule.
conflict-serializable
T1
R(A)
W(A)
T2
R(A)
R(B)
W(A)
W(B)
R(B)
W(B)
conflict-serializable
T1
R(A)
W(A)
R(B)
T2
R(A)
W(A)
W(B)
R(B)
W(B)
conflict-serializable
T1
R(A)
W(A)
R(A)
T2
R(B)
W(B)
W(A)
R(B)
W(B)
conflict-serializable
T1
R(A)
W(A)
R(A)
W(B)
T2
Serial
Schedule
R(B)
W(A)
R(B)
W(B)
Concurrency Control
354
INTRODUCTION
• Enforcing serializability by locks
– Locks
– Locking scheduler
– Two phase locking
• Locking systems with several lock modes
–
–
–
–
Shared and exclusive locks
Compatibility matrices
Upgrading/updating locks
Incrementing locks
355
Locks
•
It works like as follows :
–
–
–
A request is sent from transaction
Scheduler checks in the lock table to make sure that they are
serializible
Generates a serializable schedule of actions.
356
Consistency of transactions
• Actions and locks must relate each other
– Transactions can only read & write only if has a
lock and has not released the lock.
– Unlocking an element is compulsory.
• Legality of schedules
– No two transactions can aquire the lock on same
element without the prior one releasing it.
357
Locking scheduler
• Grants lock requests only if it is in a legal schedule.
• Lock table stores the information about current locks
on the elements.
358
The locking scheduler (contd.)
• A legal schedule of consistent transactions but
unfortunately it is not a serializable. (becoz it has
different end results)
359
Locking schedule (contd.)
• The locking scheduler delays requests that
would result in an illegal schedule.
360
Two-phase locking
• Guarantees a legal schedule of consistent
transactions is conflict-serializable.
• All lock requests proceed all unlock requests.
• The growing phase:
– Obtain all the locks and no unlocks allowed.
• The shrinking phase:
– Release all the locks and no locks allowed.
361
Working of Two-Phase locking
• Assures serializability.
• Two protocols for 2PL:
– Strict two phase locking : Transaction holds all its
exclusive locks till commit / abort.
– Rigorous two phase locking : Transaction holds all
locks till commit / abort.
• Possible to find a transaction Tj that has a 2PL
and a schedule S for Ti ( non 2PL ) and Tj that
is not conflict serializable.
362
Failure of 2PL.
• 2PL fails to provide security against deadlocks.
363
Locking Systems with Several Lock
Modes
• Locking Scheme
– Shared/Read Lock ( For Reading)
– Exclusive/Write Lock( For Writing)
•
•
•
•
Compatibility Matrices
Upgrading Locks
Update Locks
Increment Locks
364
Shared & Exclusive Locks
• Consistency of Transactions
– Cannot write without Exclusive Lock
– Cannot read without holding some lock i.e., it needs to put
a lock on the element to read or write
• This basically works on 2 principles
– A read action can only proceed a shared or an exclusive
lock
– A write lock can only proceed a exclusice lock
• All locks need to be unlocked before commit
365
Shared and exclusive locks (cont.)
• Two-phase locking of transactions
– Must precede unlocking
• Legality of Schedules
– An element may be locked exclusively by one transaction or
by several in shared mode, but not both.
366
Compatibility Matrices
• Has a row and column for each lock mode.
– Rows correspond to a lock held on an element by
another transaction
– Columns correspond to mode of lock requested.
– Example :
LOCK REQUESTED
S
X
LOCK
S
YES
NO
HOLD
X
NO
NO
367
Upgrading Locks
• Suppose a transaction wants to read as well as
write :
– It aquires a shared lock on the element
– Performs the calculations on the element
– And when its ready to write, It is granted a
exclusive lock.
• Transactions with unpredicted read write locks
can use UPGRADING LOCKS.
368
Upgrading locks (cont.)
• Indiscriminating use of upgrading produces a
deadlock.
• Example : Both the transactions want to
upgrade on the same element
369
Update locks
• Solves the deadlock occurring in upgrade lock
method.
• A transaction in an update lock can read but cant
write.
• Update lock can later be converted to exclusive
lock.
• An update lock can only be given if the element
has shared locks.
• Therefore first the transaction gets a shared lock
and when it wants to write it upgrades itself to
upgrade lock
370
Update locks (cont.)
• An update lock is like a shared lock when you
are requesting it and is like a exclusive lock
when you have it.
• Compatibility matrix :
S
X
U
S
YES
NO
YES
X
NO
NO
NO
U
NO
NO
NO
371
Increment Locks
• Mainly used in banking transactions
• Used for incrementing & decrementing stored
values.
• E.g. - Transfer money from one bank to
another, Ticket selling transactions in which
number seats are decremented after each
transaction.
372
Increment lock (cont.)
• A increment lock does not enable read or write locks on
element.
• Any number of transaction can hold increment lock on
element
• Shared and exclusive locks can not be granted if an increment
lock is granted on element
S
X
I
S
YES
NO
NO
X
NO
NO
NO
I
NO
NO
YES
373
Concurrency Control
Chapter 18
Section 18.5
374
Overview
• Assume knowledge of:
Lock
Two phase lock
Lock modes: shared, exclusive, update
• A simple scheduler architecture based on
following principle :
Insert lock actions into the stream of reads, writes,
and other actions
Release locks when the transaction manager tells it
that the transaction will commit or abort
375
Scheduler That Inserts Lock Actions
into the transactions request stream
Scheduler That Inserts Lock Actions
If transaction is delayed, waiting for a lock, Scheduler
performs following actions using two parts
• Part I: Takes the stream of requests generated by the
transaction checks serializabilty & insert appropriate
lock modes to db operations (read, write, or update)
• Part II: Take actions (a lock or db operation) from Part
I and executes it.
 Determine the transaction (T) that action belongs and
status of T (delayed or not). If T is not delayed then
1. Database access action is transmitted to the
database and executed
377
Scheduler That Inserts Lock Actions
2. If lock action is received by PartII, it checks the L Table
whether lock can be granted or not
i> Granted, the L Table is modified to include granted lock
ii>Not G. then update L Table about requested lock then
PartII delays transaction T
3. When a T (Transaction)= commits or aborts, PartI is
notified by the transaction manager and releases all locks.
If any transactions are waiting for locks PartI notifies
PartII.
3. Part II when notified about the lock on some DB element,
determines next transaction T’ to get lock to continue.
378
The Lock Table
• Used to maintain the information about the
locks
• A relation that associates database elements
with locking information about that element
• Implemented with a hash table using
DB database elements as the hash key
element A
• Size is proportional to the number of lock
Lock
elements only, notinformation
to the size of the entire
for A
database
379
Lock Table Entries Structure
Some Sort of information
found in Lock Table entry
1>Group modes
-S: only shared locks are
held
-X: one exclusive lock and
no other locks
- U: one update lock and one
or more shared locks
2>wait : one transaction
waiting for a lock on A
3>A list : T currently hold
locks on A or Waiting for
lock on A
380
Handling Lock Requests
• Suppose transaction T requests a lock on A
• If there is no lock table entry for A, then there
are no locks on A, so create the entry and
grant the lock request
• If the lock table entry for A exists, use the
group mode to guide the decision about the
lock request
381
Handling Lock Requests
• If group mode is U (update) or X (exclusive)
No other lock can be granted
 Deny the lock request by T
 Place an entry on the list saying T requests a lock
 And Wait? = ‘yes’
• If group mode is S (shared)
Another shared or update lock can be granted
 Grant request for an S or U lock
 Create entry for T on the list with Wait? = ‘no’
 Change group mode to U if the new lock is an update lock
382
Handling Unlock Requests
• Now suppose transaction T unlocks A
Delete T’s entry on the list for A
If T’s lock is not the same as the group mode,
no need to change group mode
Otherwise check entire list for new group
mode
• S: GM(S) or nothing
• U: GM(S) or nothing
• X: nothing
383
Handling Unlock Requests
 If the value of waiting is “yes" need to grant one or
more locks using following approaches
First-Come-First-Served:
 Grant the lock to the longest waiting request in
first come first sere basis
 No starvation (waiting forever for lock)
Priority to Shared Locks:
 Grant all S locks waiting, then one U lock.
 Grant X lock if no others waiting
Priority to Upgrading:
 If there is a U lock waiting to upgrade to an X
lock, grant that first.
384
Concurrency Control
Managing Hierarchies of Database Elements (18.6)
385
Managing Hierarchies of Database
Elements
• Two problems that arise with locks when
there is a tree structure to the data are:
• When the tree structure is a hierarchy of
lockable elements
– Determine how locks are granted for both large
elements (relations) and smaller elements (blocks
containing tuples or individual tuples)
• When the data itself is organized as a tree
(B-tree indexes)
– This will be discussed in the next section
Locks with Multiple Granularity
• Even with the various locks we have such as
incremental, exclusive etc, the locking also
depends on granularity
• A database element can be a relation, block or a
tuple
• Different systems use different database
elements to determine the size of the lock
• Thus some may require small database elements
such as tuples or blocks and others may require
large elements such as relations
Example of Multiple Granularity Locks
• Consider a database for a bank
– Choosing relations as database elements means we
would have one lock for an entire relation
– If we were dealing with a relation having account
balances, this kind of lock would be very inflexible and
thus provide very little concurrency
– Why? Because balance transactions require exclusive
locks and this would mean only one transaction occurs
for one account at any time
– But as each account is independent of others we could
perform transactions on different accounts
simultaneously
…(contd.)
– Thus it makes sense to have block element for the lock
so that two accounts on different blocks can be
updated simultaneously
• Another example is that of a document
– With similar arguments as above, we see that it is
better to have large element (a complete document) as
the lock in this case
Warning (Intention) Locks
• These are required to manage locks at
different granularities
– In the bank example, if the a shared lock is obtained for
the relation while there are exclusive locks on individual
tuples, unserializable behavior occurs
• The rules for managing locks on hierarchy of
database elements constitute the warning
protocol
Database Elements Organized in
Hierarchy
Rules of Warning Protocol
• These involve both ordinary (S and X) and
warning (IS and IX) locks
• The rules are:
– Begin at the root of hierarchy
– Request the S/X lock if we are at the desired element
– If the desired element id further down the hierarchy, place
a warning lock (IS if S and IX if X)
– When the warning lock is granted, we proceed to the child
node and repeat the above steps until desired node is
reached
Compatibility Matrix for Shared,
Exclusive and Intention Locks
IS
IX
S
X
IS
Yes
Yes
Yes
No
IX
Yes
Yes
No
No
S
Yes
No
Yes
No
X
No
No
No
No
• The above matrix applies only to locks held by
other transactions
Group Modes of Intention Locks
• An element can request S and IX locks at the
same time if they are in the same transaction
(to read entire element and then modify sub
elements)
• This can be considered as another lock mode,
SIX, having restrictions of both the locks i.e.
No for all except IS
• SIX serves as the group mode
Example
• Consider a transaction T1 as follows
– Select * from table where attribute1 = ‘abc’
– Here, as it does not update the table IS lock is first
acquired on the entire relation; then moving to individual
tuples (attribute = ‘abc’), S lock in acquired on each of
them
• Consider another transaction T2
– Update table set attribute2 = ‘def’ where attribute1 = ‘ghi’
– Here, it requires an IX lock on relation as update is needed
and since T1’s IS lock is compatible, IX is granted
– On reaching the desired tuple (ghi), as there is no
lock, it gets X too
– If T2 was updating the same tuple as T1, it would
have to wait until T1 released its S lock
Phantoms and Handling Insertions
Correctly
• This arises when transactions create new sub
elements of lockable elements
• Since we can lock only existing elements the
new elements fail to be locked
• The problem created in this situation is
explained in the following example
Example
• Consider a transaction T3
– Select sum(length) from table where attribute1 =
‘abc’
– This calculates the total length of all tuples having
attribute1
– Thus, T3 acquires IS for relation and S for targeted
tuples
– Now, if another transaction T4 inserts a new tuple
having attribute1 = ‘abc’, the result of T3 becomes
incorrect
Example (…contd.)
• This is not a concurrency problem since the serial
order (T3, T4) is maintained
• But if both T3 and T4 were to write an element X,
it could lead to unserializable behavior
– r3(t1);r3(t2);w4(t3);w4(X);w3(L);w3(X)
– r3 and w3 are read and write operations by T3 and w4 are the
write operations by T4 and L is the total length calculated by T3
(t1 + t2)
– At the end, we have result of T3 as sum of lengths of t1 and t2
and X has value written by T3
– This is not right; if value of X is considered to be that written by
T3 then for the schedule to be serializable, the sum of lengths of
t1, t2 and t3 should be considered
Example (…contd.)
– Else if the sum is total length of t1 and t2 then for the schedule
to be serializable, X should have value written by T4
• This problem arises since the relation has a
phantom tuple (the new inserted tuple), which
should have been locked but wasn’t since it didn’t
exist at the time locks were taken
• The occurrence of phantoms can be avoided if all
insertion and deletion transactions are treated as
write operations on the whole relation
CONCURRENCY
CONTROL
SECTION 18.7
THE TREE PROTOCOL
BASICS
B-Trees
- Tree data structure that keeps data sorted
- allow searches, insertion, and deletion
- commonly used in database and file systems
Lock
- Enforce limits on access to resources
- way of enforcing concurrency control
Lock Granularity
- Level and type of information that lock
protects.
TREE PROTOCOL
Kind of graph-based protocol
Alternate to Two-Phased
Locking (2PL)
 database elements are disjoint
pieces of data
 Nodes of the tree DO NOT
form a hierarchy based on
containment
Way to get to the node is
through its parent
Example: B-Tree
ADVANTAGES OF TREE
PROTOCOL
Unlocking takes less time as
compared to 2PL
Freedom from deadlocks
18.7.1 MOTIVATION FOR
TREE-BASED LOCKING
 Consider B-Tree Index, treating individual
nodes as lockable database elements.
 Concurrent use of B-Tree is not possible
with standard set of locks and 2PL.
 Therefore, a protocol is needed which can
assure serializability by allowing access to
the elements all the way at the bottom of the
tree even if the 2PL is violated.
18.7.1 MOTIVATION FOR
TREE-BASED LOCKING (cont.)
Reason for : “Concurrent use of B-Tree is not
possible with standard set of locks and 2PL.”
every transaction must begin with locking the
root node i.e., the root is locked to lock the
child
2PL transactions can not unlock the root until
all the required locks are acquired.
18.7.2 ACCESSING TREE
STRUCTURED DATA
Assumptions:
Only one kind of lock
Consistent transactions
Legal schedules
No 2PL requirement on transaction
18.7.2 RULES FOR ACCESSING
TREE STRUCTURED DATA
RULES:
First lock can be at any node.
Subsequent locks may be acquired only after
parent node has a lock.
Nodes may be unlocked any time.
No relocking of the nodes even if the node’s
parent is still locked
18.7.3 WHY TREE
PROTOCOL WORKS?
 Tree protocol implies a serial order on
transactions in the schedule.
Order of precedence:
Ti < s Tj
If Ti locks the root before Tj, then Ti locks
every node in common with Tj before Tj.
ORDER OF PRECEDENCE
• In above example since T3 already locked E, T2
cannot lock E until T3 released the lock
What is Timestamping?
• Scheduler assign each transaction T a unique
number, it’s timestamp TS(T).
• Timestamps must be issued in ascending
order, at the time when a transaction first
notifies the scheduler that it is beginning.
Timestamp TS(T)
• Two methods of generating Timestamps.
– Use the value of system, clock as the timestamp.
– Use a logical counter that is incremented after a
new timestamp has been assigned.
• Scheduler maintains a table of currently active
transactions and their timestamps irrespective
of the method used
Timestamps for database element X
and commit bit
• RT(X):- The read time of X, which is the highest
timestamp of transaction that has read X.
• WT(X):- The write time of X, which is the highest
timestamp of transaction that has write X.
• C(X):- The commit bit for X, which is true if and only
if the most recent transaction to write X has already
committed.
Physically Unrealizable Behavior
Three types of Physically Unrealizable Behavior
1. Read too late:
2. Write too late
3. Dirty Read
U writes X
T reads X
Read too late:
• A transaction U that started after transaction T, but
wrote a value
for XUbefore
T reads X.
T start
start
Physically Unrealizable Behavior
Write too late
• A transaction U that started after T, but read X before
T got a chance to write X.
U reads X
T writes X
T start
U start
Figure: Transaction T tries to write too late
Dirty Read
• It is possible that after T reads the value of X written
by U, transaction U will abort.
U writes X
T reads X
U start
T start
U aborts
T could perform a dirty read if it reads X when shown
Rules for Timestamps-Based
scheduling
1.
Scheduler receives a request rT(X)
a) If TS(T) ≥ WT(X), the read is physically realizable.
1. If C(X) is true, grant the request, if TS(T) > RT(X), set
RT(X) := TS(T); otherwise do not change RT(X).
2. If C(X) is false, delay T until C(X) becomes true or
transaction that wrote X aborts.
b) If TS(T) < WT(X), the read is physically
unrealizable. Rollback T.
Rules for Timestamps-Based
scheduling (Cont.)
2. Scheduler receives a request WT(X).
a) if TS(T) ≥ RT(X) and TS(T) ≥ WT(X), write is physically realizable
and must be performed.
1. Write the new value for X,
2. Set WT(X) := TS(T), and
3. Set C(X) := false.
b) if TS(T) ≥ RT(X) but TS(T) < WT(X), then the write is physically
realizable, but there is already a later values in X.
a. If C(X) is true, then the previous writers of X is
and ignore the write by T.
b. If C(X) is false, we must delay T.
committed,
c) if TS(T) < RT(X), then the write is physically unrealizable, and T
must be rolled back.
Rules for Timestamps-Based
scheduling (Cont.)
3. Scheduler receives a request to commit T. It must find all the
database elements X written by T and set C(X) := true. If any
transactions are waiting for X to be committed, these
transactions are allowed to proceed.
4. Scheduler receives a request to abort T or decides to rollback
T, then any transaction that was waiting on an element X that
T wrote must repeat its attempt to read or write.
Multiversion Timestamps
• Multiversion schemes keep old versions of data item
to increase concurrency.
• Each successful write results in the creation of a new
version of the data item written.
• Use timestamps to label versions.
• When a read(X) operation is issued, select an
appropriate version of X based on the timestamp of
the transaction, and return the value of the selected
version.
Timestamps and Locking
• Generally, timestamping performs better than
locking in situations where:
– Most transactions are read-only.
– It is rare that concurrent transaction will try to
read and write the same element and that is not
possible as transactions are given locks only when
they are serializable
• In high-conflict situation, locking performs better
than timestamps
At a Glance
Introduction
Validation based scheduling
Validation based Scheduler
Expected exceptions
Validation rules
Example
Comparisons
Summary
Introduction
What is optimistic concurrency control?
(assumes no unserializable behavior will occur)
• Timestamp- based scheduling and
• Validation-based scheduling
(allows T to access data without locks)
Validation based scheduling
Scheduler keeps a record of what the active
transactions are doing.
Executes in 3 phases
1. Read- reads from RS( ), computes local address
2. Validate- compares read and write sets
3. Write- writes from WS( )
Validation based Scheduler
Contains an assumed serial order of
transactions.
Maintains three sets:
– START( ): set of T’s started but not completed
validation.
– VAL( ): set of T’s validated but not finished the
writing phase.
– FIN( ): set of T’s that have finished.
Expected exceptions
1. Suppose there is a transaction U, such that:
 U is in VAL or FIN; that is, U has validated,
 FIN(U)>START(T); that is, U did not finish before T started
 RS(T) ∩WS(T) ≠φ; let it contain database element X.
2. Suppose there is transaction U, such that:
• U is in VAL; U has successfully validated.
•FIN(U)>VAL(T); U did not finish before T entered its validation phase.
•WS(T) ∩ WS(U) ≠φ; let x be in both write sets.
Validation rules
Check that RS(T) ∩ WS(U)= φ for any
previously validated U that did not finish
before T has started i.e. FIN(U)>START(T).
Check that WS(T) ∩ WS(U)= φ for any
previously validated U that did not finish
before T is validated i.e. FIN(U)>VAL(T)
Example
Solution
 Validation of U:
Nothing to check
 Validation of T:
WS(U) ∩ RS(T)= {D} ∩{A,B}=φ
WS(U) ∩ WS(T)= {D}∩ {A,C}=φ
 Validation of V:
RS(V) ∩ WS(T)= {B}∩{A,C}=φ
WS(V) ∩ WS(T)={D,E}∩ {A,C}=φ
RS(V) ∩ WS(U)={B} ∩{D}=φ
 Validation of W:
RS(W) ∩ WS(T)= {A,D}∩{A,C}={A}
WS(W) ∩ WS(V)= {A,D}∩{D,E}={D}
WS(W) ∩ WS(V)= {A,C}∩{D,E}=φ (W is not validated because RS(W) ∩ WS(T) IS
not equal to φ and WS(W) ∩ WS(V)= IS not equal to φ )
Comparison
Concurrency control
Mechanisms
Storage Utilization
Delays
Locks
Space in the lock table is
proportional to the number of
database elements locked.
Delays transactions but
avoids rollbacks
Timestamps
Space is needed for read and
write times with every database
element, neither or not it is
currently accessed.
Do not delay the
transactions but cause them
to rollback unless Interface
is low
Validation
Space is used for timestamps
and read or write sets for each
currently active transaction, plus
a few more transactions that
finished after some currently
active transaction began.
Do not delay the
transactions but cause them
to rollback unless interface
is low
21.1 Introduction to Information
Integration
CS257 Fan Yang
Need for Information Integration
• All the data in the world could put in a single
database (ideal database system)
• In the real world (impossible for a single
database):
databases are created independently
hard to design a database to support future
use as they have differences in the way data is
stored
University Database
• Registrar: to record student and grade
• Bursar: to record tuition payments by students
• Human Resources Department: to record
employees
• Other department….
Inconvenient
• Record grades for students who pay tuition
• Want to swim in SJSU aquatic center for free
in summer vacation?
(all the cases above cannot achieve the
function by a single database)
• Solution: one database
How to integrate
• Start over
build one database: contains all the legacy
databases; rewrite all the applications
result: painful
• Build a layer of abstraction (middleware)
on top of all the legacy databases
this layer is often defined by a collection of
classes
BUT…
Heterogeneity Problem
• What is Heterogeneity Problem
Aardvark Automobile Co.
1000 dealers has 1000 databases
to find a model at another dealer
can we use this command:
SELECT * FROM CARS
WHERE MODEL=“A6”;
Type of Heterogeneity
•
•
•
•
•
•
Communication Heterogeneity
Query-Language Heterogeneity
Schema Heterogeneity
Data type difference
Value Heterogeneity
Semantic Heterogeneity
Conclusion
• One database system is perfect, but
impossible
• Independent database is inconvenient
• Integrate database
1. start over
2. middleware
• heterogeneity problem
Chapter 21.2
Modes of Information Integration
Content Index
21.2 Modes of Information Integration
21.2.1 Federated Database Systems
21.2.2 Data Warehouses
21.2.3 Mediators
Federations
 The simplest architecture for integrating several
DBs
 One to one connections between all pairs of
DBs (Wrappers)
 n DBs talk to each other, n(n-1) wrappers are
needed
 Good when communications between DBs are
limited
Wrapper
•
Wrapper : a software translates incoming
queries and outgoing answers. In a result, it
allows information sources to conform to
some shared schema.
Federations Diagram
DB1
DB2
2 Wrappers
2 Wrappers
2 Wrappers
2 Wrappers
2 Wrappers
2 Wrappers
DB3
DB4
A federated collection of 4 DBs needs 12 components to translate queries
from one to another.
Example
Car dealers want to share their inventory. Each dealer queries the
other’s DB to find the needed car.
Dealer-1’s DB relation: NeededCars(model,color,autoTrans)
Dealer-2’s DB relation: Auto(Serial, model, color)
Options(serial,option)
wrapper
Dealer-1’s DB
wrapper
Dealer-2’s DB
Example…
For(each tuple(:m,:c,:a) in NeededCars){
if(:a=TRUE){/* automatic transmission wanted */
SELECT serial
FROM Autos, Options
WHERE Autos.serial = Options.serial AND Options.option = ‘autoTrans’
AND Autos.model = :m AND Autos.color =:c;
}
Else{/* automatic transmission not wanted */
SELECT serial
FROM Auto
WHERE Autos.model = :m AND
Autos.color = :c AND
NOT EXISTS( SELECT * FROM Options WHERE serial = Autos.serial
AND option=‘autoTrans’);
}
}
Dealer 1 queries Dealer 2 for needed cars
Data Warehouse



Sources are translated from their local
schema to a global schema and copied to a
central DB.
User transparent: user uses Data Warehouse
just like an ordinary DB
User is not allowed to update Data
Warehouse
Warehouse Diagram
User
query
result
Warehouse
Combiner
Extractor
Extractor
Source 1
Source 2
Example
Construct a data warehouse from sources DB of 2 car dealers:
Dealer-1’s schema: Cars(serialNo, model,color,autoTrans,cdPlayer,…)
Dealer-2’s schema: Auto(serial,model,color)
Options(serial,option)
Warehouse’s schema:
AutoWhse(serialNo,model,color,autoTrans,dealer)
Extractor --- Query to extract data from Dealer-1’s data:
INSERT INTO AutosWhse(serialNo, model, color, autoTans, dealer)
SELECT serialNo,model,color,autoTrans,’dealer1’
From Cars;
Example
Extractor --- Query to extract data from Dealer-2’s data:
INSERT INTO AutosWhse(serialNo, model, color, autoTans, dealer)
SELECT serialNo,model,color,’yes’,’dealer2’
FROM Autos,Options
WHERE Autos.serial=Options.serial AND
option=‘autoTrans’;
INSERT INTO AutosWhse(serialNo, model, color, autoTans, dealer)
SELECT serialNo,model,color,’no’,’dealer2’
FROM Autos
WHERE NOT EXISTS ( SELECT * FROM serial =Autos.serial
AND option = ‘autoTrans’);
Construct Data Warehouse
There are mainly 3 ways to constructing
the data in the warehouse:
1)
Periodically reconstructed from the current data in the
sources, once a night or at even longer intervals.
Advantages:
simple algorithms.
Disadvantages:
1) need to shut down the warehouse;
2) data can become out of date.
Construct Data Warehouse
2)
Updated periodically based on the changes(i.e. each
night) of the sources.
Advantages:
involve smaller amounts of data. (important when warehouse is
large and needs to be modified in a short period)
Disadvantages:
1) the process to calculate changes to the warehouse is complex.
2) data can become out of date.
Construct Data Warehouse
3)
Changed immediately, in response to each change or a
small set of changes at one or more of the sources.
Advantages:
data won’t become out of date.
Disadvantages:
requires too much communication, therefore, it is
generally too expensive.
(practical for warehouses whose underlying sources changes
slowly.)
Mediators

Virtual warehouse, which supports a virtual view or
a collection of views, that integrates several
sources.
 Mediator doesn’t store any data.
 Mediators’ tasks:
1)receive user’s query,
2)send queries to wrappers,
3)combine results from wrappers,
4)send the final result to user.
A Mediator diagram
Result
User query
Mediator
Query
Result
Result
Wrapper
Query
Result
Source 1
Query
Wrapper
Query
Result
Source 2
Example
Same data sources as the example of data warehouse, the mediator
Integrates the same two dealers’ source into a view with schema:
AutoMed(serialNo,model,color,autoTrans,dealer)
When the user have a query:
SELECT sericalNo, model
FROM AkutoMed
Where color=‘red’
Example
In this simple case, the mediator forwards the same query to each
Of the two wrappers.
Wrapper1: Cars(serialNo, model, color, autoTrans, cdPlayer, …)
SELECT serialNo,model
FROM cars
WHERE color = ‘red’;
Wrapper2: Autos(serial,model,color); Options(serial,option)
SELECT serial, model
FROM Autos
WHERE color=‘red’;
The mediator needs to interprets serial into serialNo, and then
returns the union of these sets of data to user.
Example
There may be different options for the mediator to forward user query,
for example, the user queries if there are a specific model&color car
(i.e. “Gobi”, “blue”).
The mediator decides 2nd query is needed or not based on the result of
1st query. That is, If dealer-1 has the specific car, the mediator doesn’t
have to query dealer-2.
Chapter 21 Information Integration
21.3 Wrappers in Mediator-Based Systems
Intro
• Templates for Query patterns
• Wrapper Generator
• Filter
Wrappers in Mediator-based Systems
 More complicated than that in most data warehouse
system.
 Able to accept a variety of queries from the mediator
and translate them to the terms of the source.
 Communicate the result to the mediator.
How to design a wrapper?
Classify the possible queries that the mediator can
ask into templates, which are queries with
parameters that represent constants and gets
information from various databases.
Templates for Query Patterns:

Use notation T=>S to express the
idea that the template T is turned by
the wrapper into the source query S.
• Example 1
Dealer 1
Cars (serialNo, model, color, autoTrans,
navi,…)
For use by a mediator with schema
AutoMed (serialNo, model, color, autoTrans,
dealer)
• We denote the code representing that color
by the parameter $c, then the template will
be:
SELECT *
FROM AutosMed
WHERE color = ’$c’;
=>
SELECT serialNo, model, color, autoTrans, ’dealer1’
FROM Cars
WHERE color=’$c’;
(Template T => Source query S)
• There will be total 2n templates if we have the
option of specifying n attributes.
Wrapper Generators
• The wrapper generator creates a table holds
the various query patterns contained in the
templates.
• The source queries that are associated with
each.
A driver is used in each wrapper, the task of
the driver is to:
 Accept a query from the mediator.
 Search the table for a template that matches the
query.
 The source query is sent to the source, again using a
“plug-in” communication mechanism.
 The response is processed by the wrapper.
Filter
• Have a wrapper filter to supporting more
queries.
• Example 2
• If wrapper is designed with more complicated
template with queries specify both model and
color.
SELECT *
FROM AutosMed
WHERE model = ’$m’ AND color = ’$c’;
=>
SELECT serialNo, model, color, autoTrans, ’dealer1’
FROM Cars
WHERE model = ’$m’ AND color=’$c’;
• Now we suppose the only template we have is
color. However the wrapper is asked by the
Mediator to find “blue Gobi model car.”
• Initiate a template with blue first then
followed by model name
Solution:
1. Use template with $c=‘blue’ find all blue cars
and store them in a temporary relation:
TemAutos (serialNo, model, color, autoTrans,
dealer)
2.The wrapper then return to the mediator the
desired set of automobiles by excuting the local
query:
SELECT*
FROM TemAutos
WHERE model= ’Gobi’;
INFORMATION
INTEGRATION
Sections 21.4 – 21.5
Presentation Outline
 21.4 Capability Based Optimization
 21.4.1The Problem of Limited Source
Capabilities
 21.4.2 A notation for Describing Source
Capabilities
 21.4.3 Capability-Based Query-Plan Selection
 21.4.4 Adding Cost-Based Optimization
 21.5 Optimizing Mediator Queries
 21.5.1 Simplified Adornment Notation
 21.5.2 Obtaining Answers for Subgoals
 21.5.3 The Chain Algorithm
 21.5.4 Incorporating Union Views at the
Mediator
21.4 Capability Based Optimization
• Introduction
– Typical DBMS estimates the cost of each query
plan and picks what it believes to be the best
– The strategy is to get details quickly with less cost
– Mediator – has knowledge of how long its sources
will take to answer
– Optimization of mediator queries cannot rely on
cost measure alone to select a query plan
– Optimization by mediator follows capability based
optimization
21.4.1 The Problem of Limited Source
Capabilities
• Many sources have only Web Based interfaces
• Web sources usually allow querying through a
query form
• E.g. Amazon.com interface allows us to query
about books in many different ways.
• But we cannot ask questions that are too
general
– E.g. Select * from books;
21.4.1 The Problem of Limited Source
Capabilities (con’t)
• Reasons why a source may limit the ways in
which queries can be asked
– Earliest database did not use relational DBMS that
supports SQL queries
– Indexes on large database may make certain queries
feasible, while others are too expensive to execute
– Security reasons
• E.g. Medical database may answer queries about averages,
but won’t disclose details of a particular patient's
information
21.4.2 A Notation for Describing
Source Capabilities
For relational data, the legal forms of queries are
described by adornments
Adornments – Sequences of codes that represent
the requirements for the attributes of the
relation, in their standard order
f(free) – attribute can be specified or not
b(bound) – must specify a value for an attribute but
any value is allowed
u(unspecified) – not permitted to specify a value for a
attribute
21.4.2 A notation for Describing
Source Capabilities….(cont’d)
c[S](choice from set S) means that a value must be
specified and value must be from finite set S.
o[S](optional from set S) means either do not
specify a value or we specify a value from finite set
S
A prime (f’) specifies that an attribute is not a part
of the output of the query
 A capabilities specification is a set of adornments
 A query must match one of the adornments in its
capabilities specification
21.4.2 A notation for Describing
Source Capabilities….(cont’d)
E.g. Dealer 1 is a source of data in the form:
Cars (serialNo, model, color, autoTrans, navi)
The adornment for this query form is b’uuuu
21.4.3 Capability-Based Query-Plan
Selection
• Given a query at the mediator, a capability based
query optimizer first considers what queries it
can ask at the sources to help answer the query
• The process is repeated until we obtain feasibility
or impossibility
– Enough queries are asked at the sources to resolve all
the conditions of the mediator query and therefore
query is answered. Such a plan is called feasible.
– We can construct no more valid forms of source
queries, yet still cannot answer the mediator query. It
has been an impossible query.
21.4.3 Capability-Based Query-Plan
Selection (cont’d)
• The simplest form of mediator query where we
need to apply the above strategy is join relations
• E.g we have sources for dealer 2
– Autos(serial, model, color)
– Options(serial, option)
• Suppose that ubf is the sole adornment for Auto and
Options have two adornments, bu and uc[autoTrans, navi]
• Query is – find the serial numbers and colors of Gobi models
with a navigation system
21.4.4 Adding Cost-Based
Optimization
• Mediator’s Query optimizer is not done when the
capabilities of the sources are examined
• Having found feasible plans, it must choose
among them
• Making an intelligent, cost based query
optimization requires that the mediator knows a
great deal about the costs of queries involved
• Sources are independent of the mediator, so it is
difficult to estimate the cost
21.5 Optimizing Mediator Queries
• Chain algorithm – a greed algorithm that finds
a way to answer the query by sending a
sequence of requests to its sources.
– Will always find a solution assuming at least one
solution exists.
– The solution may not be optimal.
21.5.1 Simplified Adornment Notation
• A query at the mediator is limited to b (bound)
and f (free) adornments.
• We use the following convention for
describing adornments:
– nameadornments(attributes)
– where:
• name is the name of the relation
• the number of adornments = the number of attributes
21.5.2 Obtaining Answers for
Subgoals
• Rules for subgoals and sources:
– Suppose we have the following subgoal:
Rx1x2…xn(a1, a2, …, an),
and source adornments for R are: y1y2…yn.
• If yi is b or c[S], then xi = b.
• If xi = f, then yi is not output restricted.
– The adornment on the subgoal matches the
adornment at the source:
• If yi is f, u, or o[S] and xi is either b or f.
21.5.3 The Chain Algorithm
• Maintains 2 types of information:
– An adornment for each subgoal.
– A relation X that is the join of the relations for all the
subgoals that have been resolved.
• Initially, the adornment for a subgoal is b iff the
mediator query provides a constant binding for
the corresponding argument of that subgoal.
• Initially, X is a relation over no attributes,
containing just an empty tuple.
21.5.3 The Chain Algorithm (con’t)
First, initialize adornments of subgoals and X.
Then, repeatedly select a subgoal that can be
resolved. Let Rα(a1, a2, …, an) be the subgoal:
1.Wherever α has a b, we shall find the
argument in R is a constant, or a variable in
the schema of R.
 Project X onto its variables that appear in R.
21.5.3 The Chain Algorithm (con’t)
2. For each tuple t in the project of X, issue a
query to the source as follows (β is a source
adornment).
– If a component of β is b, then the corresponding
component of α is b, and we can use the
corresponding component of t for source query.
– If a component of β is c[S], and the corresponding
component of t is in S, then the corresponding
component of α is b, and we can use the
corresponding component of t for the source query.
– If a component of β is f, and the corresponding
component of α is b, provide a constant value for
source query.
21.5.3 The Chain Algorithm (con’t)
– If a component of β is u, then provide no binding
for this component in the source query.
– If a component of β is o[S], and the
corresponding component of α is f, then treat it
as if it was a f.
– If a component of β is o[S], and the
corresponding component of α is b, then treat it
as if it was c[S].
3. Every variable among a1, a2, …, an is now
bound. For each remaining unresolved
subgoal, change its adornment so any
position holding one of these variables is b.
21.5.3 The Chain Algorithm (con’t)
4. Replace X with X πs(R), where S is all of the
variables among: a1, a2, …, an.
5. Project out of X all components that
α
correspond to variables
that do not appear
in the head or in any unresolved subgoal.
• If every subgoal is resolved, then X is the
answer.
• If every subgoal is not resolved, then the
algorithm fails.
21.5.3 The Chain Algorithm Example
• Mediator query:
– Q: Answer(c) ← Rbf(1,a) AND Sff(a,b) AND Tff(b,c)
• Example:
Relation
Data
Adornment
R
S
T
w
x
x
y
y
z
1
2
2
4
4
6
1
3
3
5
5
7
1
4
5
8
bf
c’[2,3,5]f
bu
21.5.3 The Chain Algorithm Example
(con’t)
• Initially, the adornments on the subgoals are
the same as Q, and X contains an empty tuple.
– S and T cannot be resolved because they each
have ff adornments, but the sources have either a
b or c.
• R(1,a) can be resolved because its adornments
are matched by the source’s adornments.
• Send R(w,x) with w=1 to get the tables on the
previous page.
21.5.3 The Chain Algorithm Example
(con’t)
• Project the subgoal’s relation onto its second
component, since only the second
component of R(1,a) is a variable.
a
2
3
4
• This is joined with X, resulting in X equaling
this relation.
• Change adornment on S from ff to bf.
21.5.3 The Chain Algorithm Example
(con’t)
• Now we resolve Sbf(a,b):
– Project X onto a, resulting in X.
– Now, search S for tuples with attribute a
equivalent to attribute
abin X.
a
2
4
3
5
• Join this relation with X, and remove a
because it doesn’t appear
in the head nor any
b
unresolved subgoal: 4
5
21.5.3 The Chain Algorithm Example
(con’t)
• Now we resolve Tbf(b,c):
b
c
4
6
5
7
5
8
• Join this relation with X and project onto the c
attribute to get the relation for the head.
• Solution is {(6), (7), (8)}.
21.5.4 Incorporating Union Views at
the Mediator
• This implementation of the Chain Algorithm
does not consider that several sources can
contribute tuples to a relation.
• If specific sources have tuples to contribute
that other sources may not have, it adds
complexity.
• To resolve this, we can consult all sources, or
make best efforts to return all the answers.
INFORMATION
INTEGRATION
SECTION 21.5
Eilbroun Benjamin
CS 257 – Dr. TY Lin
Presentation Outline
21.5 Optimizing Mediator Queries
21.5.1 Simplified Adornment Notation
21.5.2 Obtaining Answers for Subgoals
21.5.3 The Chain Algorithm
21.5.4 Incorporating Union Views at the
Mediator
21.5 Optimizing Mediator Queries
• Chain algorithm – a greed algorithm that finds
a way to answer the query by sending a
sequence of requests to its sources.
– Will always find a solution assuming at least one
solution exists.
– The solution may not be optimal.
21.5.1 Simplified Adornment Notation
• A query at the mediator is limited to b (bound)
and f (free) adornments.
• We use the following convention for
describing adornments:
– nameadornments(attributes)
– where:
• name is the name of the relation
• the number of adornments = the number of attributes
21.5.2 Obtaining Answers for
Subgoals
• Rules for subgoals and sources:
– Suppose we have the following subgoal:
Rx1x2…xn(a1, a2, …, an),
and source adornments for R are: y1y2…yn.
• If yi is b or c[S], then xi = b.
• If xi = f, then yi is not output restricted.
– The adornment on the subgoal matches the
adornment at the source:
• If yi is f, u, or o[S] and xi is either b or f.
21.5.3 The Chain Algorithm
• Maintains 2 types of information:
– An adornment for each subgoal.
– A relation X that is the join of the relations for all the
subgoals that have been resolved.
• Initially, the adornment for a subgoal is b iff
the mediator query provides a constant
binding for the corresponding argument of
that subgoal.
• Initially, X is a relation over no attributes,
containing just an empty tuple.
21.5.3 The Chain Algorithm (con’t)
• First, initialize adornments of subgoals and X.
• Then, repeatedly select a subgoal that can be
resolved. Let Rα(a1, a2, …, an) be the subgoal:
1.Wherever α has a b, we shall find the
argument in R is a constant, or a variable in
the schema of R.
– Project X onto its variables that appear in R.
21.5.3 The Chain Algorithm (con’t)
2. For each tuple t in the project of X, issue a
query to the source as follows (β is a source
adornment).
– If a component of β is b, then the corresponding
component of α is b, and we can use the
corresponding component of t for source query.
– If a component of β is c[S], and the corresponding
component of t is in S, then the corresponding
component of α is b, and we can use the
corresponding component of t for the source query.
– If a component of β is f, and the corresponding
component of α is b, provide a constant value for
21.5.3 The Chain Algorithm (con’t)
– If a component of β is u, then provide no binding for
this component in the source query.
– If a component of β is o[S], and the corresponding
component of α is f, then treat it as if it was a f.
– If a component of β is o[S], and the corresponding
component of α is b, then treat it as if it was c[S].
3. Every variable among a1, a2, …, an is now
bound. For each remaining unresolved
subgoal, change its adornment so any
position holding one of these variables is b.
21.5.3 The Chain Algorithm (con’t)
4. Replace X with X πs(R), where S is all of the
variables among: a1, a2, …, an.
5. Project out of X all components that
α
correspond to variables
that do not appear
in the head or in any unresolved subgoal.
• If every subgoal is resolved, then X is the
answer.
• If every subgoal is not resolved, then the
algorithm fails.
21.5.3 The Chain Algorithm Example
• Mediator query:
– Q: Answer(c) ← Rbf(1,a) AND Sff(a,b) AND Tff(b,c)
• Example:
Relation
R
w
Data
1
Adornment
x
x
2
1
3
1
4
bf
S
T
y
y
2
4
4
6
3
5
5
7
5
8
c’[2,3,5]f
z
bu
21.5.3 The Chain Algorithm Example
(con’t)
• Initially, the adornments on the subgoals are
the same as Q, and X contains an empty tuple.
– S and T cannot be resolved because they each have ff
adornments, but the sources have either a b or c.
• R(1,a) can be resolved because its adornments
are matched by the source’s adornments.
• Send R(w,x) with w=1 to get the tables on the
previous page.
21.5.3 The Chain Algorithm Example
(con’t)
• Project the subgoal’s relation onto its second
component, since only the second component
of R(1,a) is a variable.
a
2
3
4
• This is joined with X, resulting in X equaling
this relation.
21.5.3 The Chain Algorithm Example
(con’t)
• Now we resolve Sbf(a,b):
– Project X onto a, resulting in X.
– Now, search S for tuples with attribute a equivalent to
attribute a in X.
a
b
2
4
3
5
• Join this relation with X, and remove a
because it doesn’t appear
in
the
head
nor
any
b
unresolved subgoal: 4
5
21.5.3 The Chain Algorithm Example
(con’t)
• Now we resolve Tbf(b,c):
b
c
4
6
5
7
5
8
• Join this relation with X and project onto the c
attribute to get the relation for the head.
• Solution is {(6), (7), (8)}.
21.5.4 Incorporating Union Views at
the Mediator
• This implementation of the Chain Algorithm
does not consider that several sources can
contribute tuples to a relation.
• If specific sources have tuples to contribute
that other sources may not have, it adds
complexity.
• To resolve this, we can consult all sources, or
make best efforts to return all the answers.
21.5.4 Incorporating Union Views at
the Mediator (con’t)
• Consulting All Sources
– We can only resolve a subgoal when each source for
its relation has an adornment matched by the current
adornment of the subgoal.
– Less practical because it makes queries harder to
answer and impossible if any source is down.
• Best Efforts
– We need only 1 source with a matching adornment to
resolve a subgoal.
– Need to modify chain algorithm to revisit each subgoal
when that subgoal has new bound requirements.
Questions
Local-as-View Mediators
Local-as-View Mediators.
• In a LAV mediator, global predicates defined
are not views of the source data.
• for each source, expressions are defined,
involving the global predicates that describe
the tuples that the source is able to produce.
• Queries are answered at the mediator by
discovering all possible ways to construct the
query using the views provided by the source.
Motivation for LAV Mediators
• Sometimes the the relationship between
what the mediator should provide and what
the sources provide is more subtle.
• For example, consider the predicate Par(c, p)
meaning that p is a parent of c which
represents the set of all child parent facts that
could ever exist.
• The sources will provide information about
whatever child-parent facts they know.
Motivation(contd..)
• There can be sources which may provide childgrandparent facts but not child- parent facts
at all.
• This source can never be used to answer the
child-parent query under GAV mediators.
• LAV mediators allow to say that a certain
source provides grand parent facts.
• They help discover how and when to use that
source in a given query.
Terminology for LAV Mediation.
• The queries at the mediator and the queries
that describe the source will be single Datalog
rules.
• A query that is a single Datalog rule is often
called a conjunctive query.
• The global predicates of the LAV mediator are
used as the subgoals of mediator queries.
• There are conjunctive queries that define
views.
Contd..
• Their heads each have a unique view
predicate that is the name of a view.
• Each view definition has a body consisting of
global predicates and is associated with a
particular source.
• It is assumed that each view can be
constructed with an all-free adornment.
Example..
• Consider global predicate Par(c, p) meaning that
p is a parent of c.
• One source produces parent facts. Its view is
defined by the conjunctive query- It deals with
parents
V1(c, p)  Par(c, p)
• Another source produces some grand parents
facts. Then its conjunctive query will be – It deals
with grand parents, ex: parents parent is grand
parent
V2(c, g)  Par(c, p) AND Par(p, g)
Example contd..
• The query at the mediator will ask for greatgrand parent facts that can be obtained from
the sources. The mediator query is –
Q(w, z)  Par(w, x) AND Par(x, y) AND Par(y, z)
• One solution can be using the parent
predicate(V1) directly three times.
Q(w, z)  V1(w, x) AND V1 (x, y) AND V1(y, z)
Example contd..
• Another solution can be to use V1(parent
facts) and V2(grandparent facts).
Q(w, z)  V1(w, x) AND V2(x, z)
Or
Q(w, z)  V2(w, y) AND V1(y, z)
Expanding Solutions.
• Consider a query Q, a solution S that has a
body whose subgoals are views and each view
V is defined by a conjunctive query with that
view as the head.
• The body of V’s conjunctive query can be
substituted for a subgoal in S that uses the
predicate V to have a body consisting of only
global predicates.
Expansion Algorithm
• A solution S has a subgoal V(a1, a2,…an) where
ai’s can be any variables or constants.
• The view V can be of the form
V(b1, b2,….bn)  B
Where B represents the entire body.
• V(a1, a2, … an) can be replaced in solution S by
a version of body B that has all the subgoals of
B with variables possibly altered.
Expansion Algorithm contd..
 The rules for altering the variables of B are:
1. First identify the local variables B, variables
that appear in the body but not in the head.
2. If there are any local variables of B that
appear in B or in S, replace each one by a
distinct new variable that appears nowhere
in the rule for V or in S.
3. In the body B, replace each bi by ai for
i
= 1,2…n.
Example.
• Consider the view definitions,
V1(c, p)  Par(c, p)
V2(c, g)  Par(c, p) AND Par(p, g)
• One of the proposed solutions S is
Q(w, z)  V1(w, x) AND V2(x, z)
• The first subgoal with predicate V1 in the solution
can be expanded as Par(w, x) as there are no local
variables. Both these definitions shown above in
2 and 3 deal with grand parents
Example Contd.
• The V2 subgoal has a local variable p which
doesn’t appear in S nor it has been used as a
local variable in another substitution. So p can
be left as it is.
• Only x and z are to be substituted for variables
c and g.
• The Solution S now will be
Q(w, z)  Par(w, x) AND Par(x, p) AND Par(p,z)
Containment of Conjunctive Queries
 A containment mapping from Q to E is a
function т from the variables of Q to the
variables and constants of E, such that:
1. If x is the ith argument of the head of Q,
then т(x) is the ith argument of the head of E.
2. Add to т the rule that т(c)=c for any constant
c. If P(x1,x2,… xn) is a subgoal of Q, then
P(т(x1), т(x2),… т(xn)) is a subgoal of E.
Example.
• Consider two Conjunctive queries:
Q1: H(x, y)  A(x, z) and B(z, y)
Q2: H(a, b)  A(a, c) AND B(d, b) AND A(a, d)
• When we apply the substitution,
Т(x) = a, Т(y) = b, Т(z) = d, the head of Q1
becomes H(a, b) which is the head of Q2.
So,there is a containment mapping from Q1 to
Q2.
Example contd..
• The first subgoal of Q1 becomes A(a, d) which
is the third subgoal of Q2.
• The second subgoal of Q1 becomes the second
subgoal of Q2.
• There is also a containment mapping from Q2
to Q1 so the two conjunctive queries are
equivalent.
Why the Containment-Mapping Test
Works
Suppose there is a containment mapping т from
Q1 to Q2.
When Q2 is applied to the database, we look for
substitutions σ for all the variables of Q2.
The substitution for the head becomes a tuple t
that is returned by Q2.
If we compose т and then σ, we have a mapping
from the variables of Q1 to tuples of the database
that produces the same tuple t for the head of Q1.
Finding Solutions to a Mediator Query
There can be infinite number of solutions built from
the views using any number of subgoals and variables.
LMSS Theorem can limit the search which states that
• If a query Q has n subgoals, then any answer produced by
any solution is also produced by a solution that has at most
n subgoals.
If the conjunctive query that defines a view V has in its
body a predicate P that doesn’t appear in the body of
the mediator query, then we need not consider any
solution that uses V.
Example.
• Recall the query
Q1: Q(w, z) Par(w, x) AND Par(x, y) AND
Par(y, z)
• This query has three subgoals, so we don’t
have to look at solutions with more than three
subgoals.
Why the LMSS Theorem Holds
• Suppose we have a query Q with n subgoals
and there is a solution S with more than n
subgoals.
• The expansion E of S must be contained in
Query Q, which means that there is a
containment mapping from Q to E.
• We remove from S all subgoals whose
expansion was not the target of one of Q’s
subgoals under the containment mapping.
Contd..
• We would have a new conjunctive query S’
with at most n subgoals.
• If E’ is the expansion of S’ then, E’ is a subset
of Q.
• S is a subset of S’ as there is an identity
mapping.
• Thus S need not be among the solutions to
query Q.