一道关于SQL的面试题，大家帮我看看

Question

问题：一个表student中有班级classid,学号id,成绩grade 写个sql 查找比该班平均成绩高的学生的班级classid,学号id,成绩grade 
方法1：题目带的sql
select   a.classid,a.id,a.grade   
from   student   a   
where   a.grade   >   (select   avg(grade)   from   student   where   classid   =   a.classid);
方法2：_________________________（要求写的sql）select s.id,s.classid,s.grade from student s,(select avg(grade),classid from student group by classid) d
where s.classid = d.classid and s.grade > d.avg(grade);
写完之后，要说明哪种方法效率高。 
我就知道写SQL，我想知道上面两种方法哪种效率高点？为什么？ 

Answer 1

set autotrace on 打开看执行计划  看上去还是第一种好些 
不过最好还是看执行计划

Answer 2

解释计划：在PL/SQL中写好代码按F5就行了，

Answer 3

 第一种中的子查询只需执行一次。
(select   avg(grade)   from   student   where   classid   =   a.classid)
第二种中的子查询
select avg(grade),classid from student group by classid
因为这个对比
s.grade > d.avg(grade);
要被调用count(*)次！

Answer 4

已用时间:  00: 00: 00.00
SQL> create table student as
  2  select mod(level - 1,10) + 1 classid, level id, trunc(dbms_random.value* 100) grade from dual
  3  connect by level <1000000;表已创建。已用时间:  00: 00: 16.51
SQL> exec dbms_stats.gather_table_stats('TEST','STUDENT');PL/SQL 过程已成功完成。已用时间:  00: 00: 01.42
SQL> select   a.classid,a.id,a.grade
  2  from   student   a
  3  where   a.grade   >   (select   avg(grade)   from   student   where   classid   =   a.classid);已选择499582行。已用时间:  00: 00: 09.78执行计划
----------------------------------------------------------
Plan hash value: 1393824611--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         | 49950 |  1756K|  1234  (20)| 00:00:15 |
|*  1 |  HASH JOIN           |         | 49950 |  1756K|  1234  (20)| 00:00:15 |
|   2 |   VIEW               | VW_SQ_1 |    10 |   260 |   686  (28)| 00:00:09 |
|   3 |    HASH GROUP BY     |         |    10 |    60 |   686  (28)| 00:00:09 |
|   4 |     TABLE ACCESS FULL| STUDENT |   998K|  5853K|   530   (7)| 00:00:07 |
|   5 |   TABLE ACCESS FULL  | STUDENT |   998K|  9755K|   530   (7)| 00:00:07 |
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - access("CLASSID"="A"."CLASSID")
       filter("A"."GRADE">"VW_COL_1")
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      37655  consistent gets
          0  physical reads
          0  redo size
   10315229  bytes sent via SQL*Net to client
     366740  bytes received via SQL*Net from client
      33307  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     499582  rows processedSQL> Select s.Id, s.Classid, s.Grade
  2    From Student s,
  3         (Select Avg(Grade) Avg_Grade, Classid From Student Group By Classid) d
  4   Where s.Classid = d.Classid
  5     And s.Grade > d.Avg_Grade;已选择499582行。已用时间:  00: 00: 09.70执行计划
----------------------------------------------------------
Plan hash value: 2637888210--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         | 49950 |  1756K|  1234  (20)| 00:00:15 |
|*  1 |  HASH JOIN           |         | 49950 |  1756K|  1234  (20)| 00:00:15 |
|   2 |   VIEW               |         |    10 |   260 |   686  (28)| 00:00:09 |
|   3 |    HASH GROUP BY     |         |    10 |    60 |   686  (28)| 00:00:09 |
|   4 |     TABLE ACCESS FULL| STUDENT |   998K|  5853K|   530   (7)| 00:00:07 |
|   5 |   TABLE ACCESS FULL  | STUDENT |   998K|  9755K|   530   (7)| 00:00:07 |
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - access("S"."CLASSID"="D"."CLASSID")
       filter("S"."GRADE">"D"."AVG_GRADE")
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      37655  consistent gets
          0  physical reads
          0  redo size
   10315229  bytes sent via SQL*Net to client
     366740  bytes received via SQL*Net from client
      33307  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     499582  rows processedSQL> spool off
我的测试结果，好像2比1还要快点。

Answer 5

恩，貌似2效率高，2只需要执行一次avg，而1要执行n多次。

Answer 6

不同的数据量,不同的优化策略,单纯就某个抽象的SQL来说效率,那就看你预期的响应特性了.如果存在高选择度索引前者显然适合快速首行响应，而后者适合批处理响应.
具体说是不是会更好，还取决于运行SQL当时的资源状况，比如说内存，后者的瞬态内存消耗显然会要高于前者.如果没有足够的资源支持，原本可以高效运行的SQL也会变成低效率的。

Answer 7

用这个分析函数，是不是能更快点？
SELECT *
  FROM (SELECT A.CLASSID, A.ID, A.GRADE,
               AVG (GRADE) OVER (PARTITION BY CLASSID) AVGGRADE
          FROM STUDENT A)
 WHERE GRADE > AVGGRADE

Answer 8

sql语句的优化中有一条说尽量不用子查询，用关联关系更好！