Please see attached files for more information.Lab Submission Tasks: 1. Prepare a new user and schema Create a new USER and Grant the appropriate privileges to this new USER so that...

Please see attached files for more information.

Lab Submission Tasks:

1.



Prepare a new user and schema

Create


a 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

2.




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:

3.




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


.

4.





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


.

5.




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


.

6.



Write an 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

7.



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


.

8.



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.

­­­­­­­­­­­­­­­­­­­­­­__________________________________________________________________________

9.



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


.

10.



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.

Take a screenshot of the Explain Plan query and the Plan_Table _Output and



paste in the box below


.