您好,登錄后才能下訂單哦!
如果你想要?jiǎng)?chuàng)建一個(gè)很大的索引,但并不想給它分配空間,而是要先確定優(yōu)化器是否會(huì)選擇使用該索引,那么在11g中可以使用NOSEGMENT來(lái)創(chuàng)建索引就可以先進(jìn)行測(cè)試。如果確定了這個(gè)索引是有用的,可以刪除該索引,然后使用不包含NOSEGMENT的語(yǔ)句重建它。
SQL> create index idx_emp on emp(employee_id) nosegment;
Index created.
SQL> set autot traceonly;
SQL> select * from APP.EMP t where t.employee_id='105';
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 133 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T"."EMPLOYEE_ID"=105)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1305 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autot off
SQL> ALTER SESSION SET "_use_nosegment_indexes"=true; <----session級(jí)修改隱含參數(shù)_use_nosegment_indexes為true
Session altered.
SQL> set autot traceonly;
SQL> set lines 900
SQL> select * from APP.EMP t where t.employee_id='105';
Execution Plan
----------------------------------------------------------
Plan hash value: 306890541
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 133 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."EMPLOYEE_ID"=105)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1305 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。