Oracle中怎么實現(xiàn)虛擬索引,相信很多沒有經(jīng)驗的人對此束手無策,為此本文總結(jié)了問題出現(xiàn)的原因和解決方法,通過這篇文章希望你能解決這個問題。
創(chuàng)新互聯(lián)專注于余姚網(wǎng)站建設(shè)服務(wù)及定制,我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。 熱誠為您提供余姚營銷型網(wǎng)站建設(shè),余姚網(wǎng)站制作、余姚網(wǎng)頁設(shè)計、余姚網(wǎng)站官網(wǎng)定制、重慶小程序開發(fā)公司服務(wù),打造余姚網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供余姚網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。
1.創(chuàng)建一個測試表test
SQL> create table test as select * from dba_objects; Table created.
2.從表test查詢object_name等于standard的記錄
SQL> select * from test where object_name='STANDARD'; OWNER ------------------------------ OBJECT_NAME -------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ----------- CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S ------------ ------------ ------------------- ------- - - - SYS STANDARD 888 PACKAGE 19-APR-10 19-APR-10 2003-04-18:00:00:00 VALID N N N OWNER ------------------------------ OBJECT_NAME -------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ------------------- CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S ------------ ------------ ------------------- ------- - - - SYS STANDARD 889 PACKAGE BODY 19-APR-10 19-APR-10 2010-04-19:10:22:58 VALID N N N
3.查詢上面查詢的執(zhí)行計劃
SQL> set autotrace traceonly explain SQL> select * from test where object_name='STANDARD'; Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 155 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST | 8 | 1416 | 155 (1)| 00:00:02 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='STANDARD') Note ----- - dynamic sampling used for this statement
4.在表test的object_name列上創(chuàng)建一個虛擬索引
SQL> create index test_index on test(object_name) nosegment;Index created.
為了創(chuàng)建虛擬索引必須在create index語句中指定nosegment子句,并且不會創(chuàng)建索引段。
5.來驗證虛擬索引不會創(chuàng)建索引段
SQL> set autotrace off SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_INDEX'; no rows selected SQL> col OBJECT_NAME format a20; SQL> select object_name, object_type from dba_objects where object_name = 'TEST_INDEX'; OBJECT_NAME OBJECT_TYPE -------------------- ------------------- TEST_INDEX INDEX
從上面的結(jié)果可以看到索引對象已經(jīng)創(chuàng)建,但沒有創(chuàng)建索引段。
6.重新執(zhí)行sql查看創(chuàng)建的虛擬索引是否被使用
SQL> set autotrace traceonly explainSQL> select * from test where object_name='STANDARD'; Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 155 (1)| 00:00:02 | |* 1 | TABLE ACCESS FULL| TEST | 8 | 1416 | 155 (1)| 00:00:02 | -------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_NAME"='STANDARD') Note ----- - dynamic sampling used for this statement
從上面的執(zhí)行計劃可以清楚地看到創(chuàng)建的虛擬索引并沒有被使用。
7.為了能使用所創(chuàng)建的虛擬索引,需要將_USE_NOSEGMENT_INDEXES設(shè)置為true
SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;Session altered.
8.重新執(zhí)行sql查看創(chuàng)建的虛擬索引是否被使用
SQL> set long 900SQL> set linesize 900 SQL> select * from test where object_name='STANDARD'; Execution Plan ---------------------------------------------------------- Plan hash value: 2627321457 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 1416 | 5 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_INDEX | 238 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_NAME"='STANDARD') Note ----- - dynamic sampling used for this statement
看完上述內(nèi)容,你們掌握Oracle中怎么實現(xiàn)虛擬索引的方法了嗎?如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容,歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道,感謝各位的閱讀!
網(wǎng)站欄目:Oracle中怎么實現(xiàn)虛擬索引
網(wǎng)站鏈接:http://www.muchs.cn/article26/jsoecg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供建站公司、網(wǎng)站策劃、動態(tài)網(wǎng)站、外貿(mào)建站、服務(wù)器托管、網(wǎng)站維護(hù)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)