Which two tasks are performed during the optimization stage of a SQL statement?

Question

Options

• AEvaluating the expressions and conditions in the query
• BChecking the syntax and analyzing the semantics of the statement
• CSeparating the clauses of the SQL statement into structures that can be processed
• DInspecting the integrity constraints and optimizing the query based on this metadata
• EGathering the statistics before creating the execution plan for the statement

• A
  17% (9)
• B
  2% (1)
• C
  10% (5)
• D
  71% (37)

Explanation

• Oracle SQL is parsed before execution, and a hard parse includes these steps: 1. Loading into shared pool - The SQL source code is loaded into RAM for parsing. (the "hard" 2. Syntax parse - Oracle parses the syntax to check for misspelled SQL keywords. 3. Semantic parse - Oracle verifies all table & column names from the dictionary and checks to see if you are authorized to see the data. 4. Query Transformation - If enabled (query_rewrite=true), Oracle will transform complex SQL into simpler, equivalent forms and replace aggregations with materialized views, as appropriate. 5. Optimization - Oracle then creates an execution plan, based on your schema statistics (or maybe with statistics from dynamic sampling in 10g). 6. Create executable - Oracle builds an executable file with native file calls to service the SQL The parsing process performs two main functions: Syntax Check: is the statement a valid one. Does it make sense given the SQL grammar documented in the SQL Reference Manual. Does it follow all of the rules for SQL. Semantic Analysis: Going beyond the syntax ? is the statement valid in light of the objects in the database (do the tables and columns referenced exist). Do you have access to the objects ? are the proper privileges in place? Are there ambiguities in the statement ? for example if there are two tables T1 and T2 and both have a column X, the query ?select X from T1, T2 where ?? is ambiguous, we don?t know which table to get X from. And so on. So, you can think of parsing as basically a two step process, that of a syntax check to check the validity of the statement and that of a semantic check ? to ensure the statement can execute

No community discussion yet for this question.