CARDINALITY HINT用法小試

select * from wxh_tbd1 a where a.object_type='TABLE' and a.object_id in ( select object_id from wxh_tbd2 where object_type='TABLE')；

Execution Plan
----------------------------------------------------------
Plan hash value: 3614068701

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |  4030 |   909K|   121   (1)| 00:00:02 |
|*  1 |  HASH JOIN RIGHT SEMI|          |  4030 |   909K|   121   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL  | WXH_TBD2 |  4026 | 96624 |    60   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL  | WXH_TBD1 |  4219 |   852K|    60   (0)| 00:00:01 |
---------------------------------------------------------------------------------

預設走了HASI JOIN。執行計劃是對的，因為object_type為table的非常多。

 

如果我想先掃描WXH_TBD2再去nest loop wxh_tbd1呢。可以透過這個CARDINALITY去解決select * from wxh_tbd1 a where a.object_type='TABLE' and a.object_id in ( select /*+ CARDINALITY(wxh_tbd2  1) */object_id from wxh_tbd2 where object_type='TABLE');

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |   231 |    63   (2)| 00:00:01 |
|   1 |  NESTED LOOPS                |          |       |       |            |          |
|   2 |   NESTED LOOPS               |          |     1 |   231 |    63   (2)| 00:00:01 |
|   3 |    SORT UNIQUE               |          |     1 |    24 |    60   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL        | WXH_TBD2 |     1 |    24 |    60   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | OJ       |     1 |       |     1   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID| WXH_TBD1 |     1 |   207 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

This hint specifies the estimated cardinality returned by a query or portions of the query. Note if no table is specified, the cardinality is the total number of rows returned by the entire query.

For example:

SELECT /*+ CARDINALITY ( [tablename] card ) */