oracle 大数据查询优化的方案

[猪也有理想01]

各位大大
小的最近遇见了一个比较棘手的问题.是关于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掉了.剩下的第二种方案现在是一抹黑,
或者各位大大有其他解决方案,请不啬赐教!!
小的在这感激涕零,

[codearts]

看完了帖子， 楼主想表达的问题就是： 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秒，是有点慢。
原因猜测： 表中有高水位

[liuyi8903]

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

[辛鹤]

对语句进行优化，不要尽量不要写过于复杂的嵌套查询，当需要的时候，后面的子查询数量应为较大的数据。因为SQL是从右至左的查询，先查大的数据。

然后建立索引，对经常查到的条件字段 例如 时间 where 月份 = 1月 这样的语句就应该建立所以 来提高效率。对于较复杂的查询应建立组合索引。

横纵向切割表。对于大数据量，上百万条的数据可以对其进行横向切割。比如按时间的月份进行切割，或者按照其他方式来切割表，达到快速查询的目的。减少信息的检索量。

[zhj9811220]

[Quote=引用楼主 duerlatter 的回复:]
昨天晚上建立表分区已经完毕,然后进行测试,但是发现这样按照时间来建立分区的话,不能设置为自动建立分区(数据库版本的限制),必须手动进行指定,不知各位是否有办法来自动根据时间建立分区的呢?
[/Quote]

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

[傻儿哥]

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;

[傻儿哥]

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

[猪也有理想01]

[Quote=引用 42 楼 csucxcc 的回复:]
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' ……
[/Quote]


created 无效标识符

[傻儿哥]

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

[猪也有理想01]

[Quote=引用 44 楼 csucxcc 的回复:]
created 应该对应你的表中最有区分度的列了。
是 JGJL_ID ？
[/Quote]

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

[猪也有理想01]

上面的object_type 是代表什么呢?

[傻儿哥]

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

[猪也有理想01]

[Quote=引用 32 楼 liuyi8903 的回复:]
这个也不一定，所以要看执行计划后才知道是否hwm高，如果是full index scan，那么与hwm没有关系。
[/Quote]



上面的是运行计划,不知道我选择的是不是你们需要看到的呢
各位大大

[猪也有理想01]

[Quote=引用 33 楼 wxhanshan 的回复:]
对语句进行优化，不要尽量不要写过于复杂的嵌套查询，当需要的时候，后面的子查询数量应为较大的数据。因为SQL是从右至左的查询，先查大的数据。

然后建立索引，对经常查到的条件字段 例如 时间 where 月份 = 1月 这样的语句就应该建立所以 来提高效率。对于较复杂的查询应建立组合索引。

横纵向切割表。对于大数据量，上百万条的数据可以对其进行横向切割。比如按时间的月份进行切割，或者按照其……
[/Quote]

这个大大.你说的横向切割表类似于我的方案一,对吗?
我考虑过这个分割,但是因为数据库还有其他程序在运用,所以我这边切割的方式暂时不适合,
只有等到下次系统升级的时候考虑进去

[傻儿哥]

从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.

|||||

[猪也有理想01]

[Quote=引用 36 楼 csucxcc 的回复:]
从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 withou……
[/Quote]

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

[傻儿哥]

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

[傻儿哥]

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

[猪也有理想01]

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

这位大大.小的是属于小白.不知能否把这段话详细说下呢?
麻烦您了.

[Northgale]

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

[codearts]

我觉得奇怪，700W要12秒，啥机器？

select * from v$mystat ; ---记下当前的sid,假设N

然后执行你的select count(1)
同时开另一个Session, 执行：

select * from v$session_wait where sid = N;

看看有哪些等待事件？