问题:
1、以下两种方案,哪种更好,效率更高?
2、跟踪结果中的统计信息,其各个语句是什么意思?
方案一的跟踪结果:Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TBL_THINGS'Statistics
----------------------------------------------------------
301190 recursive calls
111 db block gets
1225373 consistent gets
18797 physical reads
0 redo size
794 bytes sent via SQL*Net to client
691 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
方案二的跟踪结果:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 NESTED LOOPS
3 2 VIEW
4 3 FILTER
5 4 SORT (GROUP BY)
6 5 TABLE ACCESS (FULL) OF 'TBL_THINGS'
7 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_THINGS'
8 7 INDEX (RANGE SCAN) OF 'IND_THINGS_CODE' (NON-UNIQUE)Statistics
----------------------------------------------------------
10 recursive calls
1177 db block gets
17002 consistent gets
36655 physical reads
0 redo size
781 bytes sent via SQL*Net to client
785 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
6 rows processed
1、以下两种方案,哪种更好,效率更高?
2、跟踪结果中的统计信息,其各个语句是什么意思?
方案一的跟踪结果:Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TBL_THINGS'Statistics
----------------------------------------------------------
301190 recursive calls
111 db block gets
1225373 consistent gets
18797 physical reads
0 redo size
794 bytes sent via SQL*Net to client
691 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
方案二的跟踪结果:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (UNIQUE)
2 1 NESTED LOOPS
3 2 VIEW
4 3 FILTER
5 4 SORT (GROUP BY)
6 5 TABLE ACCESS (FULL) OF 'TBL_THINGS'
7 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL_THINGS'
8 7 INDEX (RANGE SCAN) OF 'IND_THINGS_CODE' (NON-UNIQUE)Statistics
----------------------------------------------------------
10 recursive calls
1177 db block gets
17002 consistent gets
36655 physical reads
0 redo size
781 bytes sent via SQL*Net to client
785 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
6 rows processed
解决方案 »
- win 7 配置iis啦怎么asp网页还是运行不了
- 初学者问2个问题...请大家帮帮我..
- em 控制台启动不了
- Oracle 数据导入问题!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 在linux如何用C调用oracle以数组作为参数的存储过程,谢谢!
- 问一个oracle联表更新的问题(……求大侠们帮忙
- 求和问题
- 对于一个已经建好的 sequence我是否可以设置它的当前值为某一数值,语句应该怎样写?
- 两个表的SQl语句(解决立刻有分)
- navicat 连 本机 oracle 死活连不上
- PL_SQL存储过程里面写EMAIL发送问题
- Oracle817在RedHat Linux 9上安装的问题!
表:
create table tbl_things(thing_code varchar2(20) not null,
thing_name varchar2(40)); 我的意图是:找出表中相同thing_code而thing_name不同的记录。 第一中方案的sql
--建立一函数(可能有语法问题,表达大概意思)
function getcountcode(code, name) return number
var
ncount number;
begin
select count(*) into ncount from tbl_things where thing_code=code and thing_name<>name;
return ncount;
end; sql:
select thing_code, thing_name from tbl_things where getcountcode(thing_code, thing_name)>0; 方案2的SQL 语句:
select distinct a.thing_code,a.thing_name
from tbl_things a,(select thing_code from tbl_things group by thing_code
having (count(*)>1 and max(thing_name)<>min(thing_name))) b
where a.thing_code=b.thing_code; 我分别对thing_code、thing_name 建立了索引。