SQL 调优问题，“占位操作” "开关操作 "

Question

《DBA日记 第一部》中看 到这么一段话 占位操作：占位操作是开发人员常用的一种编程方法，比如 WHERE 条件是动态生成的，那么WHERE
后面的第一个条件和后面的条件是不同的，WHERE 后面的第一个条件前面没有任何运算符，而后面的
都带有运算符，因此如果WHERE 变为WHERE 1=1 那么后面的所有条件都有运算符，编程就简单很多。实际上占位操作是一种很不好的编程习惯，如果条件很复杂，大量使用占位操作，那么会导致优化器无
法获得正确的执行计划开关操作：开关操作也是一种编程人员常用的编程手段，同样也是性能杀手。比如写好一个SQL 模板，
其中有一个开关(1=:p1 and ....)，如果我们希望后面的AND 起作用，那么p1 就赋值为1，如果不希望
后面的AND 起作用，p1 就赋值非1 的值。
这个  “占位操作”   "开关操作 "能否有 达人 写两个语句 来说明一下？？

Answer 1

占位操作: 就是俗称的在WHERE 1=1
目的是为了在之后拼接条件时，直接链接上"AND"用的。你说：
实际上占位操作是一种很不好的编程习惯，如果条件很复杂，大量使用占位操作，那么会导致优化器无
法获得正确的执行计划其实，一般在一段SQL中，最多也就用到一句 1=1，不存在大量使用的情况。
查询优化器遇到1=1或是1=2这种简单的语句时，它是能识别出来的。

Answer 2

开关操作：你说是性能杀手，这个话不完全正确的。开关操作，只是解决是否运行指定的SQL语句。而SQL语句运行的性能好坏，是与当前SQL的执行路径有关系，（比如是否高效的使用索引，当然使用索引未必高效）。所以说，开关和性能没有必然的联系。

Answer 3

例如，以下的SQL实现一个功能，
当输入参数是P的时候，使用索引的方式扫描表，
当输入参数是A的时候，使用全表扫描。以下分析一下，这段SQL的执行计划和实际执行的行源操作
EXPLAIN PLAN FOR
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;
SELECT * FROM TABLE(dbms_xplan.display(NULL, NULL, 'ALL'));-------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |   356K|    31M|  1377 (100)|
|   1 |  UNION-ALL                    |                |       |       |            |
|*  2 |   FILTER                      |                |       |       |            |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_738_A     |    19 |  1786 |     4   (0)|
|*  4 |     INDEX RANGE SCAN          | TEST_738_A_IND |    19 |       |     3   (0)|
|*  5 |   FILTER                      |                |       |       |            |
|   6 |    TABLE ACCESS FULL          | TEST_738_A     |   356K|    31M|  1373   (1)|
-------------------------------------------------------------------------------------你能看到这时候，这段执行计划的成本是很高的。使用'P'参数，带入实际运行后，从trace文件中取得行源操作信息。
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)********************************************************************************
你能发现，只有索引部分的那段SQL被执行了。使用'A'参数，带入实际运行后，从trace文件中取得行源操作信息。
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)********************************************************************************
你能发现，只有全表扫描的那段SQL被执行了。综上，你能发现开关操作可能会出现很高的执行计划成本，但是实际执行的时候，SQL会根据开关的指示，去运行打开的SQL，忽略关闭的SQL。所以说，开关和性能没有必然的联系。

Answer 4

这个地方还有问题
1 不明白 
 a.created >= SYSDATE -1
  AND a.created < SYSDATE
这两个 中间为什么是 and 而不是OR2 从 trace 去看执行计划 ，这是一个什么方式
  跟 set autotrace on 这样的方式 相比有什么优点??没做过开发 确实 语句经常会有看不懂的地方

Answer 5

以上的SQL由两段子SQL组成，有开关标识，动态决定在运行的时候执行哪一段子查询。如果想查询出部分数据，那么通过索引效率较高，所以走基于created字段的索引，即第一段子SQL。如果想查询出全表的数据，那么走全表扫描的效率较高，即第二段子SQL。由于SQL在运行之前，需要进行解析，生成执行计划，所以第一段子SQL和第二段子SQL的执行计划都同时生成了，并且对其cost，进行了累加。导致了，你看到的执行计划比单独的第一段子SQL和第二段子SQL都要大。这个就会给一知半解的人造成一种假象，即使用动态开关拼接SQL后，cost变大了。但是需要注意的是，执行计划是一个理论值，就像纸上谈兵一样。真正运行的效率，是需要参考trace文件中的行源操作信息的。在上例中，通过行源操作信息，你可以发现，在输入“P”的时候，整个这段SQL，走了索引，根本就没有去走全表扫描。所以，它执行的效率是很高的。即，行源操作信息是你运行SQL的实际信息，而执行计划是SQL运行的理论值。两者在大部分情况下，是一致的，但是遇到复杂的情况时，就会产生偏差，开关操作，就是一例。希望你能明白，谢谢。

Answer 6

1、created字段是一个索引字段，在SQL语句中要使用索引，必须使用and，or会使索引字段失效。2、对于SQL来说，可以使用set autotrace on来查看其运行结果。但是，它的功能有限，显示的信息也有限，不如trace文件来的详细。另外，set autotrace on不能跟踪DDL语句或是包的执行全过程，trace文件可以。谢谢。

Answer 7

a.created >= SYSDATE -1
  AND a.created < SYSDATE
这个是我之前理解错了，
相当于 〉=9 and <10    我原来以为是  〉=10 or 〈9