统计分析中extended statistics的作用

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 |
--------------------------------------------------------------------------------

最后

以上就是优美戒指为你收集整理的统计分析中extended statistics的作用的全部内容，希望文章能够帮你解决统计分析中extended statistics的作用所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。