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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31403979/viewspace-2126866/
77
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
