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
.