千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 服务器 >

oracle hint有效范围是什么

发表于：2024-11-20 作者：千家信息网编辑
千家信息网最后更新 2024年11月20日，这篇文章给大家介绍oracle hint有效范围是什么，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。hint的范围parameter hints对整个sql有效率，其它hints
千家信息网最后更新 2024年11月20日oracle hint有效范围是什么

这篇文章给大家介绍oracle hint有效范围是什么，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

hint的范围
parameter hints对整个sql有效率，其它hints只对单个查询块起作用，要在查询块内指定hints
当子查询，内联view时候会有多个查询块，要为每个查询块 加hint控制该查询块（hint有效范围控制在查询块内）
SQL> create table t1 (a int,b varchar2(10));

Table created.

SQL> create table t2 (a2 int,b2 varchar2(10));

Table created.

SQL>

SQL> insert into t1 values(1,'a');

1 row created.

SQL> insert into t2 values(1,'b');

1 row created.

SQL> commit;

Commit complete.

SQL> create index t1_id on t1(a);

Index created.


SQL> create index t2_id on t2(a2);

Index created.


SQL> execute dbms_stats.gather_table_stats('XH','T1');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats('XH','T2');

PL/SQL procedure successfully completed.


SQL> set autotrace trace exp
SQL> select * from t1 where a=(select a2 from t2 where a2=1 );

Execution Plan
----------------------------------------------------------
Plan hash value: 3305116341

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_ID | 1 | | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T2_ID | 1 | 3 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

2 - access("A"= (SELECT "A2" FROM "T2" "T2" WHERE "A2"=1))
3 - access("A2"=1)

SQL> select /*+full(t1)*/ * from t1 where a=(select a2 from t2 where a2=1 );

Execution Plan
----------------------------------------------------------
Plan hash value: 1681039550

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| T2_ID | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("A"= (SELECT "A2" FROM "T2" "T2" WHERE "A2"=1))
2 - access("A2"=1)


全局hint使用.引用包含在其他查询块中的对象(引用的查询块已经有别名才行,查询块别名 不是表别名)
SQL> select * from t1 ts where a=(select /*+full(t2) full(ts.t1)*/a2 from t2 where a2=1 );

Execution Plan
----------------------------------------------------------
Plan hash value: 2910250514

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 5 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_ID | 1 | | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T2 | 1 | 3 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

2 - access("A"= (SELECT /*+ FULL ("T2") */ "A2" FROM "T2" "T2" WHERE
"A2"=1))
3 - filter("A2"=1)

SQL> with
2 t1_t as (select * from t1)
3 select t1_t.a,t2.b2 from t2,t1_t where t2.a2=t1_t.a
4 /

Execution Plan
----------------------------------------------------------
Plan hash value: 978323357

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 8 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | T1_ID | 1 | 3 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_ID | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

4 - access("T2"."A2"="T1"."A")


SQL> with
2 t1_t as (select * from t1)
3 select/*+full(t2) full(t1_t.t1)*/ t1_t.a,t2.b2 from t2,t1_t where t2.a2=t1_t.a
4 /

Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 8 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - access("T2"."A2"="T1"."A")

SQL>

假如子查询没有别名，可以制作一个别名为子查询(用qb_name hint制作),10g才能用qb_name

SQL> with
2 t1_t as (select /*+qb_name(sq)*/ * from t1)
3 select /*+qb_name(sq_t) full(@sq t1) full(@sq_t t2)*/ t1_t.a,t2.b2 from t2,t1_t where t2.a2=t1_t.a
4 /

Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 8 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - access("T2"."A2"="T1"."A")

SQL> SQL> select /*+qb_name(sq) full(@sq t1) full(@sq_t t2)*/* from t1 where a=(select /*+qb_name(sq_t)*/ a2 from t2 where a2=1);

Execution Plan
----------------------------------------------------------

Plan hash value: 1484901111

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("A"= (SELECT /*+ QB_NAME ("SQ_T") FULL ("T2") */ "A2"
FROM "T2" "T2" WHERE "A2"=1))
2 - filter("A2"=1)


SQL> SQL> select /*+qb_name(sq)*/* from t1 where a=(select /*+qb_name(sq_t) full(@sq t1) full(@sq_t t2)*/ a2 from t2 where a2=1);


Execution Plan
----------------------------------------------------------
Plan hash value: 1484901111

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("A"= (SELECT /*+ QB_NAME ("SQ_T") FULL ("T2") */ "A2"
FROM "T2" "T2" WHERE "A2"=1))
2 - filter("A2"=1)


使用query optimizer生成的别名

SQL> explain plan set statement_id='xh' for select * from t1 where a=(select a2 from t2 where a2=1);

Explained.

SQL> select * from table(dbms_xplan.display(null,'xh','basic +alias'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3305116341

---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 |
| 2 | INDEX RANGE SCAN | T1_ID |
| 3 | INDEX RANGE SCAN | T2_ID |
---------------------------------------------


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

1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
3 - SEL$2 / T2@SEL$2

17 rows selected.

SEL$为查询块前缀名（sel=select,cri$=create index,del$=delete,ins$=insert,misc$=lock table或多类语句,mrg$=merge,set$=union&minus,upd$=update）
顺序是按sql语句parse阶段查询块出现位置(左-右)，所以sel$1表示 select t1那个 查询块，sel$2 表示select t2那个查询块


SQL> select * from t1 where a=(select /*+full(@sel$2 t2) full(@sel$1 t1)*/a2 from t2 where a2=1 );

Execution Plan
----------------------------------------------------------
Plan hash value: 1484901111

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("A"= (SELECT /*+ FULL ("T2") */ "A2" FROM "T2" "T2" WHERE
"A2"=1))
2 - filter("A2"=1)

另外表有别名的话hint中要用别名
SQL> select * from t1 ts where a=(select /*+full(@sel$2 t2) full(@sel$1 ts)*/a2 from t2 where a2=1 );

Execution Plan
----------------------------------------------------------
Plan hash value: 1484901111

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | T1 | 1 | 5 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T2 | 1 | 3 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("A"= (SELECT /*+ FULL ("T2") */ "A2" FROM "T2" "T2" WHERE
"A2"=1))
2 - filter("A2"=1)

*另外parameter hints对整个sql有效率(例如hint all_rows,gather_plan_statistics之类)
*11g中v$sql_hint可以查看可以使用的hint列表

关于oracle hint有效范围是什么就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

很赞哦！
查询 别名 有效 范围 内容 更多 有效率 语句 制作 帮助 控制 不错 位置 作用 全局 兴趣 前缀 单个 多个 对象 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 安防网络技术论坛 王者荣耀服务器炸了没加星 梦幻服务器千变万化 工行软件开发中心属于总行吗 聊城联想服务器代理批发 网络安全法 证书 为诈骗提供网络技术 网络安全青年聚力内容 深圳云服务器散热器哪家好 数据库技术与应用的实践研究 我的世界最强斗罗服务器科技空岛 网络安全项目合同 时序数据库支持sql语句 戴尔服务器管理端密码 山西定制网络技术开发哪个正规 word服务器 qq邮箱的服务器id 幻塔传送时为什么服务器连接失败 学计算机软件开发具备条件 一般的网络技术选型是 数据库两个表的外部并 山西省网络安全等级保护局长 网络安全包括内容 一键数据库生成 数据库中实体指 东莞市易特软件开发 软件开发设计的产品经理 县级网络安全会议纪要 aca认证云数据库 中磊软件开发

相关文章

0