sql語句中常量的處理

sql語句中常量的處理 =========================================================== 作者: yangtingkun(http://yangtingkun.itpub.net) 發表於: 2005.03.01 00:24 分類: ORACLE 出處: http://yangtingkun.itpub.net/post/468/20038 --------------------------------------------------------------- 看到論壇中一個帖子：http://www.itpub.net/326343.html 兩個sql語句的執行計劃完全相同，但是執行花費的時間相差了近一倍。於是打算借這個問題說說如何處理常量可以使sql執行的更快。 當CBO發現表示式中存在常量或常量表示式時，最佳化器會在SQL執行之前將表示式的值計算出來，避免在表示式中進行多次計算。但是最佳化器無法將等號（不等號、大於號、小於號等）一邊的常量移動到等式的另一邊。 舉例說明： COL = 1000 COL = 500 + 500 COL – 500 = 500 雖然上面的三個等式是等價的，但是CBO只能將第二個轉化為第一個等式，而第三個最佳化器是沒有辦法最佳化的。 第三個等式由於對列包含了運算，因此不能使用這個列的索引（當然可以使用函式索引）。不考慮索引的因素，假設COL列上沒有索引，都執行全表掃描操作，第三種方式仍然是最費時的。對於全表掃描，Oracle會根據等式的條件對錶中每條記錄進行過濾，對於1、2來說，這裡只是一個比較的過程。而對於3來說，對於Oracle每條記錄都要執行一個“-500”的操作，然後在與500比較，當資料量大的時候，必然會造成一定效能問題。論壇中那個帖子中遇到的就是這個問題。 下面看一個經過簡化的例子： 首先構造一個居然一定資料量的表。 SQL> create table t as select * from dba_objects; 表已建立。 SQL> insert into t select * from t; 已建立6273行。 SQL> insert into t select * from t; 已建立12546行。 SQL> insert into t select * from t; 已建立25092行。 SQL> insert into t select * from t; 已建立50184行。 SQL> insert into t select * from t; 已建立100368行。 SQL> insert into t select * from t; 已建立200736行。 SQL> commit; 提交完成。 SQL> select count(*) from t; COUNT(*) ---------- 401472 然後尋找包含較多資料的一天用於測試。 SQL> select trunc(created), count(*) from t group by trunc(created) 2 having count(*) > 10000; TRUNC(CREATED) COUNT(*) ------------------- ---------- 2004-06-29 00:00:00 358144 2004-11-15 00:00:00 34304 下面包括4條sql語句，這4條sql語句完全是等價的。執行計劃也沒有其它的選擇，只能是全表掃描，來測試一下它們所需的執行時間。 為了避免資料快取帶來的誤差，每個sql執行兩次，取第二次執行時間。 語句一：推薦寫法 SQL> set timing on SQL> select count(*) 2 from t 3 where created >= to_date('2004-06-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 4 and created < to_date('2004-06-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 5 group by owner; COUNT(*) ---------- 448 99968 223872 25472 8384 已用時間: 00: 00: 00.08 SQL> select count(*) 2 from t 3 where created >= to_date('2004-06-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 4 and created < to_date('2004-06-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 5 group by owner; COUNT(*) ---------- 448 99968 223872 25472 8384 已用時間: 00: 00: 00.07 語句二：如果不能避免常量的計算或型別轉化，儘量讓計算或轉化的結果直接等於列的值，不要對列進行計算或轉化。 SQL> select count(*) 2 from t 3 where created >= to_date(to_char(to_date('2004-06-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 123.456, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') + 123.456 4 and created < to_date(to_char(to_date('2004-06-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1000, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') - 1000 5 group by owner; COUNT(*) ---------- 448 99968 223872 25472 8384 已用時間: 00: 00: 00.09 SQL> select count(*) 2 from t 3 where created >= to_date(to_char(to_date('2004-06-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss') - 123.456, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') + 123.456 4 and created < to_date(to_char(to_date('2004-06-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + 1000, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') - 1000 5 group by owner; COUNT(*) ---------- 448 99968 223872 25472 8384 已用時間: 00: 00: 00.08 即使包含了比較複雜的運算和多次資料轉化，但是常量在執行開始之間就計算好了，因此，一次的計算不會對查詢帶來多大的影響。 語句三： SQL> select count(*) 2 from t 3 where to_char(created, 'yyyy-mm-dd') = '2004-06-29' 4 group by owner; COUNT(*) ---------- 448 99968 223872 25472 8384 已用時間: 00: 00: 01.02 SQL> select count(*) 2 from t 3 where to_char(created, 'yyyy-mm-dd') = '2004-06-29' 4 group by owner; COUNT(*) ---------- 448 99968 223872 25472 8384 已用時間: 00: 00: 01.03 由於對於每條記錄都要對CREATED列進行型別轉化，因此會耗費相當多的執行時間。 語句四： SQL> select count(*) 2 from t 3 where to_char(created, 'yyyy-mm-dd hh24:mi:ss') >= '2004-06-29 00:00:00' 4 and to_char(created, 'yyyy-mm-dd hh24:mi:ss') <= '2004-06-29 23:59:59' 5 group by owner; COUNT(*) ---------- 448 99968 223872 25472 8384 已用時間: 00: 00: 02.02 SQL> select count(*) 2 from t 3 where to_char(created, 'yyyy-mm-dd hh24:mi:ss') >= '2004-06-29 00:00:00' 4 and to_char(created, 'yyyy-mm-dd hh24:mi:ss') <= '2004-06-29 23:59:59' 5 group by owner; COUNT(*) ---------- 448 99968 223872 25472 8384 已用時間: 00: 00: 02.04 由於包含了兩次條件語句，對於每條記錄需要對CREATED列進行兩次轉化，因此所需時間是語句三的二倍。 因此，在寫sql時，應該儘量避免對列操作，這樣不僅會導致無法使用索引，而且還會增加執行成本，導致sql執行速度變慢。[@more@]