ITPub博客

首页 > Linux操作系统 > Linux操作系统 > CARDINALITY HINT用法小试

CARDINALITY HINT用法小试

原创 Linux操作系统 作者:wei-xh 时间:2011-05-05 13:23:55 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2318529