Oracle函數(shù)使索引列失效的解決辦法
更新時(shí)間：2021年12月18日 09:02:25 作者：Leshami
本文詳細(xì)講解了Oracle函數(shù)使索引列失效的解決辦法，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
在索引列上使用函數(shù)使得索引失效的是常見(jiàn)的索引失效原因之一，因此盡可能的避免在索引列上使用函數(shù)。盡管可以使用基于函數(shù)的索引來(lái)解決索引失效的問(wèn)題，但如此一來(lái)帶來(lái)的比如磁盤空間的占用以及列上過(guò)多的索引導(dǎo)致DML性能的下降。本文描述的是一個(gè)索引列上使用函數(shù)使其失效的案例。
一、數(shù)據(jù)版本與原始語(yǔ)句及相關(guān)信息
1.版本信息
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
?2.原始語(yǔ)句與其執(zhí)行計(jì)劃
SQL> set autotrace traceonly exp;
SELECT acc_num,
curr_cd,
DECODE('20110728',
(SELECT TO_CHAR(LAST_DAY(TO_DATE('20110728', 'YYYYMMDD')),
'YYYYMMDD')
FROM DUAL),
0,
adj_credit_int_lv1_amt + adj_credit_int_lv2_amt -
adj_debit_int_lv1_amt - adj_debit_int_lv2_amt) AS interest
FROM acc_pos_int_tbl ACC_POS_INT_TBL1
WHERE SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)
AND business_date <= '20110728';
Execution Plan
----------------------------------------------------------
Plan hash value: 3114115399
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 336K| 12M| 96399 (1)| 00:19:17 |
| 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')
從執(zhí)行計(jì)劃可以看出，SQL語(yǔ)句使用了全表掃描，而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
ACC_POS_INT_TBL PK_ACC_POS_INT_TBL ACC_NUM 1 VALID NORMAL ASC
ACC_POS_INT_TBL PK_ACC_POS_INT_TBL BUSINESS_DATE 2 VALID NORMAL ASC
從索引的情況上來(lái)看有一個(gè)基于主鍵的索引包含了BUSINESS_DATE列，而查詢語(yǔ)句并沒(méi)有走索引而是選擇的全表掃描，而且預(yù)估所返回的行Rows與bytes也是大的驚人，cost的值96399，接近10W。
二、分析與改造SQL語(yǔ)句
1.原始的SQL語(yǔ)句分析
SQL語(yǔ)句中where子句的business_date列實(shí)現(xiàn)對(duì)記錄過(guò)濾
business_date <= '20110728'條件不會(huì)限制索引的使用
SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)使用了SUBSTR函數(shù)，限制了優(yōu)化器選擇索引
基于business_date列來(lái)建立索引函數(shù)，從已存在的索引來(lái)看，必要性不大
2.改造SQL語(yǔ)句
SUBSTR(business_date, 1, 6) = SUBSTR('20110728', 1, 6)的實(shí)質(zhì)是等于當(dāng)月，即限制返回的行為從2011.7.1日至2011.7.28
因此其返回的記錄大于等于2011.7.1，且小于2011.7.28
做如下改造
business_date >=to_char(last_day(add_months(to_date('20110728','yyyymmdd'),-1)) + 1,'yyyymmdd')
3.改造后的SQL語(yǔ)句
SELECT acc_num,
curr_cd,
DECODE('20110728',
(SELECT TO_CHAR(LAST_DAY(TO_DATE('20110728', 'YYYYMMDD')),
'YYYYMMDD')
FROM DUAL),
0,
adj_credit_int_lv1_amt + adj_credit_int_lv2_amt -
adj_debit_int_lv1_amt - adj_debit_int_lv2_amt) AS interest
FROM acc_pos_int_tbl ACC_POS_INT_TBL1
WHERE business_date >=
to_char(last_day(add_months(to_date('20110728', 'yyyymmdd'), -1)) + 1,
'yyyymmdd')
AND business_date <= '20110728';
4.改造后的執(zhí)行計(jì)劃
Execution Plan
----------------------------------------------------------
Plan hash value: 66267922
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1065K| 39M| 75043 (1)| 00:15:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| ACC_POS_INT_TBL | 1065K| 39M| 75043 (1)| 00:15:01 |
|* 3 | INDEX SKIP SCAN | PK_ACC_POS_INT_TBL | 33730 | | 41180 (1)| 00:08:15 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')
filter("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')
改造后可以看到SQL語(yǔ)句的執(zhí)行計(jì)劃已經(jīng)由原來(lái)的全表掃描改為執(zhí)行INDEX SKIP SCAN，但其cost也并沒(méi)有降低多少
三、進(jìn)一步分析
1.表的相關(guān)信息
SQL> @Tab_Stat
Enter value for input_table_name: ACC_POS_INT_TBL
old 11: WHERE table_name = upper('&input_table_name')
new 11: WHERE table_name = upper('ACC_POS_INT_TBL')
Enter value for input_owner: goex_admin
old 12: AND owner = upper('&input_owner')
new 12: AND owner = upper('goex_admin')
NUM_ROWS BLKS EM_BLKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY STA
---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- ---
33659947 437206 1322 855 0 99 77 27-SEP-11 NO
2.索引的相關(guān)信息
SQL> @Idx_Stat
Enter value for input_table_name: ACC_POS_INT_TBL
old 11: WHERE table_name = upper('&input_table_name')
new 11: WHERE table_name = upper('ACC_POS_INT_TBL')
Enter value for input_owner: goex_admin
old 12: AND owner = upper('&input_owner')
new 12: AND owner = upper('goex_admin')
BLEV IDX_NAME LF_BLKS DST_KEYS NUM_ROWS LF_PER_KEY DAT_BLK_PER_KEY CLUS_FCT LST_ANLY
---- ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- ---------
3 PK_ACC_POS_INT_TBL 155658 33777720 33777720 1 1 33777447 27-SEP-11
3 ACC_POS_INT_10DIG_IDX 160247 32850596 32850596 1 1 32763921 27-SEP-11
3.嘗試在BUSINESS_DATE列上創(chuàng)建索引
SQL> create index I_ACC_POS_INT_TBL_BS_DT on ACC_POS_INT_TBL(BUSINESS_DATE) tablespace tbs_tmp nologging;
Index created.
SQL> @Idx_Stat
Enter value for input_table_name: ACC_POS_INT_TBL
old 11: WHERE table_name = upper('&input_table_name')
new 11: WHERE table_name = upper('ACC_POS_INT_TBL')
Enter value for input_owner: goex_admin
old 12: AND owner = upper('&input_owner')
new 12: AND owner = upper('goex_admin')
BLEV IDX_NAME LF_BLKS DST_KEYS NUM_ROWS LF_PER_KEY DAT_BLK_PER_KEY CLUS_FCT LST_ANLY
---- ------------------------------ ---------- ---------- ---------- ---------- --------------- ---------- ---------
2 I_ACC_POS_INT_TBL_BS_DT 93761 908 33659855 103 506 460007 30-SEP-11
3 PK_ACC_POS_INT_TBL 155658 33777720 33777720 1 1 33777447 27-SEP-11
3 ACC_POS_INT_10DIG_IDX 160247 32850596 32850596 1 1 32763921 27-SEP-11
建立索引后聚簇因子較小，差不多接近表上塊的數(shù)量
4.使用新創(chuàng)建索引后的執(zhí)行計(jì)劃
Execution Plan
----------------------------------------------------------
Plan hash value: 2183566226
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1065K| 39M| 17586 (1)| 00:03:32 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| ACC_POS_INT_TBL | 1065K| 39M| 17586 (1)| 00:03:32 |
|* 3 | INDEX RANGE SCAN | I_ACC_POS_INT_TBL_BS_DT | 1065K| | 2984 (1)| 00:00:36 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("BUSINESS_DATE">='20110701' AND "BUSINESS_DATE"<='20110728')
從上面的執(zhí)行計(jì)劃看出，SQL語(yǔ)句已經(jīng)選擇了新建的索引盡管返回的rows,bytes沒(méi)有明顯的變化，但cost已經(jīng)少了近7倍。
以上所述是小編給大家介紹的Oracle函數(shù)使索引列失效的解決辦法，希望對(duì)大家有所幫助。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
相關(guān)文章
Oracle 11g如何清理數(shù)據(jù)庫(kù)的歷史日志詳解
這篇文章主要給大家介紹了關(guān)于Oracle 11g如何清理數(shù)據(jù)庫(kù)歷史日志的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面跟著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧。2017-07-07
Oracle數(shù)據(jù)庫(kù)中l(wèi)ead和lag函數(shù)用法示例
lag與lead函數(shù)是跟偏移量相關(guān)的兩個(gè)分析函數(shù),通過(guò)這兩個(gè)函數(shù)可以在一次查詢中取出同一字段的前N行的數(shù)據(jù)(lag)和后N行的數(shù)據(jù)(lead)作為獨(dú)立的列,這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)中l(wèi)ead和lag函數(shù)用法的相關(guān)資料,需要的朋友可以參考下2024-06-06
這篇文章主要介紹了oralce和db2兼容開發(fā)注意事項(xiàng)的相關(guān)資料,這里整理了幾個(gè)重要問(wèn)題及如何解決，需要的朋友可以參考下2016-11-11
Oracle查詢用戶擁有所有系統(tǒng)權(quán)限的方法
這篇文章主要介紹了Oracle查詢用戶擁有所有系統(tǒng)權(quán)限,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友一起看看吧2024-12-12
下面小編就為大家分享一篇Oracle多庫(kù)查詢方法，具有很的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2017-12-12
Oracle時(shí)間日期操作方法小結(jié)，需要的朋友可以參考下。2010-11-11
修改計(jì)算機(jī)名或IP后Oracle10g服務(wù)無(wú)法啟動(dòng)的解決方法
修改計(jì)算機(jī)名或IP后Oracle10g無(wú)法啟動(dòng)服務(wù)即windows服務(wù)中有一項(xiàng)oracle服務(wù)啟動(dòng)不了，報(bào)錯(cuò)，下面是具體的解決方法2014-01-01
本篇文章是對(duì)oracle SQL遞歸的使用進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-05-05
windows本地安裝配置oracle客戶端完整流程（圖文版）
這篇文章主要介紹了windows本地安裝配置oracle客戶端完整流程，小編覺(jué)得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2019-09-09
ORACLE創(chuàng)建DBlink的過(guò)程及使用方法
這篇文章主要介紹了ORACLE DBlink的創(chuàng)建和使用,本文通過(guò)實(shí)例代碼給大家給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-06-06