我有一個查詢，如下所示：
select
CASE
WHEN col BETWEEN 0 AND 20 THEN 0
WHEN col BETWEEN 20 AND 50 THEN 20
WHEN col BETWEEN 50 AND 100 THEN 40
WHEN col BETWEEN 100 AND 200 THEN 75
WHEN col BETWEEN 200 AND 10000 THEN 86
ELSE 0 END AS t_col
from TAB1;
我想獲得更好的性能并將其重寫為：
select case
WHEN col < 20 then 0
WHEN col < 50 then 20
WHEN col < 100 then 40
WHEN col < 200 then 75
WHEN col < 1000 then 86
END AS t_col
from TAB1;
我認為第二個查詢可能會更快，因為不會創建間隔，而只會將列的值與一個數字進行比較。解釋計劃為我提供了兩個查詢相同的結果。我想知道其中哪一個表現更好？
uj5u.com熱心網友回復：
好吧，讓我做一個 PoC 看看會發生什么
SQL> create table t1 ( c1 number, c2 varchar2(40) , c3 varchar2(40) ) ;
Table created.
SQL> declare
2 begin
3 for i in 1 .. 1000000
4 loop
5 insert into t1 values ( round(dbms_random.value(1,100)) , dbms_random.string('X',40) , dbms_random.string('X',40) );
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('TESTUSER','T1');
PL/SQL procedure successfully completed.
SQL> select count(*) from t1 ;
COUNT(*)
----------
1000000
設想
SQL> set autotrace traceonly
SQL> select
CASE
WHEN c1 BETWEEN 0 AND 20 THEN 0
WHEN c1 BETWEEN 20 AND 50 THEN 20
WHEN c1 BETWEEN 50 AND 100 THEN 40
WHEN c1 BETWEEN 100 AND 200 THEN 75
WHEN c1 BETWEEN 200 AND 10000 THEN 86
ELSE 0 END AS t_c1
from t1; 2 3 4 5 6 7 8 9
1000000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 2929K| 2322 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1000K| 2929K| 2322 (1)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select case
WHEN c1 < 20 then 0
WHEN c1 < 50 then 20
WHEN c1 < 100 then 40
WHEN c1 < 200 then 75
WHEN c1 < 1000 then 86
END AS t_c1
from t1; 2 3 4 5 6 7 8
1000000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 2929K| 2322 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1000K| 2929K| 2322 (1)| 00:00:01 |
--------------------------------------------------------------------------
他們的行為完全相同，對吧？不完全一樣，雖然計劃看起來一樣，但讓我們檢查一下統計資料。為此，我將在每次測驗后重繪 緩沖區快取和共享池。
SQL> set autotrace traceonly timing on
SQL> select
CASE
WHEN c1 BETWEEN 0 AND 20 THEN 0
WHEN c1 BETWEEN 20 AND 50 THEN 20
WHEN c1 BETWEEN 50 AND 100 THEN 40
WHEN c1 BETWEEN 100 AND 200 THEN 75
WHEN c1 BETWEEN 200 AND 10000 THEN 86
ELSE 0 END AS t_c1
from t1; 2 3 4 5 6 7 8 9
1000000 rows selected.
Elapsed: 00:00:02.92
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 2929K| 2322 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1000K| 2929K| 2322 (1)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
72870 consistent gets
6180 physical reads
0 redo size
19435128 bytes sent via SQL*Net to client
733901 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1000000 rows processed
SQL> alter system flush shared_pool ;
System altered.
Elapsed: 00:00:00.08
SQL> alter system flush buffer_cache ;
System altered.
Elapsed: 00:00:00.04
SQL> select t1.* , case
WHEN c1 < 20 then 0
WHEN c1 < 50 then 20
WHEN c1 < 100 then 40
WHEN c1 < 200 then 75
WHEN c1 < 1000 then 86
END AS t_c1
from t1; 2 3 4 5 6 7 8
1000000 rows selected.
Elapsed: 00:00:03.49
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 81M| 2323 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 1000K| 81M| 2323 (1)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
72878 consistent gets
6180 physical reads
0 redo size
101747627 bytes sent via SQL*Net to client
733834 bytes received via SQL*Net from client
66668 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1000000 rows processed
The second one is slower than the first one, probably because of the recursive calls done in the second one ( 18 ) against the first one ( 12 ). If you start putting more fields, and the data is huge, I am quite sure you will get better performance in the query with between than in the other one.
But obviously, it is just a feeling, you must test yourself in your own database. I am quite sure that the final query contains other fields, where conditions, etc... therefore this answer only covers what you put in the original question, and not what will happen in a real case scenario with many other fields, where conditions, indexes, etc...
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/331617.html
上一篇：內連接和左連接使用where過濾器給出相同的結果，因此使用哪個連接？
下一篇：創建postgresql存盤程序