[疑问]使用histogram的目的

Yong Huang

> 这么说来， 那我们只需要在索引列上建立HISTOGRAM,
> 但是，我发现ORACLE 默认在非索引列上也有HISTOGRAM。

Good question. 玉面飞龙  or sundog315, what do you think?

Yong Huang

sundog315

原帖由 Yong Huang 于 2010-10-27 00:42 发表
> 这么说来， 那我们只需要在索引列上建立HISTOGRAM,
> 但是，我发现ORACLE 默认在非索引列上也有HISTOGRAM。

Good question. 玉面飞龙  or sundog315, what do you think?

Yong Huang

直方图不仅用在索引选择度分析上。而且，对于cardinality的分析也起到关键性作用。而cardinality的估算会影响到表的连接方式：

SUNDOG315>create table t(id integer,text varchar2(20));

表已创建。

SUNDOG315>create table x(id integer,text varchar2(20));

表已创建。

SUNDOG315>insert into t select 1,'aaa' from dba_objects;

已创建55096行。

SUNDOG315>insert into t select 1,'bbb' from dual;

已创建 1 行。

SUNDOG315>insert into x select 1,'bbb' from dba_objects where rownum<100;

已创建99行。

SUNDOG315>commit;

提交完成。

SUNDOG315>select id,text,count(*) from t group by id,text;

        ID TEXT                   COUNT(*)
---------- -------------------- ----------
         1 aaa                       55096
         1 bbb                           1

SUNDOG315>select id,text,count(*) from x group by id,text;

        ID TEXT                   COUNT(*)
---------- -------------------- ----------
         1 bbb                          99

SUNDOG315>create index t_idx on t(id);

索引已创建。

SUNDOG315>create index x_idx on x(id);

索引已创建。

SUNDOG315>alter session set optimizer_dynamic_sampling=0;

会话已更改。

SUNDOG315>exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for all indexed columns size 254',cascade=>true);

PL/SQL 过程已成功完成。

SUNDOG315>exec dbms_stats.gather_table_stats(user,'X',null,null,method_opt=>'for all indexed columns size 254',cascade=>true);

PL/SQL 过程已成功完成。

SUNDOG315>set autot trace exp stat
SUNDOG315>select * from t,x where t.id=x.id and t.text=x.text and t.text='bbb';

已选择99行。


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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   545 |  7630 |    31   (4)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   545 |  7630 |    31   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| X    |     1 |     7 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T    |   551 |  3857 |    27   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("T"."ID"="X"."ID" AND "T"."TEXT"="X"."TEXT")
   2 - filter("X"."TEXT"='bbb')
   3 - filter("T"."TEXT"='bbb')


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

SUNDOG315>exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for all columns size 254',cascade=>true);

PL/SQL 过程已成功完成。

SUNDOG315>exec dbms_stats.gather_table_stats(user,'X',null,null,method_opt=>'for all columns size 254',cascade=>true);

PL/SQL 过程已成功完成。

SUNDOG315>select * from t,x where t.id=x.id and t.text=x.text and t.text='bbb';

已选择99行。


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

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

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time
     |

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

|   0 | SELECT STATEMENT             |       |    99 |  1386 |    28   (0)| 00:0
0:01 |

|   1 |  NESTED LOOPS                |       |       |       |            |
     |

|   2 |   NESTED LOOPS               |       |    99 |  1386 |    28   (0)| 00:0
0:01 |

|*  3 |    TABLE ACCESS FULL         | T     |     1 |     7 |    27   (0)| 00:0
0:01 |

|*  4 |    INDEX RANGE SCAN          | X_IDX |    99 |       |     0   (0)| 00:0
0:01 |

|*  5 |   TABLE ACCESS BY INDEX ROWID| X     |    99 |   693 |     1   (0)| 00:0
0:01 |

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


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

   3 - filter("T"."TEXT"='bbb')
   4 - access("T"."ID"="X"."ID")
   5 - filter("X"."TEXT"='bbb')


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        106  consistent gets
          0  physical reads
          0  redo size
       1896  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

SUNDOG315>

Yong Huang

Thanks for the excellent demo.

I wonder why the index range scan in step 4 has a cost of 0? Even for a unique scan, the cost normally should not be 0. We humans knows that given this data set, the index match "T"."ID"="X"."ID" is completely useless (because they're all 1's). But Oracle doesn't know and checks each row. But I'm surprised the cost assignment is zero.

Yong Huang