Oracle Hints-/+cardinality()/模拟表的数据量

最新推荐文章于 2024-04-30 15:29:16 发布

jxzkin

最新推荐文章于 2024-04-30 15:29:16 发布

阅读量3.6k

点赞数

CC 4.0 BY-SA版权

分类专栏： ORACLE Management

本文链接：https://blog.youkuaiyun.com/jxzkin/article/details/8308289

ORACLE Management 专栏收录该内容

78 篇文章

订阅专栏

本文通过示例解释了在 Oracle SQL 查询中使用 /*+cardinality()*/ 提示如何模拟不同数据量，以影响执行计划。通过对两个全表扫描的表进行连接操作，并调整 cardinality 的值，展示了该提示如何改变成本估算，从而优化查询性能。实验结果显示，当 cardinality 值增大时，查询成本上升，执行时间延长。

  1* select /*+cardinality(t 1000000000)*/count(*) from t,t t1 where t.object_id=t1.object_id
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 791582492

----------------------------------------------------------------------------
| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	 1 |	10 |  6216  (91)| 00:01:15 |
|   1 |  SORT AGGREGATE     |	   |	 1 |	10 |		|	   |
|*  2 |   HASH JOIN	    |	   |  1000M|  9536M|  6216  (91)| 00:01:15 |
|   3 |    TABLE ACCESS FULL| T    | 73081 |   356K|   285   (1)| 00:00:04 |
|   4 |    TABLE ACCESS FULL| T    |  1000M|  4768M|  2794  (90)| 00:00:34 |
----------------------------------------------------------------------------

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

   2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")

SQL> ed
Wrote file afiedt.buf

  1* select count(*) from t,t t1 where t.object_id=t1.object_id
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 791582492

----------------------------------------------------------------------------
| Id  | Operation	    | Name | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	 1 |	10 |   571   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE     |	   |	 1 |	10 |		|	   |
|*  2 |   HASH JOIN	    |	   | 73081 |   713K|   571   (1)| 00:00:07 |
|   3 |    TABLE ACCESS FULL| T    | 73081 |   356K|   285   (1)| 00:00:04 |
|   4 |    TABLE ACCESS FULL| T    | 73081 |   356K|   285   (1)| 00:00:04 |
----------------------------------------------------------------------------

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

   2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")