oracle select

Question

问题：oracle中的select的执行顺序到底是怎样的？
例如：
正确： select * from 
(
select rownum r,ename,sal from 
(
select ename,sal from emp order by sal
)
where rownum < 10
)
where r >= 6
错误： select * from 
(
select rownum r,ename,sal from 
(
select ename,sal from emp order by sal
)
where r < 10
)
where r >= 6内层的 where r < 10 的 r 被认为是无效的标示符。看网上的资料说，原因在于sql语句执行时的顺序的问题？
网上的select执行顺序1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选； 
3、group by子句将数据划分为多个分组； 
4、使用聚集函数进行计算；
5、使用having子句筛选分组； 
6、计算所有的表达式； 
7、使用order by对结果集进行排序自己用 trace 看到的“执行”也没明白到底反映了什么？可能更多的是效率的体现。执行计划
----------------------------------------------------------
Plan hash value: 2603316617---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     9 |   297 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |      |     9 |   297 |     4  (25)| 00:00:01 |
|*  2 |   COUNT STOPKEY          |      |       |       |            |          |
|   3 |    VIEW                  |      |    14 |   280 |     4  (25)| 00:00:01 |
|*  4 |     SORT ORDER BY STOPKEY|      |    14 |   140 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL   | EMP  |    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("R">=6)
   2 - filter(ROWNUM<10)
   4 - filter(ROWNUM<10)请大家给指点下 。。到底是怎么样的？有官方的参考文档吗？通过什么样的实验可以验证呢？谢谢大家！

Answer 1

select * from
(
select rownum r,ename,sal from
(
select ename,sal from emp order by sal
)
where r < 10
)
where r >= 6这个错误是语法错误呀。
这个子查询
select rownum r,ename,sal from
(  
select ename,sal from emp order by sal
)
where r < 10在r的确子查询select ename,sal from emp order by sal没有定义。
而rownum是隐含列，所以该成rownum可以通过语法检查。

Answer 2

谢谢 welyngj 帮助。我自己的意思可能没有表达清楚，但是这样一个简单的查询：select ename,sal as s from emp where s > 100提示的是：ORA-00904: "S": 标识符无效
    我想表达的意思是：因为select里面 where ， group by ，having 等的执行顺序的不同，导致“别名”在where等是不可用，但在 order by 正确。    比如这样 select ename,sal as s from emp where sal > 100 order by s 。我的理解是因为最后做 order by 的操作 ，按照“数据库原理”的税法，“投影”已经完成 （投影：通俗的讲就是，已经从数据库的表提取出所要查询的字段），此时数据已经是“别名”表示，所以 order by 正确。执行where的时候，还没有“投影”的操作，因此没有“别名”，所以 “标识符无效”！    我想说的问题是：[ subquery_factoring_clause ]
SELECT
   [ hint ]
   [ { { DISTINCT | UNIQUE }
     | ALL
     }
   ]
   select_list
FROM table_reference
        [, table_reference ]...
   [ where_clause ]
   [ hierarchical_query_clause ]
   [ group_by_clause ]
   [ HAVING condition ]
   [ model_clause ]
   [ { UNION [ ALL ]
     | INTERSECT
     | MINUS
     }
     (subquery)
   ]
   [ order_by_clause ]

Answer 3

  welyngj  给指点一下 ，你觉得我的想法对吗？  这是oracle官方的一个查询的结构：  [ subquery_factoring_clause ]
SELECT
   [ hint ]
   [ { { DISTINCT | UNIQUE }
     | ALL
     }
   ]
   select_list
FROM table_reference
        [, table_reference ]...
   [ where_clause ]
   [ hierarchical_query_clause ]
   [ group_by_clause ]
   [ HAVING condition ]
   [ model_clause ]
   [ { UNION [ ALL ]
     | INTERSECT
     | MINUS
     }
     (subquery)
   ]
   [ order_by_clause ]
model_clause 代表的是：从数据库的表提取出所要查询的部分字段？对不？大家说说 select 的执行顺序是怎样的呢？谢谢

Answer 4

SQL Select语句完整的执行顺序： 1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、使用order by对结果集进行排序。

Answer 5

接着一个新问题： trace 显示的结果怎么分析执行过程？谢谢大家
执行的sql ：  1* select ename,dname from emp,dept where emp.deptno=dept.deptno
SQL> /执行计划
----------------------------------------------------------
Plan hash value: 351108634----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   308 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    14 |   308 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
在网上也找资料了，但感觉都没有说的很清楚，希望各位再给指点下！或者哪有分析的比较清楚的文章给推荐下！非常感谢大家。

Answer 6

我在图书馆一本“oracle高效设计”，是这样解释的 ：    
     1* select ename,dname from emp,dept where emp.deptno=dept.deptno 
SQL> / 执行计划 
---------------------------------------------------------- 
Plan hash value: 351108634 ---------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time    | 
---------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT             |         |    14 |  308 |    4  (0)| 00:00:01 | 
|  1 |  NESTED LOOPS                |         |    14 |  308 |    4  (0)| 00:00:01 | 
|  2 |   TABLE ACCESS FULL          | EMP     |    14 |  126 |    3  (0)| 00:00:01 | 
|  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |    1  |  13  |    1  (0)| 00:00:01 | 
|*  4 |    INDEX UNIQUE SCAN        | PK_DEPT |    1  |      |    0  (0)| 00:00:01 | 
---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): 
---------------------------------------------------   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO") 
1. 画出一课“估算树”：      （1） （2）      （3）            （4）（csdn一直不知道怎么上传图！相邻之间用线连起来，序号和ID对应）。2. 颠倒过来，从左至右阅读。上一个结果做为下一个的输入。这样分析的话，应该是 ：emp full scan(2) ----> dept index scan(4) ----> 由索引得到rowid ，读取数据 (3) ----> (1)返回本次查询结果。嵌套循环，直到得到所有结果。 
Inthirties 你帮我看看，这样分析对吗？还有什么要理解的吗？非常感谢。

Answer 7

执行计划里，解读计划的原则，同级，自上而下，不同级，同左到右。可以参考这里
http://blog.csdn.net/inthirties/archive/2009/06/28/4304864.aspx
或
http://www.inthirties.com/?p=768
==================================================================
Inthirties关注Oracle数据库 维护 优化，安全，备份，恢复，迁移，故障处理如果你需要帮助或想和我一起学习的请联系
联系方式QQ：370140387
电子邮件：[email protected]
网站: http://www.inthirties.com

Answer 8

太谢谢 Inthirties 。讲的非常清楚，真的谢谢。自己努力中