Generate massive amounts of sample data: Login to your account with your username and run the following pl-sql code



Download 9.46 Kb.
Date28.05.2018
Size9.46 Kb.
#50919
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.
Download 9.46 Kb.

Share with your friends:




The database is protected by copyright ©ininet.org 2024
send message

    Main page