一道關於block尺寸計算的筆試題

yuan22003發表於2011-08-24
BloC筆試
Assume that a database designer plans to use the following SQL statement to create a relational
table Driver.
CREATE TABLE Driver (
Licence# NUMBER(8),
FName VARCHAR(30),
LName VARCHAR(30),
Address VARCHAR(50),
Phone# CHAR(10),
LicenceClass CHAR,
DOB DATE,
LicenceExpireDate DATE,
IssueState CHAR(3)) PCTFREE 20 TABLESPACE ATO;
Assume that size of disk data block is equal to 4K bytes.
1) Consider a model of data block explained in the course, assume a sample data block consists of a fixed size header (100 bytes), row directory (8 bytes per single entry), and data area. Find the total number of data blocks needed to store all 106 rows in the table Driver.
Show all calculations.
2) Assume that tablespace ATO has free extents of 10MB each. Assume that the initial storage allocation should be large enough to accommodate information about 5*105 drivers.Propose a STORAGE clause for create table statement given above.   
Show all calculations.
1. 一條記錄的長度=8+30+30+50+10+1+8+8+3=148 bytes
   每條記錄加8 bytes的指標區=148+8=156
   共106條記錄 106*156=16536 bytes
   每個block的data區=(4*1024-100)*(1-0.2)=3196.8
   16536/3196=5.17=6塊
   需要六個資料塊
2. 需要存放的資料 5*105*156=81900 bytes
   需要的塊數 81900/3196=25.63=27 塊
   總大小 27*4=108K
  
CREATE TABLE Driver (
Licence# NUMBER(8),
FName VARCHAR(30),
LName VARCHAR(30),
Address VARCHAR(50),
Phone# CHAR(10),
LicenceClass CHAR,
DOB DATE,
LicenceExpireDate DATE,
IssueState CHAR(3))   
PCTFREE 20 TABLESPACE ATO
STORAGE(INITIAL 108K); 

相關文章