动态SQ中like 条件子句中使用绑定变量

create table test as
select to_char(col) col,
owner,
object_name,
subobject_name,
object_id,
data_object_id,
object_type,
created,
last_ddl_time,
timestamp,
status,
temporary,
generated,
secondary
from dba_objects, (select rownum col from dual connect by level <= 20)

create index test_idx on TEST (col);

 

SQL> alter session set sql_trace = true ;

 

Session altered.

 

declare
 
  c1      sys_refcursor;
  v_sql_1 varchar2(2000) := 'select * from test where col like ''%'' ';

  c2      sys_refcursor;
  v_sql_2 varchar2(2000) := 'select * from test where col like ''';

  c3      sys_refcursor;
  v_sql_3 varchar2(2000) := 'select * from test where col like ' || '''%''' || '||' ||  ':bind';
                         
  r test%rowtype;

begin

  --  dbms_output.put_line(v_sql_1);
  --  dbms_output.put_line(v_sql_3);

  for i in 1 .. 1000 loop
    null;
    open c1 for v_sql_1;
    close c1;
 
    v_sql_2 := v_sql_2 || to_Char(i) || '%''';
    open c2 for v_sql_2;
    close c2;
    v_sql_2 := 'select * from test where col like ''';
 
    open c3 for v_sql_3
      using i;
    close c3;
 
  end loop;

end;

/

*******************************************************************************

[oracle@oracle udump]$ tkprof orcl_ora_31964.trc /home/oracle/like_sql.txt ;

 

TKPROF: Release 10.2.0.1.0 - Production on Tue Sep 29 22:15:21 2009

 

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 

SQL_TRACE

 

********************************************************************************

 

select *

from

 test where col like '%'

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse     1000      0.11       0.08          0          1          0           0

Execute   1000      0.22       0.09          0          0          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total     2000      0.34       0.18          0          1          0           0

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS   (recursive depth: 1)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  TABLE ACCESS FULL TEST (cr=0 pr=0 pw=0 time=30 us)

 

*******************************************************************************

select *

from

 test where col like '1%'

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          2          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.00       0.00          0          2          0           0

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS   (recursive depth: 1)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  TABLE ACCESS FULL TEST (cr=0 pr=0 pw=0 time=137 us)

 

*******************************************************************************

********************************************************************************

 

select *

from

 test where col like '%'||:bind

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse     1000      0.12       0.08          0          0          0           0

Execute   1000      0.35       0.21          0          2          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total     2000      0.47       0.30          0          2          0           0

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS   (recursive depth: 1)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  TABLE ACCESS BY INDEX ROWID TEST (cr=0 pr=0 pw=0 time=583 us)

      0   INDEX RANGE SCAN TEST_IDX (cr=0 pr=0 pw=0 time=90 us)(object id 52516)

 

*******************************************************************************

 

select *

from

 test where col like '2%'

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          2          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.00       0.00          0          2          0           0

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS   (recursive depth: 1)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  TABLE ACCESS BY INDEX ROWID TEST (cr=0 pr=0 pw=0 time=3714 us)

      0   INDEX RANGE SCAN TEST_IDX (cr=0 pr=0 pw=0 time=113 us)(object id 52516)

 

********************************************************************************

 

结论

1 在like中使用绑定变量仍会游标共享

2 Like ‘X%’ 可能会使用游标

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12474069/viewspace-615877/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12474069/viewspace-615877/