Objectives:

·To demonstrate your understanding of how a query is processed.

·Related to Objectives #3 and #4 as listed in the Syllabus Addendum.

Scenario:

As a DBA it is vital that you understand in detail how a query is processed by the DBMS. Since Team Retail is using Oracle, you need to demonstrate your knowledge of Oracle query processing. You recognize that all relational DBMS must perform the same general steps when processing a query even though the steps may have different names and the details may be somewhat different.

1.Consider the processing of a (read-only) query in Oracle. The query will proceed through multiple steps to produce the output.Assume the query contains (or could contain)select, from, where,andorderby clauses. Assume also the query may require the join of two tables. Create a flowchart or structured English "algorithm" to describe how a query is processed.

a. A description at the appropriate level of detail will include how and when syntax and semantic errors are identified through the return of a result set.

b. Include the processing that occurs when a syntax or semantic error occurs.

c. Include other errors as appropriate. Hint: remember the user has to be authorized.

d. Do notinclude details of the optimizer processing; identifying when the optimizer step occurs is sufficient.

e. Acceptable diagram formats include a flowchart, a UML activity diagram, or similar diagram style. The "algorithm" would be a numbered list of steps similar to those used in our assignment descriptions or a checklist.

f. This must be your own diagram or list of steps. Do not copy an existing diagram or algorithm.Such diagrams are usually too detailed or not detailed enough to answer this question.

2.Use the Global Engineering query from your Discussion 3 contribution that has two syntax errors. Describe in detail how this query is processed by Oracle.I recommend that you execute the query and save the results in a spool file; then refer to those results in your analysis. Consider the following in your answer.

a. What error does Oracle find first?

b. When does Oracle stop processing the query?

c. What error message does Oracle return?

d. Is that error message accurate?

e. Does Oracle identify both errors? Why or why not?

3.Use the Global Engineering query from your Discussion 3 contribution that has two semantic errors. Describe in detail how this query is processed by Oracle.I recommend that you execute the query and save the results in a spool file; then refer to those results in your analysis. Consider the following in your answer.

a. What error does Oracle find first?

b. When does Oracle stop processing the query?

c. What error message does Oracle return?

d. Is that error message accurate?

e. Does Oracle identify both errors? Why or why not?

4.Use the Global Engineering query from your Discussion 3 contribution that produces incorrect results. Execute the Explain Plan for this query. Describe in detail how this query is processed by Oracle.I recommend that you execute the query and save the results in a spool file; then refer to those results in your analysis. Consider the following in your answer.

a. How does Oracle process this query? That is, what does Explain Plan tell you about how the query is processed?

b. How would you recognize that the results were not correct?

c. What is the error that caused the query to produce incorrect results?

5.Extra Credit Oracle has to complete many steps in the processing of a DDL statements such as acreate tablestatement. List and describe the steps or substeps Oracle needs to process acreate tablestatement. Show them in the order in which they would occur.