ORACLE从零开始系列之SQL（二）

最新推荐文章于 2024-10-17 18:58:27 发布

citdjv4879

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量103

点赞数

文章标签：数据库

本小节知识点

逃离符的使用
like与索引
逻辑运算符的优先级

逃离符的使用

我们在编写SQL语句时经常会有这么一种需求，例如：查询员工表中工作名称是以HR后跟一个下划线开头的记录（HR_）,但是下划线（_）,在like中是oracle定义的特殊字符，表示一个单独的字符，那么在这种情况下我们就可以利用到逃离符。
示例如下：
SQL> select distinct(job_id) from employees;

JOB_ID
----------
AC_ACCOUNT
AC_MGR
AD_ASST
AD_PRES
AD_VP
FI_ACCOUNT
FI_MGR
HR_REP
IT_PROG
MK_MAN
MK_REP
PR_REP
PU_CLERK
PU_MAN
SA_MAN
SA_REP
SH_CLERK
ST_CLERK
ST_MAN

19 rows selected.
SQL> select job_id from employees where job_id like '%ST\_%'; --此条SQL表示查询JOB_ID字段中以ST\开头的记录，但是我们的表中没有这样的记录，故无返回结果。

no rows selected

SQL> select job_id from employees where job_id like '%ST\_%' escape '\'; --此条sql表示查询JOB_ID字段中以ST_开头的记录，其中escape声明在like条件中的逃离符具体是哪个，这里为\；也就是说like条件中的\不再是“\”而是逃离符，它使哪个符号逃离？它使like '%ST\_%中的_不再是oracle中定义的特殊符号，而就是_本身。从结果中我们也可以观察到，结果都是以ST_开头的记录。

JOB_ID
----------
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_MAN
ST_MAN
ST_MAN
ST_MAN
ST_MAN

LIKE与索引

    写过SQL代码的人都知道在写sql时，like子句一定要写成前导索引的形式（like ’a%‘），而不要写成非前导索引（ike '%a'）的形式，这是因为前导索引可以用到索引，非前导用不到索引，所以效率较差。那么有没有一种办法能够使非前导也能用到索引呢，答案是肯定的，我们来看下面的实验。

SQL> Create table emp as select * from employees;

Table created.

SQL> Create index ind on emp(email);

Index created.
SQL> exec dbms_stats.gather_table_stats(user,'EMP');

PL/SQL procedure successfully completed.
SQL> set autotrace on
SQL> Select * from emp where email like 'O%';

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        155 Oliver               Tuvault                   OTUVAULT                  011.44.1344.486508   23-NOV-99 SA_REP           7000            .15        145            80

Execution Plan
----------------------------------------------------------
Plan hash value: 257479917

------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     6 |   414 |     3   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| EMP |     6 |   414 |     3   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IND |     6 |       |     1   (0)| 00:00:01 |   --在前导模式下，执行计划走索引
------------------------------------------------------------------------------------

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

   2 - access("EMAIL" LIKE 'O%')
       filter("EMAIL" LIKE 'O%')

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          3 consistent gets
          0 physical reads
          0 redo size
       1319 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)
          1 rows processed

SQL> Select * from emp where email like '%O';

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        115 Alexander            Khoo                      AKHOO                     515.127.4562         18-MAY-95 PU_CLERK         3100                       114            30
        118 Guy                  Himuro                    GHIMURO                   515.127.4565         15-NOV-98 PU_CLERK         2600                       114            30
        130 Mozhe                Atkinson                  MATKINSO                  650.124.6234         30-OCT-97 ST_CLERK         2800                       121            50
        139 John                 Seo                       JSEO                      650.121.2019         12-FEB-98 ST_CLERK         2700                       123            50
        187 Anthony              Cabrio                    ACABRIO                   650.509.4876         07-FEB-99 SH_CLERK         3000                       121            50

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

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     5 |   345 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP |     5 |   345 |     3   (0)| 00:00:01 | --非前导模式下，执行计划走了全表扫描
--------------------------------------------------------------------------

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

   1 - filter("EMAIL" LIKE '%O')

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
       1617 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)
          5 rows processed
    我们再建立一个索引，然后换一个前导模式的写法
SQL> set autot off
SQL> create index index_job on emp (reverse(JOB_ID));

Index created.

SQL> set autot traceonly

SQL> select * from emp where reverse(job_id) like 'O%'; 此句SQL等价于select * from emp where job_id like '%0';

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2285283441

-----------------------------------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     5 |   345 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| EMP       |     5 |   345 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | INDEX_JOB |     1 |       |     1   (0)| 00:00:01 |   这里我们的利用了reverse函数索引（注意不是reverse索引）并巧妙的改写了SQL 语句成功的让直行计划走了索引。
-----------------------------------------------------------------------------------------

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

   2 - access(REVERSE("JOB_ID") LIKE 'O%')
       filter(REVERSE("JOB_ID") LIKE 'O%')

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

注：这里用的是reverse函数索引而不是reverse索引，这两者是不同的。

逻辑运算符的优先级

1 算术运算符
2 连接运算符
3 比较条件
4 IS [NOT] NULL LIKE [NOT] IN
5 [NOT] BETWEEN
6 NOT 逻辑条件
7 AND逻辑条件
8 OR逻辑条件
这8中运算符从的优先级从高到低按照上图进行排序，我们举个例子

这条SQL语句表示先选出JOB_ID为AD_PRES和工资大于15000的记录然后再选出JOB_ID为SA_REP的记录。
这是因为AND的优先级大于OR，或者我们也可以这样写：

这条SQL语句表示先选出JOB_ID为SA_REP和AD_PRES的记录再选出工资大于15000的记录，这是因为虽然AND的优先级大于
OR，但是因为有“（）”的存在，所以“（）”内的条件优先被筛选。
good luck
sean
qq 2087425608
2014.3.11