[20210408]max優化.txt
[20210408]max優化.txt
--//上午看了利用max優化的案例，連結https://blog.csdn.net/enmotech/article/details/115388519
--//第一眼覺得寫的sql語句有點怪怪的，自己也嘗試看看。
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t1 as select * from dba_objects ;
Table created.
--//分析略。
--//執行語句如下：
SELECT NVL (MAX (T1.CREATED), SYSDATE) FROM DUAL LEFT JOIN T1 ON T1.OWNER = 'OUTLN' AND OBJECT_TYPE IS NOT NULL;
--//首先寫這樣語句的開發人員應該發一個獎，邏輯思維不是一般人具備的。
--//我開始以為特殊需要這樣寫即使查詢不到，也是有返回值。
SCOTT@book> SELECT NVL (MAX (T1.CREATED), SYSDATE) FROM DUAL LEFT JOIN T1 ON T1.OWNER = 'aaOUTLN' AND OBJECT_TYPE IS NOT NULL;
NVL(MAX(T1.CREATED)
-------------------
2021-04-08 16:18:22
--//而實際上取最大值就決定一定有返回值，寫成如下應該也沒有問題。
SCOTT@book> SELECT NVL (MAX (T1.CREATED), SYSDATE) from T1 where T1.OWNER = 'aaOUTLN' AND OBJECT_TYPE IS NOT NULL;
NVL(MAX(T1.CREATED)
-------------------
2021-04-08 16:18:43
--//真心不知道這樣的查詢是否是開發需要的，因為owner=任何值這條語句都會有返回值。
--//剩下的是優化這條語句。原始連結使用了函式索引。實際上主要查詢條件裡面有1個條件OBJECT_TYPE IS NOT NULL;比較特別。
2.測試：
--//實際上可以索引建立順序可以顛倒一下。
SCOTT@book> create index i_t1_owner_created_object_type on t1(owner,created,object_type);
Index created.
SCOTT@book> SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE) from T1 where T1.OWNER = 'OUTLN' AND OBJECT_TYPE IS NOT NULL;
NVL(MAX(T1.CREATED)
-------------------
2013-08-24 11:39:07
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID d51t9cb1vzk1u, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE)
from T1 where T1.OWNER = 'OUTLN' AND OBJECT_TYPE IS NOT NULL
Plan hash value: 1529359973
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 16 (100)| | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 23 | | | 1 |00:00:00.01 | 3 |
| 2 | FIRST ROW | | 1 | 1 | 23 | 16 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T1_OWNER_CREATED_OBJECT_TYPE | 1 | 1 | 23 | 16 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OWNER"='OUTLN')
filter("OBJECT_TYPE" IS NOT NULL)
--//實際上看OBJECT_TYPE是否全部是空值。如果全部為NULL，實際上查詢還是很慢的。
SCOTT@book> update t1 set object_type=null where owner= 'SYS';
37823 rows updated.
SCOTT@book> commit ;
Commit complete.
SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE) from T1 where T1.OWNER = 'SYS' AND OBJECT_TYPE IS NOT NULL;
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5mqw41fywv1vt, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE)
from T1 where T1.OWNER = 'SYS' AND OBJECT_TYPE IS NOT NULL
Plan hash value: 1529359973
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 16 (100)| | 1 |00:00:00.01 | 411 |
| 1 | SORT AGGREGATE | | 1 | 1 | 23 | | | 1 |00:00:00.01 | 411 |
| 2 | FIRST ROW | | 1 | 1 | 23 | 16 (0)| 00:00:01 | 0 |00:00:00.01 | 411 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T1_OWNER_CREATED_OBJECT_TYPE | 1 | 1 | 23 | 16 (0)| 00:00:01 | 0 |00:00:00.01 | 411 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OWNER"='SYS')
filter("OBJECT_TYPE" IS NOT NULL)
--//很明顯出現這樣的極端的情況效率就很差。
SCOTT@book> update t1 set object_type='TABLE' where owner= 'SYS' and object_type is null and rownum=1;
1 row updated.
SCOTT@book> commit ;
Commit complete.
SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE) from T1 where T1.OWNER = 'SYS' AND OBJECT_TYPE IS NOT NULL;
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5mqw41fywv1vt, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE)
from T1 where T1.OWNER = 'SYS' AND OBJECT_TYPE IS NOT NULL
Plan hash value: 1529359973
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 16 (100)| | 1 |00:00:00.01 | 410 |
| 1 | SORT AGGREGATE | | 1 | 1 | 23 | | | 1 |00:00:00.01 | 410 |
| 2 | FIRST ROW | | 1 | 1 | 23 | 16 (0)| 00:00:01 | 1 |00:00:00.01 | 410 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T1_OWNER_CREATED_OBJECT_TYPE | 1 | 1 | 23 | 16 (0)| 00:00:01 | 1 |00:00:00.01 | 410 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OWNER"='SYS')
filter("OBJECT_TYPE" IS NOT NULL)
--//實際上主要object_type is null 使用FF表示，相當於最大值。這樣從最大端掃描如果object_type空值很多的情況下邏輯讀依舊很大。
--//索引rebuild看看：
SCOTT@book> alter index I_T1_OWNER_CREATED_OBJECT_TYPE rebuild ;
Index altered.
SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE) from T1 where T1.OWNER = 'SYS' AND OBJECT_TYPE IS NOT NULL;
Plan hash value: 1529359973
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 14 (100)| | 1 |00:00:00.01 | 130 |
| 1 | SORT AGGREGATE | | 1 | 1 | 19 | | | 1 |00:00:00.01 | 130 |
| 2 | FIRST ROW | | 1 | 1 | 19 | 14 (0)| 00:00:01 | 1 |00:00:00.01 | 130 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T1_OWNER_CREATED_OBJECT_TYPE | 1 | 1 | 19 | 14 (0)| 00:00:01 | 1 |00:00:00.01 | 130 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
--//主要是索引rebuild後null佔用空間減少。
3.繼續：
--//建立降序索引呢？
SCOTT@book> create index i_t1_owner_created_object_d on t1(owner,created,object_type desc);
Index created.
SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE) from T1 where T1.OWNER = 'SYS' AND OBJECT_TYPE IS NOT NULL;
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5mqw41fywv1vt, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE)
from T1 where T1.OWNER = 'SYS' AND OBJECT_TYPE IS NOT NULL
Plan hash value: 2698746911
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 1 |00:00:00.01 | 134 |
| 1 | SORT AGGREGATE | | 1 | 1 | 19 | | | 1 |00:00:00.01 | 134 |
| 2 | FIRST ROW | | 1 | 1 | 19 | 7 (0)| 00:00:01 | 1 |00:00:00.01 | 134 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T1_OWNER_CREATED_OBJECT_D | 1 | 1 | 19 | 7 (0)| 00:00:01 | 1 |00:00:00.01 | 134 |
------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."OWNER"='SYS')
filter(SYS_OP_UNDESCEND("T1"."SYS_NC00016$") IS NOT NULL)
--//邏輯讀也不少，效果並不好。
--//總之出現極端的情況效率就很差。修改建立索引順序呢？
create index i_t1_owner_object_d_created on t1(owner,object_type desc,created);
Plan hash value: 1529359973
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 14 (100)| | 1 |00:00:00.01 | 130 |
| 1 | SORT AGGREGATE | | 1 | 1 | 19 | | | 1 |00:00:00.01 | 130 |
| 2 | FIRST ROW | | 1 | 1 | 19 | 14 (0)| 00:00:01 | 1 |00:00:00.01 | 130 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| I_T1_OWNER_CREATED_OBJECT_TYPE | 1 | 1 | 19 | 14 (0)| 00:00:01 | 1 |00:00:00.01 | 130 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
4.看看建立函式索引的情況，原始連結就是使用它。
create index if_t1_owner_created_object_t on t1(
CASE WHEN OBJECT_TYPE IS NOT NULL THEN owner END,
created
);
--//注我建立的與原始連結不同。
SCOTT@book> SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE) from T1 where (CASE WHEN OBJECT_TYPE IS NOT NULL THEN owner END) = 'SYSTEM';
NVL(MAX(T1.CREATED)
-------------------
2017-01-18 15:21:30
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0ppn8w8p7rwfu, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ NVL (MAX (T1.CREATED), SYSDATE)
from T1 where (CASE WHEN OBJECT_TYPE IS NOT NULL THEN owner END) =
'SYSTEM'
Plan hash value: 373883219
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 2 | 8 |
| 1 | SORT AGGREGATE | | 1 | 1 | 25 | | | 1 |00:00:00.01 | 2 | 8 |
| 2 | FIRST ROW | | 1 | 1 | 25 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 8 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| IF_T1_OWNER_CREATED_OBJECT_T | 1 | 1 | 25 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 8 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."SYS_NC00017$"='SYSTEM')
--//這樣的效果更加。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2767149/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210203]max優化的困惑.txt優化
- [20210408]使用linux find注意.txtLinux
- Oracle Max()/Min()類的效能優化Oracle優化
- SEO優化-robots.txt解讀優化
- mysql：max_connection的設定及優化MySql優化
- 【優化】INDEX FULL SCAN (MIN/MAX)訪問路徑優化Index
- [20170328]使用with優化1例.txt優化
- [20170601]distinct的優化.txt優化
- [20170104]一條sql優化.txtSQL優化
- 如何針對SEO優化您的WordPress Robots.txt優化
- [20190624]12c group by優化 .txt優化
- [20220428]優化的困惑12.txt優化
- [20201224]sql優化困惑.txtSQL優化
- [20131204]sql語句優化.txtSQL優化
- [20151221]sql語句優化.txtSQL優化
- [20200401]優化的困惑5.txt優化
- [20200408]優化的困惑6.txt優化
- [20220507]優化的困惑13.txt優化
- [20181220]使用提示OR_EXPAND優化.txt優化
- [20201210]sql語句優化.txtSQL優化
- [20140301]直方圖與優化.txt直方圖優化
- [20150611]優化sql遇到問題.txt優化SQL
- 技術分享 | 調整 max-write-buffer-size 優化 pika 效能10倍的案例優化
- [20200808]優化的困惑10.txt優化
- [20181119]使用sql profile優化問題.txtSQL優化
- [20210111]優化模式optimizer_mode.txt優化模式
- [20211210]優化遇到的奇怪問題.txt優化
- [20131221]12c 優化 bug.txt優化
- [20120830]11G SPM的學習6.txt--第3方優化.txt優化
- [20190524]使用use_concat or_expand提示優化.txt優化
- [20181116]18c DML 日誌優化.txt優化
- [20181114]一條sql語句的優化.txtSQL優化
- [20131025]一條sql語句的優化.txtSQL優化
- [20151203]一條sql語句的優化.txtSQL優化
- [20150715]一條sql語句的優化.txtSQL優化
- [20120319]一條sql語句的優化.txtSQL優化
- [20130319]一條sql語句的優化.txtSQL優化
- Oracle Max()/Min()類的效能最佳化Oracle