ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle转换user_tab_columns中high_value值为十进制

Oracle转换user_tab_columns中high_value值为十进制

原创 Linux操作系统 作者:shuangoracle 时间:2012-07-09 18:00:53 0 删除 编辑
今天本想计算下基于范围的索引选择率,但是通过user_tab_columns查询出来low_value和high_value结果是raw类型的,而且我们知道它是截取了后32位,
如果我想知道他们代表确切的十进制数将是什么呢?折腾了半天从网上查到计算方法(以前用过忘了),记录下备用:
从user_tab_columns查出的结果:
SQL> select table_name,column_name,low_value,high_value from user_tab_columns where table_name='T1' and column_name='CLUSTERED';
TABLE COLUMN_NAM LOW_VALUE  HIGH_VALUE
----- ---------- ---------- ----------
T1    CLUSTERED  80         C164
实际最大最小值是这样的:
SQL> select min(CLUSTERED) min_value,max(CLUSTERED) max_value from t1;
 MIN_VALUE  MAX_VALUE
---------- ----------
         0         99
         
用utl_raw包的cast_to_number函数进行类型转换如下:
SQL> select table_name,
  2         column_name,
  3         utl_raw.cast_to_number(low_value) low,
  4         utl_raw.cast_to_number(high_value) hight
  5    from user_tab_col_statistics
  6   where table_name = 'T1'
  7     and column_name = 'CLUSTERED';
TABLE COLUMN_NAM        LOW      HIGHT
----- ---------- ---------- ----------
T1    CLUSTERED           0         99
ok,和表中时间最小最大值一致。

low_value和high_value对优化器选择执行计划有什么影响?下面这个测试有点白痴,统计信息没有变,Oracle很自然地选择了和之前一样的执行计划喽。
测试数据:
create table t(id,name)
as select rownum rn,'A' from dual connect by level<=10000;
create index idx_t_id on t(ID);
analyze table t compute statistics for table for all indexes for all indexed columns;
SQL> select table_name,column_name,high_value,low_value from user_tab_columns where table_name='T' and column_name='ID';

TABLE COLUMN_NAM HIGH_VALUE LOW_VALUE
----- ---------- ---------- ----------
T     ID         C302       C102
插一句:是不是好奇high_value和low_value怎么算的?
SQL> select upper(replace(substr(dump(min(id), 16, 0, 32),
  2                instr(dump(min(id), 16, 0, 32), ':', 1) + 1),',','0')) low_value,
  3         upper(replace(substr(dump(max(id), 16, 0, 32),
  4                instr(dump(max(id), 16, 0, 32), ':', 1) + 1),',','0')) high_value
  5   from t;
LOW_VALUE  HIGH_VALUE
---------- ----------
 C102       C302
 
即在原先最小值基础上取dump,转换为16进制,然后取前32位数据。将取得数据逗号替换为0即可。
SQL> select table_name,
  2         column_name,
  3         utl_raw.cast_to_number(low_value) low,
  4         utl_raw.cast_to_number(high_value) hight
  5    from user_tab_col_statistics
  6   where table_name = 'T' and column_name='ID';
TABLE COLUMN_NAM        LOW      HIGHT
----- ---------- ---------- ----------
T     ID                  1      10000

当id<100时走的是索引范围扫描:
SQL> select  * from t where id<100;
已选择99行。

执行计划
----------------------------------------------------------
Plan hash value: 514881935
------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    99 |   594 |     3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |    99 |   594 |     3 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_ID |    99 |       |     2 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"<100)
   
SQL> delete from t where id>=101;
已删除9900行。
SQL> commit;
提交完成。
如下查询说证明明统计信息没有变:
SQL> select table_name,
  2         column_name,
  3         utl_raw.cast_to_number(low_value) low,
  4         utl_raw.cast_to_number(high_value) hight
  5    from user_tab_col_statistics
  6   where table_name = 'T' and column_name='ID';
TABLE COLUMN_NAM        LOW      HIGHT
----- ---------- ---------- ----------
T     ID                  1      10000
SQL> select  * from t where id<100;
已选择99行。

执行计划
----------------------------------------------------------
Plan hash value: 514881935
------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    99 |   594 |     3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |    99 |   594 |     3 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_ID |    99 |       |     2 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"<100)
可以看到海是走了索引范围扫描,而此时表里只剩下100个数据而已。

重新收集下统计信息看看:
SQL> analyze table t compute statistics for table for all indexes for all indexed columns;
表已分析。
SQL> select table_name,
  2         column_name,
  3         utl_raw.cast_to_number(low_value) low,
  4         utl_raw.cast_to_number(high_value) hight
  5    from user_tab_col_statistics
  6   where table_name = 'T' and column_name='ID';
TABLE COLUMN_NAM        LOW      HIGHT
----- ---------- ---------- ----------
T     ID                  1        100
ok统计信息已经变了。
SQL> select  * from t where id<100;
已选择99行。

执行计划
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |   500 |     4 |
|*  1 |  TABLE ACCESS FULL| T    |   100 |   500 |     4 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"<100)
执行计划变了。

也就是说high_value和low_value反应了该列的最大最小值。
此例说明,如果数据改变了而不及时收集统计信息,很可能会造成优化器选择错误的执行计划。

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

请登录后发表评论 登录
全部评论

注册时间:2010-08-25

  • 博文量
    84
  • 访问量
    207063