Hint&ordered&leading&use
Oracle官方文档：Oracle Database SQL Language Reference 1、ordered hint 2、leading hint 3、use_nl 1、ordered hint /*+ ORDERED */ The ORDERED hint instructs Oracleto join tables in the order in which they appear in the FROM clause.Oracle rec
Oracle官方文档：Oracle Database SQL Language Reference
1、ordered hint
2、leading hint
3、use_nl
1、ordered hint
/*+ ORDERED */
The ORDERED hint instructs Oracleto join tables in the order in which they appear in the FROM clause.Oracle recommends that you use the LEADING hint, which is more versatile than the ORDERED hint.
When you omit the ORDERED hint from a SQL statement requiring a join, the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something that the optimizer does not know about the number of rows selected from each table. Such information lets you choose an inner and outer table better than the optimizer could.
2、leading hint
/*+ LEADING ( [ @ queryblock ] tablespec [ tablespec ]... ) */
The LEADING hint instructs the optimizerto use the specified set of tables as the prefix in the execution plan. This hint is more versatile than the ORDERED hint.
The LEADING hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting LEADING hints, then all of them are ignored. If you specify the ORDERED hint, it overrides all LEADING hints.
3、use_nl hint
The USE_NL hint instructs the optimizer to join each specified table to another row source with a nested loops join, using the pecified table as the inner table.
Use of the USE_NL and USE_MERGE hints is recommended with the LEADING and ORDERED hints. The optimizer uses those hints when the referenced table is forced tobe the inner table of a join. The hints are ignored if the referenced table is the outer table.
--USE_NL强制把referenced table作为inner table。如果referenced table 为outer table，则此hint被忽略（即不管用）--个人觉得这句话是废话。
--实例1：
--/*+ ordered */ hint实例,表BASOPT上有optid列上的索引PK_BASOPT
SQL> set autot trace exp
--不用/*+ ordered */hint，BASOPTUSER作为驱动表，用BASOPTUSER去连接BASOPT表
SQL> select optname,userid from basopt a,basoptuser b where a.optid = b.optid and b.userid = 1;
执行计划
----------------------------------------------------------
Plan hash value: 922486247
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 19 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_BASOPT | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| BASOPT | 1 | 11 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."USERID"=1)
4 - access("A"."OPTID"="B"."OPTID")
--用/*+ ordered */hint 来指定按照from后边表的顺序来连接表，用BASOPT去连接BASOPTUSER表，此时优化器选择了另一种链接方法：MERGE JOIN
SQL> select /*+ ordered */ optname,userid from basopt a,basoptuser b where a.optid = b.optid and b.userid = 1;
执行计划
----------------------------------------------------------
Plan hash value: 2164325570
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 1 | 19 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| BASOPT | 2 | 22 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_BASOPT | 2 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1 | 8 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."OPTID"="B"."OPTID")
filter("A"."OPTID"="B"."OPTID")
5 - filter("B"."USERID"=1)
--用use_nl(b)指定使用nested loops连接使用basoptuser作为內表
SQL> select /*+ ordered use_nl(b)*/ optname,userid from basopt a,basoptuser b where a.optid = b.optid and b.userid = 1;
执行计划
----------------------------------------------------------
Plan hash value: 3306984809
--------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | 19 | 7 (0)| 00:00:01
|
| 1 | NESTED LOOPS | | 1 | 19 | 7 (0)| 00:00:01
|
| 2 | TABLE ACCESS FULL| BASOPT | 2 | 22 | 3 (0)| 00:00:01
|
|* 3 | TABLE ACCESS FULL| BASOPTUSER | 1 | 8 | 2 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."USERID"=1 AND "A"."OPTID"="B"."OPTID")
SQL>
--实例2：
--/*+ leading() */ hint实例,表BASOPT上有optid列上的索引PK_BASOPT,表SYSUSER上有userid列上的索引PK_SYSUSER
SQL> select optname,c.userid from basopt a,basoptuser b,sysuser c where a.optid = b.optid and b.userid = c.userid and b.userid = 1;
执行计划
----------------------------------------------------------
Plan hash value: 1787196989
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 22 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 11 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_SYSUSER | 1 | 3 | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_BASOPT | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| BASOPT | 1 | 11 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."USERID"=1)
5 - filter("B"."USERID"=1)
6 - access("A"."OPTID"="B"."OPTID")
--设定驱动表b c
SQL> select /*+ leading(b c) */ optname,c.userid from basopt a,basoptuser b,sysuser c where a.optid = b.optid and b.userid = c.userid and b.userid = 1;
执行计划
----------------------------------------------------------
Plan hash value: 3853709033
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 22 | 4 (0)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 11 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1 | 3 | 0 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_SYSUSER | 1 | 3 | 0 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_BASOPT | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| BASOPT | 1 | 11 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."USERID"=1)
6 - access("C"."USERID"=1)
7 - access("A"."OPTID"="B"."OPTID")
--设定驱动表b a
SQL> select /*+ leading(b a) */ optname,c.userid from basopt a,basoptuser b,sysuser c where a.optid = b.optid and b.userid = c.userid and b.userid = 1;
执行计划
----------------------------------------------------------
Plan hash value: 1915872201
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 4 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 1 | 22 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 19 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_BASOPT | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| BASOPT | 1 | 11 | 1 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_SYSUSER | 1 | 3 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."USERID"=1)
5 - access("A"."OPTID"="B"."OPTID")
8 - access("C"."USERID"=1)
--设定驱动表b c,并且b和c表之间的连接使用nested loops连接
SQL> select /*+ leading(b c) use_nl(b c) */ optname,c.userid from basopt a,basoptuser b,sysuser c where a.optid = b.optid and b.userid = c.userid and b.userid
= 1;
执行计划
----------------------------------------------------------
Plan hash value: 683070851
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 22 | 4 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 11 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | BASOPTUSER | 1 | 8 | 3 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_SYSUSER | 1 | 3 | 0 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_BASOPT | 1 | | 0 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| BASOPT | 1 | 11 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."USERID"=1)
5 - access("C"."USERID"=1)
6 - access("A"."OPTID"="B"."OPTID")
SQL>
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
热门话题
主板上的数字音频输出接口-SPDIF OUT
Jan 14, 2024 pm 04:42 PM
主板上SPDIFOUT连接线序最近我遇到了一个问题，就是关于电线的接线顺序。我上网查了一下，有些资料说1、2、4对应的是out、+5V、接地；而另一些资料则说1、2、4对应的是out、接地、+5V。最好的办法是查看你的主板说明书，如果找不到说明书，你可以使用万用表进行测量。首先找到接地，然后就可以确定其他的接线顺序了。主板vdg怎么接线连接主板的VDG接线时，您需要将VGA连接线的一端插入显示器的VGA接口，另一端插入电脑的显卡VGA接口。请注意，不要将其插入主板的VGA接口。完成连接后，您可以
AMP是什么币？
Feb 24, 2024 pm 09:16 PM
什么是AMP币？AMP代币是由Synereo团队于2015年创立，作为Synereo平台的主要交易货币。AMP代币旨在通过多种功能和用途，为用户提供更好的数字经济体验。AMP代币的用途AMP代币在Synereo平台中拥有多重角色和功能。首先，作为平台的加密货币奖励系统的一部分，用户能够通过分享和推广内容来获得AMP奖励，这一机制鼓励用户更积极地参与平台的活动。AMP代币还可用于在Synereo平台上推广和传播内容。用户可以通过使用AMP代币提升他们的内容在平台上的曝光率，以吸引更多观众来查看和分
btc交易app怎么安装注册？
Feb 21, 2025 pm 07:09 PM
本篇文章将详细介绍如何安装和注册比特币交易应用。比特币交易应用允许用户管理和交易比特币等加密货币。文章逐步指导用户完成安装和注册过程，包括下载应用程序、创建账户、进行身份验证和首次存款。文章的目标是为初学者提供清晰易懂的指南，帮助他们轻松进入比特币交易的世界。
欧易交易所下载官方入口
Feb 21, 2025 pm 07:51 PM
欧易，又称OKX，是一个全球领先的加密货币交易平台。文章提供了欧易官方安装包的下载入口，方便用户在不同设备上安装欧易客户端。该安装包支持 Windows、Mac、Android 和 iOS 系统，用户可根据自己的设备类型选择相应版本下载。安装完成后，用户即可注册或登录欧易账户，开始交易加密货币和享受平台提供的其他服务。
全球数字货币交易十大APP推荐（2025货币交易软件排名）
Mar 12, 2025 pm 05:48 PM
本文推荐全球十大数字货币交易APP，涵盖币安(Binance)、OKX、火币(Huobi Global)、Coinbase、Kraken、Gate.io、KuCoin、Bitfinex、Gemini和Bitstamp。这些平台在交易对数量、交易速度、安全性、合规性、用户体验等方面各有特色，例如币安以其高交易速度和广泛服务闻名，而Coinbase则更适合新手用户。选择适合自己的平台需要综合考虑自身需求和风险承受能力。 了解全球主流数字货币交易平台，助您安全高效进行数字资产交易。
数字货币app交易软件哪个最好用 数字货币交易软件大盘点
Mar 07, 2025 pm 06:45 PM
没有单一“最好用”的数字货币交易App，选择取决于个人需求。1. OKX功能强大，币种丰富；2. Binance流动性高，交易类型多样；3. Gate.io提供质押挖矿等独特功能；4. Huobi Global界面友好，多语言支持；5. Kraken注重安全性；6. Coinbase适合新手，注重用户教育。 选择时需考虑安全性、流动性、手续费、功能、用户体验等等因素。
虚拟币三大龙头app是哪三个？
Mar 04, 2025 pm 08:51 PM
虚拟货币市场蓬勃发展，众多交易平台应运而生。本文将介绍虚拟货币领域的三大龙头应用程序，它们以出色的用户体验、强大的安全性和丰富的功能而著称。这些应用程序包括币安（Binance）、芝麻开门（gate.io）和 欧易（okx），它们为投资者提供了便捷、安全的途径，可以购买、出售、交易和跟踪虚拟货币。
十大虚拟数字币交易平台2025排名 虚拟币app交易所前十排名
Feb 21, 2025 pm 09:03 PM
本文介绍了 10 个主流的加密货币交易所，涵盖了它们的成立时间、服务范围、安全性、流动性、交易费用等基本信息。这些交易所包括：OKX、Binance、Gate.io、Bitget、Coinbase、Huobi、KuCoin、Crypto.com、Gemini 和 Kraken。
