商云方的技术博客 (沪ICP备14046774号-1)

在《Oracle 11g Performance tunning guider 》对于扩展统计信息-列组合的描述，大意为：

假设一张表已经收集了列的直方图，那么：

如果 查询条件是： Where  column1 =XXX; 那么oracle 可根据Column1上的直方图预估返回的行数；

如果 查询条件是： Where  column2 =XXX; 那么oracle 可根据Column2上的直方图预估返回的行数;

那么如果查询条件是个组合条件，比如Where Column1=XXX and Column2 = YYY; 那么Oracle 就无法知道如何预估返回的行数了；  所以呢，Oracle 就提供了扩展的统计信息-列组合统计信息；

看下面的实验：

单列条件 sold_to_org_id =5331 的预估返回行数66

单列条件 invoice_to_org_id=3662 的预估返回行数1159

目前并不存在组合列的统计信息：

看组合列查询条件 的预估返回行数，取的是两个单列查询条件中最小的一个：

 

为列组合统计信息：

–yufang.shang@hand-china.com
–统计列直方图信息
begin
  DBMS_STATS.GATHER_TABLE_STATS(‘ONT’,
        ‘OE_ORDER_HEADERS_ALL’,
        METHOD_OPT => ‘FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (SOLD_TO_ORG_ID,INVOICE_TO_ORG_ID) SIZE 250’);
end;

 

 

 

 

 

再次看组合查询的执行计划：

跟没有收集扩展的列组合统计信息前相比： Table Access By Index Rowid 的预估返回行数有变化，从9变成了2；

而实际上，这句SQL语句的返回行数是0； 只能说是预估更准了点；

 

此功能的实用价值有待遇与进一步探讨……

 

附录：本实验相关SQL：

================================================

select count(1)
  from oe_order_headers_all
where sold_to_org_id = 5331
 
select count(1)
  from oe_order_headers_all
where  INVOICE_TO_ORG_ID =3662

select count(1)
  from oe_order_headers_all
where sold_to_org_id = 5331
and  INVOICE_TO_ORG_ID =3662

–统计列直方图信息
begin
  DBMS_STATS.GATHER_TABLE_STATS(‘ONT’,
        ‘OE_ORDER_HEADERS_ALL’,
        METHOD_OPT => ‘FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (SOLD_TO_ORG_ID,INVOICE_TO_ORG_ID) SIZE 250’);
end;

–查看创建的列组合
SELECT EXTENSION_NAME, EXTENSION
  FROM DBA_STAT_EXTENSIONS
WHERE TABLE_NAME = ‘OE_ORDER_HEADERS_ALL’;
–删除列组合
begin
DBMS_STATS.DROP_EXTENDED_STATS(‘ONT’,’OE_ORDER_HEADERS_ALL’,
‘(SOLD_TO_ORG_ID,INVOICE_TO_ORG_ID)’);
end;

–查看列组合直方图信息
SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
  FROM DBA_STAT_EXTENSIONS e, DBA_TAB_COL_STATISTICS t
WHERE e.EXTENSION_NAME = t.COLUMN_NAME
   AND e.TABLE_NAME = t.TABLE_NAME
   AND t.TABLE_NAME = ‘OE_ORDER_HEADERS_ALL’;
  

–查看列组合的直方图
select *
  from DBA_TAB_HISTOGRAMS
where table_name = ‘OE_ORDER_HEADERS_ALL’
   and column_name = ‘SYS_STUC772WO5L42XA96DEQCKUGBS’