文档performance tuning 第299页有段话先记录下来;

最新推荐文章于 2025-09-30 10:05:32 发布

原创最新推荐文章于 2025-09-30 10:05:32 发布 · 907 阅读

0 ·

CC 4.0 BY-SA版权

本文深入探讨SQL查询优化器在选择执行计划中连接方式时考虑的因素，包括利用unique和primary key约束进行优化，特别关注了在不同类型的连接（如内连接、外连接和子查询）下优化器的选择策略及其背后的逻辑。

--查询优化器如何选择执行计划中的连接方式？

The Query Optimizer 11-23

How the Query Optimizer Chooses Execution Plans for Joins

The query optimizer considers the following when choosing an execution plan:

--大意是说对于含有unique,primary key的列，在做表连接时会先将此类表放在表连接顺序的第一位然后再做剩余表的连接；

■The optimizer first determines whether joining two or more tables definitely results in a row source containing at most one row. The optimizer recognizes such situations based on UNIQUE and PRIMARY KEY constraints on the tables. If such a situation exists, then the optimizer places these tables first in the join order. The optimizer then optimizes the join of the remaining set of tables.

--但对于这段就不能很好理解！

■For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule. Similarly, when a subquery has been converted into an antijoin or semijoin, the tables from the subquery must come after those tables in the outer query block to which they were connected or correlated. However, hash antijoins and semijoins are able to override this ordering condition in certain circumstances.

SQL> select count(1) from t1;

  COUNT(1)
----------
     72620

SQL> select count(1) from t3;

  COUNT(1)
----------
         2

SQL> select t1.status,t3.status 
  2  from t1,t3
  3  where t1.object_id(+)=t3.object_id
  4  /


Execution Plan
----------------------------------------------------------
Plan hash value: 2157823540

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     2 |    72 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |           |     2 |    72 |     7   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | T3        |     2 |    36 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T1        |     1 |    18 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_T1_ID |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

SQL> select t1.status,t3.status 
  2  from t1,t3
  3  where t1.object_id=t3.object_id(+)
  4  /

72620 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3402958174

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 79534 |  2796K|   294   (1)| 00:00:04 |
|*  1 |  HASH JOIN RIGHT OUTER|      | 79534 |  2796K|   294   (1)| 00:00:04 |
|   2 |   TABLE ACCESS FULL   | T3   |     2 |    36 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | T1   | 79534 |  1398K|   291   (1)| 00:00:04 |
------------------------------------------------------------------------------

--无论hash还是nl,都是T1跟在T3后面；
--换成子查询格式看看，文档意思是说子查询块要跟在外查询块，可结果确是先执行了T3再执行外查询块的T1，跟文档相反感觉；
SQL> select t1.status
  2  from t1 
  3  where     exists (select 1 from t3 where t1.object_id=t3.object_id);


Execution Plan
----------------------------------------------------------
Plan hash value: 2149433430

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     2 |    62 |     6  (17)| 00:00:01 |
|   1 |  NESTED LOOPS                |           |       |       |            |          |
|   2 |   NESTED LOOPS               |           |     2 |    62 |     6  (17)| 00:00:01 |
|   3 |    SORT UNIQUE               |           |     2 |    26 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | T3        |     2 |    26 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_T1_ID |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| T1        |     1 |    18 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------