本文共 2682 字,大约阅读时间需要 8 分钟。
Oracle表访问调优——位图索引和单值查询
看下面一个示例:
在Oracle 自带的Sample Schema SH中,做一个单值查询后,获得如下的执行计划:
SELECT MAX(cust_credit_limit),
COUNT(*) FROM customers WHERE cust_year_of_birth=1913;
确认CUSTOMERS表上的索引情况:
SELECT INDEX_NAME,
INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE FROM user_indexes WHERE table_name=upper('customers') ORDER BY index_type ASC;在CUSTOMERS表上有一个BITMAP类型的索引,名字是:CUSTOMERS_YOB_BIX
这个索引创建在COSTOMERS表的哪个列上呢?
可以通过下面的USER_IND_COLUMNS 视图获得表中列上的索引信息:
SQL> col index_name format a50;SQL> col table_name format a20;SQL> col column_name format a20;SQL> select index_name,table_name,column_name 2 from user_ind_columns 3 where table_name = upper('customers') 4 order by index_name asc;INDEX_NAME TABLE_NAME COLUMN_NAME-------------------------------------------------- -------------------- --------------------CUSTOMERS_GENDER_BIX CUSTOMERS CUST_GENDERCUSTOMERS_MARITAL_BIX CUSTOMERS CUST_MARITAL_STATUSCUSTOMERS_PK CUSTOMERS CUST_IDCUSTOMERS_YOB_BIX CUSTOMERS CUST_YEAR_OF_BIRTH
这样我们确认了在CUSTOMERS表的CUST_YEAR_BIRTH列上存在一个BITMAP类型的索引:CUSTOMERS_YOB_BIX
执行计划中的 BITMAP CONVERSION TO ROWIDS 和 BITMAP INDEX SINGLE VALUE 怎么解释?
BITMAP INDEX SINGLE VALUE 先通过键值筛选,然后BITMAP CONVERSION TO ROWIDS转换为rowid,再通过ROWID去回表TABLE ACCESS BY INDEX ROWID,这个过程和普通的索引扫描类似,只是多了一个BITMAP CONVERSION TO ROWIDS的操作。
因为存在位图索引,所以我们查询一下在CUSTOMERS表中CUST_YEAR_OF_BIRTH列上有多少个唯一值,通过下面的SQL语句实现:
SQL> SELECT num_distinct, 2 UTL_RAW.cast_to_number(low_value) low_value, 3 UTL_RAW.cast_to_number(high_value) high_value 4 FROM all_tab_col_statistics 5 WHERE table_name='CUSTOMERS' 6 AND column_name ='CUST_YEAR_OF_BIRTH';NUM_DISTINCT LOW_VALUE HIGH_VALUE------------ ---------- ---------- 75 1913 1990
在统计CUSTOMERS表一共有多少条记录?
SQL> select count(*) from customers; COUNT(*)---------- 55500
我们可以看到CUSTOMERS表一共有 55500条记录,而CUST_YEAR_OF_BIRTH有75个不同的唯一值,那么每个值平均出现的次数为:55500 / 75 = 740 (条记录)。
而出生在 1913年的有 5条记录。
所以Oracle的查询优化器选择了使用索引来进行数据访问。这时正确的选择。
虽然不能为什么时候使用索引提供一个通用的规则,但是一般情况下,可以这样理解:
Oracle的查询优化器如何在索引扫描和全表扫面之间选择?
影响优化器决策的因素如下:
Oracle中访问数据的直方图统计,是一个非常有用的参考。
下面我们演示一种 “坏SQL” 的写法,故意绕过索引。
SELECT MAX(cust_credit_limit), COUNT(*)FROM customersWHERE to_char(cust_year_of_birth)='1913';
这时,我们发现 产生了全表扫面的情况,即 TABLE ACCESS FULL。产生了大量的 physical reads。这时在查询中应该极力去避免的。