开发者社区> lfreeali> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

[20150715]一条sql语句的优化.txt

简介: [20150715]一条sql语句的优化.txt --生产系统发现一条语句。 update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') --第1眼看到的感觉真的很想骂人,什么能没有where条件呢? --我把这个表拷贝过来。
+关注继续查看

[20150715]一条sql语句的优化.txt

--生产系统发现一条语句。
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')

--第1眼看到的感觉真的很想骂人,什么能没有where条件呢?
--我把这个表拷贝过来。这个表占用1G多1点,在测试环境执行看看:

-- copy from system/xxxx@ip:1521/tyt create presc_check using select * from dmd.presc_check;

SCOTT@test> set autot traceonly
SCOTT@test> update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
4164454 rows updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 1940686096

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |             |  3670K|  7008M| 34762   (1)| 00:00:01 |
|   1 |  UPDATE            | PRESC_CHECK |       |       |            |          |
|   2 |   TABLE ACCESS FULL| PRESC_CHECK |  3670K|  7008M| 34762   (1)| 00:00:01 |
----------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
       1104  recursive calls
    4301528  db block gets
     257057  consistent gets
     127892  physical reads
1278411184  redo size
        849  bytes sent via SQL*Net to client
        829  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    4164454  rows processed
--看看redo size=1278411184/1024/1024/1024=1.1906131953001223388 1.2G.

--删除表重新拷贝看看。

SCOTT@test> select count(*) from presc_check  where  diagnosisreplace(diagnosis,',慢性病drugs','') ;
  COUNT(*)
----------
         0

--可以发现没有1条是满足diagnosisreplace(diagnosis,',慢性病drugs',''),全部都是相等的。也许是我导出的时间,因为这个作业
--是晚上3:40分执行的。但是至少说明满足diagnosisreplace(diagnosis,',慢性病drugs','')条件的很少。

--开发至少应该写成这样:
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where t.diagnosisreplace(t.diagnosis,',慢性病drugs','');

--但是这样依旧无法避开全表扫描。必须建立一个函数索引减少扫描的记录。利用instr函数就可以解决这个问题。
create index if_presc_check_diag on presc_check( instr(diagnosis,',慢性病drugs',1) );

SCOTT@test> select instr(diagnosis,',慢性病drugs',1) from presc_check where rownumINSTR(DIAGNOSIS,',慢性病DRUGS',1)
---------------------------------
                                0
                                0
                                0

--正常instr(diagnosis,',慢性病drugs',1)都是0,这样索引会包含大量0的数据,而这些是不需要的,仅仅需要保留大于0的信息,利用
--索引不保留null的特性。应该建立如下索引:

create index if_presc_check_diag on presc_check( decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1));
--这样就可以保留diagnosisreplace(diagnosis,',慢性病drugs','')不等的记录。而且索引很小。

--最终修改语句改为:
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)=1;

--测试:
SCOTT@test> update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)=1;
0 rows updated.


SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  0r2b2dphxpapc, child number 0
-------------------------------------
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)=1
Plan hash value: 1940686096
-----------------------------------------------------------------------------------
| Id  | Operation          | Name        | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |             |        |       | 34981 (100)|          |
|   1 |  UPDATE            | PRESC_CHECK |        |       |            |          |
|*  2 |   TABLE ACCESS FULL| PRESC_CHECK |  33816 |    64M| 34981   (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - UPD$1
   2 - UPD$1 / T@UPD$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_NUMBER(DECODE(INSTR("DIAGNOSIS",',慢性病drugs',1),0,NULL,'1'))=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
  
--奇怪还是没用索引。哦忘记分析表了,分析看看。

exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'presc_check',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
--问题依旧。奇怪了。

--仔细看过滤条件:
   2 - filter(TO_NUMBER(DECODE(INSTR("DIAGNOSIS",',慢性病drugs',1),0,NULL,'1'))=1)

--什么会这样。

SCOTT@test> create index if_presc_check_diag on presc_check( to_number(decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)));
Index created.


SCOTT@test> update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)=1;
0 rows updated.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0r2b2dphxpapc, child number 0
-------------------------------------
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)=1
Plan hash value: 4008913632
-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                     |        |       |     1 (100)|          |
|   1 |  UPDATE                      | PRESC_CHECK         |        |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| PRESC_CHECK         |  41645 |  1260K|     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IF_PRESC_CHECK_DIAG |      1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - UPD$1
   2 - UPD$1 / T@UPD$1
   3 - UPD$1 / T@UPD$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."SYS_NC00070$"=1)

--实际上也可以建立这样的索引:

SCOTT@test> drop index  if_presc_check_diag ;
Index dropped.

SCOTT@test> create index if_presc_check_diag on presc_check( decode(instr(diagnosis,',慢性病drugs',1),0,NULL,'1'));
Index created.

SCOTT@test> update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,'1')='1';
0 rows updated.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  22ujr6zwy4asn, child number 0
-------------------------------------
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,'1')='1'

Plan hash value: 4008913632

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                     |        |       |     1 (100)|          |
|   1 |  UPDATE                      | PRESC_CHECK         |        |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| PRESC_CHECK         |  41645 |   813K|     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IF_PRESC_CHECK_DIAG |      1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - UPD$1
   2 - UPD$1 / T@UPD$1
   3 - UPD$1 / T@UPD$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."SYS_NC00070$"='1')

--看来以后要注意这种情况的隐式转换。实际上问题出在decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)里面的NULL,oracle
--缺省认为是字符串类型null,而不是数字类型的null,改成如下:

create index if_presc_check_diagx on presc_check( decode(instr(diagnosis,',慢性病drugs',1),0,cast(NULL as number),1));

SCOTT@test> update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where decode(instr(diagnosis,',慢性病drugs',1),0,cast(NULL as number),1)=1;
0 rows updated.

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  f5b0rzmyd2uzh, child number 0
-------------------------------------
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
where decode(instr(diagnosis,',慢性病drugs',1),0,cast(NULL as number),1)=1
Plan hash value: 2418858926
------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                      |        |       |     1 (100)|          |
|   1 |  UPDATE                      | PRESC_CHECK          |        |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| PRESC_CHECK          |  41645 |  1260K|     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IF_PRESC_CHECK_DIAGX |      1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - UPD$1
   2 - UPD$1 / T@UPD$1
   3 - UPD$1 / T@UPD$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."SYS_NC00071$"=1)

--当然应该选择这个索引更佳:
create index if_presc_check_diag on presc_check( decode(instr(diagnosis,',慢性病drugs',1),0,NULL,'1'));

--应该字符类型'1'仅仅占用1个字节,而数字1占用两个字节,并且从写法讲这样写decode(instr(diagnosis,',慢性病drugs',1),0,cast(NULL as number),1)=1不是很好。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql常用sql语句
本文主要介绍基本的 SQL 语句,数据的增删查改等。
75 0
MySQL系列专题(2)-MySQL的SQL语句和高级特性
MySQL系列专题(2)-MySQL的SQL语句和高级特性
93 0
MySQL关于SQL语句的一些题目
MySQL关于SQL语句的一些题目 1、用一条SQL语句查询出每门课都大于80的学生的姓名 (1)SELECT NAME FROM score GROUP BY NAME HAVING MIN(grade) > 80 (2)SELECT DISTINCT NAME FROM score WH.
1537 0
MySQL特有的SQL语句 第一弹
关于SQL,我们总是会有无穷无尽相关的话题,有时候碰到了一些觉得不错的SQL功能会标记下来,好记性不烂烂笔头,回头来看,自己也收集了不少的点子,但是从整体来看,可能仅仅是一瓢水而已。
1195 0
***mysql中查询今天、昨天、上个月sql语句
今天 select * from 表名 where to_days(时间字段名) = to_days(now()); 昨天Select * FROM 表名 Where TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名)
1057 0
+关注
lfreeali
熟悉oracle数据库性能优化,rman备份,数据库恢复技术.
文章
问答
文章排行榜
最热
最新
相关电子书
更多
MaxCompute SQL计算成本调优以及优化方法
立即下载
SQL Server 2017
立即下载
SQL Sever迁移PG经验
立即下载