Oracle SQL Hints：优化与调优技术详解

需积分: 9 61 浏览量更新于2024-07-19 1 收藏 660KB PDF 举报

"Oracle SQL Hints" Oracle SQL Hints 是一种在SQL查询中指导数据库优化器选择特定执行计划的技术。在Oracle数据库系统中，SQL优化是提高数据库性能的关键环节，而SQL Hints则提供了对优化器行为进行精细控制的手段。黄玮的《Oracle高性能SQL引擎剖析：Oracle SQL优化与调优技术详解》一书的附录部分专门介绍了这些提示的含义和用法。提示（HINT）允许开发人员或DBA在SQL语句中插入特定的指令，以影响Oracle解析器如何处理查询。这在某些复杂或者性能敏感的场景下特别有用，因为默认的优化策略可能并不总是最佳选择。以下是一些常见的Oracle SQL Hints： 1. 访问路径提示：这些提示用于指定数据访问方法，例如： - `CLUSTER`：使用集群索引来访问数据。 - `HASH`：使用哈希连接操作。 - `ROWID`：直接通过ROWID访问行。 - `FULL`：全表扫描。 - `INDEX`：使用指定的索引。 - `INDEX_ASC` 和 `INDEX_DESC`：指定索引升序或降序扫描。 - `NO_INDEX`：避免使用索引。 - `INDEX_FFS`, `INDEX_RRS`, `INDEX_SS`, `INDEX_SS_ASC`, `INDEX_SS_DESC`, `NO_INDEX_SS`, `INDEX_RS_ASC`, `INDEX_RS_DESC`：这些是关于索引的不同变体，用于不同类型的访问。 2. 关联提示：这些提示用于控制连接操作的执行方式： - `NL_AJ`, `HASH_AJ`, `MERGE_AJ`：指定嵌套循环、哈希连接或归并连接算法。 - `USE_HASH` 和 `NO_USE_HASH`：强制或禁止使用哈希连接。 - `USE_MERGE` 和 `NO_USE_MERGE`：强制或禁止使用归并连接。 - `USE_NL` 和 `NO_USE_NL`：强制或禁止使用嵌套循环。 - `USE_NL_WITH_INDEX`：在连接操作中使用特定的索引。除此之外，还有其他一些高级提示，如 `BITMAP_TREE` 用于位图索引，以及 `USE_INVISIBLE_INDEXES` 和 `NO_USE_INVISIBLE_INDEXES` 来控制是否使用不可见索引。使用SQL Hints时，应谨慎行事，因为过度依赖或不适当的使用可能会导致性能下降或产生未预期的行为。通常，优化器能够根据统计信息做出合理的决策，但在特定情况下，如大量数据插入、临时性能问题或特定查询优化时，使用HINTs可以提供额外的控制。了解和正确应用这些提示对于提升Oracle数据库的性能至关重要。通过对查询进行调优，可以减少查询时间，降低服务器负载，从而提高整个系统的响应速度和用户体验。在实践中，应结合EXPLAIN PLAN等工具，对查询的执行计划进行分析，确保所使用的HINTs确实带来了性能提升。

Oracle 高性能 SQL 引擎剖析：Oracle SQL 优化与调优技术详解作者：黄玮

资源下载：http://www.HelloDBA.com

描述：这一提示通常是由优化器在并行查询，加在并行串行直接输入输出时，生成的内部查询

语句上，控制对索引快速完全扫描的并行操作。

示例：（以下示例在 9i 中运行）

HELLODBA.COM>create table t (A number, B varchar2(20), constraint t_pk primary key(A) ) organization

index parallel;

Table created.

HELLODBA.COM>select count(*) from t;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)

1 0 SORT (AGGREGATE)

2 1 SORT* (AGGREGATE) :Q17628000

3 2 INDEX* (FAST FULL SCAN) OF 'T_PK' (UNIQUE) (Cost=1 Card=1000000) :Q17628000

2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) FROM (SELECT /*+

INDEX_RRS(A2 "T_PK")

INDEX_RRS(A2 "T_PK")INDEX_RRS(A2 "T_PK")

INDEX_RRS(A2 "T_PK") */ 0 FROM "T" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)A2) A1

3 PARALLEL_COMBINED_WITH_PARENT

INDEX_SS

语法：INDEX_SS([<@查询块>] <表> [<索引>]) 或者 INDEX_SS([<@查询块>] <表> [(<索引字段列

表>)])

描述：指示优化器通过跳跃索引扫描来访问表；

HELLODBA.COM>exec sql_explain('select /*+index_ss(t t_tables_pk)*/count(status) from t_tables t where

table_name like :A', 'BASIC OUTLINE');

----------------------------------------------------

| Id | Operation | Name |

----------------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | SORT AGGREGATE | |

| 2 | TABLE ACCESS BY INDEX ROWID| T_TABLES |

| 3 | INDEX SKIP SCAN

INDEX SKIP SCANINDEX SKIP SCAN

INDEX SKIP SCAN | T_TABLES_PK |

----------------------------------------------------

INDEX_SS_ASC

语法：INDEX_SS_ASC([<@查询块>] <表> [<索引>]) 或者 INDEX_SS_ASC([<@查询块>] <表> [(<索引

字段列表>)])

描述：指示优化器通过跳跃索引扫描来访问表，并以索引键值的递增顺序扫描索引记录；

示例：（注意返回数据顺序）

HELLODBA.COM>select /*+index_ss_asc(t t_tables_pk)*/table_name, status from t_tables t where table_name

like 'T%' and rownum<=3;

TABLE_NAME STATUS

------------------------------ --------

TAB$

TAB$TAB$

TAB$ VALID

TABCOMPART$

TABCOMPART$TABCOMPART$

TABCOMPART$ VALID

TABLE_PRIVILEGE_MAP

TABLE_PRIVILEGE_MAPTABLE_PRIVILEGE_MAP

TABLE_PRIVILEGE_MAP VALID

INDEX_SS_DESC

语法：IINDEX_SS_DESC([<@查询块>] <表> [<索引>]) 或者 INDEX_SS_DESC([<@查询块>] <表> [(<索

引字段列表>)])

描述：指示优化器通过跳跃索引扫描来访问表，并以索引键值的递减顺序扫描索引记录；

示例：（注意返回数据顺序）

HELLODBA.COM>select /*+index_ss_desc(t t_tables_pk)*/table_name, status from t_tables t where

table_name like 'T%' and rownum<=3;

TABLE_NAME STATUS

------------------------------ --------

TYPE_MISC$

TYPE_MISC$TYPE_MISC$

TYPE_MISC$ VALID

TYPEHIERARCHY$

TYPEHIERARCHY$TYPEHIERARCHY$

TYPEHIERARCHY$ VALID

Oracle 高性能 SQL 引擎剖析：Oracle SQL 优化与调优技术详解作者：黄玮

资源下载：http://www.HelloDBA.com

TYPED_VIEW$

TYPED_VIEW$TYPED_VIEW$

TYPED_VIEW$ VALID

NO_INDEX_SS

语法：NO_INDEX_SS([<@查询块>] <表> [<索引>]) 或者 NO_INDEX_SS([<@查询块>] <表> [(<索引

字段列表>)])

描述：禁止优化器使用跳跃索引扫描访问表。

示例：

HELLODBA.COM>exec sql_explain('select /*+no_index_ss(o t_objects_idx8)*/owner, object_name from

t_objects o where object_name like :A', 'BASIC OUTLINE');

-----------------------------------------------

| Id | Operation | Name |

-----------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | INDEX FAST FULL SCAN

INDEX FAST FULL SCANINDEX FAST FULL SCAN

INDEX FAST FULL SCAN| T_OBJECTS_IDX8 |

-----------------------------------------------

INDEX_RS_ASC

语法：INDEX_RS_ASC([<@查询块>] <表> [<索引>]) 或者 INDEX_RS_ASC([<@查询块>] <表> [(<索引

字段列表>)])

描述：指示优化器通过范围索引扫描来访问表，并以索引键值的递增顺序扫描索引记录；但如

果谓词条件满足唯一键索引扫描的条件时，优化器还是会选择唯一键索引扫描。

示例：（注意返回数据顺序）

HELLODBA.COM>select /*+index_rs_asc(o t_objects_pk)*/object_id, object_name from t_objects o where

object_id < 5;

OBJECT_ID OBJECT_NAME

---------- --------------------------------------------------------------------------------------------

2 C_OBJ#

3 I_OBJ#

4 TAB$

INDEX_RS_DESC

语法：INDEX_RS_DESC([<@查询块>] <表> [<索引>]) 或者 INDEX_RS_DESC([<@查询块>] <表> [(<索

引字段列表>)])

描述：指示优化器通过范围索引扫描来访问表，并以索引键值的递减顺序扫描索引记录；但如

果谓词条件满足唯一键索引扫描的条件时，优化器还是会选择唯一键索引扫描。

示例：（注意返回数据顺序）

HELLODBA.COM>select /*+index_rs_desc(o t_objects_pk)*/object_id, object_name from t_objects o where

object_id < 5;

OBJECT_ID OBJECT_NAME

---------- --------------------------------------------------------------------------------------------

4 TAB$

3 I_OBJ#

2 C_OBJ#

AND_EQUAL

语法：AND_EQUAL([<@查询块>] <表> [<索引 1> <索引 2> ...]) 或者 AND_EQUAL([<@查询块>] <表>

[(<索引 1 字段>) (<索引 2 字段>) ...])

描述：指示优化器对由指定表上的两个或多个单字段索引获取 ROWID 数据集的取交集，并消

除重复的 ROWID。

HELLODBA.COM>exec sql_explain('select /*+AND_EQUAL(t T_TABLES_IDX1 T_TABLES_IDX3)*/* from t_tables t

where t.owner=:A and t.tablespace_name=:B', 'BASIC OUTLINE');

-----------------------------------------------------

| Id | Operation | Name |

-----------------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| T_TABLES |

Oracle 高性能 SQL 引擎剖析：Oracle SQL 优化与调优技术详解作者：黄玮

资源下载：http://www.HelloDBA.com

| 2 | AND

ANDAND

AND-

-EQUAL

EQUAL EQUAL

EQUAL | |

| 3 | INDEX RANGE SCAN | T_TABLES_IDX1 |

| 4 | INDEX RANGE SCAN | T_TABLES_IDX3 |

-----------------------------------------------------

INDEX_COMBINE

语法：INDEX_COMBINE([<@查询块>] <表> [<索引 1> ...]) 或者 AND_EQUAL([<@查询块>] <表> [(<

索引 1 字段列表>) ...])

描述：指示优化器以位图计算方式访问表。如果指定了索引，则尝试从指定索引上获取位图数

据，如果指定索引不是位图索引，则尝试进行位图转换。

HELLODBA.COM>exec sql_explain('SELECT /*+INDEX_COMBINE(t t_objects_idx2 t_objects_idx8)*/1 FROM

t_objects t where status=:A and owner=:B', 'BASIC OUTLINE');

----------------------------------------------------------

| Id | Operation | Name |

----------------------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | BITMAP CONVERSION TO ROWIDS | |

| 2 | BITMAP AND

BITMAP ANDBITMAP AND

BITMAP AND | |

| 3 | BITMAP INDEX SINGLE VALUE | T_OBJECTS_IDX2 |

| 4 | BITMAP CONVERSION FROM ROWIDS| |

| 5 | SORT ORDER BY | |

| 6 | INDEX RANGE SCAN | T_OBJECTS_IDX8 |

----------------------------------------------------------

INDEX_JOIN

语法：INDEX_JOIN([<@查询块>] <表> [<索引 1> <索引 2> ...]) 或者 INDEX_JOIN([<@查询块>] <表>

[(<索引 1 字段列表>) (<索引 2 字段列表>) ...])

描述：指示优化器对由指定表上的两个或多个索引关联后访问表。

HELLODBA.COM>exec sql_explain('SELECT /*+INDEX_JOIN(t t_objects_idx2 t_objects_idx8)*/1 FROM t_objects

t where status=:A and owner=:B', 'BASIC OUTLINE');

----------------------------------------------------------

| Id | Operation | Name |

----------------------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | VIEW | index$_join$_001

index$_join$_001index$_join$_001

index$_join$_001 |

| 2 | HASH JOIN

HASH JOINHASH JOIN

HASH JOIN | |

| 3 | BITMAP CONVERSION TO ROWIDS| |

| 4 | BITMAP INDEX SINGLE VALUE | T_OBJECTS_IDX2 |

| 5 | INDEX RANGE SCAN | T_OBJECTS_IDX8 |

----------------------------------------------------------

BITMAP_TREE

语法：BITMAP_TREE([<@查询块>] <表> AND(<索引 1>[ <索引 2> ...])) 或者 BITMAP_TREE([<@查询

块>] <表> AND((<索引 1 字段列表>)[ (<索引 2 字段列表>) ...]))

描述：指示优化器将 ROWID 转换为位图，并做位图计算；

HELLODBA.COM>exec sql_explain('select /*+BITMAP_TREE(T_OBJECTS AND(T_OBJECTS_IDX4 T_OBJECTS_IDX2))*/

owner from t_objects where owner like :A and status like :B', 'TYPICAL OUTLINE');

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1190 | 15470 | 559 (1)| 00:00:03 |

| 1 | TABLE ACCESS BY INDEX ROWID | T_OBJECTS | 1190 | 15470 | 559 (1)| 00:00:03 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |

| 3 | BITMAP AND

BITMAP ANDBITMAP AND

| 4 | BITMAP MERGE

BITMAP MERGEBITMAP MERGE

| 6 | BITMAP MERGE

BITMAP MERGEBITMAP MERGE

-----------------------------------------------------------------------------------------------

Oracle 高性能 SQL 引擎剖析：Oracle SQL 优化与调优技术详解作者：黄玮

资源下载：http://www.HelloDBA.com

USE_INVISIBLE_INDEXES

语法：USE_INVISIBLE_INDEXES([<@查询块>] <表> ([<索引 1>] ...)) 或者

USE_INVISIBLE_INDEXES([<@查询块>] <表> ([<索引列表 1>] ...))

描述：指示优化器使用不可见（INVISIBLE）的索引。

HELLODBA.COM>show parameter visible

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_use_invisible_indexes boolean FALSE

FALSEFALSE

FALSE

HELLODBA.COM>alter index t_objects_pk invisible;

Index altered.

HELLODBA.COM>exec sql_explain('select /*+qb_name(M) USE_INVISIBLE_INDEXES(o (object_id))*/count(1) from

t_objects o where object_id < 1000', 'TYPICAL OUTLINE');

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

|* 2 | INDEX RANGE SCAN| T_OBJECTS_PK

T_OBJECTS_PK T_OBJECTS_PK

T_OBJECTS_PK | 973 | 4865 | 3 (0)| 00:00:01 |

----------------------------------------------------------------------------------

NO_USE_INVISIBLE_INDEXES

语法：NO_USE_INVISIBLE_INDEXES([<@查询块>] <表> ([<索引 1>] ...)) 或者

NO_USE_INVISIBLE_INDEXES([<@查询块>] <表> ([<索引列表 1>] ...))

描述：禁止优化器使用不可见（INVISIBLE）的索引。

HELLODBA.COM>exec sql_explain('select /*+qb_name(M) NO_USE_INVISIBLE_INDEXES(o

(t_objects_pk))*/count(1) from t_objects o where object_id < 1000', 'TYPICAL OUTLINE');

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 185 (2)| 00:00:02 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

|* 2 | INDEX FAST FULL SCAN| T_OBJECTS_IDX8

T_OBJECTS_IDX8T_OBJECTS_IDX8

T_OBJECTS_IDX8 | 973 | 4865 | 185 (2)| 00:00:02 |

----------------------------------------------------------------------------------------

关联提示

NL_AJ

语法：NL_AJ([<子查询块>])

描述：指示优化器将主查询中的表与子查询中的表做嵌套循环反关联（Nested Loop Anti-Join）

操作；如果提示出现在子查询中，则不需要参数指定查询块标识；

HELLODBA.COM>exec sql_explain('select /*+nl_aj(@inv)*/* from t_tables t where not exists (select

/*+qb_name(inv)*/1 from t_users u where t.owner=u.username)', 'BASIC OUTLINE')

-----------------------------------------

| Id | Operation | Name |

-----------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | NESTED LOOPS ANTI

NESTED LOOPS ANTINESTED LOOPS ANTI

NESTED LOOPS ANTI | |

| 2 | TABLE ACCESS FULL| T_TABLES |

| 3 | INDEX UNIQUE SCAN| T_USERS_UK |

-----------------------------------------

HASH_AJ

语法：HASH_AJ([<子查询块>])

剩余84页未读，继续阅读

清晨2015

粉丝: 1
资源: 2

Oracle SQL Hints：优化与调优技术详解

wx494社区门诊管理系统小程序-php+vue+uniapp.zip（可运行源码+sql文件+文档）

HTML+CSS+JS+JQ+Bootstrap的家具风格趋势展示响应式网页.7z

高分项目，基于Python+OpenCV的实时疲劳驾驶检测系统，内含源码+演示视频+部署教程

oracle sql 语句暗示 hints

oracle_hints

Oracle 常用 hints 说明

oracle-hints.rar_oracle

Oracle的hints调整机制介绍

Oracle SQL优化：hints与执行计划解析

Oracle SQL性能优化：Hints的使用策略

最新资源