COMP5138_Database Management System_2014 Semester1_tutorial10pg-indexing_solution

School of Information Technologies

A/Prof Uwe R¨o hm COMP5138:Database Systems 1.Sem./2014 Tutorial Week10:Indexes and Concurrency Control

Example Solution Question1:System Catalog Exploration

a)What information is stored in the database system catalogs?Explore which tables you have

in your local schema and which indexes are already in place.

See e.g.the course web site for some information about Oracle’s system catalog.Note:To check which indexes are present in your schema(plus some details),you can use the following SQL commands in Oracle:

SELECT*FROM USER_INDEXES;

SELECT*FROM USER_IND_COLUMNS;

b)What are the bene?ts of making the system catalogs be relations?

Answer:There are several advantages to storing the system catalogs as relations.Relational system catalogs take advantage of all of the implementation and management bene?ts of relational tables:effective information storage and rich querying capabilities.The choice of what system catalogs to maintain is left to the DBMS implementor.

1

Question2:Index Creation with SQL

Consider our University/Student Reg.schema(Person,UnitOfStudy,Classroom etc.).

a)Check whether there are already some indexes available on your schema.To do so,issue

the SELECT statement on USER INDEXES as given above,and then explore the details on columns and indexes shown in the result.Can you explain,what you see?

Answer:Databases typically create an primary index for every primary key(in Oracle even so for secondary indexes too,cf.for instance the Student table in Oracle which should have two indices).These indices are clustered indices.Each index gets assigned an internal name.

Especially interesting:The Transcript table which has one combined index on its combined primary key.

b)Create at least one additional index on your schema,e.g.on the name of the Student table.

Answer:

CREATE INDEX Idx_Name ON Student(name);

c)Which kind of query does your index support best?

Answer:

E.g.with above name index:

SELECT...FROM Student where name=...;

d)Create an index on your University schema which supports the following SQL query:

SELECT*FROM ClassRoom WHERE seats BETWEEN100AND250

Answer:

CREATE INDEX Idx_ClassRoomSeats ON ClassRoom(seats);

e)Create a covering index which supports the following SQL query:

SELECT uosCode,MIN(year),MAX(year)

FROM UoSOffering

WHERE enrollment>10

GROUP BY uosCode

Answer:

The order of the attributes is important!

CREATE INDEX Idx_Covering ON UosOffering(enrollment,uosCode,year);

Question3:Storage and Access Costs Calculation(COMP5138)

2

Suppose we have a table Rel1(A,B,C).

Each A?eld occupies4bytes,each B?eld occupies12bytes,each C?eld occupies8bytes.Rel1 contains100,000records.There are100different values for A represented in the database,1000 different values for B,and50,000different values for C.

Rel1is stored with a primary(sparse,clustered)index on the composite key consisting of the pair of columns(A,B);assume this index has2levels.Assume that in this database,each block is4K bytes,of which250bytes are taken for header information.Assume that reading a disk block into memory takes150msec,and that the time needed for any query can be approximated by the time spent doing disk I/O.

a)Calculate the space needed for the data of Rel1.Hint:How much space is used by a single

row?Multiply by the number of rows in the table to get a quick estimate of space.[A more accurate answer can be done using the fact that rows are not split across blocks,and each block has a header;so we could work out how many rows per block,then how many blocks for the relation,and then convert this back to space.]

Answer:Each block has4096-250=3846bytes available to store data.As each row needs 4+12+8=24bytes,we can?t 3846/24 =160records in a block(note that we round down to an integer,as one doesn’t store just part of a row in a block).Since Rel1has100,000records, we need100,000/160=625blocks to store the relation;measured in bytes it is approx2.4MB.

b)Calculate the time taken to perform a table scan through the relation Rel1.

Hint:How many blocks are needed to make up the space occupied by the relation?How many blocks will be read from disk during a scan?

Answer:?T o do a table scan we must fetch each of the625blocks of the relation;measured in seconds,this takes625*150=93750ms=93.75s or approximately1.5minutes.

c)Calculate the time taken to execute a query like

SELECT C

FROM Rel1

WHERE A='AQG'and(B between'WPQ'and'XYZ')

Y ou may assume that the primary index is used to answer this.

Answer:We must descend the primary index,reading1block at each level(that is,we read 2blocks of index).Once we have a pointer to the?rst data record that satis?es the condition, we need to fetch all the data records that match(because the relation is clustered on the composite search key(A,B),the matching rows will all be together,one after another,in the data blocks).The question doesn’t give us enough information to know how many matching rows there are,so we need to make some guesses.We know that there are100different A values,so we can guess that100,000/100=1000data records have the A value’AQG’;what we need to guess is what fraction of them have B values in the given range(this is called the selectivity of the range condition).It is usual in databases to guess that selectivity is10%,if

3

one doesn’t have any better information.Thus we expect1000*10%=100records will satisfy

the matching condition,and be fetched from the data blocks.Since each block stores160

records,we probably only need to fetch1or perhaps2blocks of data to get all the matching

records;let’s guess that we only fetch1data block.Overall,we will need to fetch2index

blocks,and1data block;that is we read3blocks from disk,taking3*150=450ms.

d)Suppose that we often need to process a query

SELECT A,B

FROM Rel1

WHERE(B between'WPQ'and'XYZ')and C='UBMJ'

Why can’t the primary index be useful in processing this?What index should be created to

speed this up?How long will the query take using the extra index,assuming that the extra

index has2levels?

Answer:The primary index is ordered based on A?rst,and within that on B;the records

we want might have any or every possible A value,so the index doesn’t help us?nd them.

Instead let us create an(unclustered,secondary)index on C.We descend the index(doing

2block reads from disk,one at each level of the index).This gets us to index entries that

point at every data record with C=?OUBMJ’.We need to fetch each of these(and then check

the B value once the record is fetched).Since there are50,000different C values,each C

value occurs in approximately100,000/50,000=2data records;thus we need to fetch2data

records.because the relation is sorted by(A,B),it is unclustered on C;that is,the2data

records we fetch are probably located on different blocks of the data records.Overall,we read

2index blocks and2data blocks,or4blocks in total.This takes4*150=600ms.An alternative

solution would be to create an unclustered index on(C,B).In this case,we have to descend

the index,and then we can?nd in the index pointers to all the data records that match the full ?Owhere clause’condition.If we assume10%as the selectivity of the range condition on B, there are likely to be0.2such records(that is,we often might have no matching records,or

sometimes we will have1matching record).With this index,we often read no data blocks at all

(we can determine that the query returns no rows,just from the index);but in other instances

we fetch1data block.

Question4:COMP5138:Transactions on Oracle

Log in to the Oracle database twice(with two different browser windows)and analogous to the

previous experiment,try to update your Course table in the?rst window while concurrently looking

at the database content in the second window.

a)Transaction Isolation experiment.Execute the following two transactions line-by-line(press

’Execute’after each line)and write down the results of the SELECT COUNT()statements:

4

Window1Window2Count 0SET AUTOCOMMIT OFF;SET AUTOCOMMIT OFF;—1SELECT COUNT(*)FROM UnitOfStudy;

SELECT COUNT(*)FROM WhenOffered;

2INSERT INTO UnitOfStudy VALUES(...);—3SELECT COUNT(*)FROM UnitOfStudy;

4SELECT COUNT(*)FROM UnitOfStudy;

5INSERT INTO WhenOffered VALUES(..);—6SELECT COUNT(*)FROM WhenOffered;

7SELECT COUNT(*)FROM WhenOffered;

8Commit;—9SELECT COUNT(*)FROM UnitOfStudy;

Discuss your observations.Which of the four ACID properties are re?ected in the system responses?What would have happen if you did rollback instead of commit in Window1? Answer:In contrast to most other systems,Oracle uses Snapshot Isolation as concurrency control scheme.So the effects of concurrent transactions are different here–readers never block,but non-committed values are not seen until the commit of the updating transaction.The second window does not see the updates until?nally the?rst window commits;then suddenly the new inserted rows are counted too.This however means that the second transaction sees the changes of the committed?rst transaction...It is not completely isolated from that changes...

b)Isolation Level experiment.Execute the following two transactions line-by-line(press’Ex-

ecute’after each line)and write down the results of the SELECT COUNT()statements: Window1Window2Count 0SET AUTOCOMMIT OFF;SET AUTOCOMMIT OFF;—

SET TRANSACTION ISOLATION—

LEVEL SERIALIZABLE;—1SELECT COUNT(*)FROM UnitOfStudy;

SELECT COUNT(*)FROM WhenOffered;

2INSERT INTO UnitOfStudy VALUES(...);—3SELECT COUNT(*)FROM UnitOfStudy;

4SELECT COUNT(*)FROM UnitOfStudy;

5INSERT INTO WhenOffered VALUES(..);—6SELECT COUNT(*)FROM WhenOffered;

7SELECT COUNT(*)FROM WhenOffered;

8Commit;—9SELECT COUNT(*)FROM UnitOfStudy;

10Commit;—11SELECT COUNT(*)FROM UnitOfStudy;

Discuss the difference to the previous experiment.

Answer:This time,the second transaction is run by Oracle as SERIALIZABLE and hence it does not see any changes of the?rst transaction—even if this committed.Only after the second transaction committed itself,we see the changes in the second window.Note that this is done by a third transaction which automatically started after the commit of the previous read-only transaction.

5