不走分区（字段类型不一致）

表结构如下：

create table a_tran
(
id number,
region varchar2(50),
tran_date varchar2(50)

)partition by list (region)
(
partition p_1 values (1),
partition p_2 values (2)
);
create index idx_date on a_tran(tran_date) local;

create table para_code(
para_id number,
para_value varchar2(50)
);
create index idx_code on para_code(para_value);

 

select /*+leading(b)*/ count(*) from a_tran a,para_code b
where tran_date>='20201101'
and tran_date<='20201131'
and region = para_id
and b.para_value='计算'

执行计划如下：

 

 

由于关联的列region和para_id类型不一致，导致a_tran用不上分区表的特性。

 

修改后sql： 

select /*+leading(b)*/ count(*) from a_tran a,para_code b
where tran_date>='20201101'
and tran_date<='20201131'
and region = to_char(para_id)
and b.para_value='计算'