安科网

Oracle SQL过滤条件是IS NULL or !=的优化

windsky

windsky

2016-09-06

关注 关注

通常情况下is null或者!=这些条件如果不是具有很强的过滤性，可以先关注其它的过滤条件。但有些SQL这两种条件具有很强的过滤性，就可以考虑用以下方法。下面先讨论is null的优化，再讨论!=的优化，最后讨论is null or !=一起使用的优化。

以下测试：
Oracle version:11.2.0.4

#新建测试表
create table scott.tb_sj01 as select * from dba_objects;

#处理测试表中的数据
update scott.tb_sj01 set object_name=null where object_id<=10;

update scott.tb_sj01 set object_name='SJ' where object_id>20;

commit;

#收集表的统计信息
begin
  dbms_stats.gather_table_stats('scott','TB_SJ01');
end;

#查看测试表中object_name字段的数据分布
#可以看出object_name字段IS NULL的有9笔记录;object_name !='SJ'有11笔记录。
select nvl(object_name,'NULL') object_name,count(1) cnt
from scott.tb_sj01
group by nvl(object_name,'NULL')
order by 2 ;
/*
OBJECT_NAME            CNT
ICOL$                  1
TS$                    1
OBJ$                  1
FILE$                  1
STDBY_LINK_CT6601SB    1
UNDO$                  1
FET$                  1
I_USER#                1
UET$                  1
SEG$                  1
IND$                  1
NULL                  9
SJ                    86880
*/

1.语句：select * from scott.tb_sj01 where object_name is null的优化
SQL> set autot trace
#优化前,COST=346,consistent gets=1246
SQL> select * from scott.tb_sj01 where object_name is null;

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 283620643

-----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes| Cost (%CPU)| Time    |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |        |    9 |  684 |  346  (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TB_SJ01 |    9 |  684 |  346  (1)| 00:00:05 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("OBJECT_NAME" IS NULL)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      1246  consistent gets
          0  physical reads
          0  redo size
      1850  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed
         
#优化方法1:
#增加索引，让object_name IS NULL的也保存在索引中
SQL> create index scott.idx_tb_sj01_01 on scott.tb_sj01(object_name,1);

#优化后，COST=3,consistent gets=5
SQL> select * from scott.tb_sj01 where object_name is null;

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1042936765

----------------------------------------------------------------------------------------------

| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time    |

----------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |                |    9 |  684 |    3  (0)| 00:00:01 |

|  1 |  TABLE ACCESS BY INDEX ROWID| TB_SJ01        |    9 |  684 |    3  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IDX_TB_SJ01_01 |    9 |      |    2  (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OBJECT_NAME" IS NULL)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
      1850  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

#优化方法2:
#增加函数索引，只让object_name IS NULL的保存在索引中
create index scott.idx_tb_sj01_02 on scott.tb_sj01(decode(object_name,null,1));

#原语句改写为：
select * from scott.tb_sj01 where decode(object_name,null,1)=1;

#优化后，COST=2，consistent gets=4
SQL> select * from scott.tb_sj01 where decode(object_name,null,1)=1;

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 612345449

----------------------------------------------------------------------------------------------

| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time    |

----------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |                |  869 | 66044 |    2  (0)| 00:00:01 |

|  1 |  TABLE ACCESS BY INDEX ROWID| TB_SJ01        |  869 | 66044 |    2  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IDX_TB_SJ01_02 |    9 |      |    1  (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access(DECODE("OBJECT_NAME",NULL,1)=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
      1850  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

2.语句:select * from scott.tb_sj01 where object_name!='SJ'的优化

#优化前,COST=346，consistent gets=1246
SQL> select * from scott.tb_sj01 where object_name!='SJ';

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 283620643

-----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |        | 79650 |  5911K|  346  (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TB_SJ01 | 79650 |  5911K|  346  (1)| 00:00:05 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("OBJECT_NAME"<>'SJ')

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      1246  consistent gets
          0  physical reads
          0  redo size
      1979  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        11  rows processed

#优化方法1：
#增加函数索引，将object_name!='SJ'和object_name IS NULL的保存在索引中
create index scott.idx_tb_sj01_04 on scott.tb_sj01(decode(object_name,null,1,'SJ',null,2));
drop index scott.idx_tb_sj01_04;

#原语句改写为：
select * from scott.tb_sj01  t where decode(object_name,null,1,'SJ',null,2)=2;

#优化后，COST=4，consistent gets=4
SQL> select * from scott.tb_sj01  t where decode(object_name,null,1,'SJ',null,2)=2;

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3453712045

----------------------------------------------------------------------------------------------

| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time    |

----------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |                |  869 | 71258 |    4  (0)| 00:00:01 |

|  1 |  TABLE ACCESS BY INDEX ROWID| TB_SJ01        |  869 | 71258 |    4  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IDX_TB_SJ01_04 |    20 |      |    1  (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access(DECODE("OBJECT_NAME",NULL,1,'SJ',NULL,2)=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
      1938  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        11  rows processed

#优化方法2：
#增加函数索引，只让object_name!='SJ'的保存在索引中
create index scott.idx_tb_sj01_05 on scott.tb_sj01(case when object_name!='SJ' then 1 else null end);

#原语句改写为：
select * from scott.tb_sj01  t where (case when object_name!='SJ' then 1 else null end)=1;

#优化后，COST=3，consistent gets=4
SQL> select * from scott.tb_sj01  t where (case when object_name!='SJ' then 1 else null end)=1;

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 376302892

----------------------------------------------------------------------------------------------

| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CPU)| Time    |

----------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |                |  869 | 71258 |    3  (0)| 00:00:01 |

|  1 |  TABLE ACCESS BY INDEX ROWID| TB_SJ01        |  869 | 71258 |    3  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IDX_TB_SJ01_05 |    11 |      |    1  (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access(CASE  WHEN "OBJECT_NAME"<>'SJ' THEN 1 ELSE NULL END =1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
      1938  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        11  rows processed


3.语句：select * from scott.tb_sj01 where object_name is null or  object_name !='SJ'的优化

#优化前,COST=346，consistent gets=1247
SQL> select * from scott.tb_sj01 where object_name is null or  object_name !='SJ';

20 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 283620643

-----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |        |    40 |  3280 |  346  (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TB_SJ01 |    40 |  3280 |  346  (1)| 00:00:05 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("OBJECT_NAME"<>'SJ' OR "OBJECT_NAME" IS NULL)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      1247  consistent gets
          0  physical reads
          0  redo size
      2403  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        20  rows processed


#优化方法1：
#增加索引，将object_name!='SJ'和object_name IS NULL的保存在索引中
create index scott.idx_tb_sj01_06 on scott.tb_sj01(decode(object_name,null,1,'SJ',null,1));

#语句修改为：
select * from scott.tb_sj01 t where decode(object_name,null,1,'SJ',null,1)=1;

#优化后,COST=3，consistent gets=6
SQL> select * from scott.tb_sj01 where decode(object_name,null,1,'SJ',null,1)=1;

20 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 356892721

----------------------------------------------------------------------------------------------

| Id  | Operation                  | Name          | Rows  | Bytes | Cost (%CP
U)| Time    |

----------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |                |  869 | 71258 |    3  (0)| 00:00:01 |

|  1 |  TABLE ACCESS BY INDEX ROWID| TB_SJ01        |  869 | 71258 |    3  (0)| 00:00:01 |

|*  2 |  INDEX RANGE SCAN          | IDX_TB_SJ01_06 |    20 |      |    1  (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access(DECODE("OBJECT_NAME",NULL,1,'SJ',NULL,1)=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
      2362  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        20  rows processed

SQL> set autot off

#优化方法2，增加case when的函数索引，改写语句，同语句2，略

sql优化 优化 oracle scott

windsky

windsky

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

MySQL数据类型优化原则

MySQL支持的数据类型很多，选择正确的数据类型对于高性能至关重要。下面几个简单的原则都有助于做出更好的选择。应该尽量使用可以正确储存数据的最小数据类型。如果无法确定哪个数据类型时最好的，就选择你认为不会超过范围的最小类型。比如用MySQ内建的类型而不是使

ribavnu 2020-11-16

MySQL中使用binlog时格式该如何选择

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然

要啥自行车一把梭 2020-11-12

详解 MySQL中count函数的正确使用方法

当搞清楚count函数的运行原理后，相信上面几个问题的答案就会了然于胸。为了解决上述的问题，我创建了一张 user 表，它有两个字段：主键id和name，后者可以为null，建表语句如下。`id` int NOT NULL AUTO_INCREMENT C

vitasfly 2020-11-12

sql优化实战 把full join改为left join +union all(从5分钟降为10秒)

今天收到一个需求，要改写一个报表的逻辑，当改完之后，再次运行，发现运行超时。因为特殊原因，无法访问客户的服务器，没办法查看sql的执行计划、没办法知道表中的索引情况，所以，尝试从语句的改写上来优化。但是考虑到第2段代码中并没有prod_id这个字段，所以这

ILVNMM 2020-09-19

SQL Server 开窗函数 Over()代替游标的使用详解

今天在优化工作中遇到的sql慢的问题，发现以前用了挺多游标来处理数据，这样就导致在数据量多的情况下，需要一行一行去遍历从而计算需要的数据，这样处理的结果就是数据慢，容易卡死。第一行表示标题；第二行表示客户，是一行空行；第三行是期初余额，只显示期末余额的数据

zycchun 2020-10-16

Mysql临时表及分区表区别详解

内存表，指的是使用Memory引擎的表，建表语法是create table …这种 表的数据都保存在内存，系统重启的时候会被清空，但是表结构还在。除这两个特性看 上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。如果是使用InnoDB引擎或者MyI

康慧欣 2020-09-10

导致MySQL索引失效的一些常见写法总结

最近一直忙着处理原来老项目遗留的一些SQL优化问题，由于当初表的设计以及字段设计的问题，随着业务的增长，出现了大量的慢SQL，导致MySQL的CPU资源飙升，基于此，给大家简单分享下这些比较使用的易于学习和使用的经验。这次的话简单说下如何防止你的索引失效。

liuweiq 2020-09-09

MySQL索引的各种类型

索引是数据库存储引擎用于快速查找到指定数据的一种数据结构。可以用新华字典做类比：如果新华字典中对每个字的详细解释是数据库中表的记录，那么按部首或拼音等排序的目录就是索引，使用它可以让我们快速查找的某一个字详细解释的位置。在MySQL中，存储引擎也是用了类似

silencehgt 2020-09-07

MySQL 数据库如何解决高并发问题

我们都知道初创公司一开始都是以单体应用为首要架构，一般都是单体单库的形式。但是版本以及版本的迭代，数据库需要承受更多的高并发已经成了 架构设计 需要考虑的点。高并发的情况下，可以将多个请求的查询合在一次进行，以减少对数据库的访问速度。我们都知道，只要数据库

sunnyxuebuhui 2020-09-07

MySQL 千万级数据量如何快速分页

后端开发中为了防止一次性加载太多数据导致内存、磁盘IO都开销过大，经常需要分页展示，这个时候就需要用到MySQL的LIMIT关键字。但你以为LIMIT分页就万事大吉了么，Too young,too simple啊，LIMIT在数据量大的时候极可能造成的一个

西瓜皮儿的皮儿 2020-09-07

分区表场景下的 SQL 优化

有个表做了分区，每天一个分区。该表上有个查询，经常只查询表中某一天数据，但每次都几乎要扫描整个分区的所有数据，有什么办法进行优化吗？有一个大表，每天产生的数据量约100万，所以就采用表分区方案，每天一个分区。`tst` timestamp NOT NULL

李轮清 2020-09-15

mysql性能分析篇

数据库查询快慢是影响项目性能的一大因素， 对于数据库， 我们除了要优化SQL， 更重要的是得先找到需要优化的SQL， MYSQL数据库又一个慢查询日志功能， 用来记录查询时间超过某个设定值的SQL语句， 这将极大程序帮助我们快速定位到问题， 以便对症下药。

lifeison 2020-08-18

不得不会的mysql架构， 让你更懂她！

Connection Pool**: 连接池a. 管理缓冲用户连接，线程处理等需要缓存的需求。c. 而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。包括线程的创建，线程的 c

cyhgogogo 2020-08-18

MySQL优化SQL语句的技巧

在面对不够优化、或者性能极差的SQL语句时，我们通常的想法是将重构这个SQL语句，让其查询的结果集和原来保持一样，并且希望SQL性能得以提升。而在重构SQL时，一般都有一定方法技巧可供参考，本文将介绍如何通过这些技巧方法来重构SQL。有时候对于一个复杂SQ

minerk 2020-08-15

MySQL如何优化查询速度

前面章节我们介绍了如何选择优化的数据类型、如何高效的使用索引，这些对于高性能的MySQL来说是必不可少的。但这些还完全不够，还需要合理的设计查询。如果查询写的很糟糕，即使表结构再合理、索引再合适，也是无法实现高性能的。谈到MySQL性能优化，查询优化作为优

webliyang 2020-08-15

MySQL数据库查询性能优化策略

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。MySQL可以为多个字段创建索引,一个索引可以包括16个字段,只有查询条件中使用这些字段中的第一个字段时,索引才会被使用.子查询虽然可以使查询语句更

houdaxiami 2020-08-15

关于mysql自增id，你需要知道的

本篇文章将以问答的形式讲述关于自增id的一切。这就要求同一个叶子节点内的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子，则开辟一个新的页（节点）。自增id是增长的 不一定连续。

抱抱熊 2020-08-15

详解MySQL分区表

分区是一种表的设计模式，通俗地讲表分区是将一大表，根据条件分割成若干个小表。但是对于应用程序来讲，分区的表和没有分区的表是一样的。换句话来讲，分区对于应用是透明的，只是数据库对于数据的重新整理。本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场

caodayong 2020-08-15

Centos7 安装 Mysql8教程

首先，您需要在MySQL提供的系统中启用MySQL yum存储库。根据您的操作系统版本执行以下命令之一，这里我们选择CentOS 7 / RHEL 7的源。因此，首先禁用mysql repo文件中的所有存储库。安装MySQL 8.0后，将为MySQL根用户

徐悦TechBlog 2020-08-15

Mysql中Sleep进程连接数过多问题解决

连接数查看进入MySQL命令行，输入“show processlist;”命令后，Command列出现一大堆Sleep睡眠状态的连接进程。超过配置的最大连接数，新的客户端连接将无法连接，mysql将无法提供服务，Web系统无法访问、崩塌。造成连接数过多原因

hevenue 2020-07-30
windsky

windsky

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号