How does oracle process query

Assignment Help Database Management System

Reference no: EM13191055

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, and order by 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 not include 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?

Reference no: EM13191055

Questions Cloud

Define what is the theoretical yield for the reaction : What is the theoretical yield for this reaction under the given conditions? What is the percent yield for this reaction under the given conditions?

Which expression could be used to find the volume of drug : It is 3 feet high, and the diameter of its circular base is 2 feet. Which expression could be used to find the volume of the drum?

Determine regression coefficient (b) and the t-statistics : The Following table shows the regression coefficient (B) and the t-statistics (T) for the variables influencing business traveler demand for hotel rooms (including hotel prices and attributes) from the study Business Traveler Demand for Hotel Rooms

State the observed rotation of a mixture of oklahomane : What would be the observed rotation of a mixture of Oklahomane that is 98 parts (S)-Oklahomane and 2 parts (R)-Oklahomane

How does oracle process query : How does Oracle process this query? That is, what does Explain Plan tell you about how the query is processed - how would you recognize that the results were not correct?

What is the equation of this parabola : how to solve: The cross section of a large telescope diameter of 360 ft and a maximum depth of 36 feet. What is the equation of this parabola?

Describe how this system appears static : Explain how this system appears static but in fact is a dynamic equlibrium, Na+(aq) + Cl-(aq) --> NaCl(s), that is in constant motion though the mass of solid is constant.

Why might a large railroad be better organized as m-form : Even before the metals and manufacturing companies, US railroads in the 19th century were M-form organizations based on geography. Why might a large railroad be better organized as M-form than U-form

What is the likely magnitude of the equilibrium constant k : what is the likely magnitude of the equilibrium constant K? Hydrogen is used as a rocket fuel because it is very light and reacts explosively and completely with oxygen. For the combustion reaction

User Account

All Pages