Lab - Continuing to work with Oracle’s Query Optimizer

This assignment relates to the following Course Learning Requirements:

CLR 2 - Administer a DBMS using knowledge of SQL, database security features, globalization, and database architecture (storage, memory and processes)

Objectives of the Lab

To provide Learners with the opportunity to gain additional insight into aspects of query performance.

Background:

Review the Oracle site for background on Oracle Optimizer -- https://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#g92116

Lab Submission Tasks:

1. Logon as sysdba and unlock the SCOTT account. Also change the password of the scott account into “tiger”.  

a. From the scott user account: 

i. Enter: explain plan for

select ename, dname from emp natural join dept;

Enter: SELECT PLAN_TABLE_OUTPUT 

FROM TABLE(DBMS_XPLAN.DISPLAY()); 

ii. Clearly identify the steps that oracle is planning to use in order to execute the query submitted in (ai) , and paste a screen snapshot of the Explain Plan query and the Plan_Table_Output.

b. Set autotrace ON and re-execute the query in (ai). Paste a screen snapshot of Plan_Table_Output .

c. Set autotrace OFF. Give oracle hints on how to execute the above query. Remember you can give the hints by inserting /*+ gather_plan_statistics */   right after the select word in the select statement. 

d. Oracle uses the dbmx_xplan.display_cursor to pullout the format information stored in the library cache of the shared pool. Use the below statement to show the plan_table_output of all statistics that are related to the last executed query.

select * from table(dbms_xplan.display_cursor(format =>'allstats last')); 

Remember you may need to grant the user SCOTT the right privileges in order to display the information that has been stored in the statistical table. Indicate all your work.

2. Index null values:

a. Create an index “commi” on field comm in the emp table. Set autotrace ON, and then run a query to find all the employees with commission equals to null. Include a screenshot of the explain plan and plan table output. Has oracle used the index commi to answer the above query?

Why?

b. Drop the index commi, and then create another index on the same field with the same name but slightly a different structure. 

Create index commi on emp(comm, 1). Run the same query again as in part 2a. Include a screenshot of the explain plan and plan table output.

Has oracle used the index commi to answer the above query?

3. Index wildcards %:

a. Now we will work on the dept table. Create an index on the LOC field. Write a query that finds all the departments having their location starts with “NEW”. Include a screenshot of the explain plan and plan table output. Has oracle used the index to answer the query?

b. Write a query that finds all the departments having their location includes the word “YORK”. Include a screenshot of the explain plan and plan table output. Has oracle used the index to answer the query?

c. Create another index with different structure on the loc field.

Create index locctx on dept(loc) indextype is ctxsys.context;

Include a screenshot of the explain plan and plan table output for the following slightly changed from the above query?

Select * from dept where contains(loc, ‘YORK’) > 0; 

Has oracle used the index locctx

4. When Indexing is good?

a. Set the autotrace off and Set the timing on. Create two tables t1 and t2.

Create table t1(c1 varchar2(10));

Create table t2(c1 varchar2(10));

Run the below query to insert 1 million record in the table t1

insert into t1 select 'king' from dual connect by level < 1000000;

Take note of the time needed to insert into the table t1.

Now create an index on field c1 in the t2 table and then insert one million records to t2. Compare the two running times. Do you think indexing is good all the times?

Give a real-life situation where an index could/should be created after inserting data rather than before inserting data

Data