ITPub博客

首页 > Linux操作系统 > Linux操作系统 > min ? max ? 执行计划?

min ? max ? 执行计划?

原创 Linux操作系统 作者:lfree 时间:2007-04-28 00:00:00 0 删除 编辑

create table big_table as select * from dba_objects;
insert into big_table select * from big_table;
insert into big_table select * from big_table;

ALTER TABLE BIG_TABLE MODIFY(OBJECT_ID NULL);

CREATE INDEX I_BT_OBJECT_ID ON BIG_TABLE(OBJECT_ID)

select min(object_id),max(object_id) from big_table;

第1次看到类似的sql语句的时候,感觉会使用索引,并且会走
index full scan (min/max).但是仔细看执行计划发现,发现是使用全表扫描。修改约束OBJECT_ID NOT NULL,仅仅计划变为INDEX FAST FULL SCAN。


修改为
SELECT MIN (object_id), MAX (object_id)
FROM big_table
WHERE object_id IS NOT NULL;

执行计划走INDEX FAST FULL SCAN。

但是单独写SELECT MIN (object_id) FROM big_table;
执行计划就是INDEX FULL SCAN (MIN/MAX)。

如何要执行类似的sql应该将sql语句修改如下:
SELECT a.m1, b.m2
FROM (SELECT MAX (object_id) m1
FROM big_table) a,
(SELECT MIN (object_id) m2
FROM big_table) b

对比如下:
Execution Plan
----------------------------------------------------------
Plan hash value: 2118989048

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 6 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| I_BT_OBJECT_ID | 405K| 1978K| 3 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 5 | | |
| 7 | INDEX FULL SCAN (MIN/MAX)| I_BT_OBJECT_ID | 405K| 1978K| 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size


SELECT MIN (object_id), MAX (object_id)
FROM big_table
WHERE object_id IS NOT NULL;

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 307 (4)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FAST FULL SCAN| I_BT_OBJECT_ID | 405K| 1978K| 307 (4)| 00:00:04 |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1386 consistent gets
0 physical reads
0 redo size


再回到生产系统,一般要执行类似的sql,比如:

select min(a),max(a) from t where xx=:1 ,如果索引建立再xx,a上,如果xx的重复值很多,这样写效率就不高。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-83019/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2286
  • 访问量
    6027875