[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2767149/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
