[20210408]max优化.txt

来源：这里教程网时间：2026-03-03 16:37:13 作者：

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