ITPub博客

首页 > 数据库 > Oracle > 浅谈where

浅谈where

原创 Oracle 作者:18021073936 时间:2016-10-21 13:56:15 0 删除 编辑

1、<=
select EMPNO,sal from scott.emp where sal <=3000;
SQL> select EMPNO,sal from scott.emp where sal <=3000;

     EMPNO      SAL
---------- ----------
      7369      800
      7499     1600
      7521     1250
      7566     2975
      7654     1250
      7698     2850
      7782     2450
      7788     3000
      7844     1500
      7876     1100
      7900      950
      7902     3000
      7934     1300
注意范围查找容易产生CBO计算失误,因此要注意范围查找的SQL语句写法。
<> != ^=都是不等于的意思,CBO我们在优化部分会重点讲解

2、between and 等价于>= and <=
select EMPNO,sal from scott.emp where sal between  2500 and 3500;(包括薪水为2500和3500的员工)
SQL> select EMPNO,sal from scott.emp where sal between  2500 and 3500;

     EMPNO      SAL
---------- ----------
      7566     2975
      7698     2850
      7788     3000
      7902     3000


3、in
select * from scott.emp where empno in (7369,7499);
SQL> select * from scott.emp where empno in (7369,7499);

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30

经常有人会讨论in和exist的效率
理解他使用的成本需要非常多的其他知识
IN在ORACLE中默认会将in转换成or,它们就好比是同义词,这是在提交到优化器前发生的,叫查询转换,上例in会发生or查询转换
IN和OR操作有如下三种不同执行方式路径。
IN-List Iterators(迭代) : 发生该操作的条件是in里是值,当IN操作OR转换后,CBO根据是否在IN的字段上有索引来决定是否实现。
                          IN-List Iterators的意思是针对IN列表或OR条件中每个值,它下面的步骤都会执行一次,如果IN的值有n个,
                          in字段没有索引,就意味着要发生n次全表扫描。
                          不允许超过1000 超过了 就要改写成exists 或者其他方案了。
OR扩展(IN-LIST): 转化成CONCATENATION或UNION ALL,不适合长的in-list,否则CBO会在判断每个分支时消耗大量资源
   例如:SELECT * FROM T WHERE ID IN (1,2,3) OR CITY IN ('SHANGHAI','BEIJIN');
   OR(IN-LIST)扩展转换为:SELECT * FROM T WHERE ID IN (1,2,3) UNION ALL SELECT * FROM T WHERE CITY IN ('SHANGHAI','BEIJIN');
   
   select * from scott.emp where empno in (7566,7698,7788) 等价于
   select * from scott.emp where empno=7566
   union all
   select * from scott.emp where empno=7698
   union all
   select * from scott.emp where empno=7788;
备注:union all和union还是有区别的,执行效率也不一样。其中union all 并不会排序和过滤。
ID IN (1,2,3,4........,500,.......1000)   ID IN了这么多值对于CPU分析是非常难的。

4、like 相似匹配(%号代替0个或多个字符)
SQL> select * from scott.emp where ename like 'SMI%';

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20

Elapsed: 00:00:00.01

SQL> select * from scott.emp where ename like 'S%';

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20

LIKE在CBO中的注意事项:
索引的使用
打开执行计划:
set autotrace on
select ename from scott.emp where ename like 'S%';
结果为:
SQL> select ename from scott.emp where ename like 'S%';

ENAME
----------
SMITH
SCOTT

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     2 |    12 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    12 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("ENAME" LIKE 'S%')


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

这里因为ename上面没有建索引;这里全表扫描(TABLE ACCESS FULL)。
在测试一个例子;
create index idx_emp_ename on scott.emp(ename);
select ename from scott.emp where ename like 'S%';
结果如下:
SQL> select ename from scott.emp where ename like 'S%';

ENAME
----------
SCOTT
SMITH

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4030948918

----------------------------------------------------------------------------------
| Id  | Operation     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     2 |    12 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_EMP_ENAME |     2 |    12 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - access("ENAME" LIKE 'S%')
       filter("ENAME" LIKE 'S%')


Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      2  consistent gets
      0  physical reads
      0  redo size
    583  bytes sent via SQL*Net to client
    524  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      2  rows processed
创建索引后,这个时候会走索引范围扫描(INDEX RANGE SCAN)。      
5、is null 不能去等于空,只能用IS [NOT] NULL,因此不能使用=来测试,它不同于0或者空格,说null=null 这种是不成立的
    
SQL> select * from scott.emp where COMM is null;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10
备注:NULL在成本运算时容易算错,大家要注意
select * from qq where city not in (select * from test where a.name is not null);
或者
where not exists (select 1 from test where a.name=b.name)
      
10 rows selected.


6、order by 可以用来对行排序
我们知道数据的插入的过程是先扫描是否有空闲块,然后再插入到空闲块.
ASC  默认是升序, DESC 降序(ORDER BY后的列尽量有索引)
注意 空值排在最后一个 (默认升序)
     空值排在最早一个 DESC
select * from scott.emp  order by COMM asc;
select * from scott.emp  order by COMM desc;  你可以自己测试一下!!!!
备注:nls_sort=binary  ==>大小写敏感
      nls_sort=binary_ci  ==>大小写不敏感
      alter session set nls_sort=binary;
      show parameter nls_sort;
select * from scott.emp order by 1;
SQL> select * from scott.emp order by 1;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK          7902 17-DEC-80        800            20
      7499 ALLEN      SALESMAN          7698 20-FEB-81       1600        300       30
      7521 WARD       SALESMAN          7698 22-FEB-81       1250        500       30
      7566 JONES      MANAGER          7839 02-APR-81       2975            20
      7654 MARTIN     SALESMAN          7698 28-SEP-81       1250       1400       30
      7698 BLAKE      MANAGER          7839 01-MAY-81       2850            30
      7782 CLARK      MANAGER          7839 09-JUN-81       2450            10
      7788 SCOTT      ANALYST          7566 19-APR-87       3000            20
      7839 KING       PRESIDENT        17-NOV-81       5000            10
      7844 TURNER     SALESMAN          7698 08-SEP-81       1500      0       30
      7876 ADAMS      CLERK          7788 23-MAY-87       1100            20
      7900 JAMES      CLERK          7698 03-DEC-81        950            30
      7902 FORD       ANALYST          7566 03-DEC-81       3000            20
      7934 MILLER     CLERK          7782 23-JAN-82       1300            10

14 rows selected.
order by 1  1代表的是你select list 的第一列.

最后做一个实验,请看下面
(1)IN-List Iterators(迭代)实验
create table scott.inlist_test(a number ,b number);
insert into scott.inlist_test select rownum,1 from all_objects;
commit;
create index scott.inlist_idx on scott.inlist_test(a);
set autot traceonly    用于查看执行计划
select * from scott.inlist_test where a in (3,5,9);

SQL> select * from scott.inlist_test where a in (3,5,9);

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2853064238

--------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time       |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     3 |    78 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR         |           |       |       |        |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| INLIST_TEST |     3 |    78 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN         | INLIST_IDX  |   255 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   3 - access("A"=3 OR "A"=5 OR "A"=9)

Note
-----
   - dynamic sampling used for this statement (level=2)


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

看到了没有INLIST ITERATOR;
当IN操作OR转换后,CBO根据是否在IN的字段上有索引来决定是否实现IN-List Iterators迭代

(2)OR扩展(IN-LIST)
create table scott.or_test (a int,b int);
insert into scott.or_test select rownum,rownum from all_objects;
commit;
create index scott.a_idx on scott.or_test(a);
create index scott.b_idx on scott.or_test(b);

select * from scott.or_test where a in (1,2,3) or b in (100,101,102);
SQL> select * from scott.or_test where a in (1,2,3) or b in (100,101,102);

6 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1901925120

-----------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |     |   690 | 17940 |     2   (0)| 00:00:01 |
|   1 |  CONCATENATION              |     |    |    |         |        |
|   2 |   INLIST ITERATOR          |     |    |    |         |        |
|   3 |    TABLE ACCESS BY INDEX ROWID| OR_TEST |     3 |    78 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | B_IDX    |     1 |    |     4   (0)| 00:00:01 |
|   5 |   INLIST ITERATOR          |     |    |    |         |        |
|*  6 |    TABLE ACCESS BY INDEX ROWID| OR_TEST |   687 | 17862 |     1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN          | A_IDX    |     1 |    |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   4 - access("B"=100 OR "B"=101 OR "B"=102)
   6 - filter(LNNVL("B"=100) AND LNNVL("B"=101) AND LNNVL("B"=102))
   7 - access("A"=1 OR "A"=2 OR "A"=3)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
     40  recursive calls
      0  db block gets
    232  consistent gets
      2  physical reads
      0  redo size
    690  bytes sent via SQL*Net to client
    524  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      6  rows processed
这里比较难,性能优化部分在讲,这里主要讲一下sql基础语句。
      
    

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31403979/viewspace-2126866/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 浅谈单行函数
请登录后发表评论 登录
全部评论

注册时间:2016-10-20

  • 博文量
    4
  • 访问量
    3568