索引cost算法初探（接上）

最新推荐文章于 2022-09-20 16:50:35 发布

jgmydsai

最新推荐文章于 2022-09-20 16:50:35 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/jgmydsai/article/details/12572825

本文介绍了使用Oracle数据库连接和执行SQL语句的基本操作，并通过更新表数据和创建索引来优化表性能。同时，演示了如何使用PL/SQL进行表统计信息收集和计划解释，最后计算了索引和表的选择性以及成本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as test@192.168.1.118:1521/orcl

SQL> 
SQL> DROP TABLE t PURGE;
Table dropped
SQL> create table t as select * from dba_objects;
Table created
SQL> create index idx_t on t(object_id);
Index created
SQL> UPDATE t SET object_id = 500 WHERE object_id <= 5000 AND object_id >= 1000;
3989 rows updated
SQL> UPDATE t SET object_id = NULL WHERE object_id < 800 AND object_id >= 400;
4367 rows updated
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  /
PL/SQL procedure successfully completed
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 (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   882 |  9702 |    17   (0)| 00:00
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   882 |  9702 |    17   (0)| 00:00
|*  2 |   INDEX RANGE SCAN          | IDX_T |   882 |       |     3   (0)| 00:00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"<1000)
14 rows selected
SQL> SET serveroutput ON
SQL> DECLARE
  2    v_blevel                      user_indexes.blevel%TYPE;
  3    v_leaf_blocks                 user_indexes.leaf_blocks%TYPE;
  4    v_effective_index_selectivity NUMBER(18,4);
  5    v_clustering_factor           user_indexes.clustering_factor%TYPE;
  6    v_effective_table_selectivity NUMBER(18,4);
  7    v_num_rows                    user_tables.num_rows%TYPE;
  8    v_num_distinct                user_tab_col_statistics.num_distinct%TYPE;
  9    v_num_nulls                   user_tab_col_statistics.num_nulls%TYPE;
 10    v_high_value                  PLS_INTEGER;
 11    v_low_value                   PLS_INTEGER;
 12    v_cost                        PLS_INTEGER;
 13    v_selected_rows               PLS_INTEGER;
 14    v_count                       NUMBER := 1000;
 15  BEGIN
 16    SELECT leaf_blocks, blevel, clustering_factor
 17      INTO v_leaf_blocks, v_blevel, v_clustering_factor
 18      FROM user_indexes
 19     WHERE index_name = 'IDX_T';
 20  
 21    SELECT b.num_rows,
 22           a.num_distinct,
 23           a.num_nulls,
 24           utl_raw.cast_to_number(high_value) high_value,
 25           utl_raw.cast_to_number(low_value) low_value
 26      INTO v_num_rows, v_num_distinct, v_num_nulls, v_high_value, v_low_value
 27      FROM user_tab_col_statistics a, user_tables b
 28     WHERE a.table_name = b.table_name
 29       AND a.table_name = upper('T')
 30       AND a.column_name = 'OBJECT_ID';
 31  
 32    v_effective_index_selectivity := ((v_count - v_low_value) / (v_high_value - v_low_value));
 33    v_selected_rows               := ceil(((v_count - v_low_value) / (v_high_value - v_low_value)) * (v_num_rows - v_num_nulls));
 34    v_effective_table_selectivity := ((v_count - v_low_value) / (v_high_value - v_low_value));
 35  
 36    v_cost := v_blevel +
 37              ceil(v_leaf_blocks * (v_count - v_low_value) / (v_high_value - v_low_value) +
 38              ceil(v_clustering_factor * (v_count - v_low_value) / (v_high_value - v_low_value)));
 39  
 40    dbms_output.put_line('leaf_blocks==>' || v_leaf_blocks);
 41    dbms_output.put_line('blevel==>' || v_blevel);
 42    dbms_output.put_line('clustering_factor==>' || v_clustering_factor);
 43    dbms_output.put_line('v_high_value==>' || v_num_rows);
 44    dbms_output.put_line('high_value==>' || v_high_value);
 45    dbms_output.put_line('low_value==>' || v_low_value);
 46    dbms_output.put_line('num_rows==>' || v_num_rows);
 47    dbms_output.put_line('num_nulls==>' || v_num_nulls);
 48    dbms_output.put_line('selected_rows==>' || v_selected_rows);
 49    dbms_output.put_line('effective_index_selectivity==>' || v_effective_index_selectivity);
 50    dbms_output.put_line('effective_table_selectivity==>' || v_effective_table_selectivity);
 51    dbms_output.put_line('cost==>' || v_cost);
 52  END;

 53  /
leaf_blocks==>155
blevel==>1
clustering_factor==>1072
v_high_value==>72925
high_value==>77556
low_value==>2
num_rows==>72925
num_nulls==>4368
selected_rows==>883
effective_index_selectivity==>.0129
effective_table_selectivity==>.0129
cost==>17
PL/SQL procedure successfully completed