Ite 407 - Advanced Database Management Systems - Laboratory Exercise 3 Theory: Database Statistics
Ite 407 - Advanced Database Management Systems - Laboratory Exercise 3 Theory: Database Statistics
Ite 407 - Advanced Database Management Systems - Laboratory Exercise 3 Theory: Database Statistics
Laboratory Exercise 3
Objective
Tools/Applications
- Oracle Express covers a lot of group functions for this exercise. MySQL
may also be used.
- A script that creates an HR Schema. This can be obtained from your
Instructor. If you use Oracle Express, it is part of the installation.
Lab Activity:
2. Complete questions that follow the execution of the query below: [10]
a) Run the query below as it is:
SELECTemployee_id,job_id,department_id
FROMjob_history
ORDERBYemployee_id;
b) Write the statistics for the full table scan access in the table below
as A.
c) Create an index on employee_id and rerun the query.
d) Write the statistics for the index table scan access in the table
below as B.
I/O I/O Resultin Total I/O
Pla Ste
Operation Operatio Cost g Set Cost
n p
ns Rows
Full table scan
A A1 10 10 10 10
Sort (Order by
A 10 10 10 20
A2 Employee_id)
B1 Full index scan
B 10 10 10 10
B2 Index scan on Employee_id
B 10 10 10 20
B3 Sort (Order by
B 10 10 10 30
Employee_id)
SELECTdepartment_id,last_name,job_id
FROMemployees
WHEREdepartment_idIN(SELECTdepartment_id
FROMdepartments
WHEREdepartment_name='Executive');
b) Write the statistics for the full table scan access in the table below
as A.
c) Create an index on the appropriate columns and rerun the query.
State which columns you have used.
d) Write the statistics for the index table scan access in the table
below as B.