3612ICT/7204ICT – DATABASE SYSTEMS AND ADMINISTRATION/DATABASE TECHNOLOGY
Lab 2 – Week 3
Generate massive amounts of sample data:
1. Login to your account with your username and run the following PL-SQL code:
declare
age number;
begin
for i in 900000000..900010000 loop
age := dbms_random.value(18,99);
INSERT INTO student VALUES (i, 'John Smith', 'Database Theory', 'IT', age);
end loop;
commit;
end;
/
2. Turn autotrace on with the following command:
SQL> set autotrace on
Test the various indexes on the generated data:
1. Run the following Exact Query without an index and take note of the performance with autotrace:
SQL> select sname from student where age = 21;
2. Run the following Range Query without an index and take note of the performance with autotrace:
SQL> select sname from student where age between 40 and 99;
3. Create a hash index on student age:
SQL> create index studentageindex1 on student(age) global partition by hash (age) partitions 4;
4. Run the following Exact Query with the hash index and take note of the performance with autotrace:
SQL> select sname from student where age = 21;
5. Run the following Range Query with the hash index and take note of the performance with autotrace:
SQL> select sname from student where age between 40 and 99;
6. Drop the hash index we just created:
SQL> drop index studentageindex1;
7. Create a tree index on student age (by default, it create a b+ tree index):
SQL> create index studentageindex2 on student(age);
8. Run the following Exact Query with the tree index and take note of the performance with autotrace:
SQL> select sname from student where age = 21;
9. Run the following Range Query with the tree index and take note of the performance with autotrace:
SQL> select sname from student where age between 40 and 99;
10. Drop the b+ tree index we just created:
SQL> drop index studentageindex2;
10. Compare the performance of the queries (especially the Execution Plan) across the various exact and range queries to determine which index type would be best suited for each query.
Share with your friends: |
