Max(id) Min(id) 优化
1.最大值和最小值,如果从索引结构上来看,无非就是从root到branch再到leaf的最左边或者是最右边,一般来说能从
叶子的最左边和最右边取到值,那么执行计划里一般会出现index full scan(max/min),这是查询的速度正常来说都是
比较快的,逻辑读的次数与索引的高度有关,如果执行计划里出现index full scan(max/min),然而逻辑读又很大,这时候
可能是因为叶子的左右节点存在大量被删除的数据,index full scan(max/min)蕴含着stopkey的机制,从最左边或者最
右边的叶子节点开始扫描,读到第一个值后就停止扫描,而最左边或者最右边被删除大量数据后,要读取到第一个值所要
扫描的索引就越来越多,这时候我们可以把索引重建下.类似的情况是select * from table where rownum=1;,表里的大量
数据被删除后,oracle可能要读很多快才读取到第一行,我们可以通过重建表来解决.
2.同时求最大值和最小值,曾经看见别人写过这样一个sql,
select min(id),max(id) from table; 这时候如果要取到这2个值就必须从叶子的第一个节点扫描到最后一个节点,
这时候执行计划可能走的是index full scan,index fast full scan,full table scan,由cost决定.
我们可以改写成
select min,max from
(select min(id) min from table ) a,
(select max(id) max from table ) b ;
这样读取 select min(id) min from table 和 select max(id) max from table的时候都会很快.
例子如下:
SQL> create index i_test_max_min on test_max_min(object_id);
索引已创建。
SQL> alter table test_max_min modify object_id not null;
表已更改。
SQL> set timing on
SQL> set autot trace
SQL> select max(object_id),min(object_id) from test_max_min;
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 1866139974
--------------------------------------------------------------------------------
--------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
--------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | 13 | 27 (4)| 00
:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
| 2 | INDEX FAST FULL SCAN| I_TEST_MAX_MIN | 56996 | 723K| 27 (4)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
117 consistent gets
0 physical reads
0 redo size
483 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
改写后的sql:
SQL> select max,min from
2 (select max(object_id) max from test_max_min) a,
3 (select min(object_id) min from test_max_min) b;
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3839465953
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 4
(0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 2
(0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 13 |
| |
| 4 | INDEX FULL SCAN (MIN/MAX)| I_TEST_MAX_MIN | 56996 | 723K| 2
(0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 2
(0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 13 |
| |
| 7 | INDEX FULL SCAN (MIN/MAX)| I_TEST_MAX_MIN | 56996 | 723K| 2
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
461 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3.降序索引对max,min的影响
select max(id) from table;这样的查询语句我原来很当然的认为,因为oracle的索引默认是升序的,我建立
个降序索引这时候oracle从左边开始扫描的时候,通过降序索引就很快的得到第一个节点,但是我忽略了一点
降序索引本质上还是一个函数索引,oracle是对id做了个函数后才存到索引中的.
for example:
SQL> create table test_desc as select * from dba_objects where object_id is not null;
表已创建。
已用时间: 00: 00: 00.39
SQL> create index i_test_desc on test_desc(object_id desc);
索引已创建。
已用时间: 00: 00: 00.37
SQL> set autot trace
SQL> alter table test_desc modify object_id not null;
表已更改。
已用时间: 00: 00: 00.06
SQL> select /*+ index(test_desc) */ max(object_id) from test_desc;
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 2994522733
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 121 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN| I_TEST_DESC | 46145 | 585K| 121 (2)| 00:00:02 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
可以看到执行计划中并没有出现我们前面说到的indx full scan(max/min),说明这时候的执行是有问题的
我先对语句做个改写,用order by +rownum的方式
SQL> select object_id from (select object_id from test_desc order by object_id desc) where rownum=1
;
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 2358345415
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01
|
|* 1 | COUNT STOPKEY | | | | |
|
| 2 | VIEW | | 46145 | 585K| 2 (0)| 00:00:01
|
| 3 | INDEX FULL SCAN| I_TEST_DESC | 46145 | 585K| 2 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到逻辑读是很少的,执行计划里出现index full scan+stopkey,因为我们取的rownum是1,
因此这里的index full scan+stopkey其实和index full scan(max/min)是等价的,那为什么
select max(object_id) from test_desc的时候执行计划走的有问题呢,接下来看看其中的原因
SQL> select max(object_id) from test_desc where object_id=1;
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 1329882831
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
|* 2 | INDEX RANGE SCAN| I_TEST_DESC | 8 | 104 | 1 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SYS_OP_DESCEND("OBJECT_ID")=HEXTORAW('3EFDFF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_ID"))=1)
Note
-----
- dynamic sampling used for this statement
看其中的谓词部分,可以发现oracle对object_id做了一个sys_op_descend的函数,
那么
select max(sys_op_descend(object_id)) from test_desc应该是能够走成index full scan(max/min)的,
SQL> set autot trace
SQL> select /*+ index(test_desc) */ max(sys_op_descend(object_id)) from test_desc;
已用时间: 00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 3108832290
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 1 | 13 | 121 (2)|
00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
|
| 2 | FIRST ROW | | 46145 | 585K| 121 (2)|
00:00:02 |
| 3 | INDEX FULL SCAN (MIN/MAX)| I_TEST_DESC | 46145 | 585K| 121 (2)|
00:00:02 |
--------------------------------------------------------------------------------
-----------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
431 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
确实这时候最了正确的执行计划.
来看看sys_op_descend具体啥效果
SQL> select /*+ index(test_desc) */ max(sys_op_descend(object_id)) from test_desc;
MAX(SYS_OP_DESCEND(OBJECT_ID))
--------------------------------------------------------------------
3EFCFF
已用时间: 00: 00: 00.00
SQL> select sys_op_descend(max(object_id)) from test_desc;
SYS_OP_DESCEND(MAX(OBJECT_ID))
--------------------------------------------------------------------
3CF9E7FF
已用时间: 00: 00: 00.01
SQL> select /*+ index(test_desc) */ min(sys_op_descend(object_id)) from test_desc;
MIN(SYS_OP_DESCEND(OBJECT_ID))
--------------------------------------------------------------------
3CF9E7FF
已用时间: 00: 00: 00.01
SQL> select sys_op_descend(min(object_id)) from test_desc;
SYS_OP_DESCEND(MIN(OBJECT_ID))
--------------------------------------------------------------------
3EFCFF
已用时间: 00: 00: 00.01
从测试上看这玩意就是起个倒序的作用
4.min,max的扩展之分页的优化
我们常见的分页语句是
select * from (select * from table where id= order by name desc) where rownum<11;
这样的语句具体的意思就是id为某个值的时候,根据name做排序,然后取前10行.
这个语句存在2个部分:
a.id为某个值
b.name降序
假设我现在存
在这一个索引(id,name desc)这个索引的结构也是id相同的情况下按照name的降序排列,这个索引
同时满足前面的两个条件,因此就能提高速度,我们只要从索引中读取出10个rowid,然后根据
这10个rowid来回表,这时候速度肯定很快的,因此类似这类的分页语句可以根据sql语句的原意
来创建索引,就能提高速度,但是如果where条件里出现非等于的条件,那么我们不管怎么创建索引都
无法满足前面的2个条件(根据索引的结构就很容易明白这点),我们就必须根据字段的选择性来创建
合适的索引.
浙公网安备 33010602011771号