Reference no: EM131069249
When tuning SQL code, use the following tools.
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
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
|
Principles of humanitarian ethics
: Humanitarian ethics provides a great bridge into practical application by showing how to apply theory to real-world ethical problems. Include the following in a three-page analysis:
|
|
The gilded age
: Identify events surrounding the Native American assimilation efforts, specifically the Dawes Act. Distinguish how opportunities in mining and trade out west impacted values during the Gilded Age or were impacted by values of the Gilded Age.
|
|
What is the chemical symbol for gold
: 1) Famous New Zealand scientist Ernest Rutherford was awarded a Nobel Prize in which field 2) What is the chemical symbol for gold?
|
|
First element on the periodic table
: 1) What is the first element on the periodic table? 2) What is the centre of an atom called? 3) True or false? Acids have a pH level below 7.
|
|
Write an equivalent query in the relational algebra
: Write an equivalent query in the relational algebra - 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.
|
|
Individual member accountable for the entire project
: 1. Vote to exonerate the three group project members who didn't cheat. You're convinced they had no reason to suspect Paul Colgan of dishonesty. Exon-erating them is the right thing to do.
|
|
What do we call an action that you should not perform
: If a diabolical villain threatened to blow up a city unless you kill one innocent person, what would a Kantian say you have to do?
|
|
Compare and contrast notions of ethical leadership
: Assignment on Compare and Contrast Notions of Ethical Leadership, Assume you are an instructor of a graduate level Leadership and Ethics course. Using the Caldwell, Hayes, and Long (2010) article as a basis, compare and contrast the concepts of lead..
|
|
Pressure exerted by the gas
: The pressure of a gas is 1000 mmHg at 475 K. At the constant volume the temperature is increased to 3200 K. What will be the new pressure exerted by the gas?
|