各位大虾帮忙分析一下为什么第二个语句用不上索引？

Question

-- Create table
create table sys.tb_1
(
  CAL_MONTH                  INTEGER not null,
  ORG_NUM                    VARCHAR2(12) not null,
  ORG_CUST_SIGNAGREE_TYPE    VARCHAR2(1) not null
)
partition by range (CAL_MONTH)
(
  partition P200912 values less than (201001),
  partition P201001 values less than (201002),
  partition P201002 values less than (201003),
  partition P201003 values less than (201004),
  partition P201004 values less than (201005)
);
-- Create/Recreate indexes 
create index sys.idx_1 on sys.tb_1 (ORG_NUM);
-- Create table
create table sys.tb_2
(
  CAL_MONTH                   INTEGER not null,
  SUBS_ID                     VARCHAR2(12) not null,
  ORG_NUM                     VARCHAR2(12) not null,
  MSISDN                      VARCHAR2(15) not null,
)
partition by range (CAL_MONTH)
(
  partition P200912 values less than (201001),
  partition P201001 values less than (201002),
  partition P201002 values less than (201003),
  partition P201003 values less than (201004),
  partition P201004 values less than (201005)
);
-- Create/Recreate indexes 
create index sys.idx_2 on sys.tb_2 (ORG_NUM);---可以用上sys.tb_2的索引
select a.* from sys.tb_1 a where exists( select 1 from sys.tb_2  b
where  a.cal_month='201003' and a.cal_month=b.cal_month and a.org_num=b.org_num);
执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS                        37723529        1        182
FILTER                                        
  PARTITION RANGE ALL                        4847        1219392        221929344
   TABLE ACCESS FULL        tb_1        4847        1219392        221929344
  FILTER                                        
   PARTITION RANGE SINGLE                        31        2        36
    TABLE ACCESS BY LOCAL INDEX ROWID        TB_2        31        2        36
     INDEX RANGE SCAN        PMART        IDX_2        3        328        
--不能用上sys.tb_2的索引 ？？？？？？为什么？明明是个分区索引啊？？
select a.* from sys.tb_1 a ,sys.tb_2  b
where  a.cal_month='201003' and a.cal_month=b.cal_month and a.org_num=b.org_num;
执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS                        5        1        374
HASH JOIN                        5        1        374
  PARTITION RANGE SINGLE                        2        1        353
   TABLE ACCESS FULL        TB_1        2        1        353
  PARTITION RANGE SINGLE                        2        1        21
   TABLE ACCESS FULL        TB_2        2        1        21---可以用上sys.tb_2的索引
select a.* from sys.tb_1 a ,sys.tb_2  b
where  a.cal_month='201003' and a.org_num=b.org_num; 
执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS                        50        1        366
NESTED LOOPS                        50        1        366
  PARTITION RANGE SINGLE                        2        1        353
   TABLE ACCESS FULL        TB_1        2        1        353
  PARTITION RANGE ALL                        48        1        13
   INDEX RANGE SCAN        PMART        IDX_2        48        1        13

Answer 1

看你的执行计划，你的优化模式是基于COST的，
是否使用上索引，是ORACLE基于统计数据决定的,
不知你这两个表有没有做analyze，
如果没做，可能导致错误的执行计划。nest loop join: 扫描驱动表(如果有排序,按照 排序字段 排序后),取驱动表的逐行记录与被驱动表对比.
hash join:      先将驱动表hash到内存,再扫描被驱动表(按照物理位置来扫描),逐条取出记录与驱动表来对比.

Answer 2

多谢回复。
这个表每个月会追加200多万条记录，上个月刚做过分析（分析时间是3月12日），所以可能与是否做过分析关系不大。
目前这个表中已经有1年多的数据了，每个月追加的量也比较平稳。
目前我使用的数据库版本是10G，应该都是CBO的了。

Answer 3

未使用上索引与使用上索上实际性能差别大吗？
注意：使用上索引的那个执行计划有一个
PARTITION RANGE ALL 并没有排除分区而未使用上索引的那个执行计划都是PARTITION RANGE SINGLE
有分区排除所以，可能使用上索引那个效率并不一定高，
不知你实际情况怎样？

Answer 4

执行计划：SELECT STATEMENT, GOAL = ALL_ROWS 5 1 374
HASH JOIN 5 1 374
  PARTITION RANGE SINGLE 2 1 353
  TABLE ACCESS FULL TB_1 2 1 353
  PARTITION RANGE SINGLE 2 1 21
  TABLE ACCESS FULL TB_2 2 1 21上面的看起来要比下面的好SELECT STATEMENT, GOAL = ALL_ROWS 50 1 366
NESTED LOOPS 50 1 366
  PARTITION RANGE SINGLE 2 1 353
  TABLE ACCESS FULL TB_1 2 1 353
  PARTITION RANGE ALL 48 1 13
  INDEX RANGE SCAN PMART IDX_2 48 1 13
实际执行起来是哪个快呢？

Answer 5

搞明白hash连接的实现方式就明白为什么索引没有被使用上了
hash连接的基本思想就是根据连接字段将第一个表所有记录hash到内存中(满足过滤条件后)，然后查询第二个表的中的所有的记录（满足过滤条件后），并hash连接字段，若此hash值与第一个表中的hash值相同则证明这两个记录基本可以做连接。所以我们对第二个表会进行一个全表扫描，并且对第二表进行扫描时与第一个表没有任何关系，而此时oracle是采用的CBO算法，此时进行全表扫描肯定比索引扫描效率要高，所以不会使用索引。不过如果在过滤条件中存在索引的话，hash 连接也有可能使用到索引.
例如本例中，我们添加一个条件
select a.* from sys.tb_1 a ,sys.tb_2 b
where a.cal_month='201003' and a.cal_month=b.cal_month 
and a.org_num=b.org_num  and b.org_num = 5;
若此查询使用的是hash连接的话，是应该会走索引的.最后说明下：hash连接不使用索引时，性能可能会提升的。令补充下：你的第一个查询改成如下语句可能性能会有所提高
select a.* from sys.tb_1 a where a.cal_month='201003'
and exists( select 1 from sys.tb_2 b
where b.cal_month='201003'  and a.org_num=b.org_num);