统计分析中extended statistics的作用

最新推荐文章于 2024-07-12 23:00:00 发布

mercenarylin

最新推荐文章于 2024-07-12 23:00:00 发布

阅读量2.3k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/mercenarylin/article/details/8672814

版权

oracle 专栏收录该内容

48 篇文章 1 订阅

订阅专栏

Oracle Database can also gather statistics on a group of columns within a table 
or an expression on a column
sh@ORCL> exec dbms_stats.gather_table_stats(user,'CUSTOMERS');
PL/SQL 过程已成功完成。

sh@ORCL> set autotrace on exp;
--此时没有进行extended statistics分析，执行计划估算行数与实际相差很大
sh@ORCL> SELECT COUNT(*)
  2  FROM   customers
  3  WHERE  cust_state_province = 'CA'
  4  AND    country_id=52790;

  COUNT(*)
----------
      3341

执行计划
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    16 |   406   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |           |     1 |    16 |            |          |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  1256 | 20096 |   406   (1)| 00:00:05 |
--------------------------------------------------------------------------------

sh@ORCL> SELECT COUNT(*)
  2  FROM   sh.customers
  3  WHERE  cust_state_province = 'CA';

  COUNT(*)
----------
      3341

执行计划
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    11 |   406   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |           |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  3631 | 39941 |   406   (1)| 00:00:05 |
--------------------------------------------------------------------------------

sh@ORCL> call DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS',METHOD_OPT =>
'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS(CUST_STATE_PROVINCE,COUNTRY_ID) 
SIZE SKEWONLY');
调用完成。
--进行extended statistics分析后，执行计划估算与实际相差很小
sh@ORCL> SELECT COUNT(*)
  2  FROM   customers
  3  WHERE  cust_state_province = 'CA'
  4  AND    country_id=52790;

  COUNT(*)
----------
      3341

执行计划
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    16 |   406   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |           |     1 |    16 |            |          |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |  3331 | 53296 |   406   (1)| 00:00:05 |
--------------------------------------------------------------------------------