Prepare new user and schema

 

Create new USER and Grant the appropriate privileges to this new USER so that this USER is able to.....

CREATE/ALTER/DROP any tables and sequences.
INSERT/UPDATE/DELETE data in any table.
Have unlimited quota on tablespaces
 

Logon with  your new USER account and run the supplied script to create two tables, an Oracle Sequence, script to populate DEPARTMENT table and the four PL/SQL blocks (begin/end loops) used to populate the EMPLOYEE table.  Now, both tables should be populated

 

 

 

Begin testing the EXPLAIN PLAN functions -- Using your general USER account:

 

Enter:   EXPLAIN PLAN FOR

Select department_id

From department

Where department_id = 123;

Take a screenshot of the Explain Plan query and the Plan_Table _Output and paste in the box below.

                             

Enter:                SELECT PLAN_TABLE_OUTPUT

                            FROM TABLE(DBMS_XPLAN.DISPLAY());

Take a screenshot of the Explain Plan query and the Plan_Table _Output and paste in the box below.

                               

 

Modify the SQL query to select from the EMPLOYEE TABLE but return only 25 employee rows.

Take a screenshot of the Explain Plan query and the Plan_Table _Output and paste in the box below.

                     

 

Write Explain Plan for a query that displays DEPARTMENT.DEPARTMENT_ID, DEPARTMENT.DEPARTMENT_NAME, EMPLOYEE.LAST_NAME, EMPLOYEE.DEPARTMENT_ID.

Take a screenshot of the Explain Plan query and the Plan_Table _Output and paste in the box below.


The main purpose of the preceding activities was to reinforce the idea that a certain type of retrieval - generally considered relatively slow - will be the default approach to retrieving data.


Alter the tables by adding Primary Keys to both tables and review the new results from the EXPLAIN PLAN output


Add a primary key to the parent table (DEPARTMENT) and the child table (EMPLOYEE).

Rerun query from question 4 -  EXPLAIN PLAN.

If you obtain the same result , then

connect to SYS as sysdba and enter:
ALTER SYSTEM  FLUSH SHARED_POOL; and
bounce the database (shutdown, startup),
reconnect to your general user and rerun the EXPLAIN PLAN query.
 

Your Plan_Table_Output should now be significantly different from the non-indexed version (above)

Take a screenshot of the Explain Plan query and the Plan_Table _Output and paste in the box below.

How did 'adding a primary key' to a table change the results in the Query Plan?  Write down your findings on how a primary key affects the query's performance.


Now that you have added primary keys to both tables, rerun the query where DEPARTMENT and EMPLOYEE tables are joined together and look at how the EXPLAIN PLAN changes.

Take a screenshot of the Explain Plan query and the Plan_Table _Output and paste in the box below.


Add a foreign key to the child table referencing the parent table. This time, filter on one DEPARTMENT_ID = 123. Rerun the table join case.