This assignment is designed to help you to setup an environment to facilitate performance testing.
Prior to beginning this lab, create a text or wp document to record your results. Name the file CS3540L8Observations.
At specified steps, record your observations and/or answer the question. This document will be uploaded at the end of the lab.

1. Open a SQL*Plus Session
2. Connect to the database as SYSTEM.
3. Begin spooling to file CS3540Lab8s.txt
4. Open a second SQL*Plus Session
5. Connect to this session as idxtester
6. Begin spooling to file CS3540Lab8u.txt
7. Prepare the SQL*Plus display by setting the linesize and pagesize to 132.
8. Drop and/or Delete any tablespaces, users and/or indexes left over from prior labs.
9. At each remaining step of the lab insure that you are using the correct session/user.
   (note that you can use the SQL*Plus command: show user)
10. As the system user, query the table dba_ind_columns to insure that there are no indexes for the student and registration tables. If there are any, remove them.
    (Make sure you specify a table in upper case since there are thousands of indexes)
11. Insure that you will see script output by typing "set server output on"
12. Excute the CS3540PLSQL2.TXT script for 50,000 students and 250,000 registrations.
    If you don't have the script download and modify the script: CS3540PLSQL2.TXT
    http://rbhilton.com/wsucs/cs3540/cs3540plsql2.txt
    (note that it has errors. to see what the error is, you can type show errors at the SQL> prompt)
13. Query the count of the Student and Registration tables to insure that they were created and loaded.
14. As the system user, query the table dba_ind_columns to determine what indexes exist in the student and registration tables. Record your observation.
15. Set up for explain plans on queries by typing:
    set autotrace on explain
16. Run a query to determine the number of rows in the student table.
    Record your analysis of the resultant explain plan.
17. Run a query to determine the number of rows in the registration table.
    Record your analysis of the resultant explain plan.
18. Run the following query and record and explain the results of the explain plan.
    SELECT MAX(firstname)
    FROM Student s, Registration r
    WHERE s.studentID = r.studentID;
19. Run the following query and record and explain the results of the explain plan.
    Did the additional select critiera affect the performance of the query?
    SELECT MAX(firstname)
    FROM Student s, Registration r
    WHERE s.studentID = r.studentID
    AND courseID = '2650';
20. Create an index on the courseID column of the registration table.
    Time the index creation (set timing on) and record your results.
21. Run the query from step 18 again and record and explain the results of the explain plan.
    Did creating the index affect the performance of this query? If so, by what percent?
    
22. Run the query from step 19 again and record and explain the results of the explain plan.
    Did creating the index affect the performance of this query? If so, by what percent?
23. Experiment with other queries and index combinations. Record your results.
24. Summarize and record the effect of indexes on query performance.
25. End Spooling
    
    

Use Assignment Uploader to submit both your spool file and your observation document.