首先，我们需要明确什么是执行计划。执行计划是数据库在执行SQL语句时的一种预估性的计划，它会告诉我们数据库在执行该SQL语句时的具体操作步骤和执行顺序。通过调整执行计划，我们可以优化SQL语句的性能。
在Oracle数据库中，基于hint的3种执行计划控制方法包括：
- 使用INLINE提升性能
INLINE是一个hint，它可以让Oracle把一些短小简单的SQL代码嵌入到调用它们的SQL块中，可以减少操作的时间和I/O等待时间。这个hint应该只用于简单的SQL语句，如：
SELECT /*+ INLINE(w) */ w.worker_id, w.first_name, w.last_name, w.job_title
FROM workers w
WHERE w.job_title = 'Manager'
ORDER BY w.last_name, w.first_name;
这段代码中，我们使用了INLINE hint来嵌入SQL语句，提升查询的性能。
- 使用INDEX提示强制使用索引
有时候，Oracle的优化器会决定不使用索引来执行查询语句，从而导致性能下降。在这种情况下，我们可以使用INDEX hint来强制使用索引，如：
SELECT /*+ INDEX(w, workers_idx) */ w.worker_id, w.first_name, w.last_name, w.job_title
FROM workers w
WHERE w.job_title = 'Manager'
ORDER BY w.last_name, w.first_name;
这段代码中，我们使用了INDEX hint来强制使用名为workers_idx的索引，以提高查询性能。
- 使用FULL提示强制全表扫描
相比使用索引，有时候全表扫描可能更快。在这种情况下，我们可以使用FULL hint来强制进行全表扫描，如：
SELECT /*+ FULL(w) */ w.worker_id, w.first_name, w.last_name, w.job_title
FROM workers w
WHERE w.job_title = 'Manager'
ORDER BY w.last_name, w.first_name;
这段代码中，我们使用FULL hint来强制进行全表扫描，以提高查询性能。
以上就是基于hint的3种执行计划控制方法。在使用这些hint时，需要注意，最好只在知晓其效果后才使用。如果过度使用或误用这些hint，可能会导致SQL语句性能下降。同时也需要了解各个hint的语法和功能特点。
示例1：
原始查询语句如下：
SELECT w.worker_id, w.first_name, w.last_name, w.job_title
FROM workers w
WHERE w.job_title = 'Manager'
ORDER BY w.last_name, w.first_name;
查询计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 2005743770
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 192 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL | WORKERS | 4 | 192 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_TITLE"='Manager')
Statistics
-----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
747 bytes sent via SQL*Net to client
573 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
可以看到，执行计划是使用了全表扫描操作来执行查询。
现在我们使用FULL hint强制全表扫描，查询语句如下：
SELECT /*+ FULL(w) */ w.worker_id, w.first_name, w.last_name, w.job_title
FROM workers w
WHERE w.job_title = 'Manager'
ORDER BY w.last_name, w.first_name;
查询计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 2005743770
-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 192 | 2 (0)| 00:00:01|
| 1 | TABLE ACCESS FULL| WORKERS | 4 | 192 | 2 (0)| 00:00:01|
-------------------------------------------------------------
Statistics
-----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
747 bytes sent via SQL*Net to client
573 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
可以看到，执行计划并未发生变化，这说明本身就是最优执行计划。
示例2：
原始查询语句如下：
SELECT d.department_id, COUNT(w.worker_id)
FROM departments d
JOIN workers w ON w.department_id = d.department_id
WHERE d.department_name = 'Sales'
GROUP BY d.department_id;
查询计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 183478260
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 3 (0)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 22 | 3 (0)| 00:00:01 |
|* 2 | HASH JOIN | | 14 | 308 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENTS| 1 | 11 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| WORKERS | 107 | 2675 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Statistics
-----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
556 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，执行计划使用了HASH JOIN操作，因为这个查询需要把两个大表进行连接。
现在我们使用INLINE hint嵌入到查询语句中，查询语句如下：
SELECT /*+ INLINE(d) */ d.department_id, COUNT(w.worker_id)
FROM departments d /*+ INLINE */ JOIN workers w /*+ INLINE */
ON w.department_id = d.department_id
WHERE d.department_name = 'Sales' /*+ INLINE */
GROUP BY d.department_id;
查询计划如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 2362383159
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 3 (0)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 30 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 14 | 420 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS | WORKERS | 7 | 161 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE | WORKERS_PK | 1 | | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS | DEPARTMENTS | 2 | 22 | 1 (0)| 00:00:01 |
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPARTMENT_NAME"='Sales')
4 - access("W"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
5 - filter("D"."DEPARTMENT_NAME"='Sales')
Note
-----
- inline SQL tuning starts here
Statistics
-----------------------------------------------------------
0 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
556 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，执行计划使用了NESTED LOOPS和HASH GROUP BY操作，从而使查询更快地运行。
本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：Oracle中基于hint的3种执行计划控制方法详细介绍 - Python技术站
微信扫一扫 
支付宝扫一扫 