SQL 查询转换

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26110315/viewspace-719576/