select count(*) from (子查询)，子查询中含order by是否对效率有影响？

[shenhai800]

A、
select count(*) from (子查询1：SELECT * FROM TABA)

B、
select count(*) from (子查询2：SELECT * FROM TABA ORDER BY COND1,COND2,...COND12)

描述：
ORACLE库，TABA表有80万条记录，12个字段。

我在命令行手动执行的时候，没有明显的差异，同一PC都近1秒出结果。采用hibernate分页时总会在count时含有order by
，怀疑大并发时对性能有影响。

问题：这两个查询效率是否有实质性的差异？ 数据库是否会分析后屏蔽order by的处理？

[心中的彩虹]

[Quote=引用楼主 shenhai800 的回复:]
A、
select count(*) from (子查询1：SELECT * FROM TABA)

B、
select count(*) from (子查询2：SELECT * FROM TABA ORDER BY COND1,COND2,...COND12)

描述：
ORACLE库，TABA表有80万条记录，12个字段。

我在命令行手动执行的时候，没有明显的差异，同一P……
[/Quote]
从结果看我觉得第二个的语句没有第一个高 第二个里面还要排序就多了个时间；
在说我怎么觉得你这样有点多余样
你直接 select count(*) from taba 不好多了 还搞什么子查询。

[codearts]

直接写成：

SELECT count(1) FROM TABA

这样速度最好， ORDER BY 本身是很耗时的

[碧水幽幽泉]

[Quote=引用 4 楼 codearts 的回复:]
直接写成：

SELECT count(1) FROM TABA

这样速度最好， ORDER BY 本身是很耗时的
[/Quote]
讲得好！

[dingjun123]

当然，select count(1) from tab是最简单的了，见我下面的测试，CBO来说，没有任何区别，不管是有索引还是没有索引的
原因是oracle优化器会进过语法语义的分析，然后对我们写的SQL进行语义等价的SQL转换，如果说差别，最多是SQL转换的差别，但是这个转换很快，你几乎感觉不到，看下面多种写法的plan和statistics都是相同的，得出结论，CBO下无差别，但是SQL写法有差别

DINGJUN123>set autotrace traceonly
DINGJUN123>select count(*)
  2  from product;


执行计划
----------------------------------------------------------
Plan hash value: 3894404247

-------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |            |          |
|   2 |   INDEX FULL SCAN| SYS_C0018568 |   532 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

DINGJUN123>select count(*) from product order by id;


执行计划
----------------------------------------------------------
Plan hash value: 3894404247

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN| SYS_C0018568 |   532 |  2128 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

DINGJUN123>select count(id) from product;


执行计划
----------------------------------------------------------
Plan hash value: 3894404247

-------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |            |          |
|   2 |   INDEX FULL SCAN| SYS_C0018568 |   532 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

DINGJUN123>select count(*) from (select * from product order by id);


执行计划
----------------------------------------------------------
Plan hash value: 3894404247

-------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |            |          |
|   2 |   INDEX FULL SCAN| SYS_C0018568 |   532 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

DINGJUN123>drop table test;

表已删除。

DINGJUN123>create table test as select * from all_objects;

表已创建。

DINGJUN123>begin
  2  dbms_stats.gather_table_stats(ownname => user,tabname => 'TEST');
  3  end;
  4  /

PL/SQL 过程已成功完成。

DINGJUN123>select count(*) from test;


执行计划
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    52   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 13367 |    52   (2)| 00:00:01 |
-------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        174  consistent gets
          0  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

DINGJUN123>select count(*) from (select *  from test );


执行计划
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    52   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 13367 |    52   (2)| 00:00:01 |
-------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        174  consistent gets
          0  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

DINGJUN123>select count(*) from (select *  from test order by object_id);


执行计划
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    52   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 13367 |    52   (2)| 00:00:01 |
-------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        174  consistent gets
          0  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

[dingjun123]

这个组操作，本身就是要排序的

[DeluxWorld]

没有必要使用子查询。如果有其他的用处，也尽量不要对大表order by, 同时考虑使用索引

[tangren]

两种写法oracle实际上都没有排序，从cost就可以看出
SORT AGGREGATE 实际上是一个聚集操作，不要看到SORT就认为是排序

但是仅为了计数，从更好理解的角度出发，还是去掉order by吧，不要让人误解。

[duqiangcise]

建议用A来查询数据记录总数，因为你要的是数据记录的总数，所以在语句中加入order by没有必要（如：你的B）。
加order by 后会排序，需要消耗数据库的排序区，影响性能。

[Adebayor]

80万条记录 进行order by 效率会有很大的影响
order by 对count(*)没什么用处 干脆去掉吧

[shenhai800]

多谢大家，尤其是dingjun123、tangren。

问题的原因是由于web应用引用了hibernate框架，在用ext做list列表显示的时候，直接将sql
传给后台，hibernate直接取sql做分页计算后，再取数据显示。sql中的order by无法做简单的
处理去掉。

问问题主要是想知道，此处order by是否会在oracle执行前的分析中忽略。从dingjun123的
cost plan看应该就是这样的。

结贴了，分数用完了，就这么多分了 呵呵