上亿条数据量检索

Question

现在有一个表test(operatorId,menu_code,invoke_num),记录操作员点击不同菜单项的次数，现在有1万个用户，有1千多个功能项，这样就大约有1亿条记录，我现在要做的是不同操作员登录时取出点击次数最高的十条记录进行展示，目前我采用最简单的方法：select * from test where operatorId=? order by invoke_num ,然后取出前10条记录来，效率实在是低，请问有什么更好的优化方法吗？

Answer 1

我现在要做的是不同操作员登录时取出点击次数最高的十条记录进行展示首先搞清楚是指这个操作员点击的次数最高的十条记录还是指所有的操作员共同点击的次数最高的十条记录？
如果是前者那就没必要记录用户了。而且我从你的查询语句中也没有看出对用户进行了操作。如果是后者，可以考虑通过top 10 这样的原理来实现，不需要把所有的记录都查出来，另外增加索引，至少operatorId上可以建索引的。

Answer 2

我要取出的是当前操作员点击的次数最高的十条记录呀
oracle中没有top 10呀

Answer 3

建立一张辅助表，只存放operatorId，建立unique index在test上建立operatorId index然后写个存储过程

Answer 4

再opeation_id字段上建立个B+索引。

Answer 5

range分区不大明白呀，能详细说说嘛[Quote=引用 8 楼 minitoy 的回复:]
range分区试试呢

Answer 6

关键是按照哪个字段进行range分区呢

Answer 7

-- Create table
create table PART_TAB_EXAMPLE
(
  V NUMBER,
  B NUMBER,
  C BLOB
)
partition by range (V)
(
  partition P1 values less than ('11')
    tablespace TEST1
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition P2 values less than ('21')
    tablespace TEST2
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
);
以opeation_id建立分区.

Answer 8

可是operatorId又不是数字呀，你写的11，21之类的我应该怎么写呢，况且可能会有1万多个用户呢

Answer 9

呵呵，我没写过分区，不大明白其中的11，21的含义，这些是不是应该用operatorId（比如admin）等等来代替呀

Answer 10

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 
Connected as scott
 
SQL> 
SQL> create table t_range
  2  (
  3    V varchar2(10),
  4    B NUMBER
  5  )
  6  partition by range (V)
  7  (
  8    partition P1 values less than ('a')
  9      tablespace TEST1,
 10    partition P2 values less than ('p')
 11      tablespace TEST2,
 12    partition P3 values less than (maxvalue)
 13      tablespace USERS
 14  );
 
Table created
  
SQL> insert into t_range values('1','1');
 
1 row inserted
 
SQL> insert into t_range values('a','1');
 
1 row inserted
 
SQL> insert into t_range values('b','2');
 
1 row inserted
 
SQL> insert into t_range values('p1','2');
 
1 row inserted
 
SQL> insert into t_range values('c1','2');
 
1 row inserted
 
SQL> select * from t_range partition(p1);
 
V                   B
---------- ----------
1                   1
 
SQL> select * from t_range partition(p2);
 
V                   B
---------- ----------
a                   1
b                   2
c1                  2
 
SQL> select * from t_range partition(p3);
 
V                   B
---------- ----------
p1                  2
 
SQL> 呵呵,字符也是可以做range的,给你个例子吧.用说的总不直观.

Answer 11

在operatorId和invoke_num上建联合索引，然后SQL改称呢？
分区得看你的数据结构，看看关于分区的文档吧。还有你的Oracle版本是多少？看文档的时候注意版本，版本不一样差别还是蛮大的。select * from (select * from test where operatorId=? order by invoke_num ) where row_num <_10;

Answer 12

我的operatorId可是按照字母进行分区吗，还是一个操作员一个分区

Answer 13

按照楼主的表结构和数据分布，没办法单纯用分区来提高查询性能的。因为那些字段的数据都不是平均的。
所以不能按照楼主的查询条件分区，不按照查询条件分区就没有意义。简单的说分区就是把不破坏表结构的情况下，把表分成几个子表，查询的时候你只要查询对应的分区就可以查询得到数据的意思。（不是官方解释，是我自己的理解）
分区的时候尽量让每个分区的数据量一样才可以。不然会出很多问题，最典型的就是执行计划得不稳定。
楼主看看HASH分区能不能改善你的查询速度。
合理的索引和SQL语句才是提高性能的王道。

Answer 14

可以考虑根据invoke_num 弄成分区表
或者试试分析加在operatorId建索引看看
select *
from (select a.*,row_number() over(order by invoke_num) rn from test a where operatorId=?)
where rn<=10

Answer 15

怎么设置分区值让数据平均分配这个得看你自己的数据分布情况.你可以用substr对operatorid做个统计看看.