When we give SELECT * FROM EMP; How does oracle respond?
- 24 Answers
- 50390 Views
- Accenture, HCL, Infosys, I also Faced
- E-Mail Answers
Answer Posted / rameshg123
Phases of SQL Statement(Select Statement)
Ans: 1. Open the cursor
2. Parse the statement
During parsing, the SQL statement is
passed from the user process to Oracle and a parsed
representation of the SQL statement is loaded into a shared
SQL area. Many errors can be caught during this phase of
statement processing. Parsing is the process of
• translating a SQL statement, verifying it to be a
valid statement
• performing data dictionary lookups to check table
and column definitions
• acquiring parse locks on required objects so that
their definitions do not change during the statement's
parsing
• checking privileges to access referenced schema
objects
• determining the optimal execution plan for the
statement
• loading it into a shared SQL area
• for distributed statements, routing all or part of
the statement to remote nodes that contain referenced data
A SQL statement is parsed only if a shared SQL area for an
identical SQL statement does not exist in the shared pool.
In this case, a new shared SQL area is allocated and the
statement is parsed
Although the parsing of a SQL statement validates that
statement, parsing only identifies errors that can be found
before statement execution
3. Query process
• require read consistency
• can use temporary segments for intermediate
processing
• can require the describe, define, and fetch phases
of SQL statement processing
4. Describe Results
The describe phase is only necessary if the
characteristics of a query's result are not known; for
example, when a query is entered interactively by a user.
In this case, the describe phase is used to determine the
characteristics (datatypes, lengths, and names) of a
query's result.
5. Defining Output
In the define phase for queries, you specify the
location, size, and datatype of variables defined to
receive each fetched value. Oracle performs datatype
conversion if necessary.
6. Bind Any Variables
7. Execute the Statement
8. Parallelize the Statement
When using the parallel query option, Oracle can
parallelize queries and certain DDL operations.
Parallelization causes multiple query servers to perform
the work of the query so that the query can complete
faster. Index creation and creating a table with a subquery
can also be parallelized
9. Fetch Rows of a Query Result
In the fetch phase, rows are selected and ordered
(if requested by the query), and each successive fetch
retrieves another row of the result, until the last row has
been fetched.
| Is This Answer Correct ? | 15 Yes | 3 No |
Post New Answer View All Answers
how many sql dml commands are supported by 'mysql'? : Sql dba
What is using in sql?
What is meant by user defined function?
Cite the differences between execution of triggers and stored procedures?
how to decrement dates by 1 in mysql? : Sql dba
Does db2 use sql?
what is the syntax for using sql_variant_property? : Transact sql
What are the disadvantages of file system?
What is string join?
What does count (*) mean in sql?
What is the difference between between and in condition operators?
What is the difference between a primary key and a unique key?
What are the two types of exceptions in pl/sql?
How can you maintain the integrity of your database on instances where deleting an element in a table result in the deletion of the element(s) within another table?
Where is all the data on the internet stored?
