ITPub博客

首页 > 数据库 > Oracle > ORACLE从零开始系列之SQL(二)

ORACLE从零开始系列之SQL(二)

原创 Oracle 作者:seansql 时间:2014-03-11 17:49:08 0 删除 编辑

本小节知识点

  • 逃离符的使用
  • 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


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

请登录后发表评论 登录
全部评论

注册时间:2012-07-12

  • 博文量
    4
  • 访问量
    48886