请问以下两种SQL中:
select name, sum(price) from tbl order by sum(price);对sum(price)计算了两次?
select name, sum(price) t from tbl order by t;对sum(price)计算了一次?
如何证明?谢谢!
select name, sum(price) from tbl order by sum(price);对sum(price)计算了两次?
select name, sum(price) t from tbl order by t;对sum(price)计算了一次?
如何证明?谢谢!
--首先,你的SQL语句就有问题
--回答你的问题,我用执行计划来回答你
--两种情况执行计划一样,不用说了撒,直观点,请看下面,你就知道结果了
1、
EXPLAIN PLAN FOR SELECT ename,Sum(sal) FROM emp GROUP BY ename ORDER BY Sum(sal);
SELECT * FROM TABLE(dbms_xplan.display);Plan hash value: 1116508905
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 176 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 16 | 176 | 5 (40)| 00:00:01 |
| 2 | SORT GROUP BY | | 16 | 176 | 5 (40)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 16 | 176 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
2、
EXPLAIN PLAN FOR SELECT * FROM (SELECT ename,Sum(sal) t FROM emp GROUP BY ename) ORDER BY t;
SELECT * FROM TABLE(dbms_xplan.display);Plan hash value: 1116508905
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 176 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 16 | 176 | 5 (40)| 00:00:01 |
| 2 | SORT GROUP BY | | 16 | 176 | 5 (40)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 16 | 176 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
select name,sum(price) from tb1 group by name order by sum(price)
select name,sum(price) t from tb1 group by name order by t
两个是一样的,都是一次全表扫描
select name, sum(price) from tbl group by name order by sum(price);
select name, sum(price) t from tbl group by name order by t
SELECT * FROM TABLE(dbms_xplan.display);后发现结果是前几天我分析的那个表的结果,请问如何清空让其显示我现在使用EXPLAIN PLAN FOR SELECT ename,Sum(sal) FROM emp GROUP BY ename ORDER BY Sum(sal);得到的emp表分析呢?谢谢!
SELECT * FROM TABLE(dbms_xplan.display);后发现结果是前几天我分析的那个表的结果,请问如何清空让其显示我现在使用EXPLAIN PLAN FOR SELECT ename,Sum(sal) FROM emp GROUP BY ename ORDER BY Sum(sal);得到的emp表分析呢?谢谢!
应该不会,执行explain plan for select ....就会将此查询语句的执行计划写到执行计划表中,然后再select * from table(),因此每次执行explain plan for都会更新的。你必须要先执行explain plan for 然后才查看
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 114 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TBL | 1 | 69 | 114 |
|* 2 | INDEX RANGE SCAN | DATEINDEX | 4570 | | 15 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TBL"."FLIGHT"='0985')
2 - access("TBL"."C_DATE"=TO_DATE('03NOV10','DDMONYY'))
Note: cpu costing is off请问这里|* 2 | INDEX RANGE SCAN | DATEINDEX | 4570 | | 15 |
这行中的4570是什么意思?如果按照C_DATE"=TO_DATE('03NOV10','DDMONYY')查询当日应该有七千多记录,这里只有4570?
然后再用查看执行计划就行了