oracle中char关联问题 - 墨天轮问答

oracle中char关联问题

我来答

2025-02-18

oracle中char关联问题

create table t1(id int, name char(10));
create table t2(id int, name char(12));
insert into t1 values(100, 'yang');
insert into t2 values(200, 'yang');
commit;

select * from t1, t2 where t1.name = t2.name and t1.name = 'yang'; -- return 1 row

select * from t1, t2 where t1.name = t2.name and t1.name = rpad('yang', 10); -- return 1 row

with tmp as ( select id, name from t2 where name = rpad('yang', 12) )
select * from t1, tmp where t1.name = tmp.name and t1.name = 'yang'; -- return 1 row

with tmp as ( select id, name from t2 where name = rpad('yang', 12) )
select * from t1, tmp where t1.name = tmp.name and t1.name = rpad('yang', 10); -- return 0 row

为什么最后一个查询没有结果

我来答

添加附件

问题补充

1条回答

默认

最新

DarkAthena

第一条SQL，等号两侧都是char类型，char类型的比较是把短的值的右侧补空格补到和长侧的长度一致后再比，因此能查出来。
在ORACLE中，rpad('yang', 10)是varchar2(10)类型，当它和char(10)进行比较时，其实是跨类型比较，char会保留原本的空格，隐式转换成varchar2类型再进行比较，所以第2条查询SQL能查到记录。
对于第4条，条件 name = rpad('yang', 12)和t1.name = tmp.name and t1.name = rpad('yang', 10) 在ORACLE里会自动优化成name = rpad('yang', 12) and name = rpad('yang', 10) 和
t1.name = rpad('yang', 10) and t1.name = rpad('yang', 12);,此时条件不满足，返回0行

下面是最后一条SQL的执行计划，可以数数空格

<report db_version="11.2.0.4.0">
  <report_id><![CDATA[/orarep/xplan/typical?table=PLAN_TABLE&type=TYPICAL]]></report_id>
  <plan>
    <operation name="SELECT STATEMENT" id="0" depth="0" pos="0">
      <card>1</card>
      <bytes>39</bytes>
      <cost>0</cost>
      <io_cost>0</io_cost>
      <cpu_cost>0</cpu_cost>
    </operation>
    <operation name="FILTER" id="1" depth="1" pos="1">
      <project>&quot;T1&quot;.&quot;ID&quot;[NUMBER,22]</project>
      <predicates type="filter">NULL IS NOT NULL</predicates>
      <qblock>SEL$58A6D7F6</qblock>
      <other_xml>
        <info type="db_version">11.2.0.4</info>
        <info type="parse_schema"><![CDATA["SYSTEM"]]></info>
        <info type="dynamic_sampling">2</info>
        <info type="plan_hash">487071653</info>
        <info type="plan_hash_2">2304446132</info>
        <outline_data>
          <hint><![CDATA[USE_HASH(@"SEL$58A6D7F6" "T2"@"SEL$1")]]></hint>
          <hint><![CDATA[LEADING(@"SEL$58A6D7F6" "T1"@"SEL$2" "T2"@"SEL$1")]]></hint>
          <hint><![CDATA[FULL(@"SEL$58A6D7F6" "T2"@"SEL$1")]]></hint>
          <hint><![CDATA[FULL(@"SEL$58A6D7F6" "T1"@"SEL$2")]]></hint>
          <hint><![CDATA[OUTLINE(@"SEL$1")]]></hint>
          <hint><![CDATA[OUTLINE(@"SEL$2")]]></hint>
          <hint><![CDATA[MERGE(@"SEL$1")]]></hint>
          <hint><![CDATA[OUTLINE_LEAF(@"SEL$58A6D7F6")]]></hint>
          <hint><![CDATA[ALL_ROWS]]></hint>
          <hint><![CDATA[DB_VERSION('11.2.0.4')]]></hint>
          <hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]]></hint>
          <hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
        </outline_data>
      </other_xml>
    </operation>
    <operation name="HASH JOIN" id="2" depth="2" pos="1">
      <card>1</card>
      <bytes>39</bytes>
      <cost>4</cost>
      <io_cost>4</io_cost>
      <cpu_cost>615433</cpu_cost>
      <time>00:00:01 </time>
      <project>(#keys=1) &quot;T1&quot;.&quot;ID&quot;[NUMBER,22]</project>
      <predicates type="access">&quot;T1&quot;.&quot;NAME&quot;=&quot;NAME&quot;</predicates>
    </operation>
    <operation name="TABLE ACCESS" options="FULL" id="3" depth="3" pos="1">
      <object>T1</object>
      <card>1</card>
      <bytes>25</bytes>
      <cost>2</cost>
      <io_cost>2</io_cost>
      <cpu_cost>7591</cpu_cost>
      <time>00:00:01 </time>
      <project>&quot;T1&quot;.&quot;ID&quot;[NUMBER,22], &quot;T1&quot;.&quot;NAME&quot;[CHARACTER,10]</project>
      <predicates type="filter">&quot;T1&quot;.&quot;NAME&quot;=&apos;yang      &apos; AND &quot;T1&quot;.&quot;NAME&quot;=&apos;yang        &apos;</predicates>
      <qblock>SEL$58A6D7F6</qblock>
      <object_alias>T1@SEL$2</object_alias>
    </operation>
    <operation name="TABLE ACCESS" options="FULL" id="4" depth="3" pos="2">
      <object>T2</object>
      <card>1</card>
      <bytes>14</bytes>
      <cost>2</cost>
      <io_cost>2</io_cost>
      <cpu_cost>7591</cpu_cost>
      <time>00:00:01 </time>
      <project>&quot;NAME&quot;[CHARACTER,12]</project>
      <predicates type="filter">&quot;NAME&quot;=&apos;yang        &apos; AND &quot;NAME&quot;=&apos;yang      &apos;</predicates>
      <qblock>SEL$58A6D7F6</qblock>
      <object_alias>T2@SEL$1</object_alias>
    </operation>
  </plan>
</report>

或者把这个SQL换个形式表达，就是这样：

select *
  from t1, t2 
 where t1.name = t2.name
   and t1.name = rpad('yang', 10)
   and t2.name = rpad('yang', 12);

很明显 rpad('yang', 10)是不等于rpad('yang', 12) 的，所以条件恒为假

另外，其实这里还有个问题，rpad函数在入参为常量时，会提前优化掉，但是如果写个自定义函数调用内置的rpad，然后把sql里的函数换成自定义的rpad，由于未执行优化，所以数据能查出来

drop table t1;
drop table t2;
create table t1(id int, name char(4));
create table t2(id int, name char(5));
insert into t1 values(100, 'wang');
insert into t2 values(200, 'wang');
commit;

create or replace function rpad5(str varchar2,len number,pad varchar2 default ' ') return varchar2 is
begin
  return rpad(str,len,pad);
end;
/
                        
select * from t1, t2 where t1.name = t2.name and t1.name = rpad5('wang' ,4,' ') and t2.name = rpad5('wang' ,5,' ') ;--return 1 row

select * from t1, t2 where t1.name = t2.name and t1.name = rpad('wang' ,4,' ') and t2.name = rpad('wang' ,5,' ') ;--return 0 row

ORACLE的char类型有很多特殊处理，一旦叠加上SQL优化器的一些自动处理，会出现很多反直觉的场景

有用 3

打赏 0

回答交流

提交

问题信息

请登录之后查看

邀请回答

暂无人订阅该标签，敬请期待~~

墨值悬赏