Hint学习_02_Hint在子查询中

最新推荐文章于 2023-11-14 16:54:50 发布
最新推荐文章于 2023-11-14 16:54:50 发布
阅读量619 收藏 2
点赞数
分类专栏： oracle优化学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u010692693/article/details/103201405
版权

子查询中不加Hint

select * from t t1 where OWNER in (select OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 3356110324

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          | 21019 |  4864K|   197  (13)| 00:00:01 |
|   1 |  NESTED LOOPS                |          | 21019 |  4864K|   197  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T        |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | IND_T_ID |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS STORAGE FULL  | T        | 21019 |  4248K|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------------

子查询中错误的使用Hint

select /*+ full(t1) full(t2) */* from t t1 where OWNER in (select OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 3356110324

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          | 21019 |  4864K|   197  (13)| 00:00:01 |
|   1 |  NESTED LOOPS                |          | 21019 |  4864K|   197  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T        |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | IND_T_ID |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS STORAGE FULL  | T        | 21019 |  4248K|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------------

select /*+ full(t2) full(t1) */* from t t1 where OWNER in (select OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 3356110324

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          | 21019 |  4864K|   197  (13)| 00:00:01 |
|   1 |  NESTED LOOPS                |          | 21019 |  4864K|   197  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T        |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | IND_T_ID |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS STORAGE FULL  | T        | 21019 |  4248K|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------------

子查询中使用Hint  方式1

select /*+ full(t1) */* from t t1 where OWNER in (select /*+ full(t2) */OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 2135975663

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  1 |  HASH JOIN                 |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |  6097 |   178K|   190  (11)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| T    |   483K|    95M|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------

select * from t t1 where OWNER in (select /*+ full(t2) */OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 2135975663

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  1 |  HASH JOIN                 |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |  6097 |   178K|   190  (11)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| T    |   483K|    95M|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------

子查询中使用Hint  方式2

select /*+ full(@sel$1 t1) full(@sel$2 t2) */* from t t1 where OWNER in (select OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 2135975663

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  1 |  HASH JOIN                 |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |  6097 |   178K|   190  (11)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| T    |   483K|    95M|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------

select /*+ full(@sel$2 t2) */* from t t1 where OWNER in (select OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 2135975663

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  1 |  HASH JOIN                 |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |  6097 |   178K|   190  (11)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| T    |   483K|    95M|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------

子查询中使用Hint  方式3

select /*+ full(t1@sel$1) full(t2@sel$2) */* from t t1 where OWNER in (select OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 2135975663

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  1 |  HASH JOIN                 |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |  6097 |   178K|   190  (11)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| T    |   483K|    95M|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------

select /*+ full(t2@sel$2) */* from t t1 where OWNER in (select OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 2135975663

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  1 |  HASH JOIN                 |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |  6097 |   178K|   190  (11)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| T    |   483K|    95M|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------

子查询中使用Hint full + 自定义子查询块的名称 Hint qb_name

select /*+ full(@sel$1 t1) full(@zylong t2) */* from t t1 where OWNER in (select /*+ qb_name(zylong) */OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 2135975663

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  1 |  HASH JOIN                 |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |  6097 |   178K|   190  (11)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| T    |   483K|    95M|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------

select /*+ full(t1@sel$1) full(t2@zylong) */* from t t1 where OWNER in (select /*+ qb_name(zylong) */OWNER from t t2 where object_id=100);

Execution Plan
----------------------------------------------------------
Plan hash value: 2135975663

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  1 |  HASH JOIN                 |      |   128M|    28G|  1690  (80)| 00:00:06 |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |  6097 |   178K|   190  (11)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| T    |   483K|    95M|   195  (13)| 00:00:01 |
-----------------------------------------------------------------------------------

扩展知识1

set autotrace off
select owner,object_name,object_id from t t1 where OWNER in (select OWNER from t t2 where object_id=100);
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  5h3n2uv5jujdn, child number 0
-------------------------------------
select owner,object_name,object_id from t t1 where OWNER in (select
OWNER from t t2 where object_id=100)

Plan hash value: 3356110324

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |   186 (100)|          |
|   1 |  NESTED LOOPS                |          | 21019 |  2586K|   186   (8)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T        |     1 |    30 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | IND_T_ID |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS STORAGE FULL  | T        | 21019 |  1970K|   184   (8)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T2@SEL$2
   3 - SEL$5DA710D3 / T2@SEL$2
   4 - SEL$5DA710D3 / T1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$5DA710D3" "T2"@"SEL$2" ("T"."OBJECT_ID"))
      FULL(@"SEL$5DA710D3" "T1"@"SEL$1")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$2" "T1"@"SEL$1")
      USE_NL(@"SEL$5DA710D3" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   3 - access("OBJECT_ID"=100)
   4 - storage("OWNER"="OWNER")
       filter("OWNER"="OWNER")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
   2 - "OWNER"[VARCHAR2,30]
   3 - "T2".ROWID[ROWID,10]
   4 - "OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]

Note
-----
   - dynamic sampling used for this statement (level=2)

扩展知识2

隐含参数 _OPTIMIZER_IGNORE_HINTS 值设置为TRUE，Oracle会忽略SQL文本中的所有Hint，可以在系统或session级别设置
alter session set "_optimizer_ignore_hints" = true;

如果不想在系统级别设置上诉隐含参数，可以使用如下的 DATABASE TRIGGER 来将其影响范围缩小到session级别
create or replace trigger ignore_hints_on_logon
after logon on database
begin
execute immediate 'alter session set "_optimizer_ignore_hints" = true';
end;
/

张玉龙
关注
专栏目录
SQL优化-秒级优化，hint让IN子查询当驱动表
南山南的Python和大数据学习笔记
03-26 838
关注我的微信公众号：pythonislover,领取python,大数据，SQL优化相关视频资料！~ Python大数据与SQL优化笔 QQ群：771686295 在sql优化中，一个比较重要的一点就是驱动表的选择，HASH JOIN和NEST LOOP来说驱动表的选择是至关重要的。 对于NEST LOOP说最好是小表作为驱动表，与大表的连接键上减少索引，并且保证索引的选择性比较...
oracle中子查询的执行顺序是,Oracle sql语句执行顺序
weixin_33044131的博客
04-03 1428
sql语法的分析是从右到左一、sql语句的执行步骤：1)语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。2)语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。3)视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。4)表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达式。5)选择优化器，不同的优化器一般产生不同的“执行计划”6)选择连接方式，...
ORACLE如何在有视图的SQL中使用hint(提示)来设置视图内的SQL查询方式
MKing's Blog
03-06 3483
ORACLE如何在有视图的SQL中使用hint(提示)来设置视图内的SQL查询方式oracle的hint(提示)我想大家都知道使用吧，如果这个没用的话那就不用往下看了。本文主要介绍ORACLE如何在有视图的SQL中使用hint(提示)来设置视图内的SQL查询方式.本文不介绍通常hint的使用方法，这些在ORACLE的参考文档里都有了。在实际开发中，经常会用到
oracle中子查询的执行顺序是,oracle SQL子查询与主查询谁先执行?
weixin_29012765的博客
04-03 696
说不上来子查询与主查询，看语句了，看了前面几条的回答我有些疑问，按我的理解来说，是where 先处理的，再 select 你说的子主我还真整不明白对于where a=1 and b=1 是按a,b的顺序进行。，本着认真的态度，我用mssql做了测试，oracle应该一样的吧。我的方法简单，就是让子查询用函数处理，因为函数中不能用update之类的，所以我变通的将数据用cmdshell写入到磁盘文...
oracle 强制查询,如何强制子查询先执行？
weixin_39559750的博客
04-14 562
语句如下：SELECT e.circcomcode AS ComCode,'CNY',SUM(a.ChgAmount * c.planexchangerate *decode(c.CoinsFlag,1,decode(d.CoinsRate, null, 100, d.CoinsRate) / 100,1)) AS SumAmountfrom PrpPmain c,PrpCcoins d,stat...
INFA技术超群_中文KB_00018_PWC_如何在Lookup组件中使用含子查询和Hint的SQL
05-19
INFA技术超群_中文KB_00018_PWC_如何在Lookup组件中使用含子查询和Hint的SQL
sql学习 hint研究之子查询生效范围.sql
02-11
sql学习 hint研究之子查询生效范围.sql
sql join 子查询_SQL优化-秒级优化，hint让IN子查询当驱动表
05-13
在上面的示例中，我们使用了 HINT `DRIVING_SITE(table2)`，以让 IN 子查询作为驱动表。这将强制 SQL 引擎先执行子查询，再将结果与 `table1` 中的数据进行比较。 需要注意的是，使用 HINT 可能会导致 SQL 语句的...
oracle hint失效,HINT不起作用是为何？
weixin_30695909的博客
04-09 744
虽然你可以要求ORACLE走哪条路，但是，必须都是能够达到最终目的的路径才行，如果指一条根本不通的路，你让人家怎么走？ SQL> create table t as select * from dba_objects;表已创建。SQL> create index t_idx on t(object_id);索引已创建。SQL> set autot trace exp statS...
Oracle 11g dba_free_space视图查询慢解决方案——hint调整执行计划
weixin_41429228的博客
02-28 1947
ora11g中查询表空间的使用情况时需要通过dba_free_space视图来查看表空间的剩余量，但有时可能由于回收站的对象较多等原因，导致该视图查询速度较慢，本文将从其执行计划的角度分析其原因并提出一种相对实用且无风险的解决方式。
ORACLE--子查询
u014259295的博客
01-10 414
oacle基础知识 1、创建表空间 2、创建用户 3、创建一个表 4、数据库标准语言（DDL、DML、TCL、DQL、DCL） 5、数据库相关运算符（特别注意||运算符） 6、ORACLE相关函数及语法使用（group by、 order by、 having等等相关） http://www.w3school.com.cn/sql/sql_delete.a
Oracle知识点（七）--子查询
咸鸭蛋豆腐的博客
09-05 312
子查询 1.定义 也叫内部查询，先于主查询执行。 注意：子查询的结果被主查询使用。 子查询可以嵌入where、having、from子句中。 子查询可以【单独执行】。 2.语句 select list from table where expr operator(select list from table); 3.expr operato操作符 expr operato包括比较运算符： ①单行运算符 <，>，=，<>，<=，>= 例：查询出A表中比张三年龄更大的其他同
论Sharding-jdbc组件 采用 hint-strategy 策略 的实现
05-25 2891
因为在项目中，需要用到分库，但不分表。网上找到sharding-jdbc组件，因为业务要求分库，而不是采用数据库字段形式，进行路由。所以需要采用：hint方式，但网上资料少的可怜，而且版本不同，实现起来略有差异，而且网上的实现，注释不全。经过3天的研究，终于了解了hint方式的用法，现在把成果贴出来，供大家参考！ maven 包引入 <dependency> <groupId>org.apache.shardingsphere</groupId> <.
Oracle Exadata的TABLE ACCESS STORAGE FULL执行计划
ckawt40802的专栏
09-10 3798
这个TABLE ACCESS STORAGE FULL的执行计划只有在ORACLE EXADATA上才回出现。 Oracle在Exadata上增加了一个硬件Exad...
Oracle所有执行计划关键词 + 所有hint
最新发布
m0_47243617的博客
11-14 1705
执行计划里面的关键词大全和hint。
Oracle执行计划中集中常见的访问路径
4cott的博客
03-10 474
阅读《SQL优化核心思想》第4章 访问路径(ACCESS PATH) 笔记 访问路径值得是通过哪种扫描方式获取数据。常见的访问路径有 一、TABLE ACCESS FULL 表示全表扫描，一般是多块读，HINT:FULL(表名/别名) Oracle里最小的存储单位是块（Block），物理上连续的块组成了区(extent)，区又组成了段(segment)，因为区里面的数据块在物理上是连...
[20151212优化sql语句要注意关键字DISTINCT
12-14 211
[20151212]优化sql语句要注意关键字DISTINCT.txt --做sql语句优化要特别注意带DISTINCT语句，有一些情况往往是开发写错或者写少了连接条件，或者没有用exists等关键字。 --举最近优化生产...
How to find query block and object alias
西索的领域
05-09 428
Example #1From the file, sqlhc_*_[SQL_ID]_3_execution_plans.html#1 From Execution Plans section, find what you are looking for. (In this example, assume line id 18.)|* 18 | 12 | TABLE ACCESS STORAGE F...
Oracle执行计划
hustzw07的博客
09-12 601
执行计划 有时候我们要分析某条SQL的性能问题，通常做法是先看执行计划。通过执行计划看 SQL 执行的瓶颈，比如内存消耗、行源和时间。因此弄懂执行计划也就成了SQL优化的必备技能。 在Oracle 中有三种方式查看执行计划。 autotrace 这种方式在 PLSQL Developer 中很常见，而且提供了多种参数选择。如下： 序号 命令 说明 1
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值