代码之家 › 专栏 › 技术社区 › graham.reeds

文字字符串可以工作,但变量需要永远

plsql oracle

graham.reeds · 技术社区 · 15 年前

select
    count(*)
from
    address a
where
    a.primary_name like upper('cambourne court') and
    a.secondary_name like upper('flat 9');

但是,更换 upper('flat 9') 一个变量 second_name:=upper('flat 9')

为什么会这样?

编辑

declare
    address_details address%rowtype;
    current_loc varchar2(32);

    prime_name varchar2(255);
    prime_number varchar2(255);
    second_name varchar2(255);
    street_name varchar2(255);
    town_name varchar2(255);
    success boolean;

    the_count number;
begin

prime_name:=upper('&&primary_name');
prime_number:=upper('&&primary_number');
second_name:=upper('&&secondary_name');
street_name:=upper('&&street_name');
town_name:=upper('&&town_name');


success:=true;

-- error checking here (removed for brevity)


if success then
    current_loc := 'finding address';
    select
        count(*)
    into
        the_count
    from
        dependency d,
        address a,
        street s
    where
        d.dep_obj_id1 = 2 and
        d.dep_obj_id2 = 1 and   
        a.loc_id = d.dep_id1 and
        s.loc_id = d.dep_id2 and 
        a.primary_name like prime_name and
        a.secondary_name like second_name and
        s.name like street_name and
        s.town like town_name;

end if;

dbms_output.put_line('success: address found '||the_count); 


exception 
    when too_many_rows then 
        dbms_output.put_line('failure: too many rows while '||current_loc); 
    when no_data_found then 
        dbms_output.put_line('failure: no rows found while '||current_loc); 
    when others then
        dbms_output.put_line('failure: general error while '||current_loc); 

end; 
/

更新 :我重新启动了SQL*Plus,它似乎修复了中断。

4 回复 | 直到 15 年前

dpbradley 15 年前

您的症状对应于具有与表中的列同名的PL/SQL变量。

对一个不正确的答案感到有点内疚,所以我试着复制,但没有得到你的结果:

SQL> select * from address
  2  ;

PRIMARY_NAME               SECONDARY_NAME
------------------------------ ------------------------------
CAMBOURNE COURT            FLAT 9
CAMBOURNE COURT            FLAT 10

SQL> declare
  2  second_name varchar2(30) := upper('flat 9');
  3  x pls_integer;
  4  cursor c is
  5  select
  6      count(*)
  7  from address a
  8  where
  9      a.primary_name like upper('cambourne court') and
 10      a.secondary_name like upper('flat 9')
 11  ;
 12  begin
 13  select count(*) into x
 14   from address a
 15  where
 16      a.primary_name like upper('cambourne court') and
 17      a.secondary_name like upper('flat 9');
 18  dbms_output.put_line('literal: '||x);
 19  select count(*) into x
 20   from address a
 21  where
 22      a.primary_name like upper('cambourne court') and
 23      a.secondary_name like second_name;
 24  dbms_output.put_line('variable: '||x);
 25  end;
 26  /
literal: 1
variable: 1

PL/SQL procedure successfully completed.

Alex Poole 15 年前

111条记录表明第二个\u名称不包含您期望的值;您是如何捕获的 &&secondary_name ,您能检查它在省略的验证节前后实际具有的值吗?从结果来看,它似乎包含“%”而不是“flat 9”,但我假设您已经检查过了。

速度问题表明,优化程序正在以一种改变连接顺序和/或所用索引的方式改变行为。默认情况下,可以加入 street 与每个人划船 address 记录有一个坎伯恩法院,然后才做依赖性检查,但它会有很大的变化,根据什么索引,它认为它可以使用和任何可用的统计资料。不同的是,对于文字,即使您使用 like 没有通配符,因此它可能知道它可以在主\名称和/或次\名称上使用索引;在变量版本中,它不知道在解析查询时必须采用更坏的情况,即“%”。如果它返回111个地址,它可能会得到。

explain plan 很难猜测到底发生了什么,但是您可以尝试添加一些优化提示,以至少尝试获得正确的连接顺序,甚至使用索引-尽管如果您可以有以%开头的值,那么这可能不会保持不变。这可能会告诉你什么是不同的做法。

Gary Myers 15 年前

运行它之后,从v$sql中找到该statemnet的sql\u id

select sql_text, sql_id from v$sql where lower(sql_text) like '%address%street%';

然后把它插进

select * from table(dbms_xplan.display_cursor('1mmy8g93um377'));

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."LOC_ID"="D"."DEP_ID1" AND "S"."LOC_ID"="D"."DEP_ID2")
   4 - filter(("A"."PRIMARY_NAME" LIKE :B4 AND "A"."SECONDARY_NAME" LIKE 
              :B3))
   6 - filter(("S"."NAME" LIKE :B2 AND "S"."TOWN" LIKE :B1))
   7 - filter(("D"."DEP_OBJ_ID1"=2 AND "D"."DEP_OBJ_ID2"=1))

Josep 15 年前

亚历克斯指出了可能的原因。表被编入索引,使用带有变量的“like”是索引停用的一种情况。优化器将带有没有通配符或占位符的常量的“like”表达式视为“=”,因此会考虑索引(如果存在)。

将索引放到这些列上,常量或变量的性能也会一样差。其实不要这样做,只是自动跟踪和比较计划。

当做,