ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL 查询转换

SQL 查询转换

原创 Linux操作系统 作者:pingley 时间:2012-03-26 13:46:35 0 删除 编辑
SQL 查询转换
oracle 查询优化器是oralce数据库内置的一个软件,用来决定更加有效的执行一个SQL 语句。oracle 查询优化器的组件如下图。
从图中我们可以知道解析以后的SQL语句需要通过三个步骤来生成执行计划。分别是:
1、查询转换。
2、成本估计。
3、生成执行计划。
本文根据有关资料,结合自己的理解,阐述SQL查询转换。SQL查询转换的目的是将原始的SQL 语句在保证结果相同的情况先把原始的SQL语句转换为oracle 认为效率更佳SQL语句。oracle 把每一条查询语句称作一个查询块(query block)。
比如下面的SQL语句,有两个查询块,其中在括号中的子查询称作内部查询块(inner query block),相应的包在外面的是外部查询块(outer query block)。
SQL> select customer#,firstname,lastname
  2  from  customers
  3  where customer# 
  4  in (select customer# from orders);
//上面的查询表述的意思是查询有订货的客户的id号,和名字。
在oracle 中SQL语句的查询转换可以分成4类分别是:
1、视图合并。
2、谓词前置。
3、子查询解嵌套。
4、查询重写物化视图。
视图合并
如果不考虑视图合并,那么查询优化器将会为查询视图的query block 产生一个执行子计划。然后查询优化器根据执行子计划对剩余的query block 进行处理生成一个整体的执行计划。这种方式并不是最优化的。因为整体的执行计划被分为了单独的几个部分。在视图合并的情况下,SQL查询转换器合并对视图的query block 到他的外层query block 中。
使用下面的SQL语句先创建一个视图:
create view computer_book as
select isbn,title,pubdate,cost,retail,discount
from books
where category='COMPUTER';

SQL> alter system set events 'immediate trace name flush_cache';
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace on
SQL> select title,retail
  2  from computer_book
  3  where cost > 10;
TITLE                              RETAIL
------------------------------ ----------
DATABASE IMPLEMENTATION             55.95
HOLY GRAIL OF ORACLE                75.95
HANDCRANKED COMPUTERS                  25
E-BUSINESS THE EASY WAY              54.5
Execution Plan
----------------------------------------------------------
Plan hash value: 2688610195
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     4 |   168 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOOKS |     4 |   168 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
//操作中没有我们原始的SQL中指定的视图 computer_books.因为SQL查询转换器对原始的SQL语句进行了转换:
select title,retail
from books
where category='COMPUTER'
and cost >10;
这样就合并消除了对视图的操作。
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("CATEGORY"='COMPUTER' AND "COST">10)
//这里的过滤条件和我们在SQL语句给给出的不一样。
Statistics
----------------------------------------------------------
        366  recursive calls
          0  db block gets
         70  consistent gets
         22  physical reads
          0  redo size
        627  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          4  rows processed
我们来验证一下上面的语句视图合并以后是否真的是转换为:
select title,retail
from books
where category='COMPUTER'
and cost >10;
SQL> select title,retail
  2  from books
  3  where category='COMPUTER'
  4  and cost >10;
TITLE                              RETAIL
------------------------------ ----------
DATABASE IMPLEMENTATION             55.95
HOLY GRAIL OF ORACLE                75.95
HANDCRANKED COMPUTERS                  25
E-BUSINESS THE EASY WAY              54.5
Execution Plan
----------------------------------------------------------
Plan hash value: 2688610195
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     4 |   168 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOOKS |     4 |   168 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("CATEGORY"='COMPUTER' AND "COST">10)
从上面的输出中我们可以确定以下几点:
一、查询的结果是相同的。
二、执行计划是通过的plan hash value 相等,操作相同,过滤条件相同。
三、至于统计信息,因为生成统计信息的因素很多,所以不相同是正常的。
注意:可合并的视图中不能包括集合操作符,聚集函数,distinct,group by,connect by 等。
谓词前置
SQL 查询优化器会把相关的存在于外查询中的谓词推前(push)到视图查询块中(view query block).对于那些不能进行视图合并的SQL语句,这项技术可以提供子计划(subplan)的效率,通过把谓词前置来达到访问索引或者用作过滤条件的目的。
SQL> create view computer_and_family as
  2  (select isbn,title,pubdate,cost,retail,discount
  3  from books
  4  where category='COMPUTER')
  5  union
  6  (select isbn,title,pubdate,cost,retail,discount
  7  from books
  8  where category='FAMILY LIFE');
View created.
上面的查询是有些蠢,但是为了学习将就着吧。
SQL> select title
  2  from computer_and_family
  3  where cost > 10;
TITLE
------------------------------
REVENGE OF MICKEY
HANDCRANKED COMPUTERS
PAINLESS CHILD-REARING
HOLY GRAIL OF ORACLE
DATABASE IMPLEMENTATION
E-BUSINESS THE EASY WAY
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2041586477
--------------------------------------------------------------------------------------------
| Id  | Operation            | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                     |     6 |   180 |     6  (34)| 00:00:01 |
|   1 |  VIEW                | COMPUTER_AND_FAMILY |     6 |   180 |     6  (34)| 00:00:01 |
|   2 |   SORT UNIQUE        |                     |     6 |   378 |     6  (67)| 00:00:01 |
|   3 |    UNION-ALL         |                     |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| BOOKS               |     4 |   252 |     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| BOOKS               |     2 |   126 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("CATEGORY"='COMPUTER' AND "COST">10)
   5 - filter("CATEGORY"='FAMILY LIFE' AND "COST">10)
//这里的过滤条件不是我们原始SQL语句提供的,因为SQL查询转换器已经把原始的SQL语句转换成以下的语句了:
select title
from (select isbn,title,pubdate,cost,retail,discount
from books
where category='COMPUTER' and cost>10
union
select isbn,title,pubdate,cost,retail,discount
from books
where category='FAMILY LIFE' and cost>10);
下面来验证下SQL转换器进行的转换。
SQL> select title
  2  from (select isbn,title,pubdate,cost,retail,discount
  3  from books
  4  where category='COMPUTER' and cost>10
  5  union
  6  select isbn,title,pubdate,cost,retail,discount
  7  from books
  8  where category='FAMILY LIFE' and cost>10);
TITLE
------------------------------
REVENGE OF MICKEY
HANDCRANKED COMPUTERS
PAINLESS CHILD-REARING
HOLY GRAIL OF ORACLE
DATABASE IMPLEMENTATION
E-BUSINESS THE EASY WAY
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 187588122
------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     6 |   102 |     6  (34)| 00:00:01 |
|   1 |  VIEW                |       |     6 |   102 |     6  (34)| 00:00:01 |
|   2 |   SORT UNIQUE        |       |     6 |   378 |     6  (67)| 00:00:01 |
|   3 |    UNION-ALL         |       |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| BOOKS |     4 |   252 |     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| BOOKS |     2 |   126 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("CATEGORY"='COMPUTER' AND "COST">10)
   5 - filter("CATEGORY"='FAMILY LIFE' AND "COST">10)
从两个执行计划的对比中我们可以确定一下几点:
1、结果是相同的。
2、操作和过滤条件是相关的。
从而我们可以确定SQL转换器执行的是谓词前置(因为创建视图的语句中存在集合操作符 union 所有不能进行视图合并。)
子查询解嵌套
在子查询解嵌套中,SQL转换器将会把嵌套的查询转换成相等连接,然后对这个join 进行优化。
只有当转换以后的join语句与转换前的语句执行的结构一样的时候才进行这种转换,当然其他转换的前提也是保证执行结果相同。
我来执行下面的语句看看他的执行计划.
SQL> select first_name,last_name
  2  from hr.employees
  3  where department_id in
  4  (select department_id from hr.departments);
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   106 |  1908 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   106 |  1908 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPARTMENT_ID" IS NOT NULL)
过滤条件是 department_id is not null。并不是我们原始的SQL语句提供的过滤条件。因为SQL转换器已经将原始的语句转换成:
select first_name,last_name
from hr.employees
where department_id is not null;
因为department_id 是departments 表的主键。并且department_id 是employees表的外键,外键要么参照父表中的主键值,要么为空。所有这里就转换成了department_id is not null.
我们来验证下这种推测是否正确。
SQL> select first_name,last_name
  2  from hr.employees
  3  where department_id is not null;
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   106 |  1908 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   106 |  1908 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPARTMENT_ID" IS NOT NULL)
由两个语句的执行输出和执行计划可知.
1、两个SQL语句的结果是相等的。
2、plan hash value,查询操作,过滤条件都相同。
原始SQL被SQL转换器进行子查询解嵌套的时候,解的很彻底,因为最后面只要查询一张表了。
比employees.department_id=departments.department_id 还更高效。
重写物化视图
物化视图可以理解为一个查询的结果集,这个结果集是实际存储与数据库中的,不像视图只是一个定义。当数据库识别到SQL查询语句或者查询语句的一部分已经被保存为物化视图的时候,oracle 将会根据物化视图重写查询。这可以提供查询执行的效率,因为查询的结果已经预先得出了。
我们先来创建一个关于部门的工作表,这里的salary 包含了提成的部分.
SQL> create materialized view "department_salary" 
  2  enable query rewrite as
  3  select department_name,sum(salary + nvl(commission_pct,0) * salary) as salary
  4  from hr.employees join hr.departments
  5  using(department_id)
  6  group by department_name;
Materialized view created.
我们再来执行下面的查询,来确定每个部门的总工资。
SQL> select department_name,sum(salary + nvl(commission_pct,0) * salary) as salary
  2  from hr.employees join hr.departments
  3  using(department_id)
  4  group by department_name;
DEPARTMENT_NAME                    SALARY
------------------------------ ----------
Accounting                          20300
Administration                       4400
Executive                           58000
Finance                             51600
Human Resources                      6500
IT                                  28800
Marketing                           19000
Public Relations                    10000
Purchasing                          24900
Sales                              377140
Shipping                           156400
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 341668383

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |    11 |   330 |     2   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| department_salary |    11 |   330 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
         33  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        724  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed.
从上面的执行计划很明显可以看出oracle 在执行SQL语句的时候并没有去访问employees,departments表。而是使用预先存放的物化视图department_salary.如果在物化视图定义的时候

捕获.JPG

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

下一篇: 磁盘分区的删除
请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    169
  • 访问量
    717984