ITPub博客

首页 > 数据库 > Oracle > [20210408]max优化.txt

[20210408]max优化.txt

原创 Oracle 作者:lfree 时间:2021-04-08 16:52:34 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2891
  • 访问量
    6669819