函数使得索引列失效 - 数据库编程

1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| ACC_POS_INT_TBL | 336K| 12M| 96399 (1)| 00:19:17 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(SUBSTR("BUSINESS_DATE",1,6)='201107' AND
"BUSINESS_DATE"<='20110728')
从执行计划可以看出，SQL语句使用了全表扫描，而where 子句中只有唯一的一列business_date
3.表上的索引信息

SQL> set autotrace off;
SQL> set linesize 190
SQL> @Idx_Info
Enter value for owner: goex_admin
old 10: AND owner = upper('&owner')

new 10: AND owner = upper('goex_admin')
Enter value for table_name: ACC_POS_INT_TBL
old 11: AND a.table_name = upper('&table_name')
new 11: AND a.table_name = upper('ACC_POS_INT_TBL')

TABLE_NAME INDEX_NAME COL_NAM CL_POS STATUS IDX_TYP DSCD
------------------ ------------------------ -------------------- ------ -------- --------------- ----
ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX SYS_NC00032$ 1 VALID FUNCTION-BASED ASC
NORMAL

ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX BUSINESS_DATE 2 VALID FUNCTION-BASED ASC
NORMAL

ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX CURR_CD 3 VALID FUNCTION-BASED ASC
NORMAL

函数使得索引列失效(二)