ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 最大取值数据列行获取

最大取值数据列行获取

原创 Linux操作系统 作者:realkid4 时间:2011-03-13 21:34:12 0 删除 编辑

 

一个同事问起如果获取一个数据列最大取值所在的一行记录,感觉是一个有趣的问题。一般看起来很简单的问题,解法可能有多样,我们选择的路径可能有多种方式。

 

环境准备

 

原始问题是:获取数据表中指定一个数据列中最大的日期记录,一条即可。如此,我们利用dba_objects的last_ddl_time数据列来构建实验环境。

 

 

SQL> create table t (id number(10), da date);

 

Table created

 

SQL> insert into t select object_id, last_ddl_time from dba_objects;

 

50351 rows inserted

 

SQL> commit;

 

Commit complete

 

 

获取最大last_ddl_time列对应的记录,我们可以使用多种方法。

 

 

利用rownum筛选最大数据集合

 

想到只需要一条最大的记录,只管想到了rownum。利用rownum伪劣,可以进行删选结果的剔除。(这个问题在笔者之前的系列中有所涉及,可以参考http://space.itpub.net/17203031/viewspace-687124)

 

设计的查询和结果。

 

SQL> select * from (select * from t order by da desc) where rownum<2;

 

        IDS DA

----------- -----------

53876 2011-3-11 2

 

 

此种方法的执行效率方面,是如果呢?我们通过autotrace工具进行查看。

 

SQL> set timing on;

SQL> set autotrace traceonly;

SQL> select * from (select * from t order by da desc) where rownum<2;

 

已用时间:  00: 00: 00.07

执行计划

----------------------------------------------------------

Plan hash value: 3299198703

--------------------------------------------------------------------------------

| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |      |     1 |    22 |    43   (5)| 00:00:01 |

|*  1 |  COUNT STOPKEY          |      |       |       |            |          |

|   2 |   VIEW                  |      | 50351 |  1081K|    43   (5)| 00:00:01 |

|*  3 |    SORT ORDER BY STOPKEY|      | 50351 |   590K|    43   (5)| 00:00:01 |

|   4 |     TABLE ACCESS FULL   | T    | 50351 |   590K|    43   (5)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<2)

   3 - filter(ROWNUM<2)

统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        157  consistent gets

          0  physical reads

          0  redo size

        465  bytes sent via SQL*Net to client

        385  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

1     rows processed

 

 

从执行计划中,我们看出几个问题。首先在没有索引的情况下,查询整体的效率还是可以接受的(5万余条纪录消耗0.07s)。这个显然是通过stopkey操作,将不必要的排序和返回列加以屏蔽的结果。性能最大的消耗是进行全表扫描和排序(存在一次memory内部的排序),同时存在一些逻辑读情况。

 

这种方法存在一些缺陷,是需要注意的。首先,如果da列存在重复值,而需求要求返回最大的多列时,这样rownum的取定就存在问题了。同时,方法中的FTS,将来也会是一个比较大的缺陷方面。即使在da列加入了索引,这部分FTS的负担也较难进行优化。

 

 

利用子查询实现功能

 

思路:先利用子查询获取到取值最大的具体值,再进行查询。这样的方法比较中规中矩,但是能够解决rownum方案的缺陷。

 

SQL> select * from t where da=(select max(da) from t );

 

        IDS DA

----------- -----------

      53876 2011-3-11 2

 

SQL> select * from t where da=(select max(da) from t );

 

已用时间:  00: 00: 00.02

 

执行计划

----------------------------------------------------------

Plan hash value: 1287471683

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |    88 |  1056 |    86   (5)| 00:00:02 |

|*  1 |  TABLE ACCESS FULL  | T    |    88 |  1056 |    43   (5)| 00:00:01 |

|   2 |   SORT AGGREGATE    |      |     1 |     8 |            |          |

|   3 |    TABLE ACCESS FULL| T    | 50351 |   393K|    43   (5)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("DA"= (SELECT MAX("DA") FROM "T" "T"))

统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        315  consistent gets

          0  physical reads

          0  redo size

        465  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

 

 

这种方法虽然没有用到很高级的语法和技巧,但是还是获取的很好的性能效率。首先,执行时间较使用rownum有所缩短(只有0.02s)。其次就是排序操作sort被避免,因为sort操作会大量消耗PGA的空间。

 

唯一缺点就是进行逻辑读的数量较高,达到了315。那么,有无优化的空间呢?可以借助索引进行优化。

//建立索引

SQL> create index idx_t_da on t(da);

 

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

//进行查询

SQL> select * from t where da=(select max(da) from t );

 

已用时间:  00: 00: 00.01

 

执行计划

----------------------------------------------------------

Plan hash value: 3678432891

---------------------------------------------------------------------------

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |          |    87 |  1044 |    46   (5)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | T        |    87 |  1044 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN           | IDX_T_DA |    87 |       |     1   (0)| 00:00:01 |

|   3 |    SORT AGGREGATE            |          |     1 |     8 |            |          |

|   4 |     INDEX FULL SCAN (MIN/MAX)| IDX_T_DA | 50351 |   393K|            |          |

-----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("DA"= (SELECT MAX("DA") FROM "T" "T"))

统计信息

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          5  consistent gets

          0  physical reads

          0  redo size

        465  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

 

 

在数据表T的da列上,我们加入了normal类型的索引idx_t_da。这样在进行max值获取的过程中,Oracle是有选择不进行FTS操作来获取最大值,而是直接读取索引树的两端叶节点。

 

从结果看,这是我们目前取得的最好执行路径。首先,执行时间缩短为0.01s,消除了没有索引方案时进行FTS的瓶颈。其次,没有进行sort操作占用过大的排序空间。最后是大幅度减少了逻辑读数量,只有5(没有索引时要消耗315),提升近60倍。

 

 

这里,我们注意到Oracle提供了一种操作“INDEX FULL SCAN (MIN/MAX)”,相当于只对于索引树进行两端搜索,获取到最大值/最小值的rowid之后,就立刻停止。

 

 

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7678394