手工更改表（列）统计信息示例

手工更改表（列）统计信息方法

1. 首先创建函数raw_to_date，将raw转换为date，因为数据字典里保存的数据是raw类型
   

SELECT low_value, low_value low_value_actual, high_value, high_value high_value_actual

FROM user_tab_col_statistics

WHERE table_name = ‘CC_SERVICE_ORDER_ASK_HIS_TEST’

AND column_name = ‘ACCEPT_DATE’;

SQL> create or replace function raw_to_date(rawval raw)

2 return date

3 as

4 v date;

5 begin

6 dbms_stats.convert_raw_value(rawval,v);

7 return v;

8 end;

9 /

Function created

采用raw_to_date转换后，为

SELECT low_value, raw_to_date(low_value) low_value_actual, high_value, raw_to_date(high_value) high_value_actual

FROM user_tab_col_statistics

WHERE table_name = ‘CC_SERVICE_ORDER_ASK_HIS_TEST’

AND column_name = ‘ACCEPT_DATE’;

1. 创建测试表及索引
   

SQL> create table CC_SERVICE_ORDER_ASK_HIS_TEST as select * from CC_SERVICE_ORDER_ASK_HIS where rownum<10000;

Table created

—创建基于accept_date列的索引

SQL> create index ind_accept_date on CC_SERVICE_ORDER_ASK_HIS_TEST(accept_date);

Index created

—创建基于prod_num列的索引

SQL> create index ind_prod_num on CC_SERVICE_ORDER_ASK_HIS_TEST(prod_num);

Index created

1. 收集表统计信息
   

SQL> analyze table CC_SERVICE_ORDER_ASK_HIS_TEST compute statistics;

Table analyzed

1. 查看列accept_date的统计信息
   

SELECT low_value, raw_to_date(low_value) low_value_actual, high_value, raw_to_date(high_value) high_value_actual

FROM user_tab_col_statistics

WHERE table_name = ‘CC_SERVICE_ORDER_ASK_HIS_TEST’

AND column_name = ‘ACCEPT_DATE’;

可以看到列accept_date的最大值为2011/1/1 19:31:57，

1. 此时如果sql的where条件里包含accept_date>:1和prod_num=:2，由于列prod_num的可选择性高，理论上走ind_prod_num索引为最佳选择。
   

1. 如果我们手工更改accept_date列的统计信息，使得accept_date的值在2011/1/1 18:31:57之前
   

SQL> DECLARE

2 srec DBMS_STATS.STATREC;

3 v_distcnt NUMBER;

4 v_density NUMBER;

5 v_nullcnt NUMBER;

6 v_avgclen NUMBER;

7 numvals DBMS_STATS.NUMARRAY;

8 charvals DBMS_STATS.CHARARRAY;

9 datevals DBMS_STATS.DATEARRAY;

10 begin

11 DBMS_STATS.get_column_stats (ownname => ‘HNCCSACCESS’,

12 tabname => ‘CC_SERVICE_ORDER_ASK_HIS_TEST’,

13 colname => ‘ACCEPT_DATE’,

14 distcnt => v_distcnt,

15 density => v_density,

16 nullcnt => v_nullcnt,

17 srec => srec,

18 avgclen => v_avgclen

19 );

20 datevals := DBMS_STATS.datearray (to_date(‘20110101 02:00:00′,’yyyymmdd hh24:mi:ss’), to_date(‘20110101 16:00:00′,’yyyymmdd hh24:mi:ss’));

21 DBMS_STATS.prepare_column_values (srec, datevals);

22 DBMS_STATS.set_column_stats (ownname => ‘HNCCSACCESS’,

23 tabname => ‘CC_SERVICE_ORDER_ASK_HIS_TEST’,

24 colname => ‘ACCEPT_DATE’,

25 distcnt => v_distcnt,

26 density => v_density,

27 nullcnt => v_nullcnt,

28 srec => srec,

29 avgclen => v_avgclen

30 );

31 COMMIT;

32 END;

33 /

PL/SQL procedure successfully completed

此时查询数据字典里列accept_date的统计信息，最小值为2011/1/1 2:00:00，而最大值则为2011/1/1 16:00:00

SELECT low_value, raw_to_date(low_value) low_value_actual, high_value, raw_to_date(high_value) high_value_actual

FROM user_tab_col_statistics

WHERE table_name = ‘CC_SERVICE_ORDER_ASK_HIS_TEST’

AND column_name = ‘ACCEPT_DATE’;

1. 此时我们再看刚刚sql的执行计划，发现发生了改变，Oracle认为ind_accept_date索引才是最佳选择，因为数据字典表里的统计信息表明该索引根本就没有符合条件的数据（实际上是有的）。新的sql plan如下：
   

1. 因此对于类似的where条件中带有时间条件索引列，则可手工更改时间列的统计信息，来控制sql是否走时间列索引，从而改变sql的plan

未经允许不得转载：SRE空间 » 手工更改表（列）统计信息示例