When tuning SQL code, use the following tools. (must be done on oracle express, and include a separate word document with the SQL statements. Include sql file and word document at the end please)
The following settings turn on the execution plan output.
SET AUTOTRACE ON;
SET SERVEROUTPUT ON;
SET TIMING ON;
Set AUTOTRACE to TRACEONLY for queries with voluminous output.
This query lists the current indexes.
SELECT c.index_name, c.table_name, c.column_name, i.index_type
FROM User_Ind_Columns c, User_Indexes i
WHERE c.index_name = i.index_name;
The index types are all set to "normal", the default B-tree; OracleXE doesn't support bitmapped indexes. You can deactivate/activate the indexes using the following command.
ALTER INDEX indexName [INVISIBLE, VISIBLE];
You can create/drop indexes using the following commands.
[CREATE, DROP] INDEX IndexName ON Tablename(fieldList);
You can execute code multiple times using the following anonymous code block.
DECLARE
dummy INTEGER;
BEGIN
FOR i IN 1..1000 LOOP
YourSelectCommand;
END LOOP;
END;
/
With this rather large database in place and these helpful tools, do the following exercises.
Exercise 4.1
Create a view that for the CPDB "birthday czar", which includes each person's full name, age (using
TRUNC(MONTHS_BETWEEN(SYSDATE, birthdate)/12) ) and birthdate (only), and then try the following.
Retrieve the GenX people from the database (i.e., those born from 1961-1975);
Update the Person base table to include a GenX birthdate for some person who had a NULL birthdate before and then re-run your query on the view from the previous question. Do the results of the view query change? Why or why not?
Try to insert a new person using your new view. If this doesn't work, explain (but do not implement) the modifications you'd have to make to your view so that it does. Be sure that you understand what is required for a view to be updateable and what happens to the fields of the inserted record in the base table not included in the view.
DROP your new view - does this affect your base tables in any way?
Exercise 4.2
Do the following for the query on which the view in the previous exercises is based.
Write an equivalent query in the relational algebra