柱状图信息案例一则（2）

最新推荐文章于 2021-04-11 10:51:41 发布

cujiaowu9180

最新推荐文章于 2021-04-11 10:51:41 发布

阅读量151

点赞数

文章标签：数据库

create table test as select * from dba_objects;
--更新统计信息，不含柱状图
SQL> exec dbms_stats.gather_table_stats(user,'test',method_opt => 'for all columns size 1');

PL/SQL 过程已成功完成。

SQL> select owner, max(object_id) object_id from test where wner='SYS' group by owner
2 ;

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

-----------------------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    11 |    51   (0)| 00:00:01 |
|   1 | SORT GROUP BY NOSORT|      |     1 |    11 |    51   (0)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL | TEST | 1404 | 15444 |    51   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - filter("OWNER"='SYS')

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        217 consistent gets
          0 physical reads
          0 redo size
        474 bytes sent via SQL*Net to client
        400 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

--更新统计信息，含柱状图信息

SQL> exec dbms_stats.gather_table_stats(user,'TEST',method_opt =>'for columns owner size 254');

PL/SQL 过程已成功完成。

SQL> select owner, max(object_id) object_id from test where wner='SYS' group by owner;

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

-----------------------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 6751 | 74261 |    51   (0)| 00:00:01 |
|   1 | SORT GROUP BY NOSORT|      | 6751 | 74261 |    51   (0)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL | TEST | 6751 | 74261 |    51   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - filter("OWNER"='SYS')

统计信息
----------------------------------------------------------
          1 recursive calls
          0 db block gets
        217 consistent gets
          0 physical reads
          0 redo size
        474 bytes sent via SQL*Net to client
        400 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

注意红字部分，出现了严重的错误，这在单独执行时也没什么，在关联查询时可能出现影响选择索引的情况，如下例：

SQL> select distinct a.owner, a.object_id
2    from test a,
3         (select owner, max(object_id) object_id from test
4          where wner='SYS'
5          group by owner) b
6   where a.owner = b.owner
7     and a.owner='SYS'
8     and a.object_id = b.object_id;

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

--------------------------------------------------------------------------------
---------

--------------------------------------------------------------------------------
---------

| 0 | SELECT STATEMENT | | 1 | 30 | 54 (2)| 0
0:00:01 |

| 1 | HASH UNIQUE | | 1 | 30 | 54 (2)| 0
0:00:01 |

|* 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 11 | 2 (0)| 0
0:00:01 |

| 3 | NESTED LOOPS | | 1 | 30 | 53 (0)| 0
0:00:01 |

| 4 | VIEW | | 1 | 19 | 51 (0)| 0
0:00:01 |

| 5 | HASH GROUP BY | | 1 | 17 | 51 (0)| 0
0:00:01 |

|* 6 | TABLE ACCESS FULL | TEST | 1404 | 23868 | 51 (0)| 0
0:00:01 |

|* 7 | INDEX RANGE SCAN | I_TEST_1 | 1 | | 1 (0)| 0
0:00:01 |

--------------------------------------------------------------------------------
---------

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

   2 - filter("A"."OWNER"='SYS')
   6 - filter("OWNER"='SYS')
   7 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats(user,'test',method_opt => 'for all columns size 25

PL/SQL 过程已成功完成。

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

------------------------------------------------------------------------------
| Id | Operation             | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 2705 | 81150 |   104   (2)| 00:00:02 |
|   1 | HASH UNIQUE          |      | 2705 | 81150 |   104   (2)| 00:00:02 |
|* 2 |   HASH JOIN           |      | 2705 | 81150 |   103   (1)| 00:00:02 |
|* 3 |    TABLE ACCESS FULL | TEST | 6751 | 74261 |    51   (0)| 00:00:01 |
|   4 |    VIEW               |      | 6751 |   125K|    51   (0)| 00:00:01 |
|   5 |     HASH GROUP BY     |      | 6751 |   112K|    51   (0)| 00:00:01 |
|* 6 |      TABLE ACCESS FULL| TEST | 6751 |   112K|    51   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   2 - access("A"."OWNER"="B"."OWNER" AND
              "A"."OBJECT_ID"="B"."OBJECT_ID")
   3 - filter("A"."OWNER"='SYS')
   6 - filter("OWNER"='SYS')

在oracle 新版本11G上，这个问题已经得到修正

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27378/viewspace-628268/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27378/viewspace-628268/