When we give SELECT * FROM EMP; How does oracle respond:

Showing Answers 1 - 19 of 19 Answers


  • Oct 21st, 2005

whenever you want to retrieve  all columns data  from emp table then you can secify this.

  Was this answer useful?  Yes


  • Nov 13th, 2005

when ever we want to see emp table,then we will give select*from emp query

  Was this answer useful?  Yes


  • Jan 28th, 2006

Oracle first parsed that SQL then make a execution plan for that which will take minimum CPU I/O and Memory and internally it's declare a implicit cursor which will return all the record from that particular table EMP by using set theory of cartesdian product.

Parag Sathe

  • Feb 1st, 2007

This will results as FULL TABLE scan and oracle will return all the columns from this table.

  Was this answer useful?  Yes

Select command executes and retrieves all of the coulumns values are in table EMP. means you will see columns name and related data for every column from the table EMP

  Was this answer useful?  Yes

Following this as an example let us see how the select execution happens in

SELECT ename,sal,job FROM emp
WHERE job='clerk'

How does the query execution occur?

  1. SQL*plus checks the syntax on client side.

  2. If syntax is correct the query is stamped as a valid SQL statement and
    encrypted into OCI (Oracle Call Interface) packets and sent via LAN using TCP
    to the server.

  3. Once the packets reach the server the server process will rebuild the
    query and again perform a syntax check on server side.

  4. Then if syntax is correct SP will continue execution of the query.

  5. The SP will go to the library cache. The L.C. will keep the recently
    executed SQL statements along with their execution plan.

  6. In the library cache the server process will search from the MRU (Most
    Recently Used) end to the LRU (Least Recently Used) end for a match for the
    SQL statement. It does this by using a hash algorithm that returns a hash
    value. If the hash value of the query we have written matches with that of the
    query in L.C. Then SP need not generate an execution plan (soft parsing) but
    if no match is found then SP has to proceed with the generation of execution
    plan (hard parsing).

  7. Parsing is the process undertaken by Oracle to generate an execution plan.

  8. The first step in parsing involves performing a semantic check. This is
    nothing but check for the existence of the obj and its structure in the

  9. This check is done by SP in the data dictionary cache. Here SP will ask
    for the definition of the object, if already available within the DDC , SP
    will process the check. If not available then SP will retrieve the required
    information from the system tablespace.

  10. After this SP will approach the optimizer, who will read the SQL statement
    and generate the execution plan of the query.

  11. After generation of the e-plan's the SP will pick the best e-plan and go
    to the L.C.

  12. SP will keep the e-plan in the L.C. Along with the original SQL text.

  13. At this point in time the parsing ends and the execution of the SQL
    statement will begin.

  14. SP will then go to the database cache and checks whether the data required
    by the query is already available or not in the cache.

  15. If available that data can be returned to the client else it brings the
    data from the database files.

  16. If sorting and filtering is required by the query then the PGA is utilized
    along with the temporary tablespace for performing sort run.

  17. After sort run the data is returned to the client and SQL*plus client will
    show the data to the users.


  • Jan 24th, 2012

When ever a query is executed in oracle it will undergo different stages parse,bind,execute,fetch. when this code is being executed oracle will fetch all the data is present in the particular table

  1. SELECT*FROM tablename


  • Mar 13th, 2012

when ever this query is executed in oracle it will fetch all the data is present in the particular table but it will take more time. when we mentioned column names it will reduce the processing time.
Note:- we will need to execute this type of queries when we don't know the column names only.

  Was this answer useful?  Yes

Ganesh Kumar.M

  • Mar 19th, 2012

When we write sql query .... The oracle will do these following steps initially.

1.Syntactic check.
2.semantic Check.
3.Plan execution.
4.hard parsing.
Soft Parsing.

and then it will fetch data from particular table.


  • Mar 23rd, 2012

answer for the select * from emp is : Oracle responds to the total content of emp table will be selected and displayed this is the answer for this question

  Was this answer useful?  Yes

ravinder lathwal

  • May 15th, 2012

whenever we hit a query on any table it go through from many stage
3.plan execution
4.hard parsing
5.if query is running 2nd time its come in soft parsing
6.then fetch all data from table.

  Was this answer useful?  Yes


  • Jun 16th, 2012

Oracle responds it will select all the columns from the emp table and display output to the client.

  Was this answer useful?  Yes

sudheer kumar

  • Jul 31st, 2012

it display all records in the table as a output

  Was this answer useful?  Yes

Ashok Kumar Lenka

  • Dec 6th, 2012

This is good question. You have to understand it properly.
When You Write Select * from EMP;

First Oracle try to find weather this Object (EMP) is there in the data base or not , If it is not there then it will through the error message.
Ok ,If EMP is there , then it will load the full table to the system table space which is called the temp table space.After that oracle came to know that user want to see all the record from the table (*) . Then it display all the records at the Output window.

I thing you got the answer ..

  Was this answer useful?  Yes


  • Dec 13th, 2012

First it will check the syntax and semantics in library cache, after that it will create execution plan. Already data in the buffer cache it will directly return to the client. If not it write the fetched to the dB buffer cache after that it will send server. Server sends to the client.

  Was this answer useful?  Yes


  • Jul 11th, 2013

It retrieve or fetch all data from Emp table,and show all data of table like Emp as result.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions