disable or 扩展

最新推荐文章于 2022-04-28 21:13:22 发布

转载最新推荐文章于 2022-04-28 21:13:22 发布 · 99 阅读

本文通过对比两种不同SQL提示符下的执行计划，展示了在使用特定提示符时如何影响数据库的查询方式。具体而言，通过use_concat提示符，原本的IN列表被拆分为多个单独的查询，每个查询分别针对列表中的每一个值，这导致了执行计划中出现多次的表访问操作。

SQL> select /*+ use_concat */ * from hao2 where object_id=1 or object_id=2 or object_id=3;

执行计划
----------------------------------------------------------
Plan hash value: 2739802529

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

3 - access("OBJECT_ID"=1 OR "OBJECT_ID"=2 OR "OBJECT_ID"=3)

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          9 consistent gets
          0 physical reads
          0 redo size
       1249 bytes sent via SQL*Net to client
        338 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          2 rows processed

SQL> alter session set events '10157 trace name context forever';

会话已更改。

SQL> select /*+ use_concat */ * from hao2 where object_id=1 or object_id=2 or object_id=3;

执行计划
----------------------------------------------------------
Plan hash value: 1202901203

----------------------------------------------------------------------------------------
| Id | Operation                    | Name    | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     3 |   282 |     6   (0)| 00:00:01 |
|   1 | CONCATENATION               |         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HAO2    |     1 |    94 |     2   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | HAO2IND |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| HAO2    |     1 |    94 |     2   (0)| 00:00:01 |
|* 5 |    INDEX RANGE SCAN          | HAO2IND |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| HAO2    |     1 |    94 |     2   (0)| 00:00:01 |
|* 7 |    INDEX RANGE SCAN          | HAO2IND |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - access("OBJECT_ID"=1)
   5 - access("OBJECT_ID"=3)
   7 - access("OBJECT_ID"=2)