min max and page

棉花糖ONE 2009年03月11日 19:57 阅读(51) 评论(1) 分类：个人日记 权限: 公开
字体：大▼ 小 中 大 更多▼ 设置置顶 权限设置 推荐日志 转为私密日志 删除编辑


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个条件(根据索引的结构就很容易明白这点),我们就必须根据字段的选择性来创建
合适的索引.

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

转载于:http://blog.itpub.net/22936840/viewspace-750412/