使用CARDINALITY这个hint，可以通过指定返回的记录数来迫使oracle改变执行计划，当优化器对某个结果集返回的行数估计出现较大偏差时，通过这个hint可以尝试让优化器产生正确的执行计划。

sys@EBANK>select a.object_name,a.object_type from a,b where a.object_id=b.object_id and b.object_id=1000;

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2826201550

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     8 |   256 |   278   (1)| 00:00:04 |
|* 1 | HASH JOIN         |       |     8 |   256 |   278   (1)| 00:00:04 |
|* 2 |   INDEX RANGE SCAN | IDX_B |     1 |     3 |     1   (0)| 00:00:01 |
|* 3 |   TABLE ACCESS FULL| A     |     8 |   232 |   276   (0)| 00:00:04 |
----------------------------------------------------------------------------

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

   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - access("B"."OBJECT_ID"=1000)
   3 - filter("A"."OBJECT_ID"=1000)

Statistics
----------------------------------------------------------
        448 recursive calls
          0 db block gets
       1050 consistent gets
          0 physical reads
          0 redo size
        569 bytes sent via SQL*Net to client
        380 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          5 sorts (memory)
          0 sorts (disk)
          8 rows processed

对于上述查询，oracle在做hash join时，优化器估计到b表返回的行数只有1，比a表返回的行数少，因此选择b作为build table，下面再看如果指定了b表返回的行数较大，优化器是如何选择的：

sys@EBANK>select /*+ cardinality(b 100000) */ a.object_name,a.object_type from a,b where a.object_id=b.object_id and b.object_id=1000;

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4182542269

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   800K|    24M|   279   (1)| 00:00:04 |
|* 1 | HASH JOIN         |       |   800K|    24M|   279   (1)| 00:00:04 |
|* 2 |   TABLE ACCESS FULL| A     |     8 |   232 |   276   (0)| 00:00:04 |
|* 3 |   INDEX RANGE SCAN | IDX_B |   100K|   292K|     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - filter("A"."OBJECT_ID"=1000)
   3 - access("B"."OBJECT_ID"=1000)

Statistics
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        994 consistent gets
          0 physical reads
          0 redo size
        569 bytes sent via SQL*Net to client
        380 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          8 rows processed

很明显，优化器选择了a表作为build table，因为b表返回的记录数要远大于a表，oracle总是选择返回记录数较少的row source作为build table。

ITPub博客

hint---CARDINALITY

最新文章