oracle千万数据快速查找满足max条件的数据行示例

场景

假设我们有这样的一个访问记录表accessTable，记录项分别为用户唯一标识（userId）、访问时间(visitTime)、访问时上报的版本信息(ver)。需求为查询每个用户最近一次访问时上报的版本是什么,环境是在oracle下。本人非DBA，有问题一定要指出来啊。

思路一

使用oracle的分析函数。具体函数使用见这里。

SELECT *
FROM (SELECT t.userId,
t.visitTime,
t.ver,
Row_number()
OVER(
partition BY t.userId
ORDER BY t.visitTime DESC) rk
FROM accessTable t) rank_tab
WHERE rank_tab.rk = 1

这里的思路是通过row_number分析函数为数据排名，排名按用户标识分组，并按访问时间由大到小。实际运行时此种方式仍速度极慢。

思路二

这个思路是从一个同事处得来的，首先在此表示感谢，他的实现思路是利用ORACLE的rowid实现。其实我们只需要找到每个userId的最大访问时间的数据行。

SELECT acc_tab.*,acc_tab.rowid
FROM accessTable acc_tab,
(SELECT Substr(tr_tab.time_rowid, 15) rk_rowid
FROM (SELECT t.userId,
Max(To_char(t.visitTime, ‘yyyymmddhh24miss’)
|| t.rowid) AS time_rowid
FROM accessTable t
GROUP BY t.userId) tr_tab)r_tab
WHERE acc_tab.rowid = r_tab.rk_rowid

实现时是将日期字符串和该条记录对应的rowid串起来拼出一个长串，对这个长串来求最大，求出最大的字符串的同时我们可以从字符串中拆出对应的rowid,然后通过rowid关联从而快速的得到满足条件的记录，这种方法在实际环境下效率是不错的。拼串时要注意拼的内容，字符串比较会认为22比111大。

由于水平有限，非专业DBA，错漏难免，如有意见和建议请一定及时指出，防止水平有限误导他人。

下面总结了一些关于千万级数据的一些经验与

1、防止运用 Hibernate框架

Hibernate用起来虽然方便，但对于海量数据的操作显得力不从心。

关于Oracle千万级记录插入：

试过用Hibernate一次性执行 5万条左右数据的插入，若ID运用 sequence方式生成，Hibernate将分5万次从数据库取得5万个sequence，构造成相应对象后，再分五万次将数据保存到数据库。花了我十分钟时间。主要的时间不是花在插入上，而是花在5万次从数据库取sequence上，弄得我相当郁闷。虽然后来把ID生成方式改成increase处理了疑问，但还是对那十分钟的等待心有余悸。

关于Oracle千万级记录查询：

Hibernate对数据库查询的主要思想还是面向对象的，这将使许多我们不须要查询的数据占用了大量的系统资源（包括数据库资源和本地资源）。由于对Hibernate的偏爱，本着不抛弃、不放弃的作风，做了包括配SQL，改良 SQL等等的相当多的尝试，可都以失败告终，不得不忍痛割爱了。

2、写查询语句时，要把查询的字段一一列出

查询时不要运用类似select * from x_table的语句，要尽量运用 select id,name from x_table，以防止查询出不须要的数据浪费资源。对于海量数据而言，一个字段所占用的资源和查询时间是相当可观的。

3、减少不必要的查询条件

当我们在做查询时，常常是前台提交一个查询表单到后台，后台分析这个表单，而后执行查询操作。在我们分析表单时，为了方便起见，常常喜欢将一些不须要查询的条件用永真的条件来代替（如：select count(id) from x_table where name like ‘%’），其实这样的SQL对资源的浪费是相当可怕的。

我试过对于同样的近一千万条记录的查询来说，运用 select count(id) from x_table 执行 表查询须要 11秒，而运用 select count(id) from x_table where name like ‘%’却花了33秒。