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 ) */
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-694500/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-694500/