Oracle where语句与Order by的优先级谁高?
实际操作是Where优先级高
Select * from usersite where email='[email protected]' and rownum <10 order by active desc
(active为0和1)
如果usersite的记录email为[email protected]超过10条,active只有一条记录为1,
上面的结果返回集中不包含active为1 的那条记录
实际操作是Where优先级高
Select * from usersite where email='[email protected]' and rownum <10 order by active desc
(active为0和1)
如果usersite的记录email为[email protected]超过10条,active只有一条记录为1,
上面的结果返回集中不包含active为1 的那条记录
一般在外层再进行取结果这样select * from (
Select * from usersite where email='[email protected]' order by active desc
)where rownum <10
SQL> explain plan for Select * from sizes s where s.struct_id=24 and rownum <10 order by s.measure_size desc;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3459659780
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 432 | 3 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 9 | 432 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| SIZES | 47 | 2256 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<10)
3 - filter("S"."STRUCT_ID"=24)
16 rows selected
如果rownum<10这个条件时,执行计划是
SQL> explain plan for Select * from sizes s where s.struct_id=24 order by s.measure_size desc;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2279842070
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47 | 2256 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 47 | 2256 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| SIZES | 47 | 2256 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("S"."STRUCT_ID"=24 AND "S"."MEASURE_SIZE" IS NOT NULL)
14 rows selected不知道你看明白了没
rownum 级别高于order by
是先取前多少条 在排序的