关于Oracle 分区表与其他表关联时的疑问

Question

我针对JSZB的JSRQ字段建立的分区表如下：
partition by range (JSRQ)
(
  partition PART_00 values less than ('2007-06-30')
    tablespace JSZB02
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    ),
  partition PART_01 values less than ('2007-12-31')
    tablespace JSZB02
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      minextents 1
      maxextents unlimited
    )
)
执行查询：select count(*) from jszb partition(part_01) t；时速度挺快的，
但我需要查明细JSMXB的数据，故得到以下查询语句：
select count(*) from jszb partition(part_01) t,jsmxb a where a.jyh=b.jyh
执行这条语句时，速度超慢jszb.jyh为jszb的主键，存在唯一索引
jsmxb.jyh和jsmxb.sxh为jsmxb的主键，也存在索引实在搞不懂为什么速度受到大影响，达人们帮忙啊！！

Answer 1

jszb记录数：1529535
jsmxb记录数：1804646

Answer 2

select count(*) from jszb partition(part_01) t,jsmxb a where a.jyh=b.jyh 的执行计划Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3799 Card=1 Bytes=
          33)   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=3799 Card=693524 Bytes=22886292)
   3    2       INDEX (FAST FULL SCAN) OF 'PK_YB_JSMXB' (INDEX (UNIQUE
          )) (Cost=3232 Card=1735798 Bytes=36451758)   4    2       TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'YB_JSZB_1' (T
          ABLE) (Cost=0 Card=1 Bytes=12)   5    4         INDEX (UNIQUE SCAN) OF 'PK_YB_JSZB_1' (INDEX (UNIQUE
          )) (Cost=0 Card=1)Statistics
----------------------------------------------------------
       5561  recursive calls
          0  db block gets
    4349127  consistent gets
      35101  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         95  sorts (memory)
          0  sorts (disk)
          1  rows processed

Answer 3

晕，这么大的数据量，你的两表关联查询，就算是进行索引的扫描也会很久啊。笛卡儿乘积啊兄弟。
看看jszb partition(part_01) t 和 jsmxb各有多少数据量，数据量少点的放后面。就这个数据量的情况来看，估计也好不到哪里去
另外把count(*)改为count(t.jyh)试试看。

Answer 4

在分区表上就jyh建local索引试试看？这里执行计划是索引全扫。而你的主键似乎没有建成分区索引，导致扫描索引的时候扫到了其他分区去了。

Answer 5

local索引的建法：就是针对每个分区分别建立索引
create index idx_m_send_id on sms_confirm_month(send_id)
local
(
partition idx_m_send_id_1 tablespace SMS_INDEX1,
partition idx_m_send_id_2 tablespace SMS_INDEX1,
partition idx_m_send_id_3 tablespace SMS_INDEX1,
partition idx_m_send_id_4 tablespace SMS_INDEX1,
partition idx_m_send_id_5 tablespace SMS_INDEX1,
partition idx_m_send_id_6 tablespace SMS_INDEX1,
partition idx_m_send_id_7 tablespace SMS_INDEX1,
partition idx_m_send_id_8 tablespace SMS_INDEX1,
partition idx_m_send_id_9 tablespace SMS_INDEX1,
partition idx_m_send_id_10 tablespace SMS_INDEX1,
partition idx_m_send_id_11 tablespace SMS_INDEX1,
partition idx_m_send_id_12 tablespace SMS_INDEX1
);

Answer 6

我上面的例子，send_id是PK，我为了查询，分别在每个区上建立了分区索引。
我每个区的数据量在1000万左右。

Answer 7

我在分区列上建了 local索引但我分区列不是表的主键

Answer 8

你这里where条件里面是主键，你在别的列上建分区索引，查询用不到那个列，就不会用那个索引。

Answer 9

这种数据量，如果不是分区索引问题的话，就是你机器太烂了，基本的sql调优起的作用也不大了

Answer 10

好像是的哦我用的数据库服务器确实比较垃圾的刚才把jszb的主键索引修改成分区表空间，感觉速度快了点，贴出执行计划，如下：SQL> select count(*) from yb_jsmxb a,yb_jszb_1 partition(part_01) b where a.bcjyh=b.bcjyh;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11475 Card=1 Bytes
          =33)   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=11475 Card=693524 Bytes=22886292)
   3    2       PARTITION RANGE (SINGLE) (Cost=4791 Card=583691 Bytes=
          7004292)   4    3         TABLE ACCESS (FULL) OF 'YB_JSZB_1' (TABLE) (Cost=479
          1 Card=583691 Bytes=7004292)   5    2       INDEX (FAST FULL SCAN) OF 'PK_YB_JSMXB' (INDEX (UNIQUE
          )) (Cost=3232 Card=1735798 Bytes=36451758)Statistics
----------------------------------------------------------
         21  recursive calls
          0  db block gets
      22340  consistent gets
      12183  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed