No Slide Title
Download
Report
Transcript No Slide Title
Disk Characteristics
Rotational Delay
Disk Controller
To disk subsystem
Seek time
Capacity
Rotational Speed (RPM)
Seek Time
Interface (SCSI, ATA, Serial ATA, Serial Attached SCSI, Fiber Channel)
Disk Block and Record Storage
Disk Block
Record size (Fixed / Variable)
Spanned vs. Unspanned
Blocking Factor
b6
b7
b8
b9
b10
b11
b12
b13
Disk Block
C101^Smith^Joe^Hanover^NJC102^Green^Ed^Lodi^NY
Record 1
Field separator
Record 2
Record separator
Spanned vs. Unspanned
Record Organization
Unspanned Record Organization
R1 R2
b6
R3 R4
b7
R5 R6
b8
b9
Spanned Record Organization
R1 R2 R3 R4 R5 R6
b6
b7
b8
b9
Block
101
102
103
104
FNAME
JOHN
FRANKLIN
JOYCE
RAMESH
JAMES
JENNIFER
AHMAD
ALICIA
LNAME
SMITH
WONG
ENGLISH
NARAYAN
BORG
WALLACE
JABBAR
ZELAYA
Primary Index
I-Field
123456789
453453453
888665555
987987987
Block
101
102
103
104
EMPID
123456789
333445555
453453453
666884444
888665555
987654321
987987987
999887777
DNO
5
5
5
5
1
4
4
4
SALARY
30000
40000
25000
38000
55000
43000
25000
25000
Block
101
102
103
104
FNAME
JOHN
FRANKLIN
JOYCE
RAMESH
JAMES
JENNIFER
AHMAD
ALICIA
LNAME
SMITH
WONG
ENGLISH
NARAYAN
BORG
WALLACE
JABBAR
ZELAYA
EMPID
123456789
333445555
453453453
666884444
888665555
987654321
987987987
999887777
DNO
5
5
5
5
1
4
4
4
SALARY
30000
40000
25000
38000
55000
43000
25000
25000
Secondary Index
I-Field
BORG
ENGLISH
JABBAR
NARAYAN
SMITH
WALLACE
WONG
ZELAYA
Record Pointer
Block
103
102
104
102
101
103
101
104
Record Offset
1
1
1
2
1
2
2
2
Block
101
102
103
104
FNAME
JAMES
JENNIFER
AHMAD
ALICIA
JOHN
FRANKLIN
JOYCE
RAMESH
LNAME
BORG
WALLACE
JABBAR
ZELAYA
SMITH
WONG
ENGLISH
NARAYAN
Clustering Index
I-Field
1
4
5
Block
101
101
103
EMPID
888665555
987654321
987987987
999887777
123456789
333445555
453453453
666884444
DNO
1
4
4
4
5
5
5
5
SALARY
55000
43000
25000
25000
30000
40000
25000
38000
Secondary Index
I-Field
BORG
ENGLISH
JABBAR
NARAYAN
SMITH
WALLACE
WONG
ZELAYA
Record Pointer
Block
101
104
102
104
103
101
103
102
Record Offset
1
1
1
2
1
2
2
2
Block
101
102
103
104
FNAME
JAMES
JENNIFER
AHMAD
ALICIA
JOHN
FRANKLIN
JOYCE
RAMESH
LNAME
BORG
WALLACE
JABBAR
ZELAYA
SMITH
WONG
ENGLISH
NARAYAN
EMPID
888665555
987654321
987987987
999887777
123456789
333445555
453453453
666884444
DNO
1
4
4
4
5
5
5
5
SALARY
55000
43000
25000
25000
30000
40000
25000
38000
B Tree Index
NARAYAN
ENGLISH
BORG ENGLISH
101
104
JABBAR NARAYAN
102
104
WALLACE
SMITH WALLACE
103
101
WONG ZELAYA
103
102
Block
101
102
103
104
FNAME
JAMES
JENNIFER
AHMAD
ALICIA
JOHN
FRANKLIN
JOYCE
RAMESH
LNAME
BORG
WALLACE
JABBAR
ZELAYA
SMITH
WONG
ENGLISH
NARAYAN
EMPID
888665555
987654321
987987987
999887777
123456789
333445555
453453453
666884444
DNO
1
4
4
4
5
5
5
5
SALARY
55000
43000
25000
25000
30000
40000
25000
38000
B+ Tree Index
NARAYAN
ENGLISH
BORG ENGLISH
101
104
JABBAR NARAYAN
102
104
WALLACE
SMITH WALLACE
103
101
WONG ZELAYA
103
102
Block
101
102
103
104
FNAME
JAMES
JENNIFER
AHMAD
ALICIA
JOHN
FRANKLIN
JOYCE
RAMESH
LNAME
BORG
WALLACE
JABBAR
ZELAYA
SMITH
WONG
ENGLISH
NARAYAN
EMPID
888665555
987654321
987987987
999887777
123456789
333445555
453453453
666884444
DNO
1
4
4
4
5
5
5
5
SALARY
55000
43000
25000
25000
30000
40000
25000
38000
B* Tree Index
NARAYAN
104
ENGLISH
WALLACE
104
101
BORG ENGLISH
101
104
JABBAR NARAYAN
102
104
SMITH WALLACE
103
101
WONG ZELAYA
103
102
Some Definitions
• Block Size: Minimum Fixed sized unit that can be read or written on disk (in
Bytes)
• Record Size: Size of a data record for a particular table. Add together
field/column sizes (in Bytes)
• Blocking Factor: The number of data records than can fit in a data block (in
Records per Block)
• Index Entry Size: The size of an index entry calculated as either (in Bytes):
– (Primary, Clustering) The size of the indexed column + size of the block pointer
– (Secondary) The size of the indexed column + size of the block pointer + the size
of the record offset
• Fan Out: The number of index entries that can fit in a Block (in Index entries
per block)
• Number of first level index entries:
Example: Primary Index
Block Size: 512 Bytes
Record Size: 35 Bytes
bFactor: 512 / 35 = 14 rec/block
nRecords: 6200
nBlocks: 6200 / 14 = 443 blocks
Fan Out: 512 / 19 = 26 index entry/block
Index Entry Size: 19 Bytes
443 / 26 = 17.03846 = 18 Blocks
First Index Level:
Second Index Level: 18 / 26 = 1 Block
Total Number of Levels:
2 Levels
(check with Log26 443)
Total Size of Primary Index:
(18 + 1) = 19 Blocks
I1 I27 I53 … I418
Index
2nd Level
1st Level
I1 I2 I3 I4… I26
I27 … I52
Data
IB1
R1 … R14 R15 … R28
Block 1
Block 2
I53 … I78
IB2
… …
B3 B4
…
… …
B26 B27
…
…
I418 … I443
IB3
… …
B52 B53
IB17
……
…
B78 B79
…
…R6200
B443
Example: Secondary Index
Block Size: 512 Bytes
Record Size: 35 Bytes
bFactor: 512 / 35 = 14 rec/block
Index Entry Size: 20 Bytes
nRecords: 6200
nBlocks: 6200 / 14 = 443 blocks
Fan Out: 512 / 20 = 25 index entry/block
6200 / 25 = 248 Blocks
First Index Level:
Second Index Level: 248 / 25 = 10 Blocks
10 / 25
Third Index Level:
Total Number of Levels:
3nd
=
1 Block
Total Size: 259 Blocks
3 Levels (check with Log25 6200)
I1 I626 …. I5626
Level
IB259
2nd Level
Index
IB249
IB1
I26 … I50
IB258
I51 … I75
R1 … R14 R15 … R28
Block 2
… …
B3 B4
…
… …
B26 B27
I6176 … I6200
…
IB3
IB2
Block 1
I5626 … I6176
IB250
I1 I2 I3 I4… I25
1st Level
Data
I626 … I1250
I1 I26 I51 … I625
IB248
…
… …
B52 B53
……
…
B78 B79
…
…R6200
B443
Example: Secondary Index – Close Up View
I1 I626 I1251
I1876 I2501 I3126 I3751 I4376 I5001 I5626
I6251
IB259
I1 I26 I51 I76 …. I526 I551 I576 I601
IB249
I626 I651 I676 I701 I726 …. I1176 I1201 I1226
IB250
I1 I2 I3 I4 I5 I6 I7 I8 I9 I10 I11 I12 I13 I14 I15 I16 I17 I18 I19 I20 I21 I22 I23 I24 I25
I26 I27 I28 I29 I30 I31 I32 I33 I34 …
IB1
R1 R2 R3 R4 R5 R6 R7 R8 R9 R10 R11 R12 R13 R14
Data Block 1
IB2
R15 R16 R17 R18 R19 R20 R21 R22 R23 R24 R25 R26 R27 R28
Data Block 2
Ordered File
Block
b1
b2
b3
b4
FNAME
JAMES
JOYCE
AHMAD
RAMESH
JOHN
JENNIFER
FRANKLIN
ALICIA
LNAME
BORG
ENGLISH
JABBAR
NARAYAN
SMITH
WALLACE
WONG
ZELAYA
SSN
888665555
453453453
987987987
666884444
123456789
987654321
333445555
999887777
DNO
1
5
4
5
5
4
5
4
SALARY
55000
25000
25000
38000
30000
43000
40000
25000
Unordered File
Block
b1
b2
b3
b4
FNAME
RAMESH
ALICIA
JAMES
JOHN
JENNIFER
FRANKLIN
AHMAD
JOYCE
LNAME
NARAYAN
ZELAYA
BORG
SMITH
WALLACE
WONG
JABBAR
ENGLISH
SSN
666884444
999887777
888665555
123456789
987654321
333445555
987987987
453453453
DNO
5
4
1
5
4
5
4
5
SALARY
38000
25000
55000
30000
43000
40000
25000
25000
Indexed File
Block
b1
b2
b3
b4
FNAME
RAMESH
ALICIA
JAMES
JOHN
JENNIFER
FRANKLIN
AHMAD
JOYCE
LNAME
NARAYAN
ZELAYA
BORG
SMITH
WALLACE
WONG
JABBAR
ENGLISH
Index
I-Value
BORG
ENGLISH
JABBAR
NARAYAN
SMITH
WALLACE
WONG
ZELAYA
Record Pointer
Block
b2
b4
b4
b1
b2
b3
b3
b1
SSN
666884444
999887777
888665555
123456789
987654321
333445555
987987987
453453453
DNO
5
4
1
5
4
5
4
5
SALARY
38000
25000
55000
30000
43000
40000
25000
25000