一道oracle公司的笔试题,不太会算,请求各位高手的帮助要求:计算过程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.
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.
每条记录加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);不知道对不对,请高手指点.
需要的块数 81900/3196=25.63=26 块 总大小 26*4=104K
STORAGE(INITIAL 104K);