Select from a nested query in Oracle 9i

Question

In MS SQL 2005 or T-SQL, you can do something like:

SELECT T.NAME, T.DATE 
  FROM (SELECT * FROM MyTable WHERE ....) AS T

I failed to try the similar SQL on Oracle 9i DB. In MS SQL, the nested SQL is treated as a temporary/dynamic view created on fly and destroyed afterward. How can I do the similar thing in Oracle? I really don't want to create a view to do it.

Answer 1

I believe it chokes on the "as".

SELECT T.NAME, T.DATE 
  FROM (SELECT * FROM MyTable WHERE ....)  T

should work.

Answer 2

The only thing you need to change is remove the keyword "AS". Oracle uses that only for column aliases (e.g. SELECT dummy AS some_name FROM dual), although even then you don't need it.

Answer 3

If you really need to create a temporary, physical result set then you would do that with a subquery factoring clause:

with t as
(SELECT /*+ materliaze */ 
        *
 FROM   MyTable
 WHERE ....)
SELECT T.NAME, T.DATE 
FROM T
/

It's generally not worthwhile except for specific situations in which the optimizer's accurate estimation of an intermediate result set in a query is critical to an efficient execution plan, in which case dynamic sampling can be used to see the size of the result set, or where a calculated result set is used multiple times in the rest of the query.

You ought to be able to just drop the AS, and Oracle will logically merge the in-line view into the master query if it is safe to do so.