• Oracle预估的基数算法


    SQL> create table t as select * from dba_objects;  
      
    Table created.  
      
    SQL> create index idx_t on t(object_id);  
      
    Index created.  
    
    
    SQL> BEGIN  
      2    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'TEST',  
      3                                  tabname          => 'T',  
      4                                  estimate_percent => 100,  
      5                                  method_opt       => 'for all columns size auto',  
      6                                  degree           => DBMS_STATS.AUTO_DEGREE,  
      7                                  cascade          => TRUE);  
      8  END;  
      9  /  
    
    
    SQL> alter session set optimizer_features_enable='9.2.0';
    
    Session altered.
    
    
    SQL> explain plan for  select owner from t where object_id<1000;  
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display());
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    --------------------------------------------
    Plan hash value: 1594971208
    
    ---------------------------------------------------------------------
    | Id  | Operation		    | Name  | Rows  | Bytes | Cost  |
    ---------------------------------------------------------------------
    |   0 | SELECT STATEMENT	    |	    |	958 | 10538 |	 26 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T     |	958 | 10538 |	 26 |
    |*  2 |   INDEX RANGE SCAN	    | IDX_T |	958 |	    |	  4 |
    ---------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("OBJECT_ID"<1000)
    
    Note
    -----
       - cpu costing is off (consider enabling it)
    
    18 rows selected.
    
    
    那么这个958 Oracle是怎么估算的呢?
    Oracle预估的基数等于有效选择性*(num_rows-num_nulls)
    
    其中 有效选择性 ,< 的有效选择性算法为:
    
    (limit-low_value)/(high_value-low_value)
    
    
    
    
    set linesize 200
    SQL> select b.num_rows,
           a.num_distinct,
           a.num_nulls,
           utl_raw.cast_to_number(high_value) high_value,
           utl_raw.cast_to_number(low_value) low_value,
           (b.num_rows - a.num_nulls) "NUM_ROWS-NUM_NULLS",
           utl_raw.cast_to_number(high_value) -
           utl_raw.cast_to_number(low_value) "HIGH_VALUE-LOW_VALUE"
      from dba_tab_col_statistics a, dba_tables b
     where a.owner = b.owner
       and a.table_name = b.table_name
       and a.owner = 'TEST'
       and a.table_name = upper('T')
       and a.column_name = 'OBJECT_ID';  2    3    4    5    6    7    8    9   10   11   12   13   14  
    
      NUM_ROWS NUM_DISTINCT  NUM_NULLS HIGH_VALUE  LOW_VALUE NUM_ROWS-NUM_NULLS HIGH_VALUE-LOW_VALUE
    ---------- ------------ ---------- ---------- ---------- ------------------ --------------------
         73964	  73964 	 0	77085	       2	      73964		   77083
    
    
    那么估算为:
    SQL> 
    select ceil((1000-2)/77083*73964) from dual;SQL> 
    
    CEIL((1000-2)/77083*73964)
    --------------------------
    		       958
    
    Oracle 就是根据这个算法的
    

  • 相关阅读:
    2016年总结,不一样的2016
    appium 遇到的坑
    Python xml 解析百度糯米信息
    Python 3.4 链接mysql5.7 数据库使用方法
    python3.x爬取美团信息
    基于python3的手机号生成脚本
    python3.x 学习心得
    H3C SNMP OID
    jython获取was5.1的jvm监控参数
    使用Jyhon脚本和PMI模块监控WAS性能数据
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13352414.html
Copyright © 2020-2023  润新知