[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')
--//這樣的效果更加。