oracle 大数据查询优化的方案

Question

各位大大
小的最近遇见了一个比较棘手的问题.是关于oracle大数据查询速度慢的问题.因小的对数据库优化方面的只是见识甚少.希望在这里求助各位大大.小的这里万分感激...oracle数据库版本:oracle9I具体问题描述如下:
某数据库中的一张表A,表A的数据库量现在大概有10亿条数据之多.
最初的做法是全部存放在一个表空间中.造成现在的查询速度龟爬似的.现在不得不对数据库做出重新评估和优化.最初的前提:
1.中应该建立的索引都已具备
2.查询语句都已经做到优化:现在类似于 select count(1) from A 这么简单的一句统计SQL都需要100S左右现在我想到的方案如下:方案一:分布式(也不知道是不是叫分布式.名称叫错的话,各位大大请勿见笑 :))我以时间戳作为标志来进行动态建立表A.A1.A2....
另外用一张表B来存储建立时间戳和对应的表名
再次查询的时候先查询B表时间戳对应的表名,然后得到表名之后再去查询动态创建的表A,A1,A2,A3...方案一的弊端:
本身系统已经投入使用,另外还有其他程序来进行访问该库的表A,所以按照方案一的方法必定造成很大的改动
此方案被pass掉
方案二:建立表分区.(这里说到的建立表分区是指的范围分区)
建立分区的代码如下:PARTITION BY RANGE(JGSJ)
(
   PARTITION PART_JGJL2010051910 VALUES LESS THAN (to_date('2010-05-19 10','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051910
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ),    ……  ……   ……
     PARTITION PART_JGJLDEFULT VALUES LESS THAN (maxvalue) TABLESPACE TS_JGJL_DAT
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    )  
  );
昨天晚上建立表分区已经完毕,然后进行测试,但是发现这样按照时间来建立分区的话,不能设置为自动建立分区(数据库版本的限制),必须手动进行指定,不知各位是否有办法来自动根据时间建立分区的呢?
以上方案是我目前想到的两种,第一种被Pass掉了.剩下的第二种方案现在是一抹黑,
或者各位大大有其他解决方案,请不啬赐教!!
小的在这感激涕零,

Answer 1

你这个是业务表?
感觉你之前的设计没有考虑到这一点,现在又不改动前端,
现在只能考虑表截断操作了,参考http://topic.csdn.net/t/20040609/09/3075938.html我强烈建议改动前端程序......重新设计后台....

Answer 2

如果只是count慢，那么想看一下你的表结构情况 以及约束情况 。

Answer 3

如果有主键，或者unique index 以及为not null的，再加上cbo,你的count是应该要走INDEX (FAST FULL SCAN)的。
这样比起你的fts要快很多。

Answer 4

分页这个问题我已经做过的分页.
但是分页的时候需要一个count  所以必须要一个统计的SQL
好像大多时间都用在了统计的上面

Answer 5

表结构如下:create table JGJL
(
  JGJL_ID    NUMBER not null,
  JGJLBH     VARCHAR2(20),
  HPHM       VARCHAR2(15),
  HPLX_ID    NUMBER,
  HPYS_ID    NUMBER,
  FX_ID      NUMBER,
  LD_ID      NUMBER,
  CLSD       NUMBER,
  SBDM_ID    NUMBER,
  KSBCLLX_ID NUMBER(6),
  SBLXBH     VARCHAR2(20),
  XSCD       VARCHAR2(20),
  JGSJ       DATE not null,
  ZDXZSD     NUMBER,
  ZXXZSD     NUMBER,
  CWKC       NUMBER,
  CSYS       VARCHAR2(8),
  XXDZ       VARCHAR2(200),
  JKD_ID     NUMBER
)
PARTITION BY RANGE(JGSJ)
(
   PARTITION PART_JGJL2010051910 VALUES LESS THAN (to_date('2010-05-19 10','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051910
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ),
   PARTITION PART_JGJL2010051911 VALUES LESS THAN (to_date('2010-05-19 11','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051911
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ),
   PARTITION PART_JGJL2010051912 VALUES LESS THAN (to_date('2010-05-19 12','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051912
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ),
   PARTITION PART_JGJL2010051913 VALUES LESS THAN (to_date('2010-05-19 13','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051913
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ),
   PARTITION PART_JGJL2010051914 VALUES LESS THAN (to_date('2010-05-19 14','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051914
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ),
   PARTITION PART_JGJL2010051915 VALUES LESS THAN (to_date('2010-05-19 15','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051915
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ),
   PARTITION PART_JGJL2010051916 VALUES LESS THAN (to_date('2010-05-19 16','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051916
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ),
   PARTITION PART_JGJL2010051917 VALUES LESS THAN (to_date('2010-05-19 17','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051917
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ),
   PARTITION PART_JGJL2010051918 VALUES LESS THAN (to_date('2010-05-19 18','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051918
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ) ,
   PARTITION PART_JGJL2010051919 VALUES LESS THAN (to_date('2010-05-19 19','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051919
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ) ,
   PARTITION PART_JGJL2010051920 VALUES LESS THAN (to_date('2010-05-19 20','yyyy-mm-dd HH24')) TABLESPACE TS_JGJL_DAT2010051920
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    ) ,
   PARTITION PART_JGJLDEFULT VALUES LESS THAN (maxvalue) TABLESPACE TS_JGJL_DAT
   pctfree 10
   pctused 40
   initrans 1
   maxtrans 255
   storage
   (
      initial 32M
      next 16M
      minextents 1
      maxextents 255
      pctincrease 0
    )  
  );
comment on table JGJL
  is '车辆经过记录表';
comment on column JGJL.JGJL_ID
  is '经过记录ID';
comment on column JGJL.JGJLBH
  is '编号';
comment on column JGJL.HPHM
  is '号牌号码';
comment on column JGJL.HPLX_ID
  is '号牌种类ID';
comment on column JGJL.HPYS_ID
  is '号牌颜色ID';
comment on column JGJL.FX_ID
  is '方向ID';
comment on column JGJL.LD_ID
  is '路段ID';
comment on column JGJL.CLSD
  is '车辆速度';
comment on column JGJL.SBDM_ID
  is '设备代码ID';
comment on column JGJL.KSBCLLX_ID
  is '可是别车辆类型ID';
comment on column JGJL.SBLXBH
  is '设备类型编号';
comment on column JGJL.XSCD
  is '行驶车道';
comment on column JGJL.JGSJ
  is '经过时间';
comment on column JGJL.ZDXZSD
  is '最大限制数度';
comment on column JGJL.ZXXZSD
  is '最小限制数度';
comment on column JGJL.CWKC
  is '车外廓长';
comment on column JGJL.CSYS
  is '车身颜色';
comment on column JGJL.XXDZ
  is '详细地址';
comment on column JGJL.JKD_ID
  is '监控点ID';
alter table JGJL
  add constraint PK_JGJL primary key (JGJL_ID)
  using index 
  tablespace TS_JGJL_DAT
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table JGJL
  add constraint FK_JGJL_REFERENCE_FXB foreign key (FX_ID)
  references FXB (FX_ID);
alter table JGJL
  add constraint FK_JGJL_REFERENCE_HPYSB foreign key (HPYS_ID)
  references HPYSB (HPYS_ID);
alter table JGJL
  add constraint FK_JGJL_REFERENCE_HPZLB foreign key (HPLX_ID)
  references HPZLB (HPLX_ID);
alter table JGJL
  add constraint FK_JGJL_REFERENCE_JKDDMB foreign key (JKD_ID)
  references JKDDMB (JKD_ID);
alter table JGJL
  add constraint FK_JGJL_REFERENCE_KSBCLLXB foreign key (KSBCLLX_ID)
  references KSBCLLXB (KSBCLLX_ID);
alter table JGJL
  add constraint FK_JGJL_REFERENCE_LDDMB foreign key (LD_ID)
  references LDDMB (LD_ID);
alter table JGJL
  add constraint FK_JGJL_REFERENCE_SBDMB foreign key (SBDM_ID)
  references SBDMB (SB_ID) on delete cascade;
create index JGJL_CX on JGJL (FX_ID,JKD_ID,LD_ID,XSCD,JGSJ,HPHM,HPLX_ID)
  tablespace TS_JGJL_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    minextents 1
    maxextents unlimited
  );
  

Answer 6

查询语句需要用到分区字段JGSJ，否则还是全表扫描索引可以考虑下分区索引

Answer 7

不知这位大大  
我使用JGSJ建立的分区.
查询的时候必须也要根据这个来查询的吗?
 
因为页面查询的时候是组合查询的.
条件可以任意选择.而这个JGSJ这个条件也不是必须的.
所以我只是一个简单的查询统计就需要很久时间  -_#至于你说的分区索引,是针对每个分区都建立一个索引? 而不是全盘索引?

Answer 8

下面是我做出的一个测试select t.*, t.rowid from jgjl t where rownum <= 50
-- 结果是: 50 rows selected in 0.095 seconds
select count(1) from jgjl t
-- 结果是: 1 row selected in 16.172 seconds
-- 统计条数是 : 7575177条数据
所以现在基本上所有的瓶颈在这个统计语句上了. 不知您有什么好的解决方法呢?  还请不吝赐教

Answer 9

你的查询要动态的定位到分区位置，可能要修改下前台程序。不然的话分区有可能更慢！
SELECT COUNT(*) FROM T PARTITION (P1);

Answer 10

如果是小机的话 可以考虑下 并行处理 
parallel 

Answer 11

前端程序可以修改的. 因为还有其他的程序来操作查询我的这张表,.
所以我的意思是在透明的情况下做出优化,.
你上面说的这个情况,是不是有点类似于我方案一提出的呢比如我没有条件限制的话,那么统计一次大概流程是这样的SELECT COUNT(*) FROM T PARTITION (P1);
  +
SELECT COUNT(*) FROM T PARTITION (P2);
  +
SELECT COUNT(*) FROM T PARTITION (P3);
  +
SELECT COUNT(*) FROM T PARTITION (P4);
  +
…………我说的可能不对。还请您多多指正

Answer 12

多CPU 并行处理 并行处理技术通过利用所有可用的硬件资源取得这样的高性能：多个CPU、多个I/O通道、多个存储阵列和磁盘驱动器，以及大量的内存。数据库软件越能有效地利用所有这些资源，

Answer 13

pk都没有？ 分区你可以考虑使用范围+ hash 这个更有利于使用并行查询。但前提是cpu io有关系

Answer 14

10亿条数据肯定要做分区，其实LS的说法可行，范围分区加哈希子分区，然后根据CPU和I/O负载加并行，别的优化方法貌似也没有了。

Answer 15

这么多数据，一般都要将一个时间段以前的数据归档到另一台服务器。
减少OLAP环境的负荷，统计可以通过日结等生成报表。
还有分区不一定能够提高查询速度。分区主要是为提高扩展性，高可用性设计，便于管理。
如果使用了分区，你在查询中要按分区键来查询才能利用分区排除。
索引也进行相应的分区，可建立本地分区索引。
10亿数据量是多长时间？需要每小时建立一个分区？
oracle 9i、10g都不能自动分区,你可以生成一个定时运行的脚本，或建立一个JOB来定时执行，
或者一次手动建立足够多的分区。

Answer 16

聆听大佬一番话.茅塞顿开.
以前见过别人用主键ID进行分区.那么统计的时候就是根据主键进行统计的对吗
10亿条数据  简单的一个统计就需要90s 所以造成查询等待时间过慢.
我这里是测试的数据.数据量比较大.一小时大概200W左右的数据.所以我这里测试分区的性能利用1小时进行一次分区,
现在我想到的唯一解决的方法是我们数据只保留三个月之内的数据.然后我计算出所用的ID 然后根据ID进行分区.  然后写一个job进行执行统计分区.
那么我统计的时候用时间来进行扫描统计.不知道这样的效果会不会好点呢?
期待你的再次回答,小的感激涕零

Answer 17

今天重新做的测试,我做了hash分区,下面是运行的结果select count(*) from JGJL;-- 1 row selected in 12.192 seconds
-- 得到的记录数为:7575177
才7百多万条数据都需要12秒.还是无法接受.,
现在问题就出现在这里了.今天测试了下查询速度倒是不慢,
现在问题的瓶颈出现在是我页面要进行分页,我必须知道符合条件的记录数,用户选择的条件是随意组合的,也可以不选,在不选的情况下就是上面的那个统计语句,
现在大部分的时间都耗费在统计这里了.
不知各位大大有没有意见给小的呢,count(*) 的全盘扫描太耗时了,不知怎么优化呢?期待各位大大的回答

Answer 18

刚开始研究分区表的问题，也来看看
一般分区表需要定期增加分区，也最好定期删除分区，这样保证表中的数据不至于过于庞大，什么时候增减一般要根据业务需要来判断。
在unix系统上，用一个特定的进程来维护分区表，这个进程在指定时间段执行指定的操作来完成任务。需要注意的是分区表维护时，对实时访问系统可能的影响要考虑好

Answer 19

看完了帖子， 楼主想表达的问题就是： select count(1) from t耗费100s, 其中t表有10亿数据。想要进行优化。1）楼主为啥不把这个语句的执行计划帖出来？ 2）
select count(1) from jgjl t
-- 结果是: 1 row selected in 16.172 seconds
-- 统计条数是 : 7575177条数据从这里看，表已经有主键了，count(1)的执行计划应该只扫描主键而已，700W耗了16秒，是有点慢。
原因猜测： 表中有高水位

Answer 20

这个也不一定，所以要看执行计划后才知道是否hwm高，如果是full index scan，那么与hwm没有关系。

Answer 21

对语句进行优化，不要尽量不要写过于复杂的嵌套查询，当需要的时候，后面的子查询数量应为较大的数据。因为SQL是从右至左的查询，先查大的数据。然后建立索引，对经常查到的条件字段 例如 时间 where 月份 = 1月 这样的语句就应该建立所以 来提高效率。对于较复杂的查询应建立组合索引。横纵向切割表。对于大数据量，上百万条的数据可以对其进行横向切割。比如按时间的月份进行切割，或者按照其他方式来切割表，达到快速查询的目的。减少信息的检索量。

Answer 22

从34楼看是快速索引扫描啊。
JGJL_ID    NUMBER not null,
这个列上有索引，所以count(*)的时候直接扫描的索引了，直接读的是索引块信息。
|||||||
What is Index FFS? 
In Oracle there are some SQL queries that can be resolved by reading the index without touching the table data. 
INDEX FAST FULL SCAN is the equivalent of a FULL TABLE SCAN, but for an index.  
It reads using multiblock reads, but results are NOT returned sorted. 
For a query to make use of Index FFS the column should be defined as NOT NULL 
or at least one column in a composite index is NOT NULL. |||||

Answer 23

JGJL_ID 这里设置了 not null
按照道理来说扫描的是索引模块,但是不应该需要统计一次要那么长的时间吧,
\目前为止我还是不太明白具体的原因分析在哪的问题,...
还请这位大大指点迷津

Answer 24

在做查询的时候，是不是可以考虑先排序，再分页，选择完Rowid，再回表查询。
这种查询方式在OLTP网站的应用中，如论坛分页计数中使用很多。

Answer 25

关于分区的建立原则：
在高可用的OLTP环境中，如果能保证所有或者绝大部分的读操作都落在分区关键字上，那么对分区的性能是有好处的。如果有很大部分的读是落在非分区关键字上，则可能会给OLTP环境带来比较大的负面影响。

Answer 26

select /*+order use_nl(t,test) */
object_id,object_name,subobject_name,status
   from (select rid from (
     select rownum rn, rid from (
        select rowid rid from test 
        where owner='SYS'
        and object_type='TABLE'
         order by created desc)
         where rownum<=500)
         where rn>=451)t,
        test 
        where t.rid=test.rid;
        
        
        
 先在索引上进行选择，利用索引的排序特性，利用StopKey 终止继续选择（500 以上的记录），
 然后直接在索引上分页，再回表。
 
 
   |       Rowid:SYS TABLE 2002-1-1
   |       Rowid:SYS TABLE 2002-1-2
   |       Rowid:SYS TABLE 2002-1-3
   |       Rowid:SYS TABLE 2002-1-4
   | 索引  .....
   | 顺序      
   |       Rowid:SYS TABLE 2003-1-1 --------------------->
   |       Rowid:SYS TABLE 2003-1-2    |              | 获得50个rowid，再回表(2)
   |       Rowid:SYS TABLE 2003-1-3    |            ----->
   |       Rowid:SYS TABLE 2003-1-4    |扫描过程(1)
   |       Rowid:SYS TABLE 2003-1-5    |
   |       .....                       |
   |                                   |
   |       Rowid:SYS TABLE 2004-1-1    |
   |       Rowid:SYS TABLE 2004-1-2    |
   |       Rowid:SYS TABLE 2004-1-3    |
   |       Rowid:SYS TABLE 2004-1-4 --------------------->   
 
          ......    
 
 Nested Loop Join(NL),适用于一个小表（也可以说是小的结果集）关联一个大表，可以认为是在小表上做循环，
 然后根据小表的结果返回到大表上去查询数据。
 
 用在LZ的表上，可以用这种方式查询:
 select  t.*, t.rowid from jgjl t where rownum <= 50
 
 
 select /*+order use_nl(t,jgjl) */
JGJL_ID,JGJLBH,HPHM,HPLX_ID，HPYS_ID ,FX_ID,LD_ID,CLSD
   from (select rid from (
     select rownum rn, rid from (
        select rowid rid from jgjl 
        where owner='SYS'
        and object_type='TABLE'
         order by created desc)
         where rownum<=500)
         where rn>=451)t,
        test 
        where t.rid=jgjl.rid;

Answer 27

select /*+order use_nl(t,jgjl) */
JGJL_ID,JGJLBH,HPHM,HPLX_ID，HPYS_ID ,FX_ID,LD_ID,CLSD
   from (select rid from (
     select rownum rn, rid from (
        select rowid rid from jgjl 
        where owner='SYS'        and object_type='TABLE'
         order by created desc)
         where rownum<=500)
         where rn>=451)t,
        test 
        where t.rid=jgjl.rid;
test 改成 jgjl，owner 改成 JGJL

Answer 28

created 无效标识符

Answer 29

 created 应该对应你的表中最有区分度的列了。
 是 JGJL_ID ？

Answer 30

区分度的列我不太清楚是指的什么
JGJL_ID 是一个唯一主键
JGSJ是一个日期,表示一辆车在这里经过的时间.

Answer 31

上面的object_type 是代表什么呢?

Answer 32

不好意思，这列是我直接从别的案例上抓过来，对于你的表也是没有的。
and 这块
根据你对自己的表的查询需求来做了。

Answer 33

首先要建立分区，然后建分区索引，上亿的数据肯定是要分区的这点毋庸质疑，LZ要写相关的动态sql
在插入数据开始的时候要判断一下这个分区是否存在

Answer 34

我觉得奇怪，700W要12秒，啥机器？select * from v$mystat ;  ---记下当前的sid,假设N然后执行你的select count(1)
同时开另一个Session, 执行：select * from v$session_wait where sid = N;看看有哪些等待事件？