• 博客访问: 29676
  • 博文数量: 79
  • 用 户 组: 普通用户
  • 注册时间: 2017-08-25 10:20
  • 认证徽章:
个人简介

喜欢折腾 建立一个学习交流群558043289,有问题可以交流。

文章分类

全部博文(79)

文章存档

2018年(78)

2017年(1)

我的朋友

分类: Oracle

2018-05-27 16:03:10

原文地址:HINT篇---优化器相关 作者:oracle_mao

一:和优化器相关的hint
1、/*+ ALL_ROWS */
 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
2、/*+ FIRST_ROWS*/
 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
3、/*+ RULE*/
 表明对语句块选择基于规则的优化方法.
详解:对于optimizer_mode默认就是ALL_ROWS 的,all_rows的意思就是说所有的结果全查出来后在一起返回给用户,比较适合报表等平时的查询,而first_rows是只有查出来一条就显示一条,比较适合分页的查询,但如果要将所有都查出来的话肯定是all_rows快,first_rows(n),这里的n为3就是查出3条就显示。而rule是表示按照RBO的方式走。
实验:
SQL> select  /*+first_ROWS(2) */ object_name from t1 where object_id = 600;

64 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    87 |   306   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |     3 |    87 |   306   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=600)


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

SQL> select  /*+first_ROWS(20) */ object_name from t1 where object_id = 600;

64 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    21 |   609 |  2135   (2)| 00:00:26 |
|*  1 |  TABLE ACCESS FULL| T1   |    21 |   609 |  2135   (2)| 00:00:26 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=600)


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
      23304  consistent gets
          0  physical reads
          0  redo size
       1667  bytes sent via SQL*Net to client
        568  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         64  rows processed
对于first_rows(n)取值不同,消耗的cost值也不同,时间也就不一样。所以说对于要求访问速度快的话,first_rows还是比较有用的。
SQL> select  /*+all_ROWS */ object_name from t1 where object_id = 600;

64 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    63 |  1827 |  6420   (2)| 00:01:18 |
|*  1 |  TABLE ACCESS FULL| T1   |    63 |  1827 |  6420   (2)| 00:01:18 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=600)


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

SQL> select  /*+first_ROWS */ object_name from t1 where object_id = 600;

64 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    63 |  1827 |  6420   (2)| 00:01:18 |
|*  1 |  TABLE ACCESS FULL| T1   |    63 |  1827 |  6420   (2)| 00:01:18 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=600)


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
      23304  consistent gets
          0  physical reads
          0  redo size
       1667  bytes sent via SQL*Net to client
        568  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         64  rows processed
而对于first_rows不加n得到的结果和all_rows消耗的cost值是一样的。所以在使用first_rows的时候还是需要带一个参数较好。

 

阅读(4) | 评论(0) | 转发(0) |
0

上一篇:oracle 索引详解

下一篇:没有了

给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册