How does Inline view differ from Inline table in oracle?

Question

Could anyone tell the difference between Inline view and Inline table ?

Explanation with SQL code might be good to understand the concept easily.

Answer 1

The world is struggling to optimize the database query, so am I. Well if I say I have something which can speed the application by factors to 80%, if used at right situations, what will you say... Here I give you a problem, suppose you want to calculate the lowest and highest salary across the department with the name of employees with their respective manager. One way to do it is to create a temp table which contain the aggregated salary for the employees.

create table tmp_emp_sal as select t.emp_id,max(t.sal) as maxsal,min(t.sal) as minsal,avg(t.sal) as avgsal from sal t group by t.emp_id

and then use it in query further.

select concat(e.last_nm, e.first_nm) as employee_name,concat(m.last_nm,m.first_nm) as manager_name,tt.maxsal,tt.minsal,tt.avgsal from emp e,emp m,dept d,tmp_test tt where e.dept_id = d.dept_id and s.emp_id = tt.emp_id and e.mgr_id = m.emp_id   order by employee_name, manager_name

Now I will optimize the above code by merging the two DML and DDL operations in to a single DML query.

select concat(e.last_nm, e.first_nm) as employee_name,concat(m.last_nm, m.first_nm) as manager_name,tt.maxsal,tt.minsal,tt.avgsal from emp e,emp m, dept d,(select t.emp_id, max(t.sal) as maxsal, min(t.sal) as minsal, avg(t.sal) as avgsal from sal t group by emp_id) tt where e.dept_id = d.dept_id and s.emp_id = tt.emp_id and e.mgr_id = m.emp_id order by employee_name,manager_name

The above query saves user from the following shortcomings :- Eliminates expensive DDL statements. Eliminates a round trip to the database server. Memory usage is much lighter because it only stores the final result rather than the intermediate steps as well. So its preferable to use inline views in place of temp tables.

Answer 2

"this question has been asked to me in an interview."

We hear this a lot. The problem with these type of questions is you're asking the wrong people: you should have have the courage to say to your interviewer, "I'm sorry, I'm not familiar with the term 'inline table' could you please explain it?"

Instead you ask us, and the thing is, we don't know what the interview had in mind. I agree with Alex that the nearest thing to 'inline table' is the TABLE() function for querying nested table collections, but it's not a standard term.

I have been on the opposite side of the interviewing table many times. I always give credit to a candidate who asked me to clarify a question; I always mark down a candidate who blusters.