使index失效
1. Query the data dictionary to determine whether an existing index or index
partition is usable or unusable.
For example, issue the following query (output truncated to save space):
hr@PROD> SELECT INDEX_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED
2 FROM USER_INDEXES
3 UNION ALL
4 SELECT PARTITION_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED
5 FROM USER_IND_PARTITIONS;
INDEX OR PART NAME STATUS SEG
------------------------------ -------- ---
I_EMP_ENAME N/A N/A
JHIST_EMP_ID_ST_DATE_PK VALID YES
JHIST_JOB_IX VALID YES
JHIST_EMPLOYEE_IX VALID YES
JHIST_DEPARTMENT_IX VALID YES
EMP_EMAIL_UK VALID NO
.
.
.
COUNTRY_C_ID_PK VALID YES
REG_ID_PK VALID YES
P2_I_EMP_ENAME USABLE YES
P1_I_EMP_ENAME UNUSABLE NO
22 rows selected.
The preceding output shows that only index partition p1_i_emp_ename is
unusable.
2. Make an index or index partition unusable by specifying the UNUSABLE keyword.
The following example makes index emp_email_uk unusable:
hr@PROD> ALTER INDEX emp_email_uk UNUSABLE;
Index altered.
The following example makes index partition p2_i_emp_ename unusable:
hr@PROD> ALTER INDEX i_emp_ename MODIFY PARTITION p2_i_emp_ename UNUSABLE;
Index altered.
3. Optionally, query the data dictionary to verify the status change.
For example, issue the following query (output truncated to save space):
hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS,
2 SEGMENT_CREATED
3 FROM USER_INDEXES
4 UNION ALL
5 SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS,
6 SEGMENT_CREATED
7 FROM USER_IND_PARTITIONS;
INDEX OR PARTITION NAME STATUS SEG
------------------------------ -------- ---
I_EMP_ENAME N/A N/A
JHIST_EMP_ID_ST_DATE_PK VALID YES
JHIST_JOB_IX VALID YES
JHIST_EMPLOYEE_IX VALID YES
JHIST_DEPARTMENT_IX VALID YES
EMP_EMAIL_UK UNUSABLE NO
.
.
.
COUNTRY_C_ID_PK VALID YES
REG_ID_PK VALID YES
P2_I_EMP_ENAME UNUSABLE NO
P1_I_EMP_ENAME UNUSABLE NO
22 rows selected.
A query of space consumed by the i_emp_ename and emp_email_uk segments
shows that the segments no longer exist:
hr@PROD> SELECT SEGMENT_NAME, BYTES
2 FROM USER_SEGMENTS
3 WHERE SEGMENT_NAME IN ('I_EMP_ENAME', 'EMP_EMAIL_UK');
no rows selected
note:
Because a function-based index depends upon any function it is using, it can be
invalidated when a function changes. If the function is valid, you can use an ALTER
INDEX...ENABLE statement to enable a function-based index that has been disabled.
The ALTER INDEX...DISABLE statement lets you disable the use of a function-based
index.
partition is usable or unusable.
For example, issue the following query (output truncated to save space):
hr@PROD> SELECT INDEX_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED
2 FROM USER_INDEXES
3 UNION ALL
4 SELECT PARTITION_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED
5 FROM USER_IND_PARTITIONS;
INDEX OR PART NAME STATUS SEG
------------------------------ -------- ---
I_EMP_ENAME N/A N/A
JHIST_EMP_ID_ST_DATE_PK VALID YES
JHIST_JOB_IX VALID YES
JHIST_EMPLOYEE_IX VALID YES
JHIST_DEPARTMENT_IX VALID YES
EMP_EMAIL_UK VALID NO
.
.
.
COUNTRY_C_ID_PK VALID YES
REG_ID_PK VALID YES
P2_I_EMP_ENAME USABLE YES
P1_I_EMP_ENAME UNUSABLE NO
22 rows selected.
The preceding output shows that only index partition p1_i_emp_ename is
unusable.
2. Make an index or index partition unusable by specifying the UNUSABLE keyword.
The following example makes index emp_email_uk unusable:
hr@PROD> ALTER INDEX emp_email_uk UNUSABLE;
Index altered.
The following example makes index partition p2_i_emp_ename unusable:
hr@PROD> ALTER INDEX i_emp_ename MODIFY PARTITION p2_i_emp_ename UNUSABLE;
Index altered.
3. Optionally, query the data dictionary to verify the status change.
For example, issue the following query (output truncated to save space):
hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS,
2 SEGMENT_CREATED
3 FROM USER_INDEXES
4 UNION ALL
5 SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS,
6 SEGMENT_CREATED
7 FROM USER_IND_PARTITIONS;
INDEX OR PARTITION NAME STATUS SEG
------------------------------ -------- ---
I_EMP_ENAME N/A N/A
JHIST_EMP_ID_ST_DATE_PK VALID YES
JHIST_JOB_IX VALID YES
JHIST_EMPLOYEE_IX VALID YES
JHIST_DEPARTMENT_IX VALID YES
EMP_EMAIL_UK UNUSABLE NO
.
.
.
COUNTRY_C_ID_PK VALID YES
REG_ID_PK VALID YES
P2_I_EMP_ENAME UNUSABLE NO
P1_I_EMP_ENAME UNUSABLE NO
22 rows selected.
A query of space consumed by the i_emp_ename and emp_email_uk segments
shows that the segments no longer exist:
hr@PROD> SELECT SEGMENT_NAME, BYTES
2 FROM USER_SEGMENTS
3 WHERE SEGMENT_NAME IN ('I_EMP_ENAME', 'EMP_EMAIL_UK');
no rows selected
note:
Because a function-based index depends upon any function it is using, it can be
invalidated when a function changes. If the function is valid, you can use an ALTER
INDEX...ENABLE statement to enable a function-based index that has been disabled.
The ALTER INDEX...DISABLE statement lets you disable the use of a function-based
index.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29802484/viewspace-1984443/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 如何批量使外來鍵(FK)失效
- 【INDEX】索引失效或者不可用 UNUSABLEIndex索引
- 自動重建失效index的shell指令碼Index指令碼
- spring動態註冊bean會使AOP失效?SpringBean
- transition動畫z-index層級失效問題動畫Index
- 10g可以通過命令使index unusable!Index
- sqlldr 匯入重複資料導致PK INDEX失效SQLIndex
- 【INDEX_SS】使用HINT使SQL用索引跳躍掃描(Index Skip Scan)方式快速獲取資料IndexSQL索引
- 前端除錯:記Iscroll4 疑難雜症之z-index失效前端除錯Index
- 聊聊如何修復springboot使maven-resources-plugin佔位符失效問題Spring BootMavenPlugin
- 如何使外來鍵(Foreign Key)或其他constraint失效的語句AI
- KEEP INDEX | DROP INDEXIndex
- openview失效View
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- INDEX UNIQUE SCAN,INDEX FULL SCAN和INDEX FAST FULL SCANIndexAST
- Clustered Index Scan and Clustered Index SeekIndex
- oracle hint之full,index,index_asc,index_desc,index_combile示例OracleIndex
- IndexIndex
- Index的掃描方式:index full scan/index fast full scanIndexAST
- rebuild index 和 recreate index (重新建立index) 的區別RebuildIndex
- pk 、unique index 和 index 區別Index
- global index & local index的區別Index
- alter index rebuild與index_statsIndexRebuild
- scrapy軟連線失效和pip軟連線失效
- B-index、bitmap-index、text-index使用場景詳解Index
- 面試官:聊聊索引失效?失效的原因是什麼?面試索引
- Index Full Scan vs Index Fast Full ScanIndexAST
- Using index condition Using indexIndex
- 【Oracle】global index & local index的區別OracleIndex
- Index Full Scans和Index Fast Full ScansIndexAST
- What is meant by Primary Index and Secondary IndexIndex
- Index Full Scan 與 Index Fast Full ScanIndexAST
- SpringBoot框架:兩個方法同時呼叫時父方法使內部方法的DataSource註解失效的解決辦法Spring Boot框架
- laravel 登入失效Laravel
- css失效問題CSS
- pyautogui點選失效GUI
- 索引失效場景索引
- Oracle索引失效-likeOracle索引