查詢作為條件的SQL
解決一個客戶效能問題的時候，碰到一個有意思的SQL語句。
首先建立一個測試環境：
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A, DBA_QUEUES B;
Table created.
SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (ID);
Table altered.
SQL> CREATE INDEX IND_T_TYPE ON T(OBJECT_TYPE);
Index created.
SQL> CREATE TABLE T_TYPE (TYPE VARCHAR2(30) PRIMARY KEY, SUPERTYPE NUMBER);
Table created.
SQL> INSERT INTO T_TYPE SELECT OBJECT_TYPE, MOD(ROWNUM, 3)
2 FROM (SELECT DISTINCT OBJECT_TYPE FROM T);
41 rows created.
SQL> CREATE INDEX IND_TYPE ON T_TYPE (SUPERTYPE);
Index created.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_TYPE')
PL/SQL procedure successfully completed.
SQL> SET TIMING ON
SQL> SET AUTOT TRACE
SQL> SELECT *
2 FROM T T1, T T2
3 WHERE T1.ID = 500
4 AND T2.ID != 500
5 AND (SELECT SUPERTYPE FROM T_TYPE WHERE TYPE = T1.OBJECT_TYPE)
6 = (SELECT SUPERTYPE FROM T_TYPE WHERE TYPE = T2.OBJECT_TYPE)
7 AND T1.OBJECT_TYPE IN (SELECT TYPE FROM T_TYPE WHERE SUPERTYPE = 2) ;
1167560 rows selected.
Elapsed: 00:00:38.32
Execution Plan
----------------------------------------------------------
Plan hash value: 2153988938
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2054K| 411M| 5006 (1)| 00:01:11 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | 2054K| 411M| 5005 (1)| 00:01:11 |
| 3 | NESTED LOOPS | | 1 | 111 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 99 | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_T | 1 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| T_TYPE | 14 | 168 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0074670 | 1 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | T | 2054K| 193M| 5001 (1)| 00:01:11 |
| 9 | TABLE ACCESS BY INDEX ROWID | T_TYPE | 1 | 12 | 1 (0)| 00:00:01 |
|*10 | INDEX UNIQUE SCAN | SYS_C0074670 | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | T_TYPE | 1 | 12 | 1 (0)| 00:00:01 |
|*12 | INDEX UNIQUE SCAN | SYS_C0074670 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( (SELECT /*+ */ "SUPERTYPE" FROM "T_TYPE" "T_TYPE" WHERE "TYPE"=:B1)=
(SELECT /*+ */ "SUPERTYPE" FROM "T_TYPE" "T_TYPE" WHERE "TYPE"=:B2))
5 - access("T1"."ID"=500)
6 - filter("SUPERTYPE"=2)
7 - access("T1"."OBJECT_TYPE"="TYPE")
8 - filter("T2"."ID"<>500)
10 - access("TYPE"=:B1)
12 - access("TYPE"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
92598 consistent gets
0 physical reads
0 redo size
54760599 bytes sent via SQL*Net to client
856699 bytes received via SQL*Net from client
77839 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1167560 rows processed
這個SQL不常見，將兩個連線查詢作為判斷相等的條件，在執行計劃中Oracle用FILTER執行計劃實現了兩個查詢相等的判斷。
按道理來說，這個查詢的效能要比普通的管理慢，但是發現改寫後使用管理的SQL並沒有比這個SQL擁有更好的效能：
SQL> SELECT T1.*, T2.*
2 FROM T T1, T T2, T_TYPE TY1, T_TYPE TY2
3 WHERE T1.ID = 500
4 AND T2.ID != 500
5 AND T1.OBJECT_TYPE = TY1.TYPE
6 AND T2.OBJECT_TYPE = TY2.TYPE
7 AND TY1.SUPERTYPE = TY2.SUPERTYPE
8 AND T1.OBJECT_TYPE IN (SELECT TYPE FROM T_TYPE WHERE SUPERTYPE = 2) ;
1167560 rows selected.
Elapsed: 00:00:37.85
Execution Plan
----------------------------------------------------------
Plan hash value: 1952256050
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 686K| 153M| 5016 (2)| 00:01:11 |
|* 1 | HASH JOIN | | 686K| 153M| 5016 (2)| 00:01:11 |
| 2 | NESTED LOOPS | | 14 | 1890 | 5 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 123 | 4 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 111 | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T | 1 | 99 | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_T | 1 | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T_TYPE | 41 | 492 | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C0074670 | 1 | | 0 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | T_TYPE | 14 | 168 | 0 (0)| 00:00:01 |
|*10 | INDEX UNIQUE SCAN | SYS_C0074670 | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | T_TYPE | 14 | 168 | 1 (0)| 00:00:01 |
|*12 | INDEX RANGE SCAN | IND_TYPE | 14 | | 0 (0)| 00:00:01 |
|*13 | TABLE ACCESS FULL | T | 2054K| 193M| 5001 (1)| 00:01:11 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_TYPE"="TY2"."TYPE")
6 - access("T1"."ID"=500)
8 - access("T1"."OBJECT_TYPE"="TY1"."TYPE")
9 - filter("SUPERTYPE"=2)
10 - access("T1"."OBJECT_TYPE"="TYPE")
12 - access("TY1"."SUPERTYPE"="TY2"."SUPERTYPE")
13 - filter("T2"."ID"<>500)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
92228 consistent gets
0 physical reads
0 redo size
54760599 bytes sent via SQL*Net to client
856699 bytes received via SQL*Net from client
77839 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1167560 rows processed
可以看到，雖然邏輯讀和執行時間都有所提到，但是效能提高几乎可以忽略。
雖然這種寫法很少見，但是Oracle生成的FILTER執行計劃還是比較高效的，不過對於前面的SQL很難進行調整，因為如果不改寫SQL的話，很難透過HINT來改變執行計劃，來消除FILTER，雖然兩個SQL是等價的，但是CBO對二者沒有辦法生成相同的執行計劃，即使嘗試HINT也無濟於事。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-683248/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL多條件查詢SQL
- 資料型別為date作為查詢條件資料型別
- 查詢正常作為條件報錯的問題
- sql 查詢條件問題SQL
- SQL SERVER 條件語句的查詢SQLServer
- linq to sql的多條件動態查詢SQL
- SQL中多條件查詢括號的用途SQL
- MongoDB查詢條件MongoDB
- MongoDB條件查詢MongoDB
- mysql條件查詢MySql
- 查詢條件和條數，先查詢兩條免費的，後面為vip
- Laravel 多條件查詢Laravel
- 條件查詢JSPJS
- 寫一個“特殊”的查詢構造器 – (四、條件查詢：複雜條件)
- SQL Server解惑——查詢條件IN中能否使用變數SQLServer變數
- jQuery製作淘寶商城商品列表多條件查詢功能jQuery
- mongodb條件查詢不等於MongoDB
- golang beego orm 查詢條件 or andGolangORM
- 【mybatis-plus】條件查詢MyBatis
- 多條件查詢---ssh版本
- 複合條件查詢的重構
- oracle date資料的條件查詢Oracle
- mysql拆分字串做條件查詢MySql字串
- AntDesignBlazor示例——列表查詢條件Blazor
- 菜品條件分頁查詢
- Spark SQL解析查詢parquet格式Hive表獲取分割槽欄位和查詢條件SparkSQLHive
- RANK函式基於條件的查詢函式
- 查詢條件為ROWNUM=1仍產生長時間等待
- 一條查詢sql的執行之路SQL
- 等於NULL的查詢條件導致查詢結果不正確Null
- Linq查詢之多個排序條件排序
- Linq兩個from查詢條件
- 【索引】反向索引--條件 範圍查詢索引
- mysql多條件過濾查詢之mysq高階查詢MySql
- 報表查詢條件的 N 種使用方式
- mysql帶AND關鍵字的多條件查詢MySql
- MySQL全面瓦解7：查詢的過濾條件MySql
- 排除表和query查詢條件的expdp、impdp