Answer must be submitted in a sql script from oracle sql plus/oracle 11g
Assignment: Gain understanding of execution plans and index creation. Learn how to employ the 'Explain plan for' statement, create index statement and how to select from the plan_table.
Tasks:
By using the student and faculty tables create a select query which outputs all the students for a specific advisor. Make the execution plan, select out the explain plan. Make an index which allows for a better join among the two tables. Make and display the revised execution plan. The first line in your script must be to delete all rows from the plan_table so that in the end there are only two plans in the table.
Format: Submit the code to create the tables, insert data, run the select, show the execution plan, create an index and then produce and exhibit the execution plan again. You should submit the code as scripts that the instructor can run from an SQL prompt on an Oracle DBMS. Your submission should comprise an explanation of the scripts, their actions, and the order in which they must be run. Your code should not have any "side effects" (that is, it should not do anything not listed in the assignment description, and all of its actions must be explained in the program header).
Goals:
• Make and illustrate SQL skills.
• Develop your understanding of some database administration issues.