如何把PLSQL中SQL中的变量替换成常量

Question

在PLSQL程序中，写了一个简单的，有动态参数的SQL语句。测试脚本如下：
DECLARE
  lv_input_parameter VARCHAR2(1) := 'P';
  CURSOR test_cur(p_mode VARCHAR2)
  IS
    SELECT * 
      FROM test_738_a 
     WHERE DECODE(p_mode, 'P', created, SYSDATE -1) >= SYSDATE -1
       AND DECODE(p_mode, 'P', created, SYSDATE) <= SYSDATE;
BEGIN
  FOR rec IN test_cur(lv_input_parameter) LOOP
    NULL;
  END LOOP;
END;以上脚本希望达到的目的是，
如果SQL中输入参数的值是P的时候，使用created列上的索引，
如果SQL中输入参数的值不是P的时候，忽略WHERE子句，直接进行全表扫描即可。但是，在trace文件中发现执行计划是这样的。
Rows     Row Source Operation
-------  ---------------------------------------------------
     53  TABLE ACCESS FULL TEST_738_A (cr=4970 pr=4935 pw=0 time=505282 us)即，虽然输入参数是P，但是由于在SQL中，使用的是变量的形式，
所以，在执行计划的时候，没有进行变量窥探。请问，有没有解决的办法？即输入P的时候，使用索引，反之，全表扫描。谢谢。

Answer 1

如果把脚本中的变量改成常量的话：SELECT * 
      FROM test_738_a a
     WHERE DECODE('P', 'P', a.created, SYSDATE -1) >= SYSDATE -1
       AND DECODE('P', 'P', a.created, SYSDATE) <= SYSDATE;相应的执行计划，就变成了Rows     Row Source Operation
-------  ---------------------------------------------------
     53  TABLE ACCESS BY INDEX ROWID TEST_738_A (cr=16 pr=0 pw=0 time=197 us)
     53   INDEX RANGE SCAN TEST_738_A_IND (cr=3 pr=0 pw=0 time=125 us)(object id 770511)这样就成了，期望的结果了。

Answer 2

没测  我觉得这样应该可以，试试看。另外，把那个* 换成具体的栏位名字
declare
  -- Local variables here
  lv_input_parameter VARCHAR2(1) := 'P';
  str                varchar2(1000) := '';
  refc               sys_refcursor;
begin
  -- Test statements here
  if lv_input_parameter = 'P' then
    str := ' where created>= sysdate-1 and created <= sysdate';
  end if;  open refc for 'SELECT * FROM test_738_a ' || str;  loop
    exit when refc%notfound;
    null;
  end loop;end;

Answer 3

我明白你的意思了，你是通过动态SQL的方式来实现的。但是，项目上，一段SQL有个好几百行，不太方便修改（客户不同意）。所以，有其他的方案么？谢谢。

Answer 4

http://www.itpub.net/thread-943141-3-1.html这个 会不会有帮助?

Answer 5

我还实验了一种方法，虽然还是没有成功，但是不妨说一下。1、创建一张临时表（test_738_b），一行一列，存放p_mode的值。2、对临时表做统计情报，收集直方图
BEGIN
  dbms_stats.gather_table_stats(ownname => USER,
                                tabname => 'TEST_738_B',
                                method_opt => 'FOR ALL COLUMNS ');
END3、把临时表的值，带入到SQL中，
SELECT  * 
      FROM test_738_a a,
           test_738_b b
     WHERE DECODE(b.value, 'P', a.created, SYSDATE -1) >= SYSDATE -1
       AND DECODE(b.value, 'P', a.created, SYSDATE) <= SYSDATE结果：
两张表都进行的全表扫描，所以b.value在直方图中的值，并没有派上用场。

Answer 6

给你一个思路。我也是经常用这种方式来实现你说的这种案例。
第一：你写一个方法。返回当前值的的一个Table样式。
第二：你用自己的SQL与返回的Table相匹配。
然后一后面实现你的逻辑。

Answer 7

加hint不行吗？
反正谓词里面没有created字段，也不会用上这个索引。

Answer 8

加hint index(a test_738_a_ind),直接被优化器无视掉了。

Answer 9

ulihss你好，能说的详细点吗？我没看懂。

Answer 10

decode也算是一个函数吧，你把decode放在表达式左边是直接略过所有的索引的，所以得想法放在右边或直接不要这么写。
如果非要这样弄，这种情况下，用函数索引也许有效。试试建一个

Answer 11

 lv_input_parameter VARCHAR2(1) := 'P';
  CURSOR test_cur(p_mode VARCHAR2)
  IS
  SELECT *  
  FROM test_738_a  
  WHERE DECODE(p_mode, 'P', created, SYSDATE -1) >= SYSDATE -1
  AND DECODE(p_mode, 'P', created, SYSDATE) <= SYSDATE;什么需求？先判断lv_input_parameter  就可以了啊if lv_input_parameter == 'P'then
CURSOR test_cur(p_mode VARCHAR2)
  IS
  SELECT *  
  FROM test_738_a  
  WHERE created between  SYSDATE -1 and SYSDATE;
else
 CURSOR test_cur(p_mode VARCHAR2)
  IS
  SELECT *  
  FROM test_738_a ;
 end if;这样就可以啊，有什么特殊需求？

Answer 12

我实验了很多种的可能，最后发现，需要写成以下的形式
  CURSOR test_cur(p_mode VARCHAR2)
  IS
    SELECT * 
      FROM test_738_a a
     WHERE DECODE(p_mode, 'P', 1, 2) = 1
       AND a.created >= SYSDATE -1
       AND a.created < SYSDATE
    UNION ALL
    SELECT * 
      FROM test_738_a a
     WHERE DECODE(p_mode, 'P', 1, 2) = 2;

Answer 13

以下是进行全表扫描的运行统计信息
SELECT * 
FROM
 TEST_738_A A WHERE DECODE(:B1 , 'P', 1, 2) = 1 AND A.CREATED >= SYSDATE -1 
  AND A.CREATED < SYSDATE UNION ALL SELECT * FROM TEST_738_A A WHERE 
  DECODE(:B1 , 'P', 1, 2) = 2
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.43          0          0          0           0
Execute      1      0.00       0.65          0          0          0           0
Fetch     3570  22200.00   57279.65       4936       8496          0      356914
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3572  22200.00   57280.73       4936       8496          0      356914Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)Rows     Row Source Operation
-------  ---------------------------------------------------
 356914  UNION-ALL  (cr=8496 pr=4936 pw=0 time=2878377 us)
      0   FILTER  (cr=0 pr=0 pw=0 time=10 us)
      0    TABLE ACCESS BY INDEX ROWID TEST_738_A (cr=0 pr=0 pw=0 time=0 us)
      0     INDEX RANGE SCAN TEST_738_A_IND (cr=0 pr=0 pw=0 time=0 us)(object id 770511)
 356914   FILTER  (cr=8496 pr=4936 pw=0 time=2521297 us)
 356914    TABLE ACCESS FULL TEST_738_A (cr=8496 pr=4936 pw=0 time=2521256 us)********************************************************************************
你可以发现，这时候索引扫描没有去执行。以下是索引扫描时，运行的统计信息
SELECT * 
FROM
 TEST_738_A A WHERE DECODE(:B1 , 'P', 1, 2) = 1 AND A.CREATED >= SYSDATE -1 
  AND A.CREATED < SYSDATE UNION ALL SELECT * FROM TEST_738_A A WHERE 
  DECODE(:B1 , 'P', 1, 2) = 2
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00      19.66          0          0          0           0
Execute      1    100.00      33.53          0          0          0           0
Fetch        1    100.00     247.35          6         15          0          43
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    200.00     300.54          6         15          0          43Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)Rows     Row Source Operation
-------  ---------------------------------------------------
     43  UNION-ALL  (cr=15 pr=6 pw=0 time=2258 us)
     43   FILTER  (cr=15 pr=6 pw=0 time=2207 us)
     43    TABLE ACCESS BY INDEX ROWID TEST_738_A (cr=15 pr=6 pw=0 time=2147 us)
     43     INDEX RANGE SCAN TEST_738_A_IND (cr=3 pr=3 pw=0 time=1811 us)(object id 770511)
      0   FILTER  (cr=0 pr=0 pw=0 time=4 us)
      0    TABLE ACCESS FULL TEST_738_A (cr=0 pr=0 pw=0 time=0 us)********************************************************************************
你可以发现，这时，全表扫描的执行路径，并没有去执行。综上，可以看出，根据不同的输入条件，
虽然优化器的执行计划中的cost = 索引扫描 + 全表扫描
看上去比较大。
但是在实际运行SQL的时候，oracle会把参数带入SQL，选择合适的执行路径。
从而拥有较好的性能。