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的处理?
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的处理?
加order by 后会排序,需要消耗数据库的排序区,影响性能。
order by 对count(*)没什么用处 干脆去掉吧
在说我怎么觉得你这样有点多余样
你直接 select count(*) from taba 不好多了 还搞什么子查询。
原因是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 processedDINGJUN123>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 processedDINGJUN123>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 processedDINGJUN123>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 processedDINGJUN123>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 processedDINGJUN123>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 processedDINGJUN123>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
SORT AGGREGATE 实际上是一个聚集操作,不要看到SORT就认为是排序但是仅为了计数,从更好理解的角度出发,还是去掉order by吧,不要让人误解。
传给后台,hibernate直接取sql做分页计算后,再取数据显示。sql中的order by无法做简单的
处理去掉。问问题主要是想知道,此处order by是否会在oracle执行前的分析中忽略。从dingjun123的
cost plan看应该就是这样的。结贴了,分数用完了,就这么多分了 呵呵